Re: HAProxy for PostgreSQL Failover
Alan, On 2011-06-15 19:54, Alan Gutierrez wrote: > I'd like to use HAProxy to implement a simple proxy that can perform > failover for a pair of PostgreSQL configured as master/slave with > PostgreSQL 9.0 streaming replication to replicate the master to the > slave. Only the master is active for client connections, unless the > master fails, then the clients should connect to the slave while an > administrator recovers the master. You might also want to have a look at pgpool-II [1] which is a proxy specifically designed for failover, replication and loadbalancing of Postgres servers. Recent versions can take advantage of the built-in asynchronous replication feature of Postgres 9. Using this, you can * configure failover and recovery * potentially utilize the second machine for read-only queries. --Holger [1] http://pgpool.projects.postgresql.org
Re: HAProxy for PostgreSQL Failover
Hello Alan, You can use the built-in health check for PostgreSQL servers (since 1.5-dev4). Regards, Rauf On Wed, Jun 15, 2011 at 10:54 AM, Alan Gutierrez wrote: > I'd like to use HAProxy to implement a simple proxy that can perform > failover for a pair of PostgreSQL configured as master/slave with PostgreSQL > 9.0 streaming replication to replicate the master to the slave. Only the > master is active for client connections, unless the master fails, then the > clients should connect to the slave while an administrator recovers the > master. > Thus, I need a setup in HAProxy, where all traffic goes to the master, until > the master fails, then all traffic goes to the slave. > I've studied the MySQL balancing setup described here: > http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/ > With this as a basis, I could create a daemon on the HAProxy machine that > tests the master and reports on the health of the master via HTTP. The HTTP > health check is used with an ACL in a frontend. If the master is up, the > master backend is used. If it is down the slave backend is used. > Or else, I could test for the health of the master, and when it fails I can > kill, reconfigure and restart HAProxy. It might be easier. > Sound like a good use of HAProxy? Are their any known issues with HAProxy > and PostgreSQL? > -- > Alan Gutierrez
HAProxy for PostgreSQL Failover
I'd like to use HAProxy to implement a simple proxy that can perform failover for a pair of PostgreSQL configured as master/slave with PostgreSQL 9.0 streaming replication to replicate the master to the slave. Only the master is active for client connections, unless the master fails, then the clients should connect to the slave while an administrator recovers the master. Thus, I need a setup in HAProxy, where all traffic goes to the master, until the master fails, then all traffic goes to the slave. I've studied the MySQL balancing setup described here: http://www.alexwilliams.ca/blog/2009/08/10/using-haproxy-for-mysql-failover-and-redundancy/ With this as a basis, I could create a daemon on the HAProxy machine that tests the master and reports on the health of the master via HTTP. The HTTP health check is used with an ACL in a frontend. If the master is up, the master backend is used. If it is down the slave backend is used. Or else, I could test for the health of the master, and when it fails I can kill, reconfigure and restart HAProxy. It might be easier. Sound like a good use of HAProxy? Are their any known issues with HAProxy and PostgreSQL? -- Alan Gutierrez