PostreSQL 9.3 Streaming Replication

Introduction

This tutorial will walk through configuring master/slave servers for postgresql 9.3 using streaming replication.

Assumptions

• You are not trying to configure an existing data set (i.e. fresh install)
• You are running Ubuntu 14.04 LTS
• You have run apt-get update ; apt-get upgrade
• You have two servers to use as a master and a slave
• $HOME and ~/ for the user postgres are set to /var/lib/postgresql
• We will work out of /var/lib/postgresql for all/most commands
• We are not using archiving

Also important to note is that all actions should be run as postgres user, NOT ROOT.

su - postgres
cd /var/lib/postgresql

Hosts/servers

• master (db1 / 192.168.1.10)
• slave (db2 / 192.168.20)

Directories

• Data (/var/lib/postgresql/9.3/main)
• Configs (/etc/postgresql/9.3/main)

Step 1 - Stop pgsql

First make sure postgresql is not running on both servers (master and slave).

service postgresql stop

Step 2 - Remove existing data

On both servers, we will clear any existing data in the data directory (see above).

rm -rf /var/lib/postgresql/9.3/main/*

Step 3 - Configure pg_hba.conf on master

To allow our slave to connect to our master, we will need to make sure the pg_hba.conf has an entry for ourreplicator role to connect.

vi /etc/postgresql/9.3/main/pg_hba.conf

Now what we want to do is add two entries (ssl and non-ssl) for the user/role replicator

host    replication    replicator    192.168.1.20/32    md5
hostssl    replication    replicator    192.168.1.20/32    md5

This now allows user replicator from IP 192.168.1.20 using md5 password method.

Step 4 - Configure postgresql.conf on master

Next, we will update the postgresql.conf file and change the settings required to get streaming replication working.

vi /etc/postgresql/9.3/main/postgresql.conf

There are X settings to change, you can make them look like the following:

listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Be sure to check the last setting, checkpoint_segments, as this may have been manually updated if you use the pgtune tool.

Step 5 - Initialize the database on master

Now we are ready to initialize the database directory on the master server. This will create all the necessary files/directories needed to run normally.

initdb /var/lib/postgresql/9.3/main

At this point we can start postgresql on the master server.

service postgresql start

We should now have a fresh instance of postgresql up and running.

Step 6 - Add replicator user to master

We now need to add our replicator user which will be used to connect and read the replication pseudo-table.

psql -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'your-password';"

Now that our master server is primed and ready, we can move onto the slave.

Step 7 - Configure postgresql.conf on the slave

At this point I would add a note that the following worked for me, but based on the comments in the config file, some are only relevant to the master and may not need to be set, so feel free to try without them.

vi /etc/postgresql/9.3/main/postgresql.conf

Now change these settings, and again it's possible that only the hot_standby option need be set.

listen_addresses = '*'
hot_standby = on
wal_level = hot_standby
max_wal_senders = 3
wal_keep_segments = 8
checkpoint_segments = 8

Step 8 - Copy data from master to slave

On the slave, we will use the pg_basebackup utility to get a copy of the master's data. This will prompt for a password which is the one you configured in Step 6 when you created the replicator user.

pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/9.3/main -U replicator -P -v -x

This should complete successfully, if you see any errors then you will need to try to figure them out before continuing. Now we need to create a recovery.conf file and we will link it in the data directory.

vi /etc/postgresql/9.3/main/recovery.conf
primary_conninfo = 'host=192.168.1.10 port=5432 user=replicator password=your-password'
trigger_file = '/var/lib/postgresql/9.3/main/failover'
standby_mode = 'on'

There are a couple of other options you can specify for the primary_conninfo (and probably more).

• keepalives_idle=60
• sslmode=require

Step 9 - Starting the slave

We should now have our fully loaded slave with a copy of the master's data ready to go. We can now start postgresql on the slave.

service postgresql start

Finished

That wraps up this tutorial. Part of my learning curve was getting stuck with trying to figure out archiving and how that tied into streaming replication, which it seems they are different solutions. I struggled for hours trying to get this to work before finally disabling the archive options.