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

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(dot)paquier(at)gmail(dot)com
> To: johnlumby(at)hotmail(dot)com
> CC: oleksandr(dot)shulgin(at)zalando(dot)de; pgsql-general(at)postgresql(dot)org
>
> On Mon, Mar 14, 2016 at 11:08 PM, John Lumby <johnlumby(at)hotmail(dot)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