Re: [GENERAL] Multiple Slave Failover with PITR

On Sun, Sep 2, 2012 at 4:12 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Do we ever want to document a way to connect slaves to a new master,
> rather than recreating the slave?

I have written an instruction for myself and I am planning to publish
it on http://code.google.com/p/pgcookbook/. See the attachment.

Hope you will find it useful. If anybody would like to provide any
criticism I will highly appreciate it.

>
> ---------------------------------------------------------------------------
>
> 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. +
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Sergey Konoplev

a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +79160686204