So, I'm looking to add high availability to a postgresql server. I've looked into some tutorials on the web but, haven't been able to find exactly what I'm looking for.
PostgreSQL Replication and Hot Standby – In Practice http://blog.3dtin.com/2012/07/26/postgresql-replication-and-hot-standby-in-practic/
Is a great tutorial for how to set that up, BUT failover is manual.
Replication and Load Balancing with PostgreSQL and Pgpool2 http://www.keyup.eu/en/blog/89-replication-and-load-balancing-with-postgresql-and-pgpool2
Seems to be perfect only if the pgpool server was not the single point of failure.
So, my question is, if I add a HAProxy cluster, just like any pool of web servers distribute load, would I be able to have the automatic failover to the pgpool and therefore to the postgresql servers??
Application Servers | | ip1 -------- (floating) ip3 -------- ip2 HAPROXY ------- hearbeat -------- HAPROXY pgpool II pgpool II | \ / | | \ / | | \ / | ip4<-----\-----------(to same master ip4) | postgres ip5<------------------(to same slave ip5) Master postgres slave