Postgres automatic failover, multiple clients to multiple masters I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website.

The majority of implementations that I have found require a single point of failure with PgPool II or PGBouncer proxying the connection for every app server to the correct database.

Example:

App Server 1 \              Spare
App Server 2 -  PGPool II - Master
App Server 3 /              Spare


I'm looking to try a method of automatic fail-over, where each app server will fail-over automatically to a new master DB quickly and correctly. It can still use PGPool or PGBouncer, but each app server should be separate and autonomous with no message/state passing from app server to app server.

Example:

App Server 1 -  PGPool II \ Master 1
App Server 2 -  PGPool II - Master 2
App Server 3 -  PGPool II / Master 3


A maximum time for switch over should be 1-2 seconds.


Does this make sense and is it possible?
Is this an easy to implement via some tool/configuration? If so, how? If not, why?
I can recommend using Slony for PostgreSQL replication from my experience with it.  

I can't respond better then the Slony docs section on failover so I'll quote it:


  If some more serious problem occurs on the "origin" server, it may be necessary to SLONIK FAILOVER to a backup server. This is a highly undesirable circumstance, as transactions "committed" on the origin, but not applied to the subscribers, will be lost. You may have reported these transactions as "successful" to outside users. As a result, failover should be considered a last resort. If the "injured" origin server can be brought up to the point where it can limp along long enough to do a controlled switchover, that is greatly preferable.
  
  Slony-I does not provide any automatic detection for failed systems. Abandoning committed transactions is a business decision that cannot be made by a database system. If someone wants to put the commands below into a script executed automatically from the network monitoring system, well ... it's your data, and it's your failover policy.


To your question: does it make sense?, personally, I would say, no. I agree with the Slony docs that it's better to have a human understand why there was a failure and then manually (and quickly) failover. 

Having managed PostgreSQL for more than a decade, unless there's a hardware failure, it's difficult to make it seriously fail. And I recommend involving a human to confirm hardware failure and check the state of the system before failing over.

If you are getting started with a high-availability PostgreSQL, I would first look at the hardware, tuning and RAID configuration behind your PostgreSQL database before considering automatic failover.