Re: [GENERAL] warm standby possible with 8.1?

[Yar Tykhiy]

On Tue, Mar 09, 2010 at 05:48:41PM -0500, Greg Smith wrote:
> zhong ming wu wrote:
> >Is it possible to have a warm standby with 8.1?
> 
> No.  You can set that up so that it replays an entire pile of log
> files sitting there when you start the server, which it sounds like
> you haven't managed yet because you're trying to treat it like a
> warm-standby.  But 8.1 isn't capable of applying log files one at a
> time; it applies whatever you've got, and then it's done with
> recovery and transitions to live.  You can't just stop the result
> and then feed it the next file, as you've already discovered through
> experimentation.

Guys, I'm afraid there may be some confusion here.  I've got a warm
standby happily running with simple home-made archive and restore
scripts on a legacy Postgresql installation as old as 8.0.  And yes, I
did failover multiple times (I posted a report or two on that to this
list.)

What Zhong isn't going to get is converting the master node to a warm
standby node as easily as by just stopping it and renaming recovery.done
to recovery.conf.  The way to go here is to take a file-level DB backup
from the master node and bootstrap a new warm standby node from it, then
let it catch up with the master node WAL-wise.

Yar

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Warm Standby Setup Documentation

[Yar Tykhiy]

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 
> postg...@192.168.x.x:/usr/local/pgsql/walfiles/%f  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.

Yar

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general