Re: PG 9.0 EBS Snapshot Backups on Slave

On Mon, Jan 23, 2012 at 8:02 PM, Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
> On Monday, January 23, 2012 07:54:16 PM Andrew Hannon wrote:
>> It is worth noting that, the slave (seemingly) catches up eventually,
>> recovering later log files with streaming replication current. Can I trust
>> this state?
>>
>
> Should be able to. The master will also actually retry the logs and eventually
> ship them all too, in my experience.
>

Right, as long as the failure case is temporary, the master should
retry, and things should work themselves out. It's good to have some
level of monitoring in place for such operations to make sure replay
doesn't get stalled.

That said, have you tested this backup? I'm a little concerned you'll
have ended up with something unusable because you aren't starting xlog
files that are going on during the snapshot time. It's possible that
you won't need them in most cases (we have a script called
"zbackup"[1] which does similar motions using zfs, though on zfs the
snapshot really is instantaneous, in I can't remember a time when we
got stuck by that, but that might just be faulty memory. A better
approach would probably be to take the omnipitr code [2], which
already had provisions for slaves from backups and catching the
appropriate wal files, and rewrite the rsync bits to use snapshots
instead, which would give you some assurances against possibly missing
files.

[1] this script is old and crufty, but provides a good example:
http://labs.omniti.com/labs/pgtreats/browser/trunk/tools/zbackup.sh

[2] https://github.com/omniti-labs/omnipitr

Robert Treat
conjecture: xzilla.net
consulting: omniti.com