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.