How To Setup Binary Replication Between 2 PostgreSQL 9.4 Hosts (Hot-Standby)

Utilising a master/slave (hot-standby) setup to provide a resilience layer at database level can be easy. The following assumes you have 2 PgSQL hosts at 10.10.50.1 and 10.10.50.2, both running Ubuntu 14.04 LTS and PostgreSQL 9.4 (9.4.5).

1. On the master 10.10.50.1, edit the following in postgresql.conf:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 3

listen_addresses can also be scoped down to single or multiple server bound IP addresses, for added security/best practice

wal_level defines what type of data, and how much of it is written to/stored in the Write Ahead Log. Setting to hot_standby tells PgSQL to write all the data that would have been written with “archive” mode, plus the data needed to reconstruct the status of running transactions.

max_wal_senders defines the number of process to use (max) to send replication data to the slave. This can be fine-tuned for your DB load and network capacity.

2. On the master, 10.10.50.1, edit the following in pg_hba.conf:

host replication all 10.10.50.2/32 trust

This entry allows the slave to communicate back to the master, but only for replication based tasks.

3. On the slave, 10.10.50.2, edit the following in postgresql.conf:

hot_standby = on

4. On the slave, 10.10.50.2, create a new configuration file named “recovery.conf” and add the following:

standby_mode = 'on'
primary_conninfo = 'host=10.10.50.1'

5. We now need to sync the DB data from the master to the slave, so they can begin at the same point. Your mileage may vary with this, but the rsync command that would work in this scenario is the following, note the excludes, these are important, don’t sync those:

rsync -av -e "ssh -p 22" --exclude pg_xlog --exclude postgresql.conf /var/lib/postgresql/9.4/main/* root@10.10.50.1:/var/lib/postgresql/9.4/main/

6. Once the sync has completed, start the Slave DB, once up, start the Master DB. Replication will now be in effect.