The easiest approach to high availability with pgpool is to use PostgreSQL onboard tools along with Linux HA. In this case, in our world, the best approach is to run pgpool without statement-level replication and use PostgreSQL streaming replication to sync the data.

pgpool can be configured to do load balancing and automatically send write requests to the first and read requests to the second node.

What happens in case of failover? Let us assume the master will crash. In this case Linux HA would trigger the failover and move the service IP of the master to the slave. The slave can then be promoted to be the new master by Linux HA (if this is desired). pgpool would then simply face a broken database connection and start over and reconnect.

Of course, we can also use londiste or some other technology such as Slony to replicate data. However, for the typical case, streaming replication is just fine.

Practical experience has shown that using PostgreSQL onboard and operating system level tools is a good way to handle failovers easily and, more important, reliably.

In addition to streaming replication and Linux HA you can also use mechanisms provided by pgpool to handle failovers. This section will use those means provided by pgpool.

The first thing you have to do is to add the failover command to your pool configuration. Here is an example:

failover_command = '/usr/local/bin/pgpool_failover_streaming.sh %d %H

Whenever pgpool detects a node failure, it will execute the script we have defined in the pool configuration and react according to our specifications. Ideally this failover script will write a trigger file—this trigger file can then be seen by the slave system and turn it into a master.

The recovery.conf file on the slave might look like this:

standby_mode = 'on'
primary_conninfo = 'host=master_host user=postgres'
trigger_file = '/tmp/trigger_file0'

The trigger_file is checked for every 5 seconds. Once the failover occurs, pgpool can treat the second server as the new master.

The logical next step after a failover is to bring a new server back into the system. The easiest and most robust way of doing that is to:

Overall this will only need a handful of seconds of service interruption.