Re: Question about switchover with PG9 replication

Hello,

Thanks for all your answers.

I have read that the cascaded replication will be probably available in
future releases (cf.
http://wiki.postgresql.org/wiki/Streaming_Replication#Synchronization_capabilityat
the bottom of the page)

As I understand the streaming replication, we are able to have multiple
standbys (and only one master of course), and in case of a failover happens
(or in case of a switchover is required), one of the standbys will be
promoted as the new master.

With asynchronous replication, I understand that the DBA is able to choose
which standby will become the new master (using the trigger file), and must
apply the procedure of switchover for streaming replication as described in
chapter "How to use" in
http://wiki.postgresql.org/wiki/Streaming_Replication#Synchronization_capability,
including the copy of a backup from the new master to the other standby's
(step 6 in the procedure of switchover). Is it right ?

With synchronous replication (PG9.1), I understand that, during a failover
or a switchover, the server which will be promoted as the new master must be
the standby which has been sync'ed the most recently, or the one which has
been set with the highest priority in the configuration file. Is it right ?

For future releases, I read that :
- a new feature will allow transfers of base backup via the direct
connection between the primary and the standby.
- a new feature will provide the capability to check the progress and gap of
streaming replication via one query. A collaboration of HS and SR is
necessary to provide that capability on the standby side.

Does that mean that the process for restarting the replication after a
switchower or a failover will still require to copy a backup from the new
master to the other standbys, for synchronous replication and for
asynchronous replication ? Or can we expect that we shall be able to restart
the replication after a switchover by setting the location of the xlog of
the new master on the old master (and other standby's), so there is a
possibility that copy of backup from the new master to standby will not be
required ?

2011/2/28 Robert Treat <rob(at)xzilla(dot)net>

> On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon <cyril(dot)scetbon(at)free(dot)fr>
> wrote:
> > Le 07/02/2011 09:57, Wouter D'Haeseleer a écrit :
> >
> > Question 1 : is it possible to have such a replication configuration with
> > the streaming replication of PG9 (cascaded replication) ?
> >
> > Nope, as far as I have tested pg only has 1 master and can have a number
> of
> > slaves, so having 2 masters is not possible.
> >
> > The second host named master2 is a slave which has slaves too, not a
> master
> > on which you can write.
> >
>
> It's good to point that out for those that didn't pick up on that, but
> unfortunately for you it doesn't change the equation wrt your
> scenario.
>
> > Question 2 : All the procedures I have seen describing a switchover
> between
> > 2 PG servers require to copy (or rsync) the database from the new master
> > (old slave) to the new slave (old master).
> > Is it possible to do switchover between sites (between PGMaster1 and
> > PGMaster2) whithout copying all the database from the new PG master to
> the
> > new PG slave ?
> > If it is not possible yet, shall it be possible in future releases ?
> >
> > Nope this is not possible, pg requires to have an updates basebackup at
> the
> > slave, this is because if you loose a lot of streamed wal files it is
> > impossible for the slave to catch up and have consistent data.
> >
>
> Yeah, this is kind of a cop-out because in a switchover you'd be able
> to garauntee no loss of wal (stream or file based). The issue is more
> just that the built in replication system isn't very mature yet. It's
> being worked on, and switchover is something on the list, but it's not
> an option yet.
>
> Robert Treat
> play: xzilla.net
> work: omniti.com
> hiring: l42.org/Lg
>