PostgreSQL 9.4 Replication HOWTO

6 Oct 2015

I'm going to assume this install takes place on a stock install of Ubuntu 15.04 without the PostgreSQL packages installed. On your master, you will have PostgreSQL installed as own in my PostgreSQL 9.4 Compile/Install HOWTO.

On the master, edit postgresql.conf like so:

#wal_level = minimal  ==>  wal_level = hot_standby
#max_wal_senders = 0  ==>  max_wal_senders = 2

On the master, add the following line to pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host   replication      all             192.168.1.138/32        trust

NOTE that obviously the trust method of authentication is a bad idea in secure setups. Also, obviously, ADDRESS will be the address of the machine you are replicating to.

Reload/restart your master pg instance so that it reads in this new configuration.

On the slave, make sure pg is NOT running.

You probably have a data directory on the slave from following the instructions on my PostgreSQL 9.4 Compile/Install HOWTO. Rename it and create a new empty data directory.

# su - postgres
$ cd /usr/local/pgsql-9.4.4
$ mv data orig_data

Now on the slave, do a pg_basebackup:

$ /usr/local/pgsql-9.4.4/bin/pg_basebackup \
    -D /usr/local/pgsql-9.4.4/data \
    -h 192.168.1.196 \
    --xlog-method=stream

Your slave will now have a directory /usr/local/pgsql-9.4.4/data with everything from the master.

Now create a slave file /usr/local/pgsql-9.4.4/data/recovery.conf with the following contents:

standby_mode = on
primary_conninfo = 'host=192.168.1.196 port=5432'

To put your slave in read-only mode, make the following edit to your slave's /usr/local/pgsql-9.4.4/data/recovery.conf:

#hot_standby = off  ==>  hot_standby = on

Now start your slave and it will be in hot standby mode!