Re: Using old master as new replica after clean switchover - Mailing list pgsql-docs

On Thu, 25 Oct 2018 20:45:57 +0900
Michael Paquier <michael@paquier.xyz> wrote:

> On Thu, Oct 25, 2018 at 11:15:51AM +0200, Jehan-Guillaume de Rorthais wrote:
> > On Thu, 25 Oct 2018 02:57:18 -0400
> > Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:  
> >> My research shows that some people already rely on the following when
> >> planned failover (aka switchover) procedure, doing it in production:
> >> 
> >>  1) shutdown the current master
> >>  2) ensure that the "master candidate" replica has received all WAL data
> >> including shutdown checkpoint from the old master
> >>  3) promote the master candidate to make it new master
> >>  4) configure recovery.conf on the old master node, while it's inactive
> >>  5) start the old master node as a new replica following the new master.  
> > 
> > Indeed.  
> 
> The important point here is that the primary will wait for the shutdown
> checkpoint record to be replayed on the standbys before finishing to
> shut down.

Yes. However, it gives up if the connection to the standby fails. This is
obvious. But that's why we really need to double check on the standby the
shutdown checkpoints has been received. Just in case of some network troubles
or such.

> > The only additional nice step would be to be able to run some more safety
> > tests AFTER the switchover process on te old master. The only way I can
> > think of would be to run pg_rewind even if it doesn't do much.  
> 
> Do you have something specific in mind here?  I am curious if you're
> thinking about things like page-level checks for LSN matches under some
> threshold or such, because you should not have pages on the previous
> primary which have LSNs newer than the point up to which the standby has
> replayed.

This could be a decent check. Heavy and slow, but safe.

Other ideas I have (see bellow) are only related to ease the existing
procedure.

Both are interesting projects I could hopefully work on.

> >> if so, let's add it to the documentation, making it official. The patch is
> >> attached.  
> > 
> > I suppose we should add the technical steps in a sample procedure?  
> 
> If an addition to the docs is done, symbolizing the steps in a list
> would be cleaner, with perhaps something in a dedicated section or a new
> sub-section.  The failover flow you are mentioning is good practice
> because that's safe, and there is always room for improvements in the
> docs.

The hardest part to explain here is how to check the shutdown checkpoint hit
the standby-to-promote. 
* in PAF, I'm using pg_waldump to check if the shutdown checkpoint has been
  received. 
* in manual operation, I force a checkpoint on the standby and compare "Latest
  checkpoint's REDO location" from the controldata file with the one on the old
  master.

I'm not sure how to explain clearly one or the other method in the doc.

Two ideas come in mind to improve this.

What about logging the shutdown checkpoint on the old master?
On the standby side, we could cross-check it with a function confirming:
1/ the very last XLogRecord received was the old master shutdown checkpoint
2/ the received shutdown checkpoint has the same LSN

Second idea would be that an old master detect it has been started as a new
standby and only replay XLogRecord from the new master if its TL fork is
following its previous TL and shutdown checkpoint?