PostreSQL 9.3 Streaming Replication Howto / Tutorial

Update 2016-06-03

This is a re-creation of original post from my old blog.

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

service postgresql stop  

Step 2 - Remove existing data

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 our replicator 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.