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.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
