One of my first projects at my new gig was to set up streaming replication on Postgres 9.4 database server.

Replication is a handy way of mirroring data stored in postgres across several servers. It’s useful for scaling reads to a database cluster (in our case, a driving force for the change was to make sure our nightly backups were not running from the master server).

The first thing to do is to set up a standby server with as many similarities to your master server as possible. For simplicity’s sake, I made sure to match our 64 bit Ubuntu 12.04 Precise Pangolin PostGres 9.4 server with a server on the same internal network with the same specs.

Step 1 – Set up postgres on standby.

Here is a bash script to set up postgres on your standby server:

# update package source
echo "deb http://apt.postgresql.org/pub/repos/apt/ utopic-pgdg main" > /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
sudo apt-key add -
curl http://launchpadlibrarian.net/173841617/init-system-helpers_1.18_all.deb > ~/ish.deb
dpkg -i ~/ish.deb
# apt-get-fu
sudo apt-get clean
sudo apt-get update
sudo apt-get upgrade
#installation of postgres
apt-get install postgresql-9.4 postgresql-common postgresql-client-common -f

DigitalOcean has a nice tutorial on setting up postgres. I recommend you check it out if this is your first time installing postgres.

Step 2 – Allow standby to talk to master.

On the standby, execute the following bash command to find (and make a note of) your ip address:

ip addr show

Back on the master, edit postgresql.conf at /etc/postgresql/9.4/main/postgresql.conf to include the following. Make sure you replace STANDBY_IP with your standby’s ip address:

# make sure the standby can listen to this server
listen_address = 'STANDBY_IP'
# standby settings
wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 4
wal_keep_segments = 4

Also on the master, edit pg_hba.conf /etc/postgresql/9.4/main/pg_hba.conf to include the following. Make sure you replace with your standby’s ip address:

hostssl replication replication_web STANDBY_IP md5

These settings will allow you to transfer 32MB of data in the time it takes to make a copy of your server across the network. For more on what these settings denote, or if you have a server that will write more than 32MB of data in 10 minutes, check out the official postgres docs.

Step 3 – Set up replication user on master.

On the master, su into the postgres user with su postgres and execute:

psql

Once you are in the psql prompt, create a replication user. Make sure to replace INSERT_PASSWORD_HERE with a secure password that you’ve generated.

CREATE USER replication_web REPLICATION LOGIN ENCRYPTED PASSWORD 'INSERT_PASSWORD_HERE';

Restart postgres on the master with the following command

/etc/init.d/postgresql restart

Great. Our master server is configured, and it’s time to move onto the standby.

Step 4 – Configure standby on the standby

This step is optional unless you have restricted firewall rules (which I do recommend you set up, but they are largely outside the scope of this tutorial), make sure your firewall on your master allows you to connect to master server from standby server. Consult the man page of your firewall software for how to do this. Here are the docs for ufw, and iptables — two popular firewalls for Ubuntu.

On the standby, edit the following configuration lines in postgresql.conf at /etc/postgresql/9.4/main/postgresql.conf:

wal_level = hot_standby
max_wal_senders = 3
checkpoint_segments = 4
wal_keep_segments = 4
hot_standby = on

IMPORTANT WARNING — Make sure that you (1) make a backup before running the next command block (2) and you are running the commands on the standby server, NOT YOUR MASTER SERVER. Disclaimer: I am not responsible for any lost data

Execute the following commands on the standby as the postgres user. Note that you may be prompted for a password, and please make sure you update the *config* section at the top of the script with your information:

###########################################
### config
MASTER_IP="INSERT_HERE"
REPLICATION_USER_PASSWORD="INSERT_HERE"
###########################################
### create recovery.conf
echo "
standby_mode = 'on'
primary_conninfo = 'host=$MASTER_IP port=5432 user=replication_web password=$REPLICATION_USER_PASSWORD
sslmode=require'
" > /var/lib/postgresql/9.4/main/recovery.conf
###########################################
### stop postgres
/etc/init.d/postgresql stop
###########################################
### remove old files
cd /var/lib/postgresql/9.4/
rm -Rf main/
###########################################
### sync all files except the xlogs
rsync -av --exclude main/postmaster.pid --exclude main/pg_xlog --exclude main/postgresql.pid $MASTER_IP:/var/lib/postgresql/9.4/ .
###########################################
### sync the xlogs
cd /var/lib/postgresql/9.4/main/
rsync -av --exclude postgresql.conf --exclude postgresql.pid $MASTER_IP:/var/lib/postgresql/9.4/main/pg_xlog .
###########################################
### start postgres
/etc/init.d/postgresql start

 

 

This script will stop postgres, delete the old slave data files, configure the standby to connect to the master, copy over the databases, and restart the postgres server.

Step 5 – Monitor & Test

You’ll want to watch the logs with the following command to make sure that the slave did not have any connection issues when it started up:

tail -f /var/log/postgresql/postgresql-9.4-main.log

Here are some queries you can use to monitor the streaming replication between your two machines:

###########################################
## returns true when replication is running
select pg_is_in_recovery() ;

###########################################
## various stats about your replication thread.
select * from pg_stat_replication;

###########################################
## how far behind your standby thread is from the master
select now() - pg_last_xact_replay_timestamp() AS replication_delay;

###########################################
## information about your last replication transactions
select pg_last_xact_replay_timestamp();
select pg_last_xlog_receive_location();
select pg_current_xlog_location();