Re: Simplifying replication

On Wed, Oct 27, 2010 at 3:53 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Tue, 2010-10-26 at 22:03 -0400, Robert Haas wrote:
>> On Tue, Oct 26, 2010 at 9:59 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>> >
>> >> If you set wal_keep_segments=0, archive_mode=on, and
>> >> archive_command=<something>, you might run out of disk space.
>> >>
>> >> If you set wal_keep_segments=-1, you might run out of disk space.
>> >>
>> >> Are you any more screwed in the second case than you are in the first
>> >> case?
>> >
>> > It is the same to the user either way.  In either case you have to
>> > change some settings and restart the master.
>>
>> Except that changing wal_keep_segments doesn't require restarting the master.
>>
>> The point of allowing -1 was to allow someone to set it to that value
>> temporarily, to be able to do a hot backup without having to guess how
>> large to set it.  If you don't have enough disk space for a backup to
>> complete, you're kind of hosed either way.
>
> You're not hosed either way. Fujii designed this carefully to avoid that
> and it works. The case of archive_command failing isn't comparable
> because that is a failure case, not a normal working server.
>
> You don't need to guess the setting of wal_keep_segments. It's a safety
> net that has been deliberately created to avoid the crash that would
> otherwise happen. I've not heard a better proposal, yet, though I too am
> hopeful there is a better one.

I think you might be confused about what the use case Bruce and I are
imagining, because this doesn't make any sense at all in that context.
The specific use case is that you have archive_mode=off,
wal_level=archive or wal_level=hot_standby, and you want to take a hot
backup. If you do pg_start_backup(), copy the data directory, and do
pg_stop_backup(), you won't necessarily end up with enough xlog to
reach a consistent state. To do that, you must copy all the WAL files
that exist after pg_stop_backup() has completed, but you may not
actually be able to do that, because they might get recycled before
you can copy them. You can fix this by temporarily increasing
max_wal_segments to a sufficiently large value, and then dropping it
back down to whatever you like after you've copied the files out of
pg_xlog. If -1 isn't an option, you can always use a million, which
is almost certainly large enough to be safe. But what you definitely
DON'T want to do is temporarily set wal_keep_segments to a value like
7, because if you should manage to go through 8 segments while running
the backup, you may find that you now have an unusable backup. And
you may not realize this until you try to restore it.

I wonder if we should document this procedure. I see that it is not
in the docs at present, and it might be useful to somebody.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company