Re: Using pg_start_backup() and pg_stop_backup()

On Wed, Jul 17, 2013 at 8:24 AM, David B Harris <dbharris(at)eelf(dot)ddts(dot)net> wrote:
> On Wed Jul 17, 08:12am +0900, Michael Paquier wrote:
>> On Tue, Jul 16, 2013 at 11:10 PM, David B Harris <dbharris(at)eelf(dot)ddts(dot)net> wrote:
>> > 4. Copy all logs from start of pg_start_backup() through to when
>> > pg_stop_backup() finished (using the backup history file, I
>> > guess, which I haven't actually been able to find yet :)
>> I assume that you use WAL archiving, so why would you do that manually
>> as I understand it is the case at step #4? When using pg_stop_backup
>> it is even ensured that all the necessary WAL files are ready to be
>> archived. You still need for the file to be actually archived before
>> starting the recovery though
>
> I'm not using WAL archiving actually (these installations [there are a
> few of them] have never used log-shipping). Not yet anyway. Even if I
> were though, a step #4 would still be necessary I believe - if I read
> the documentation right, the last log segment that's supposed to be
> transferred isn't ready until after pg_stop_backup().
Yep.

>> > Is #4 actually necessary? (I can
>> > imagine ways of writing to the cluster files which might make it
>> > unnecessary, maybe somebody implemented that and didn't update the
>> > documentation?)
>>
>> Not really, archiving would do the rest for you, and it is managed by
>> the server. Just be sure to set up restore_command in recovery.conf
>> when starting a node from the backup you took.
>
> Yeah, so if I understand right, you're using WAL archiving for step #4,
> and your process looks like this:
>
> 1. Issue pg_start_backup('label')
> 2. Perform rsync of cluster directory
> 3. Issue pg_stop_backup()
> 4. In parallel, WAL archiving has copied all the logs from the
> start of pg_start_backup() through to the end of pg_stop_backup()
Yes exactly. This avoids to have to maintain your own scripts and rely
on the server features... What do you actually do to copy the
necessary WAL files. Do you fetch them directly from the master's
pg_xlog folder?

> A configuration like this is provided like this in the documentation
> (again section 24.3), though some assembly is required. I'm hoping to
> avoid it since it'll be introducing a second channel to the backup which
> I can almost guarantee will be forgotten in time. (BTW, we tend to
> consider the lifespan of our installations in terms of decades.)
>
> I mean, if it's necessary, the so be it of course, but the question
> still remains: I've tested a fair bit under fairly adverse conditions
> and not had a single failure. Luck?
It looks so, and wal_keep_segments is set to a value high enough on
the master side so as all the necessary WAL files are kept intact
somewhere even if they are not archived.
--
Michael