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.