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.
Pre-Requisites:
- 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
Description:
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 :
|
1 2 3 |
firewall-cmd --zone=public --add-port=5444/tcp --permanent systemctl restart firewalld.service |
ii) Configuration of master server in postgresql.conf file
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 |
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
|
1 |
pg_ctl -D /opt/as9.6/data restart |
v) Take the data directory backup for replication using below command
|
1 |
./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:
|
1 |
touch recovery.conf |
|
1 2 3 4 5 6 7 |
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
|
1 |
hot_standby = on |
Save and exit
viii) Now goto the directory /pgadmin/as9.6/bin and start the cluster by using:
|
1 |
./pg_ctl –D /pgdata/BACKUP start |
To check replication status from master end
|
1 |
Select * from pg_stat_replication; |
To check replication status from slave end
|
1 |
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.
