Re: [GENERAL] 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 r...@xzilla.net On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon cyril.scet...@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
Re: [GENERAL] Question about switchover with PG9 replication
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
Re: [GENERAL] Question about switchover with PG9 replication
On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon cyril.scet...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] Question about switchover with PG9 replication
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.
Re: [GENERAL] Question about switchover with PG9 replication
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 a...@crankycanuck.ca -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
