A friend contacted me today, asking me "What are the best practices for failover with Postgres?" And he mentioned pgpool-II.
He was interested in 9.0, since 9.1 hasn't been released yet. (but, it's looking like we're gearing up for a September release!) My off-the-cuff response was:
There isn't a single solution, although pgpool-II is a common one.
pgpool-II is what I've used in AWS. I've also seen people use heartbeat (I guess pacemaker now?). I think either works fine. The frustrating bit is that we don't have the ability to refresh the failed system easily.
There's also repmgr: https://github.com/greg2ndQuadrant/repmgr
It's new, but might be worth exploring.
What are you doing for HA and Postgres?
Have some feedback? Corrections? Ideas for other posts? Contact me @selenamarie.