Thread: switchover/switchback

starting with 9.3 i ve read on the EDB site about the possibility to
switchover/switchback in a controlled manner.
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.

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?


thank you in advance



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-admin-f2076596.html

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,

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