I need a reliable SQL DB for our application and have been looking at Postgresql's streaming replication support. It seems that it's not hard to setup master/slave replication and you can use the slave as a hot standby for read-only queries. All good so far. However how do I automatically promote a slave to master when the master fails (or the slave thinks the master has failed)? I know I can write a file on the slave to make it become master but am looking for scripts et al to do that (and make sure the master stays down etc.).
Anyone know of a nice tutorial on how to do this on Ubuntu 12.04? Hosted at Hetzner (for bonus points)? Chef recipes or something?
2 Answers 2
Have a look at repmgr. It's a free software product that is meant to manage failovers and switchovers with PostgreSQL built-in replication.
It's not easy to do the task by script recently. Generally speaking, we shoud do the task by hand. When we need switch role between primary node and standby node, first, it's easy activate the standby node to primary node (just create a trigger file in standby node). But it's not easy to swtich the primary node to standby node because it needs complex steps.