18 Jul 2012

Playing with PostgreSQL 9.1 replication

The idea of using Postgres (PG) on my next projects or even migrating some of the existing ones is becoming more and more appealing. I’m already using it in a multi-tenant Rails application and I’m very happy with it.

One of the entreprisy features it offers is replication. Replication is very useful to gain high availability.

I’ve played a bit today with this feature and these are the steps I’ve followed to create a master (read/write) - slave (read) setup on my LAN.

Prerequisites and notes

Be sure to have the same PG version on both machines. I have different versions and was not able to make it work until I upgraded one of them to match the other one.

The data directory using PostgreSQL 9.1 on Ubuntu 12.04 is located in




wal_level = hot_standby

max_wal_senders = 5
wal_keep_segments = 32


host    replication     all     trust

Restart the service:

sudo service postgres stop

Slave server

Stop the PG service on the slave server if it is running:

sudo service postgres stop

Edit /etc/postgresql/9.1/main/postgresql.conf:

hot_standby = on

Create /var/lib/postgresql/9.1/main/recovery.conf:

standby_mode = 'on'
primary_conninfo = 'host='
trigger_file = '/tmp/pgsql.trigger'

Final steps

All the configuration files are now ready. The last step is to send a copy of the current master database to the slave server.

sudo -u postgres psql -c ";SELECT pg_start_backup('backup', true)";
rsync -a -v -e ssh /var/lib/postgresql/9.1/main --exclude postmaster.pid
sudo -u postgres psql -c ";SELECT pg_stop_backup()";

Now you can start the service again on the slave server.

sudo service postgres start

You should see something like this in the PG’s log file:

2012-07-18 12:20:34 CEST LOG:  database system was interrupted; last known up at 2012-07-18 12:19:24 CEST
2012-07-18 12:20:34 CEST LOG:  entering standby mode
2012-07-18 12:20:34 CEST LOG:  redo starts at 0/16000020
2012-07-18 12:20:34 CEST LOG:  record with zero length at 0/160000A0
2012-07-18 12:20:34 CEST LOG:  streaming replication successfully connected to primary

Now you can work with the master database and the changes are automatically replicated to the slave server.

This configuration allows you to use the slave database to perform read-only queries.

If the master server goes down, creating the file /tmp/psql.trigger/ will switch to read-write mode the slave database. Note that the docs say that:

It’s important to prevent the original master from restarting after fail-over, lets you end up with a “split brain” problem and data loss.

I have not tested this fail-over functionality yet.

Useful articles about replication

Binary Replication Tutorial

Streaming Replication in PostgreSQL 9.1

Adventures in scaling PostgreSQL