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
*/var/lib/postgresql/9.1/main/*
Master
/etc/postgresql/9.1/main/postgresql.conf
wal_level = hot_standby
max_wal_senders = 5
wal_keep_segments = 32
listen_addresses='*'
/etc/postgresql/9.1/main/pg_hba.conf
host replication all 192.168.3.15/32 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=192.168.3.30'
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 192.168.3.15:/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.