We use this, pgbouncer, and a bash script to link the two, for completely automated failover.

Queries done through pgbouncer just pause as if the query is really really slow when the db goes down, then when pglookout does the failover, the bash script switches pgbouncer's config and those pending queries are sent immediately.