Many thanks Alex, I had not noticed pg_rewind before. The name pg_rewind is not exactly very descriptive of what this utility does. But it is exactly what I need, thank-you. I tried it an eventually got it to work but did hit one strange problem - I ran it like this pg_rewind -D /mnt/bluebild/pgrepl95 --source-server=host=10.19.0.21 port=5432 user=postgres password=postgres -P --debug after which my postgresql server would not start and I discovered that the rewind had actually copied the source's postgresql.conf, replacing the one on the target (i.e. the old primary). And indeed in its debug I found received chunk for file "postgresql.conf", offset 0, size 16482 received chunk for file "postgresql.conf.20160314114055", offset 0, size 16464 And I now see in its description in the Doc that it intends to do this. But why would it do that? Maybe a note about it should be added to the wiki https://wiki.postgresql.org/wiki/Streaming_Replication (not sure if I can) Cheers, and thanks, John ________________________________ > From: oleksandr.shulgin@xxxxxxxxxx > Date: Mon, 14 Mar 2016 18:54:11 +0100 > Subject: Re: how to switch old replication Master to new > Standby after promoting old Standby > To: johnlumby@xxxxxxxxxxx > CC: pgsql-general@xxxxxxxxxxxxxx > > On Mon, Mar 14, 2016 at 6:28 PM, John Lumby > <johnlumby@xxxxxxxxxxx<mailto:johnlumby@xxxxxxxxxxx>> wrote: > > From: johnlumby@xxxxxxxxxxx<mailto:johnlumby@xxxxxxxxxxx> > To: > pgsql-general-owner@xxxxxxxxxxxxxx<mailto:pgsql-general-owner@xxxxxxxxxxxxxx> > 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 > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
