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

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(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
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com