Re: Using pg_start_backup() and pg_stop_backup()

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().

> > 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()

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?

--
Arguing with an engineer is like wrestling with a pig in mud.
After a while, you realise the pig is enjoying it.

OpenPGP v4 key ID: 4096R/59DDCB9F
Fingerprint: CC53 F124 35C0 7BC2 58FE 7A3C 157D DFD9 59DD CB9F
Retrieve from subkeys.pgp.net