To: Simon Riggs <simon@2ndQuadrant.com>
Cc: Ray Stell <stellr@cns.vt.edu>,  A J <s5aly@yahoo.com>,
	Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: Switchover of Master and Slave roles
References: <693391.31715.qm@web120020.mail.ne1.yahoo.com>
	<20110607181244.GA3861@cns.vt.edu>
	<264025.13168.qm@web120005.mail.ne1.yahoo.com>
	<4DEE2B49020000250003E2A2@gw.wicourts.gov>
	<349220.32155.qm@web120008.mail.ne1.yahoo.com>
	<20110607194139.GB3861@cns.vt.edu>
	<BANLkTinEcZbDJjqr_cV-ku5z8PUFgKnPRQ@mail.gmail.com>
From: Jerry Sievers <gsievers19@comcast.net>
Date: Tue, 07 Jun 2011 20:53:45 -0400
In-Reply-To: <BANLkTinEcZbDJjqr_cV-ku5z8PUFgKnPRQ@mail.gmail.com> (Simon
	Riggs's message of "Tue\, 7 Jun 2011 20\:46\:47 +0100")
Message-ID: <87k4cx9m92.fsf@comcast.net>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Simon Riggs <simon@2ndQuadrant.com> writes:

> On Tue, Jun 7, 2011 at 8:41 PM, Ray Stell <stellr@cns.vt.edu> wrote:
>
>> On Tue, Jun 07, 2011 at 11:59:07AM -0700, A J wrote:
>>> We then rely on filesystem tools (or other third party tools) to get the
>>> original master in sync with the new master efficiently and then make it join as
>>> slave.
>>
>> the doc would not be corrupted by an additional few words that stated as such:
>>
>> "To return to normal operation, a standby server must be recreated using
>> external tools such as rsync, either on the former primary system when
>> it comes up, or on a third, possibly new, system."
>
> You need repmgr: http://www.repmgr.org/

Well, I am not sure on the 9.x versions but, we used to do reversible
master/standby with log shipping on the 8.x versions quite routinely,
sometimes reshaping clusters of multiple standbys., designating a new
master, restarting the old master in recovery mode and repointing the
remaining standbys..

The procedure was similar to...

apps down and locked out
pg_rotate_xlog() on master
verify final log received on standbys and consumed
bring up new master
kill immediate on old master and remaining standbys
configure old master as standby w/recovery.conf and point to new xlog
    repository
repoint other standbys to new repository and restart

There was no need to reinitialize from scratch or rsync the old master
or other standbys.

Somewhere between 8.2 and 8.4 there was a change that would require
you to increment the recovery target timeline during this operation.
It took me a bit of head scratching to get it right.

This capability was apparently little known and used, perhaps due to
uncertainty that it was viable, given the undocumented nature of
this. 

Disclaimer: I do not know if this still works on 9.x using either log
shipping or streaming replication.

I covered this strategy briefly during my talk at the 2010 Pg-East
conference in Philly.

YMMV


> -- 
>  Simon Riggs                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: gsievers19@comcast.net
p: 305.321.1144