Setting up streaming replication in 9.5

Streaming Replication (SR) provides the capability to continuously ship and apply the WAL XLOG records to x number of standby servers in order to keep them in sync with primary node.

This blog will help you to easily setup a streaming replication for postgresql version 9.5

Primary : Represents primary db which will be our active node which can accept writes

 

Standby : Represents secondary db which will be our replica node which can accept only reads

 

Step 1: Create a replication user

 

Create a User for replication in the Primary. Don’t use superuser postgres in order to setup replication.

 

postgres=# CREATE USER replica WITH REPLICATION ENCRYPTED PASSWORD 'secret'; CREATE ROLE

 

Step 2: Add replication user entry on pg_hba.conf file

 

Allow replication connections from Standby to Primary by adding a similar line to the pg_hba.conf file of the Primary. 

 

host replication replica 192.168.1.20/32 md5

 

To get the changes into effect execute below query on postgresql db on Primary.

 

select pg_reload_conf();

Step 3: Clean data dir of Standby

 

Before proceeding further step make sure your Standby data dir is empty , you can take a backup of data dir and empty the contents of data dir

 

mkdir  /usr/local/pgsql/data_backup mv /usr/local/pgsql/data/*  /usr/local/pgsql/data_backup/   rm -rf /usr/local/pgsql/data/*

Step 4: Take a backup of Primary from standby (Receiver)

 

Use pg_basebackup  to backup the data directory of the Primary from the Standby. While creating the backup, you can also tell pg_basebackup  to create the replication specific files and entries in the data directory using "-R" (this will create the relevant recovery.conf file on Standby).

 

pg_basebackup -h 192.168.1.20 -U replica -p 5432 -D /usr/local/pgsql/data/ -P -Xs -R

 

Example O/p 

 

pg_basebackup -h 192.168.1.19 -U replica -p 5432 -D /usr/local/pgsql/data -P -Xs -R Password: 29857/29857 kB (100%), 1/1 tablespace

 

Step 5: Edit the postgresql.conf file on Standby

 

Edit the postgresql config file as it contains the same data as in Primary. We need to change some parameters on the standby node (ie Receiver). Below variables has to be considered

 

 

listen_addresses = 192.168.1.20   ## set your local ip of the server #hot_standby = on             # comment this to make it for read only

Step 6: Start Postgresql on Standby

 

Start the postgresql service on standby db and check the status

 

/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data   /usr/local/pgsql/bin/pg_ctl status -l logfile -D /usr/local/pgsql/data

Step 7: Testing replication

 

Below commands will verify the wal status

 

On Primary 

 

select pg_current_xlog_location();   ===> to see current wal lsn on Primary

select * from pg_stat_replication;  ===> to see repl status on Primary

 

On Receiver

select pg_last_xlog_receive_location(); ===> to see last received wal on Receiver

select pg_last_xlog_replay_location();  ===> to see last wal applied  on Receiver

 

If you try to create a db/table on standby you will get below error.

 

postgres=# create database test1; ERROR:  cannot execute CREATE DATABASE in a read-only transaction postgres=#