Re: Master-slave failover question

On Wed, Jan 8, 2014 at 1:55 AM, Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> wrote:

> > I checked it out. Looks like the process involves manual identification
> of
> > failure(?). But this is a nice method for manual switchovers.
>
> Correct, this is about manual detection of failure. There were a lot
> of discussions around automatic failover but
> I have never seen anyone
> defined a really working (for me) criteria to detect the failure.
>
>
I guess there are few aspects involved: Auto Detection of Failure,
Auto-promotion of slave to master and automatic switch of application to
point to New Master.

- Auto-detection of Failure: This can be tricky and may lead to split-brain
scenario when both the nodes think they have lost there fellow node and
Slave promotes itself as master and Master. If you have a proper log
monitoring in place, this can be detected and you can bring things back in
shape. I agree to your point "
I have never seen anyone defined a really working (for me) criteria to
detect the failure.
"

- Auto-promotion of slave to master: On failure detection you can use a
shell script/ssh to create trigger file which shall promote the slave

- Auto switch of Application to New Master: You can use Virtual IP or use a
middle-tier e.g. pgbouncer on a different node

- SO far I have used pgpool in HA mode to cater to all these points. I have
run into split brain scenario but once detected, you just need to rebuild
slave and attach it back. Despite split brain my application continues to
use original master (since Virtual IP has not shifted).

- Even if Virtual IP shifts my application would connect to slave (not
promoted to a standalone master), which I can live with.

- You will run into an issue if you fail to notice split brain scenario.
Later when you actually loose your master you will face a problem (you
won't have a slave to failover to).

- If the scenario is of a DR site, I would not recommend auto-failovers.
The setup I am talking about is HA setup and I am using synchronous
streaming replication.

- In same setup I have a DR site, which is using file based log shipping
(archive restore) and has band width constraints. The replication is not
synchronous.

- In DR there could be a data/transaction loss while switching 2nd Node and
hence IMHO decision and process should be manual.

> > As a suggestion, won't it be a good idea to run the pgbouncer on
> Application
> > Server [so that even if the Master Server has to be shutdown, IP address
> > changes or starting pgbouncer on slave is not needed]?
>
> It depends. In some cases it is worth doing if you have a single
> application server (and do not planning to have more), or the overhead
> of a high number of app->db network connections per second is
> significantly high in comparison to a local one.

I can't recollect where but I read recommended setup for pgbouncer to be
installed away from db server (to avoid process contention and few other
points were mentioned which I can not exactly recollect). I have seen/done
setups which has pgbouncer running on dbserver and they seem to holding
fine.
Recently I had done a benchmark of running pgbouncer on to Client (windows
machine running HammerDB as benchmark tool) and then tried to run it on db
server. There was not much of a difference.

However, modern
> architectures usually use multiple application servers and persistent
> connections, and in this case installing pgbouncer on database servers
> is preferable.

I guess one can always setup multiple pgbouncer (one on each application
node) (?).

> I also do not recommend to install it on a separate
> machine as it brings another point of failure in any case.

Agree

> And again,
> it depends on your situation.

That's fair

Best Regards,
*Sameer Kumar | Database Consultant*

*ASHNIK PTE. LTD.*101 Cecil Street, #11-11 Tong Eng Building, Singapore
069533
M : *+65 8110 0350* T: +65 6438 3504 | www.ashnik.com
www.facebook.com/ashnikbiz | www.twitter.com/ashnikbiz

[image: email patch]

This email may contain confidential, privileged or copyright material and
is solely for the use of the intended recipient(s).