Replication: Replication is a way of copying and distributing data and database objects from master to slave servers. These databases are synchronized to maintain consistency. Note that replication is not a replacement of backing up databases because changes to master also affect slave servers.
- 2 Computers with a working network connection.
- These computers should be installed with the same OS.
- These computers should be running the same EDB version.
- Fire walls should be enabled on both servers.
- ssh complete package should be pre-installed
The following are the IP address of master and slaves
Master server IP: 192.168.2.11
Slave server IP: 192.168.2.12
Configuration of database replication between Master and Slave Server.
Procedure: In Master Side
i) Enable the port 5444 from firewall using following command in both servers :
firewall-cmd --zone=public --add-port=5444/tcp --permanent
systemctl restart firewalld.service
ii) Configuration of master server in postgresql.conf file
wal_level = hot_standby /* to enable streaming replication */
archive_mode = on /* to enable archive process */
archive_command = cp -i %p /data/archive/as9.6/%f /* create my archive folder and give postgres permission */
max_wal_senders = 2 (This should be non-zero value) /* number of parallel wal senders to be initiated */
wal_keep_segments = 100
iii) Configuration of master server in pg_hba.conf file:
Add ip address of both servers In IPv4 Section:
host replication enterprisedb 192.168.2.11/32 trust (Master IP)
host replication enterprisedb 192.168.2.12/32 trust (Slave IP)
iv) Now restart the server using below command
pg_ctl -D /opt/as9.6/data restart
v) Take the data directory backup for replication using below command
./pg_basebackup -h 192.168.2.11 -p 5444 -D /pgdata/BACKUP -U enterprisedb -v -P
vi) Go to the new ‘/pgdata/BACKUP’ directory and create the new recovery file ‘recovery.conf’ with touch:
standby_mode = 'on' /* this parameter for read only transaction */
primary_conninfo = 'host=192.168.2.11 port=5444 user=enterprisedb password=admin' /* primary server connection information */
restore_command = 'scp /data/archive/as9.6/%f %p' /* we need to specify the archive path to restore the transaction from primary server */
trigger_file = '/tmp/trigger.failover' /* we need to create if primary goes down standby server will come up*/
vii) Edit postgresql.conf file
hot_standby = on
Save and exit
viii) Now goto the directory /pgadmin/as9.6/bin and start the cluster by using:
./pg_ctl –D /pgdata/BACKUP start
To check replication status from master end
Select * from pg_stat_replication;
To check replication status from slave end
Select * from pg_is_in_recovery();
If the o/p is true then it is in recovery mode.
If the o/p is false then it is out of recovery.