Re: clarification about standby promotion

On Thu, 9 Feb 2017 18:27:30 +0000
Rakesh Kumar <rakeshkumar464(at)outlook(dot)com> wrote:

> >Sure, but when you are doing a switchover, the standby is supposed to be
> >connected to the master when you shutdown the master. So based on the doc,
> >the standby should receive **everything** from the master before the master
> >actually shutdown.
>
> We use 9.5 and even in that version there is no handshake during role
> reversal. In fact PG does not have concept of handshake and role reversal
> unlike in Db2, oracle and sqlserver you can switchover from one to other by a
> single command.
>
> Our DBAs use home grown script for switchover which does the following:
>
> 1 - first kill postmaster in the outgoing primary.

Kill ? You mean "pg_ctl stop -m fast" right ?

> 2 - promote the standby as the new primary
> 3 - use timeline to resync former primary (of step 1) with the new primary
> (step 2).

Use timeline to resync ? Timeline is an internal mechanism in PostgreSQL, not
a tool, so I don't get this step...You mean using pg_rewind ?

So far, I stick to my procedure (given in another answer) which looks a lot
more safer.

> I hope a more elegant way exists as in other RDBMS.

Me too. But it require a lot of work as a master is not able to "demote" as a
standby without a restart. As far as I know, the standby code path is only
accessible during startup.

Note that you could switchover in one command as well using external tools like
PAF [1][2]. But PAF comes with a lot more features than just switchover and
rely on Pacemaker...

[1] https://github.com/dalibo/PAF
[2] http://www.dalibo.org/_media/2016-pgconfeu-paf.html.gz

Cheers,