Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jan 9 '12 at 9:44

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers 2

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.

share|improve this answer
add comment

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

share|improve this answer
    
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? –  Saurabh Jan 10 '12 at 10:25
    
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. –  user5701 Jan 11 '12 at 4:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.