Re: [GENERAL] Multiple Slave Failover with PITR

Do we ever want to document a way to connect slaves to a new master,
rather than recreating the slave?

---------------------------------------------------------------------------

On Tue, Mar 27, 2012 at 10:47:48AM -0700, Ken Brush wrote:
> Hello everyone,
>
> I notice that the documentation at:
> http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial
>
> Doesn't contain steps in a Multiple Slave setup for re-establishing
> them after a slave has become the new master.
>
> Based on the documentation, here are the most fail-proof steps I came up with:
>
> 1. Master dies :(
> 2. Touch the trigger file on the most caught up slave.
> 3. Slave is now the new master :)
> 4. use pg_basebackup or other binary replication trick (rsync, tar
> over ssh, etc...) to bring the other slaves up to speed with the new
> master.
> 5. start the other slaves pointing to the new master.
>
> But, that can take time (about 1-2 hours) with my medium sized DB
> (580GB currently).
>
> After testing a few different ideas that I gleaned from posts on the
> mail list, I came up with this alternative method:
>
> 1. Master dies :(
> 2. Touch the trigger file on the most caught up slave
> 3. Slave is now the new master.
> 4. On the other slaves do the following:
> 5. Shutdown postgres on the slave
> 6. Delete every file in /data/pgsql/data/pg_xlog
> 7. Modify the recovery.conf file to point to the new master and
> include the line "recovery_target_timeline='latest'"
> 8. Copy the history file from the new master to the slave (it's the
> most recent #.history file in the xlog directory)
> 9. Startup postgres on the slave and watch it sync up to the new
> master (about 1-5 minutes usually)
>
> My question is this. Is the alternative method adequate? I tested it a
> bit and couldn't find any problems with data loss or inconsistency.
>
> I still use the fail-proof method above to re-incorporate the old
> master as a new slave.
>
> Sincerely,
> -Ken
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +