Re: [GENERAL] clarification about standby promotion - Mailing list pgsql-general
| From | Venkata B Nagothi |
|---|---|
| Subject | Re: [GENERAL] clarification about standby promotion |
| Date | |
| Msg-id | CAEyp7J8AJpJdZYoHxW+PojPLZ6ADxop0OeUwNQ7T21OJRYS2mg@mail.gmail.com Whole thread Raw |
| In response to | Re: [GENERAL] clarification about standby promotion (Jehan-Guillaume de Rorthais <ioguix@free.fr>) |
| List | pgsql-general |
On Fri, Feb 10, 2017 at 2:42 AM, Jehan-Guillaume de Rorthais <ioguix@free.fr> wrote:
Sure, but when you are doing a switchover, the standby is supposed to beOn 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.
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.
Yes, Standby would receive everything from the master before master shuts down.
To perform switch-over / switch-back, It is important that, the standby receives something called last known position from the master automatically even after the master shuts down, which does not automatically happen in 9.2 and this happens automatically from 9.3. This improvement is only applicable in the case when you want to swap roles of master and standby.
> 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.
I think, i need to be more clear here. What i meant was - If you wish to intentionally promote standby, then, yes, before just shutting down the master check the last *xlog* or checkpoint position and in real-time, this must be done after the application is shutdown completely and the database is not encountering any data changes. In application terminology, the data must be in complete sync between master and standby. But, yes, i agree that the only way to check the master-standby position sync after the master shuts down is by comparing pg_controldata output of master and standby.
Regards,
Venkata B N
Database Consultant
pgsql-general by date: