Thread: WAL shipping to two machines (PITR)
We currently have a PITR solution in place that is facilitated via WAL shipment. This is implemented on 13 databases, where the two primary machines which contain the production databases and the PITR machine are physically located in the same facility. 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? We've considered rsync the shipped wal files, to the remote machine, but that entails some delicate timing issues. Any suggestions, pointers would be greatly appreciated. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Geoffrey wrote: > We currently have a PITR solution in place that is facilitated via WAL > shipment. This is implemented on 13 databases, where the two primary > machines which contain the production databases and the PITR machine are > physically located in the same facility. > > 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? > > We've considered rsync the shipped wal files, to the remote machine, but > that entails some delicate timing issues. > > Any suggestions, pointers would be greatly appreciated. Bad taste to respond to my own post, oh well. So, I'm thinking the proper approach to this is to set pg_standby on one PITR to not remove archived WAL files and set the second PITR machine to remove them. Obviously, there would be a problem if the second PITR machine were to remove an archive before it was processed by the first, but the second machine would be the remote machine, so the possibility of this happening should be low. On the other hand, there is the issue of the first PITR machine being shutdown for any period of time, thus losing an archive file. Again, any thoughts on such an approach would be greatly appreciated. I've googled this thing, but no joy, but could be poor google foo. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
Geoffrey wrote: > Geoffrey wrote: >> We currently have a PITR solution in place that is facilitated via WAL >> shipment. This is implemented on 13 databases, where the two primary >> machines which contain the production databases and the PITR machine >> are physically located in the same facility. >> >> 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? >> >> We've considered rsync the shipped wal files, to the remote machine, >> but that entails some delicate timing issues. >> >> Any suggestions, pointers would be greatly appreciated. > > Bad taste to respond to my own post, oh well. > > So, I'm thinking the proper approach to this is to set pg_standby on one > PITR to not remove archived WAL files and set the second PITR machine to > remove them. Obviously, there would be a problem if the second PITR > machine were to remove an archive before it was processed by the first, > but the second machine would be the remote machine, so the possibility > of this happening should be low. On the other hand, there is the issue > of the first PITR machine being shutdown for any period of time, thus > losing an archive file. > > Again, any thoughts on such an approach would be greatly appreciated. > I've googled this thing, but no joy, but could be poor google foo. I should RTFM before posting. It appears that this parameter to pg_standby (-k) is deprecated, so is likely not a good approach to this problem. Again, any pointers to working solutions would be greatly appreciated. -- Until later, Geoffrey Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. - Benjamin Franklin
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@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@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/
We are using two slaves this way: - Active server moves WAL files to local directory, doesn't care about slaves (basically archive_command = 'mv %p /some/arch_dir/%f') - Slaves pull WAL files via rsync - Slaves also do full syncs every several hours (pg_start_backup / rsync / pg_stop_backup) - Active server also has "cleanup daemon": removes redundant archived WALs based on last full backup time The slaves are not warm, however they periodically create LVM snapshots and pg_dump databases.