I am trying to help someone who wants a dynamic failover server. They have a very active pgsql server. I set up streaming replication to another server so there is always a live backup available, but now they have decided they want to be able to read and write to either server at any time. Essentially they are looking for a cluster, even though that is the wording they are using.
I have been looking at pgpool-II. How well does it work, and how hard would be it be to convert a primary server and a streaming replication server to be a load-balanced pgpoo-II cluster?
If the two databases get out of sync (such as one is down for a while and then back up) do they automatically sync the databases back up?