Re: Question about switchover with PG9 replication - Mailing list pgsql-general
| From | Jean-Armel Luce |
|---|---|
| Subject | Re: Question about switchover with PG9 replication |
| Date | |
| Msg-id | AANLkTimXDTGttH8uir6SUtEdw66ZFA7cpkN9tQmcqnkO@mail.gmail.com Whole thread Raw |
| In response to | Re: Question about switchover with PG9 replication (Robert Treat <rob@xzilla.net>) |
| List | pgsql-general |
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 ?
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 ?
2011/2/28 Robert Treat <rob@xzilla.net>
On Sun, Feb 27, 2011 at 3:46 AM, Cyril Scetbon <cyril.scetbon@free.fr> wrote:It's good to point that out for those that didn't pick up on that, but
> 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.
>
unfortunately for you it doesn't change the equation wrt your
scenario.Yeah, this is kind of a cop-out because in a switchover you'd be able
> 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.
>
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
pgsql-general by date: