High Availability on a Shoestring -- Postgresql 9.0 Streaming Replication

This is the first in a series of posts on setting up a high availability web application system on a shoestring budget. The goal is to have no single point of failure and to not spend a lot of money.

Our initial server setup while developing a new web application had everything on one server. We soon added another server and put postgresql on it. For a normal web app, you might be content to risk it and use just one or two servers, but when you need to try to make the site as resilient as possible, you’re going to have to splurge on some more servers for redundancy.

The first step in making our app more resilient was to add another database server and set up replication between it and the existing server. We like postgresql and have been using it on a lot of projects, but it hasn’t had good built-in replication. Until now.

Postgresql 9.0 (finally) has streaming master-slave replication built in. It’s about as easy to set up as mysql replication. Our servers are all Debian Lenny, and postgresql 9.0 packages don’t exist for it or in the backports. Building it from source is very easy.

So now we’ll assume you have two db servers, db0 and db1. Postgresql 9.0 is installed on both (see this post for instructions on installing Postgresql 9.0 on Debian Lenny) and you have initialized the databases. db0 will be the master, db1 will be the hot standby slave.

To set up replication, edit postgresql.conf on both servers and set the following variables:

hot_standby = on
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 32

Restart postgresql on the master, stop it on the slave.

Now you need to make a backup of the master, copy it to the slave, then start the slave in recovery mode.

To make a backup, on the master do the following:

psql -c "SELECT pg_start_backup('replication backup', true)" postgres
rsync -C -a --delete -e ssh --exclude postgresql.conf --exclude pg_hba.conf --exclude postmaster.pid \
    --exclude postmaster.opts --exclude pg_log --exclude pg_xlog \
    --exclude recovery.conf --exclude recovery.done \
    /var/lib/postgresql/9.0/main/ db1:/var/lib/postgresql/9.0/main/
psql -c "SELECT pg_stop_backup()" postgres

The backup should be on db1 now. To tell db1 who the master is, create a file named recovery.conf in the postgres data directory on db1. Make it look like:

standby_mode='on'
primary_conninfo='host=db0'
trigger_file = '/tmp/trigger_file0'

Start postgresql on db1 and you should be all set.

So now any changes to the database on db0 will be replicated to db1. db1 is in permanent recovery mode, but since it is in “hot standby” recovery mode, it can handle read-only queries. If db1 goes down, you should be able to restart it and it will recover anything that happened while it was down as long as the master didn’t use up all 32 write-ahead log segments (see wal_keep_segments in the postgresql.conf file above). If it has been down for a long time, or just to be safe, you can repeat the backup steps to make sure you get everything.

What about when db0 goes down? You can manually tell db1 to exit recovery mode and become the master by creating the trigger file specified in recovery.conf. Then you need to tell your clients to connect to db1 (more on this in a future post) and when db0 comes back up, you need to make it a slave to db1. Once they are back in sync, you can switch it back and make db0 the master.

This was pretty easy to set up, but we wanted more out of the setup. We wanted the failover to happen automatically and we wanted to be able to use the hot standby slave to process some queries and not just have it sit there. More on that in a future post…