Real time replication with PostgreSQL Part 2
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:
- We installed ubuntu 15.10 on each server, applied updates and then installed postgres-server from the apt repository.
- Then on the master we restored a copy of an application database by simply piping in a backup (taken using pg_dump).
- Next, we edited the postgresql.conf file on the master to set the type of replication we want to perform.
- Then we edited the pg_hba.conf file on the master to indicate which server has permissions to replicate by adding the following line:
- We restarted the PostgreSQL service on the master.
- 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:
- Finally we created a config file named recovery.conf in /var/lib/postgresql/9.4/main to give the server its standby configuration:
wal_level = hot_standby max_wal_senders = 3
host replication all 192.168.1.101/32 trust
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
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!