On 03/15/2016 06:54 AM, John Lumby wrote:
Thank you both for the advice.
pg_rewind is a nice utility and not only more robust than what I came up with
but also easier to use and avoids need to shut down new Primary.
Re editing the wiki, I do have a community account but it seems I need more than that :
==> Editing this wiki now requires "editor" privileges.
https://wiki.postgresql.org/wiki/WikiEditing
"Editing this wiki now requires "editor" privileges.
If you just created a new community account or if your current account
used to have "editor" privileges, you can ask on either the PostgreSQL
-www Mailinglist or the PostgreSQL IRC Channel for "editor" privileges.
Please include your community account name in those requests.
This is due, in large part, to recent spam activity. "
If anyone who has such privileges would like to edit the page,
here is what I would add to it -- feel free to edit/rewrite
after the bullet
. How to restart streaming replication after failover
and before the sub-bullet
Repeat the operations from 6th;
add this:
Starting with the old Standby now running as Unreplicated and the old Primary shut down but servicable,
with its databases intact, the task is to put the old Primary into Standby mode
as rapidly and unintrusively as possible.
This implies not requiring to shut new Primary down and not requiring to make another full base backup.
A utility named pg_rewind makes this much simpler and more robust, and it is included in standard
postgresql distribution since 9.5. - it is documented under PostgreSQL Server Applications.
To use pg_rewind :
First and most important, it is essential to have *previously* set the configuration parameter
wal_log_hints = on
in both the old Primary and old Standby, *before* the failover.
An alternative is described in the documentation but setting this parameter is simpler.
If you did not set this or the alternative, then , set it for future,
and don't use pg_rewind this time. See next.
Secondly , note that pg_rewind will potentially update *every* file in the old Primary cluster,
including configuration files. It is likely that configuration files may not match exactly on the two systems,
so make a copy of postgresql.conf and postgresql.auto.conf for later restore.
Thirdly, double-check that old Primary is shut down.
Now run pg_rewind on old Primary using the form
pg_rewind -D ${pg_cluster_dir} \
--source-server="host=${source_server_ip} port=${source_server_port} user=${replication_user} password=${replication_password}" \
-P
You can add --debug if you want a blow-by-blow account of every change it makes.
Now restore your configuration files, first perhaps comparing what differences there were.
Finally, create the recovery.conf for the new Standby
You can now start the new Standby.
There are some limitations with pg_rewind described in documentation.
If you could not use it or it failed, then treat your old Primary as an empty cluster
and commission it from the start as described next
Cheers, John Lumby
----------------------------------------
Date: Mon, 14 Mar 2016 23:46:28 +0100
Subject: Re: Re: how to switch old replication Master to new Standby after promoting old Standby
From: michael.paquier@xxxxxxxxx
To: johnlumby@xxxxxxxxxxx
CC: oleksandr.shulgin@xxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx
On Mon, Mar 14, 2016 at 11:08 PM, John Lumby <johnlumby@xxxxxxxxxxx> wrote:
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?
To make its code more simple. This way there is no need to apply any
kind of file-based filters to decide if some files should be copied or
not, and it is not that much a big deal to copy the configuration
files of the target node before performing the rewind.
Maybe a note about it should be added to the wiki
https://wiki.postgresql.org/wiki/Streaming_Replication
(not sure if I can)
With a community account you could edit this page.
--
Michael
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
