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

From: johnlumby@hotmail.com
To: pgsql-general-owner@postgresql.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