Replication(Master/Slave) of PostgreSQL database

13 Wednesday Jul 2016

Before you proceed this handy procedure, be sure you take backup of the databases.

pg_dumpall > dump.sql

Prerequisite:

1. Two Ubuntu(14.04) instances: Master(187.12.0.1)
2. Slave(187.12.0.2)
3. PostgreSQL 9.4.x

Steps to be followed on MASTER:

Step 1: Create a replication user on the master with REPLICATION permissions:

sudo -u postgres createuser -U postgres replicator -P -c 5 --replication

-P prompts you for the new user’s password. -c sets a limit for the number of connections for the new user. The value 5 is sufficient for replication purposes.

–replication grants the REPLICATION privilege to the user named replication.

Step 2: Configure master for streaming replication

vi /etc/postgresql/9.4/main/postgresql.conf listen_address = '' wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8

Here each wal_keep_segments is of 16MB. If you expect your database to have more than 128MB of changes in the time it will take to make a copy of it across the network to your slave, or in the time you expect your slave to be down for maintenance or something, then consider increasing those values.
Step 3: Configure master to allow the connection from the slave

vi /etc/postgresql/9.4/main/pg_hba.conf
#if your server is not ssl ready, use hostnossl
hostssl replication replicator 187.12.0.2 md5 Or hostnossl replication replicator 187.12.0.2 md5

here hostssl means this host can only connect via SSL.

Restart the server now.

Steps to be followed on SLAVE:

Step 1: Configure master for streaming replication

vi /etc/postgresql/9.4/main/postgresql.conf wal_level = hot_standby max_wal_senders = 3 checkpoint_segments = 8 wal_keep_segments = 8 hot_standby = on

No change required for pg_hba.conf

Step 2: Copy the database from master and begin replication

#Stop server

sudo service postgresql stop

#Clean up old cluster directory
ATTENTION: script is going to delete the old database cluster on your slave.

sudo -u postgres rm -rf /var/lib/postgresql/9.4/main

#Start base backup as replicator with pg_basebackup command

sudo -u postgres pg_basebackup -h 187.12.0.1 -D /var/lib/postgresql/9.4/main -U replicator -v -P
sudo -u postgres bash -c "cat > /var/lib/postgresql/9.4/main/recovery.conf <<- _EOF1_ standby_mode = 'on' primary_conninfo = 'host=187.12.0.1 port=5432 user=replicator password=PASSWORD sslmode=require' trigger_file = '/tmp/postgresql.trigger' _EOF1_ " NOTE: remove sslmode=require is ssl is not configured for the server.

#Start the PostgreSQL server

sudo service postgresql start

Great, we have completed the replication of PostgreSQL database. To make sure everything working as expected execute and watch:

sudo -u postgres psql -x -c "select * from pg_stat_replication;"

Happy learning.

Originally published at mysteps4learning.wordpress.com on July 13, 2016.