Real time replication with PostgreSQL Part 2

Bespoke Software

In this second part of our two part post on real time data replication in PostgreSQL we're going to look at a simple setup of ‘hot standby’ master-slave replication in PostgreSQL.

For the purpose of demonstration we've setup a test rig with two virtual servers. The master server is on IP 192.168.1.100, and the standby is on 192.168.1.101.

The step by step setup was:

  1. We installed ubuntu 15.10 on each server, applied updates and then installed postgres-server from the apt repository.

  2. Then on the master we restored a copy of an application database by simply piping in a backup (taken using pg_dump).

  3. Next, we edited the postgresql.conf file on the master to set the type of replication we want to perform.

  4. 	wal_level = hot_standby
    	max_wal_senders = 3
    

  5. Then we edited the pg_hba.conf file on the master to indicate which server has permissions to replicate by adding the following line:

  6. 	host    replication     all     192.168.1.101/32    trust
    

  7. We restarted the PostgreSQL service on the master.

  8. The next step was to delete all the files in the /var/lib/postgres directory on the standby server. Then we used the pg_basebackup command (logged in as the postgres user) to take a 'starting point' copy of the master:

  9. 	cd /var/lib/postgresql/9.4/main
    	rm -rf *
    	pg_basebackup -h 192.168.1.100 -D /var/lib/postgresql/9.4/main/ -X stream
    

  10. Finally we created a config file named recovery.conf in /var/lib/postgresql/9.4/main to give the server its standby configuration:

  11. 	standby_mode = 'on'
    	primary_conninfo = 'host=192.168.1.100'
    

We also gave the standby server PostgreSQL service a restart for good measure. To test it we inserted and updated a few rows on the master, queried the standby and lo and behold there was the data 'real time replicated'.

PostgreSQL binary replication was pretty easy to setup, however before using it as a reliable backup option you'd want to stress test it and try out a few nasties, such as dropping the network connection between servers and checking replication catches up, also keeping a periodical pg_dump would still never be a bad idea!


With bespoke software, SkillsLogic can help you streamline processes, improve collaboration and analyse your data.

Talk to us today and find out how we can help transform your business.


Contact Us

Contact Us

SkillsLogic Ltd,
Cooper Buildings,
Sheffield Technology Park,
Arundel Street,
Sheffield,
S1 2NS

+44 (0)114 383 0093
info@skillslogic.com


Follow us on Twitter: