Re: Warm Standby Setup Documentation

On Mar 28, 2010, at 7:45 PM, Yar Tykhiy wrote:

> On Fri, Mar 26, 2010 at 01:35:43PM -0500, Ogden wrote:
>> On Mar 26, 2010, at 1:32 PM, Greg Smith wrote:
>>
>>> Bryan Murphy wrote:
>>>> The one thing you should be aware of is that when you fail over, your spare has no spares.  I have not found a way
aroundthis problem yet.  So, when you fail over, there is a window where you have no backups while you're building the
newspares.  This can be pretty nerve wracking if your database is like ours and it takes 3-6 hours to bring a new spare
onlinefrom scratch. 
>>>
>>> If there's another server around, you can have your archive_command on the master ship to two systems, then use the
secondone as a way to jump-start this whole process.  After fail-over, just start shipping from the new primary to that
3rdserver, now the replacement standby, and sync any files it doesn't have.  Then switch it into recovery.  Much faster
thandoing a new base backup from the standby on larger systems. 
>>
>> How is it possible to use the archive_command to ship to different ones?
>>
>> archive_command = 'rsync -a %p postgres@192.168.x.x:/usr/local/pgsql/walfiles/%f </dev/null'
>> archive_timeout = 120         # force a logfile segment switch after this
>>
>> I suppose you can put multiple commands there then?
>
> You can always wrap as many commands as you like in a script.
> However, there is a pitfall to watch out for when shipping WALs to
> multiple standby servers.  Namely your script has to handle failures
> of individual WAL shipping targets so that a single target going down
> doesn't disrupt operation of the whole cluster.  Please see
> http://archives.postgresql.org/pgsql-general/2009-10/msg00590.php
> for discussion.


Is it as simple as doing this:

archive_command = '/var/lib/pgsql/data/warm_standby.sh %p %f  </dev/null'

Where /var/lib/pgsql/data/warm_standby.sh  is:

#!/bin/sh

rsync -a $1 postgres@192.168.1.26:/usr/local/pgsql/walfiles/$2
rsync -a $1 postgres@192.168.1.27:/usr/local/pgsql/walfiles/$2
...

For each warm standby "slave"?

Is it safe to do it this way? I wish there were some scripts out there that I can see as examples.

Thank you

Ogden