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: [GENERAL] Re: how to switch old replication Master to new Standby
after promoting old Standby
From: michael.paqu...@gmail.com
To: johnlu...@hotmail.com
CC: oleksandr.shul...@zalando.de; pgsql-general@postgresql.org
On Mon, Mar 14, 2016 at 11:08 PM, John Lumby <johnlu...@hotmail.com> 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.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
