Re: how to switch old replication Master to new Standby after promoting old Standby

On Mon, Mar 14, 2016 at 6:28 PM, John Lumby <johnlumby(at)hotmail(dot)com> wrote:

>
> From: johnlumby(at)hotmail(dot)com
> To: pgsql-general-owner(at)postgresql(dot)org
> Subject: how to switch old replication Master to new Standby after
> promoting old Standby
> Date: Mon, 14 Mar 2016 13:23:29 -0400
>
> In a scenario involving replication where no failure occurs but I want to
> interchange Master <->Standby,
> *and* want to avoid making another full base backup and rsyncing it across,
> I have found it easy to promote old Standby to new Master with pg_ctl
> promote,
> but very difficult to restart the old Primary as a new Standby *without*
> performing new base backup.
>
> Assume current 9.5.1 and using streaming replication with a named
> replication slot if relevant
>
> Second assumption - I am able to temporarily prevent any relational
> updates to the database before I start the switchover
> e.g. the
> default_transaction_read_only = on
> setting together with being able to control what transactions do if
> anything
>
> So I reach a point where both systems have postgresql running without any
> replication,
> both have identical content in all databases (that I can control),
> and I am willing to tolerate short restarts if need be and also to
> scp/rsync the contents of pg_xlog
> and other small files but *not* the entire cluster directory or any
> database base directories.
>
> What do I do next?
>
> Here is what I have found seems to work but I am not sure it is robust:
>
> 1. shut down both new Master and intended-to-be-new-Standby
> 2. on intended-to-be-new-Standby, remove the entire content of pg_xlog and
> the global/pg_control
> 3. from new Master , tar + scp the entire content of pg_xlog and the
> global/pg_control to intended-to-be-new-Standby
>

That does seem like a very risky strategy to me. Have you taken a look at
pg_rewind (which is now part of the distribution)?

--
Alex