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=# |
