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.
E.g.:
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
Best Answer
Yep, you don't need HAProxy with the newest versions pgpool-II. I'm not sure in which version it is introduced, but "watchdog" feature that is now embedded in pgpool does everything needed for automatic fialover and real HA.
Take a look at this post - it'll help for sure.