Re: WAL shipping to two machines (PITR)

On Sat, Nov 07, 2009 at 08:10:23AM -0500, Geoffrey wrote:
> We now want to add a second PITR machine that is in a remote location.
> The question is, what is the best solution for such an effort? We've
> considered shipping the wal files to both locations, but the concern is
> that if one fails, how do we maintain the other?

Where do you put your trust in machine availability, is the local one
going to be up as much as the database and the worry about the remote
machine mainly about the reliability of the network?

If that's the case, how about just copying to the local machine and then
have it copy over to the remote one as needed. Could you just use the
following:

archive_command = 'ssh pgbackup(at)onsite archivewal %f < %p'

and make the "archivewal" script do something like the following:

#!/bin/sh
f="/var/pgbackup/$1"
[ -f "$f" ] && exit 1
set -o noclobber # just in case
cat > "$f"
scp "$f" pgbackup(at)offsite:/var/pgbackup/ \
< /dev/null >> offsitelog 2>&1 &

I.e. write stdin to the file specified and then try and scp it over to
the offsite location in the background. You could have a cron job to
mop up when the network goes down and files don't copy.

--
Sam http://samason.me.uk/