Re: [GENERAL] clarification about standby promotion - Mailing list pgsql-general

On Thu, 9 Feb 2017 10:41:15 +1100
Venkata B Nagothi <nag1010@gmail.com> wrote:

> On Thu, Feb 9, 2017 at 4:53 AM, Benoit Lobréau <benoit.lobreau@gmail.com>
> wrote:
>
> > Hi,
> >
> >
> > I would like to clarify something about standby promotion. From the
> > sentence below. I understand that, during the promotion process, postgres
> > will replay all the available wals (from the archive or pg_xlog).
> >
>
> Yes, that is correct.
>
>
> > https://www.postgresql.org/docs/9.5/static/warm-standby.
> > html#STREAMING-REPLICATION
> >
> > 25.2.2. Standby Server Operation
> > ...
> > Standby mode is exited and the server switches to normal operation when
> > pg_ctl promote is run or a trigger file is found (trigger_file). Before
> > failover, any WAL immediately available in the archive or in pg_xlog will
> > be restored, but no attempt is made to connect to the master.
> >
> > I have seen several articles like this one (https://www.enterprisedb.com/
> > switchoverswitchback-postgresql-93) where they say that
> > pg_last_xlog_receive_location() and pg_last_xlog_replay_location() should
> > be checked before promotion. I don't understand why since they will be
> > replayed anyway. Did something changed since 9.3 about this ?
> >
>
> The above link is about improvements related to switch-over/switch-back
> process from the version 9.3. What you are asking is about standby
> promotion process. When the standby is promoted, as mentioned in the docs,
> the standby server attempts to apply the available WAL during the promotion
> process and will not attempt to connect to master.


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.


> Which means, you would not know if there are any pending WALs yet to be
> streamed from master or in other words, standby may not know if the master is
> a-head. It is important that you know the standby current position by
> executing the above mentioned *xlog* functions.

Sure, but in the link pointed by Benoit, the check is only comparing what the
**standby** received with what the standby **replayed**. It seems there's no
point to do such check.

What you are describing is to check the very last LSN from the master (its
shutdown checkpoint) with what the slave received. The only way to check this
is to compare LSN from the shut down master to the LSN the slave received.

in PAF[1], this check is:

  * shut down the master
  * use pg_controldata to find its shutdown checkpoint
  * force a checkpoint on the standby (which in theory received everything
    from the master by streaming)
  * using pg_dumpxlog, check you received the shutdown checkpoint


[1] https://github.com/dalibo/PAF

--
Jehan-Guillaume de Rorthais
Dalibo