Re: Streaming replication failover/failback

On Nov 16, 2016, at 11:39 PM, Jehan-Guillaume de Rorthais <ioguix(at)free(dot)fr> wrote:
>
> On Wed, 16 Nov 2016 15:51:26 -0900
> Israel Brewster <israel(at)ravnalaska(dot)net <mailto:israel(at)ravnalaska(dot)net>> wrote:
>
>> I've been playing around with streaming replication, and discovered that the
>> following series of steps *appears* to work without complaint:
>>
>> - Start with master on server A, slave on server B, replicating via streaming
>> replication with replication slots.
>> - Shut down master on A
>> - Promote slave on B to master
>> - Create recovery.conf on A pointing to B
>> - Start (as slave) on A, streaming from B
>>
>> After those steps, A comes up as a streaming replica of B, and works as
>> expected. In my testing I can go back and forth between the two servers all
>> day using the above steps.
>>
>> My understanding from my initial research, however, is that this shouldn't be
>> possible - I should need to perform a new basebackup from B to A after
>> promoting B to master before I can restart A as a slave. Is the observed
>> behavior then just a "lucky fluke" that I shouldn't rely on?
>
> No, it's not a "lucky fluke".
>
> See
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459 <https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=985bd7d49726c9f178558491d31a570d47340459>
>
> The only thing you should really pay attention is that the standby was in
> Streaming Rep when you instructed the master to shut down, and that it stays
> connected until the full stop of the master.
>
> If you really want to check everything, use pg_xlogdump on the standby and make
> sure the standby received the "shutdown checkpoint" from the master and wrote
> it in its WAL.
>
>> Or is it expected behavior and my understanding about the need for a new
>> basebackup is simply off?
>
> This is expected, but taking a new basebackup was a requirement for some time.
>
>> Does the new pg_rewind feature of 9.5 change things? If so, how?
>
> pg_rewind helps if your standby was not connected when you lost/stopped your
> master. It reverts the last transactions the master received and that was not
> streamed to the promoted standby.

Ah, ok. So kinda an emergency recovery tool then? One step before resorting to backups? In any case, it sounds like it's not something I should need in a *normal* failover scenario, where the master goes down and the slave gets promoted.

Thanks for the information!
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------
>
> Regards,
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general <http://www.postgresql.org/mailpref/pgsql-general>