Re: WAL shipping to two machines (PITR)

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