3

I am trying to configure pgpool2 and postgresql 9.1 to handle failover.

I currently have streaming replication running, and are using pgpool2 for read-only load balancing.

I have 2 servers in my setup, both running postgresql - 1 master and 1 slave. The master is also running pgpool2.

My question is how do I configure this setup to handle failover? Specifically in the case that the master crashes, and the slave has to take over and run pgpool2 as well. Most documentation and examples I have been able to find assumes that pgpool2 is running on a separate server and thus "never" crashes.

I may or may not be attacking the problem using the wrong tools. In my production setup I have a total of 3 identical servers all in independent locations. The main goal of the setup is to achieve a high uptime. Thus failover should be automatic, and bringing a failed node back up should cause only minimal downtime. I want all 3 nodes to be as close to identical as possible, and be able to run with just 1 or 2 nodes available. And if possible I want to use load balancing to improve performance.

If anyone can help me gain some insight into how to do this using my current setup or suggest a different/better setup.

Thank you!

1 Answer 1

1

As you've commented, pgpool needs to run on a completely separate server from the database cluster, or what you're trying to accomplish will never work. pgpool basically needs to be able to independently failover to the backup server if the master database server goes down. That can't happen if pgpool goes down with the master. It also can't happen if the backup server goes down, and pgpool is running there.

Most people setup pgool with some sort of HA monitoring infrastructure, like heartbeat, so that the pgpool server dies, a backup can be brought up automatically.

1
  • That is sort of what I ended up doing, i created a cluster of the two servers using Corosync and Pacemaker. So when pgpool goes down on one of the cluster nodes the other takes over automatically. I then handle the failover of the postgresql servers using repmgr from pgpool. I had to sacrifice the synchronous part as postgresql locks up if the slave goes down and there is no node to verify the syncronious.
    – aneez
    Mar 26, 2012 at 7:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .