Configuration of PostgreSQL Database Replication

 

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 :

 

ii) Configuration of master server in postgresql.conf file

 

iii) Configuration of master server in pg_hba.conf file:

Add ip address of both servers In IPv4 Section:

 

iv) Now restart the server using below command

 

v) Take the data directory backup for replication using below command

 

vi) Go to the new ‘/pgdata/BACKUP’ directory and create the new recovery file ‘recovery.conf’ with touch:

 

vii) Edit postgresql.conf file

Save and exit

 

viii) Now goto the directory /pgadmin/as9.6/bin and start the cluster by using:

 

 To check replication status from master end

 

To check replication status from slave end

If the o/p is true then it is in recovery mode.

If the o/p is false then it is out of recovery.

Nikhil Palle
Author: Nikhil Palle

2 comments

  1. I went to couple times of PgSQL meetup around NYC.
    Anyone has done Oracle DB migration over to PgSQL?

Leave a Reply

Your email address will not be published. Required fields are marked *