Automating failover in PostgreSQL 9.1
Check out repmrg:


  repmgr is a set of open source tools that helps DBAs and System
  administrators manage a cluster of PostgreSQL databases..
  
  By taking advantage of the Hot Standby capability introduced in
  PostgreSQL 9, repmgr greatly simplifies the process of setting up and
  managing database with high availability and scalability requirements.
  
  repmgr simplifies administration and daily management, enhances
  productivity and reduces the overall costs of a PostgreSQL cluster by:
  
  
  monitoring the replication process; allowing DBAs to issue high
  availability operations such as switch-overs and fail-overs.
  


It does two things:


repmgr: command program that performs tasks on your cluster and then exits
repmgrd: management and monitoring daemon that watches the cluster and can automate remote actions.


For automatic failover, repmgrd does the trick and is not a SPOF in your network, like pgPool. However, it is still important to monitor all deamons and bring them back up after failure.

Version 2.0 is about to be released, including RPM's.