Postgresql – PgPool 2 with HA Proxy

high-availabilitypgpoolpostgresqlpostgresql-9.1

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.