Re: Re: how to switch old replication Master to new Standby after promoting old Standby - Mailing list pgsql-general

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