PostgreSQL Archiving Replication

2 minute read , Feb 13, 2015

In this mode PostgreSQL replicates the WAL archive logs.

Configuring the master server

First, create a new user in PostgreSQL, for replication purposes. We’ll use it to connect to the master instance from the slave and replicate data. To create the new user, execute:


The user created is called rep. Make sure to replace the word password with something better for production use. Next we edit the master instance configuration files. Edit the pg_hba.conf file. It controls the client authentication for PostgreSQL. Add the line in below at the end of the file, using the real IP of the slave server. If we have it configured in the /etc/hosts file, we can add the hostname or an alias as well in /etc/postgresql/9.3/main/pg_hba.conf:

host replication rep <slave-IP>/32 md5

Then, edit the main PostgreSQL config file /etc/postgresql/9.3/main/postgresql.conf and set the following parameters:

listen_addresses = 'localhost,'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on

To apply the changes, restart PostgreSQL.

$ sudo service postgresql restart

Configuring the slave server

Stop the service first:

$ sudo service postgresql stop

Edit the access permissions file by adding the following line at the end of /etc/postgresql/9.3/main/pg_hba.conf:

host replication rep <master-IP>/32 md5

Then in the /etc/postgresql/9.3/main/postgresql.conf file apply the following changes:

listen_addresses = 'localhost,'
wal_level = 'hot_standby'
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 1
hot_standby = on
Initial replication

Before the slave server can replicate from the master, it’s recommended to transfer the initial data structure. Go to the master server and dump a backup file:

$ sudo -u postgres psql -c "select pg_start_backup('initial_backup');"

Then, copy the dump to the slave server, except for the xlogs files:

$ rsync -cva --inplace --exclude=*pg_xlog* /var/lib/postgresql/9.3/main/ :/var/lib/postgresql/9.3/main/
$ sudo -u postgres psql -c "select pg_stop_backup();

The pg_stop_backup() command will do the backup cleanup.

Log in to the slave server again and configure a recovery file /var/lib/postgresql/9.3/main/recovery.conf with following content:

standby_mode = 'on'
primary_conninfo = 'host= port=5432 user=rep password=password'
trigger_file = '/tmp/postgresql.trigger.5432'

This file’s purpose is that later, if we create an (empty) trigger_file on the slave machine, the slave will reconfigure itself to act as a master.

To apply the changes, restart PostgreSQL in the slave server.

$ sudo service postgresql start

At this point the replication should be already working. If not, check the log file /var/log/postgresql/postgresql-9.3-main.log for more details.

Leave a Comment