Overview
I provide the config files I used, you can just copy them if you want to save some time but keep in mind that some settings need to be changed for your setup, like IP adresses and such. Check the quick start page for that.
The database system I ended up with looks like this:
Let's review it bottom up:
The replication system used is called streaming replication and allows all the PostgreSQL machines to be in sync with low latency.
There's a Master or Primary machine that can both read and write to the database that then replicates any changes to all the Slaves or Standby machines.
The Slaves use a feature called Hot-Standby that allows read-only queries to be executed on them. A Slave can be promoted to Master in case we detect a failure on the Master, this is called failover and can be automated to achieve High Availability. I made a script to automate the failover, it's called whenever PgPool II loses the connection to one of the PostgreSQL machines.
PgPool II is a middle-ware application that sits between your database system and the client in a transparent way (meaning that the client doesn't even need to know it's not talking to a PostgreSQL server, it can communicate in the same way) and can do some really usefull things.
For this project the features we're interested in are load Balancing (to distribute the read queries among all the machines, improving performance) and detection of failed connections, to initiate the failover process.
The load balancing needs to be somewhat intelligent because only the master can write to the database, and that's what PgPool does for us. Keep in mind that SELECT can sometimes write to the database, if you select a function that happens to do so. To solve this issue you can use PgPool's white and black lists to define what functions can be sent to read-only servers. We'll look at that later.
Since single points of failure must not exist in this setup, two PgPool servers are used: one in an active state and another as a backup. An heartbeart protocol is used so the backup server can promote itself to active in case the active one goes dark.
A Virtual IP is used to make the PgPool II switchover possible in a transparent way. If the active server goes down, the backup server "grabs" the V-IP and the client applications don't need to do anything except for maybe reconnecting if they were in the middle of a connection.
