I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.
I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I want one of the Slaves to become a Master. After Master get back to normal state it should sync with other servers in cluster (apply all changes done while it was down) and claim back Master role or become a Slave.
The problems I see with PostgreSQL and current scenario are the following.
1) I don't see built-in tools for detecting Master server outage. I read that pgpool can handle it and create trigger file, I also read that people use Linux heartbeat or similiar tools for this. Okay, I can detect failover and assign a new Master in cluster. Will the other Slaves understand that there is a new Master and they should backup it now?
2) I don't understand the failback procedure. Master and Slave host configurations are different. So will I have two Masters after crashed Master failback? How will servers get back in sync? I saw only manual solutions like "transfer data folder to server and restart it". So what is the solution or best practice or at least key principal here?
3) How should I handle server outage on client side? When I create connection I explicitly specify server IP. Should I develop some kind of ConnectionManager which will know my Master-Slave structure, send requests to Master only and in case of connection loss will switch to backup servers and so on? I read that pgpool can be an entry point for applications and manage connections in correct way. Is pgpool the only solution here? Does it handle failover and failback well?
4) Are there any solutions (commercial also) so I could avoid manually copying the data, reconfiguring PostgreSQL instances and other stuff which should be done by hands? So kind of cluster configuration when everyone in sync, it's clear who is Master and everything switches automatically without operator attention?
According to these threads and articles
Streaming replication and failover on PostgreSQL
Automating failover in PostgreSQL 9.1
http://denishjpatel.blogspot.com/2010/11/possibility-of-graceful-switchover.html
there is no single fully automatic solution to solve these questions. Am I right?
Thanks!