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 around this problem yet. So, when you fail over, there is a window where you have no backups while you're building the new spares. This can be pretty nerve wracking if your database is like ours and it takes 3-6 hours to bring a new spare online from scratch.
>>>
>>> If there's another server around, you can have your archive_command on the master ship to two systems, then use the second one as a way to jump-start this whole process. After fail-over, just start shipping from the new primary to that 3rd server, now the replacement standby, and sync any files it doesn't have. Then switch it into recovery. Much faster than doing 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(at)192(dot)168(dot)x(dot)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(at)192(dot)168(dot)1(dot)26:/usr/local/pgsql/walfiles/$2
rsync -a $1 postgres(at)192(dot)168(dot)1(dot)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