Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
I went to couple times of PgSQL meetup around NYC.
Anyone has done Oracle DB migration over to PgSQL?
Yes
Many tools are available for migration. one which i used enterprisedb migration tool kit.