Re: Failover, Wal Logging, and Multiple Spares

On 18/08/2009, at 9:36 AM, Bryan Murphy wrote:

> Ok, I've asked this a few times, but nobody ever responded. I think
> I finally got it though, could somebody confirm my logic?
> Basically, you setup a chain of servers, and when fails you
> replicate to the next link in the chain, like so:
>
> Master (A) --> Warm Standby (B) --> Warn Standby (C) --> etc.
>
> Master Fails, now becomes:
>
> Old Master (A) xxxxx> New Master (B) --> Warm Standby (C)
>
> And, of course, you might have an additional replication chain from
> Master (A) just in case you goof something up in the failover
> process, but that's the basic idea.

Excuse me, but I fail to see how you are going to replicate from one
warm standby to another warm standby. I don't think PostgreSQL can do
that. That said, the idea of just partially degrading a warm standby
cluster by electing a new master node looked very attractive to me, too.

> On Sun, Aug 16, 2009 at 9:35 PM, Bryan Murphy
> <bmurphy1976(at)gmail(dot)com> wrote:
> Assuming we are running a Postgres instance that is shipping log
> files to 2 or more warm spares, is there a way I can fail over to
> one of the spares, and have the second spare start receiving updates
> from the new master without missing a beat? I can live with losing
> the old master, and at least at the moment it would be a controlled
> failover, but I would like to to know if it's possible during an
> uncontrolled failover as well (catastrophic hardware failure).
>
> Right now, we have just that setup, but every time I've failed over
> to the new master, we've had to rebuild our spares from scratch and
> unfortunately this is a multi-hour long process. We can't afford
> the risk of not having a warm spare for that length of time. We're
> planning to move entirely to a slony cluster, but I'd like to fail
> over to a more powerful machine before we begin the slony migration
> as the current server is already overloaded.

Encouraged by Bruce Momjian, I tried and had some success in this
area. It was a controlled failover but it worked like a charm. An
obvious condition was that the warm standbys be in perfect sync; you
can't do the trick if some of them received the last WAL segment while
the others didn't.

Please see http://archives.postgresql.org/pgsql-general/2009-07/msg00215.php
for my report. Of course, questions and comments are welcome.

Cheers,
Yar