Thread: Master-slave failover question
On Wed, Jan 8, 2014 at 1:55 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> I checked it out. Looks like the process involves manual identification ofCorrect, this is about manual detection of failure. There were a lot
> failure(?). But this is a nice method for manual switchovers.
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 ApplicationIt depends. In some cases it is worth doing if you have a single
> Server [so that even if the Master Server has to be shutdown, IP address
> changes or starting pgbouncer on slave is not needed]?
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
This email may contain confidential, privileged or copyright material and is solely for the use of the intended recipient(s).