Streaming replication and failover on PostgreSQL

Question

I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is not providing automatic failover for streaming replication. We can switch the slave to master using a trigger file but it is not manageable. So I'd like a solution with automatic failover and high availability.

Different solutions are available:

1. Repmgr
2. Linux Heartbeat
3. Pgpool-II (for automatic failover only)
4. Any other tool in case you used.

My question is which solution should be used?

Answer 1

In our shop we selected repmgr and pgbouncer instead of pgpool. repmgr has some nice tooling to setup and maintain the cluster of replicated database servers. In our case 1 master and 2 slaves (one failover and one live read performance test that can become the failover of the new master). pgpool has issues with changes in the configuration, in most cases you have to restart the service and therefor you have some downtime. This is a problem when you need 24x7x365 availability.

repmgrd (the deamon) helps to select the new master after a failover, you really don't want a split brain situation. We have one virtual ip-address for the master database, the database that is master at that moment. When another server becomes master, this is the only server using this address. Every database server also has it's own ip-address for read only queries.

repmgr is maintained by the same guys that created streaming replication in the first place, so they know what they talk about. Version 2.0 is about to be released.

Prepare for the worst situation, do some serious testing by pulling some power and network plugs! When something goes wrong, many other things already went wrong and will bite you in the back when you can't afford it.

Replication is one thing, a working failover after some serious problems, is another thing.

Answer 2

We are using two different solutions in combination at the same time...

Pgpool-II for synchronous replication and Slony2 for asynchronous (triggered) replication.

Performance is excellent

• Saurabh: Thank you for response... Actually I am trying Pgpool-II with streaming replication. It is providing the automatic failover. But If I will start the primary node again, Can pgpool-II will start as master again or standby node?
• user5701: As far as i know definately not. you will have to do a manually recovery of the primary node. Our setup is a little bit different. it's a multi master evironment and all the nodes have equal rights. If one node goes out of sync the load balancers refuse redirecting clients to this node.