Date: Sun, 3 Mar 2019 14:44:48 +0100
From: "Jehan-Guillaume (ioguix) de Rorthais" <ioguix@free.fr>
To: Shreeyansh Dba <shreeyansh2014@gmail.com>
Cc: MirrorX <mirrorx@gmail.com>, pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: switchover/switchback
Message-ID: <20190303144448.6c262a8c@firost>
In-Reply-To: 
 <CAGDYbUNKSaK34-=H-Mg=fQuE-J2h-RHmx=Y2wUpocAQtZ474sA@mail.gmail.com>
References: <1551441770909-0.post@n3.nabble.com>
	<CAGDYbUOqWt2CVTvBoMywTa-70UZsJy3DtN_bpBLMt4btvQKGjA@mail.gmail.com>
	<20190302084940.3512dd63@firost>
	<CAGDYbUNKSaK34-=H-Mg=fQuE-J2h-RHmx=Y2wUpocAQtZ474sA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Precedence: bulk

On Sun, 3 Mar 2019 11:53:57 +0530
Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:

> Go through the link for more details.
> https://repmgr.org/docs/4.0/performing-switchover.html

OP was asking if switchover/switchback was safe and how it could fail. Pointing
to repmgr doc which explains how to perform a switchover using repmgr does not
answer the question. It neither explain how safe it is or what controls to
perform during the procedure.


> On Sat, Mar 2, 2019 at 1:19 PM Jehan-Guillaume (ioguix) de Rorthais <
> ioguix@free.fr> wrote:  
> 
> > On Sat, 2 Mar 2019 10:24:21 +0530
> > Shreeyansh Dba <shreeyansh2014@gmail.com> wrote:
> >  
> > > Yes, this works, when your master goes down, you can promote a slave as a
> > > new master and to bring the old master in sync will have to rebuild it  
> > from  
> > > the new master but manual intervention is there.
> > > You can achieve this by using the pgpool auto failover mechanism by  
> > adding  
> > > your owned scripts as per your requirement to avoid the manual  
> > intervention.
> >
> > OP is asking about switchover, not failover. Some are talking about
> > "controlled
> > failover".
> >
> > Note that OP is not (yet) asking about high availability here. You might
> > be able
> > to do switchover with pgPool, but I would not recommend it as it requires
> > to
> > write your own scripts all by yourself.
> >  
> > > On Fri, Mar 1, 2019 at 7:20 PM MirrorX <mirrorx@gmail.com> wrote:
> > >  
> > > > starting with 9.3 i ve read on the EDB site about the possibility to
> > > > switchover/switchback in a controlled manner.  
> >
> > Yes, this is supported.
> >  
> > > > this way the current master is stopped, the slave is promoted to  
> > master and  
> > > > then since the old master's xlog position is behind the new master's,  
> > it  
> > > > can
> > > > be directly connected as a new slave without the need of it being  
> > rebuilt  
> > > > and also without the need to use pg_rewind (since the xlog position  
> > hasnt  
> > > > moved forward). this is been possible after 2 commits, the one about  
> > the  
> > > > walsender sending all the xlogs to the slave when the master is being  
> > shut  
> > > > down and the second is about the timeline and how the slave can  
> > continue  
> > > > replicating when the timeline has changed.  
> >
> > Exact.
> >  
> > > > i have tested this many times and it has always worked. however, i  
> > find it  
> > > > strange that there is little awareness about it and pg professionals  
> > that i  
> > > > meet keep saying that the old master (new slave) needs to be rebuilt.
> > > > could someone clarify if this works and if not why it could potentially
> > > > fail?  
> >
> > This has been discussed few weeks ago on pgsql-hackers. This certainly
> > deserve a
> > doc patch and maybe some useful tooling.
> >
> > The only risk I am aware of, is a race condition were the
> > standby-to-be-promoted disconnect from the master during the shutdown
> > (think
> > network issue or anything else). That's why you need to check on the
> > standby if
> > it received the shutdown checkpoint from the old master before promoting
> > it.
> >
> > The procedure would be:
> >
> > 1. smart/fast shutdown the master
> > 2. note the LSN of the shutdown checkpoint on the master
> > 3. look for shutdown checkpoint on the standby @ noted LSN
> > 4. if found, promote the standby
> > 5. create the recovery.conf on the old master and start it
> >
> > Regards,
> >  


-- 
Jehan-Guillaume de Rorthais
Dalibo