Thread: Question about switchover with PG9 replication

Hello,

I have a few questions about streaming replication.

We have a database of nearly 300 GB.
We are using Postgres 8.3.1 and Slony for replication on Ubuntu 10.04.

We have 4 Postgres servers PGMaster1, PGSlave1, PGMaster2 and PGSlave2 on 2 distant sites (distance between the 2 sites is nearly 1000 kms).

PGMaster1 and PGSlave1 are in the site S1, and PGMaster2 and PGSlave2 are in the other site S2.

Using Slony, we are replicating as following :

PGSlave1 has subscribed as a slave of PGMaster1 (local replication in S1)
PGMaster2 has subscribed as a slave of PGMaster1 (distant replication from S1 to S2)
PGSlave2 has subscribed as a slave of PGMaster2 (local replication in S2; this is a cascaded replication)

All the SQL command UPDATE, INSERT and DELETE are sent to PGMaster1 and the data modifications are then propagated to the other servers by Slony.
Read are load-balanced between the 4 servers

We often need to do switchovers between PGMaster1 and PGMaster2 (for maintenance operations, upgrade, ...).

We would like to upgrade to Postgres 9, and use the streaming replication of PG9

Question 1 : is it possible to have such a replication configuration with the streaming replication of PG9 (cascaded replication) ?

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 ?

Thanks

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.

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.

On Mon, Feb 07, 2011 at 09:20:36AM +0100, Jean-Armel Luce wrote:

> 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 ?

You can't do database replication without copying the whole database, no.

A


--
Andrew Sullivan
ajs@crankycanuck.ca

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.

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.

-- 
Cyril SCETBON

On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon <cyril.scetbon@free.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

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_capability at 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 ?