Postgresql streaming replication

If you read the postgresql streaming replication documents it sounds really really complicated, but it isn't. There is a clever little tool called repmgr that can set up multiple slaves for you quite easily.  repmgr can also take care of promoting a slave to a master automatically if needed but you still have to tell the database clients that the server has changed.

Getting started

You need two servers with postgresql installed. For this discussion we will use 9.3 but you can use 9.4 as well. If you have an RPM based system such as Centos 7 it goes like this:

 yum -y install http://yum.postgresql.org/9.3/redhat/rhel-7-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
yum -y groupinstall "PostgreSQL Database Server 9.3 PGDG" 
yum -y install repmgr93 

 

You will need a pair of SSH keys preferably without a password because repmgr uses rsync to copy a snapshot of the master server onto the slave. The key should belong to the postgres user which means it should be in /var/lib/pgsql/.ssh/ folder (a folder that you will most probably have to create). The id_rsa.pub should be in the authorized_keys file on the master. Don't forget to check the permissions and ownerships of this files SSH is very particular about it (chmod 700 .ssh and chmod 600 .ssh/*

Setting up the master

Yum placed a file named repmgr.conf in /etc/repmgr/9.3/repmgr/ for me. I decided the hell with it and created a file like this:

cluster=roadlk
node=1
node_name=192.168.1.1
conninfo='host=192.168.1.1 user=repmgr dbname=repmgr'
pg_bindir=/usr/pgsql-9.3/bin/

Note that in some places in the repmgr documentation the username is given as repmgr_usr in and other places it's repmr similarly the database name is given as repmgr_db or repmgr it doesn't matter what you choose for the username or db name but make sure that it's the same everywhere

Now register the master with repmgr repmgr -f /etc/repmgr/9.3/repmgr.conf --verbose master register a minor detail is that you will need to run this command as postgres user and repmgr will probably need to added to the path.

The slave

If you were to do a manual replication this is the tricky part. But thanks to repmgr it's a one liner (run as postgres).

repmgr -d repmgr -U repmgr --force --verbose standby clone 192.168.1.1

If you didn't do an initdb you don't need the --force option. Now here is the gotcha, the above command copies over the postgresql.conf and the pg_hba.conf files from the master. That means you will need to edit postgresq.conf and change the listen directive. Without it the server will try to listen on the wrong IP address.

Next steps are straightforward, create the repmgr.conf file

cluster=roadlk
node=2
node_name=node2
conninfo='host=192.168.1.2 user=repmgr dbname=repmgr'
pg_bindir=pg_bindir=/usr/pgsql-9.3/bin/

Start the server (systemctl start postgresql-9.3-service in Centos 7 /etc/init.d/postgresql start in older versions) and finally register the slave (run this command as postgres).

repmgr -f /path/to/repmgr/node2/repmgr.conf --verbose standby register

Now you can go and put the kettle on or whatever else it is you like to wear. The rest of this post is about what lead to my writing about postgresql replication.

For nearly two years road.lk operated with just log shipping WAL archives. We took a pg_basebackup about once a month (alright alright whenever I remembered to).  All the log files were rsynced to another machine but not applied. A warm standby it certainly wasn't. The logs just sat there in a folder waiting to be applied if needed. But we did test it once in a while though and the last time process took hours and hours, so after the carpool was launched I decided that streaming replication is the way to go. 

On Monday road.lk was offline for nearly an hour. It was due to the primary database server crashing, no data was lost but the automatic failover hadn't kicked in automatically. To make the failover automatic, you need to change the /etc/hosts file on the server and send a HUP signle to gunicorn but the tiny bash script that I wrote for this had a bug. It's just my luck that the outage occurred the very moment I stepped out and I am the only one with the keys to the server.