Re: Streaming replication failover/failback

> On Nov 16, 2016, at 4:24 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 11/16/2016 04:51 PM, Israel Brewster 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
>
> You don't say how active the database is, but I going to say it is not active enough for the WAL files on B to go out for scope for A in the time it takes you to do the switch over.

Yeah, not very - this was just in testing, so essentially no activity. So between your response and the one from Jehan-Guillaume de Rorthais, what I'm hearing is that my information about the basebackup being needed was obsoleted with the patch he linked to, and as long as I do a clean shutdown of the master, and don't do too much activity on the *new* master before bringing the old master up as a slave (such that WAL files are lost), then the above failover/failback procedure is perfectly fine to rely on in production - I don't have to worry about there being any hidden gotchas like the new slave not *really* replicating or something.

Thanks!
-----------------------------------------------
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
-----------------------------------------------

>
>> on? Or is it expected behavior and my understanding about the need for a
>> new basebackup is simply off? Does the new pg_rewind feature of 9.5
>> change things? If so, how?
>>
>> Thanks for your time!
>> -----------------------------------------------
>> Israel Brewster
>> Systems Analyst II
>> Ravn Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7293
>> -----------------------------------------------
>>
>>
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
> --
> 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>