EDB Postgres Replication Server (EPRS) provides a robust data platform that replicates between PostgreSQL databases in single-master or multi-master mode, or from non-PostgreSQL databases to PostgreSQL in single-master mode.

-bash-4.2$ cd /var/lib/edb/as13/data

-bash-4.2$ rm -rf *

-bash-4.2$ ls -lrt

total 0

in order for the EDB server to support the XDB replication the database is to be intitated by using “no-redwood-compat” as below

-bash-4.2$ ./initdb -D /var/lib/edb/as13/data –no-redwood-compat

-bash-4.2$ ./pg_ctl -D /var/lib/edb/as13/data start

waiting for server to start….2022-09-19 12:49:43 IST LOG:  redirecting log output to logging collector process

2022-09-19 12:49:43 IST HINT:  Future log output will appear in directory “log”.

 done

server started

Check if postgresql DB and Oracle DB is up and running

-bash-4.2$ ps -ef|grep postgres

postgres  1105     1  0 17:15 ?        00:00:00 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/

enterpr+  5091     1  0 17:37 ?        00:00:00 /usr/edb/as13/bin/edb-postgres -D /var/lib/edb/as13/data

-bash-4.2$ ps -ef|grep pmon

oracle    5607     1  0 17:42 ?        00:00:00 ora_pmon_dev

Go to Enterprisedb 🡪sign in🡪 downloads

Replication server🡪 download the file and move it to the shared folder

bash-4.2$ cd /var/lib/edb

-bash-4.2$ ls

as13

-bash-4.2$ mkdir tool

-bash-4.2$ chmod 700 tool

-bash-4.2$ chown -R enterprisedb. Tool

[[email protected] sf_shared]# cp xdbreplicationserver-6.2.18-1-linux-x64\ \(1\).run  /var/lib/edb/tool

[[email protected] sf_shared]# cd /var/lib/edb/tool

[[email protected] tool]# ls

xdbreplicationserver-6.2.18-1-linux-x64 (1).run

[[email protected] tool]# ./xdbreplicationserver-6.2.18-1-linux-x64\ \(1\).run

Set up name as user/password for tool as admin/admin

Leave the post number as it is and change user as enterprisedb and complete the setup

PreRequistes:

Set up the ipaddress in the pg_hba.conf

Set the listen_address=* in postgresql.conf

Setup and up the listener in oracle 

After the prerequisites is completed follow below steps.

SQL> create user muthu identified by muthu;

User created.

SQL> grant connect,resource,unlimited tablespace,create trigger, DBA to muthu;

Grant succeeded.

SQL> conn muthu/muthu

Connected.

In XDB replication, the table to be replicated should have atleast one primary key. Only then the tables can be replicated.

SQL> create table emp(id number(10) constraint pk_id primary key, name varchar2(20));

Table created.

SQL> insert into emp values(&id, ‘&name’);       

SQL> commit

  2  ;

Commit complete.     

SQL> select * from emp;

          ID NAME

———- ——————–

1 a

2 b

3 c

4 d

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME=’EMP’;

CONSTRAINT_NAME CONSTRAINT_TYPE TABLE_NAME

———-                            ———-                     ———-

FK_DEPTNO                          R                     EMP

PK_EMP                   P                     EMP

PK_ID                                 P                     EMP

Click on Application🡪postgres Plusaddons🡪XDB replication Console

Right click on “replication server” 🡪 “register Publication server” (publication sever means master server). Set the below values and save

HOST:ipaddress

PORT:9051

Username:admin

Password:admin

Click on  + symbol🡪SMR and set as below

When we click on test, it will show error

-bash-4.2$ cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer

-bash-4.2$ ls -lrt

total 8472

-r–r–r–. 1 root         root   15216 Mar 31 21:25 xdb_license.txt

-r–r–r–. 1 root         root   64009 Mar 31 21:25 xdb_3rd_party_licenses.txt

drwxrwxr-x. 4 root         root      45 Sep 20 12:28 installer

drwxrwxr-x. 5 root         root    4096 Sep 20 12:28 lib

drwx——. 2 enterprisedb root       6 Sep 20 12:29 xdata

drwxrwxr-x. 3 root         root    4096 Sep 20 12:29 etc

drwxrwxr-x. 4 root         root      31 Sep 20 12:29 scripts

drwxrwxr-x. 2 root         root    4096 Sep 20 12:29 bin

-rwx——. 1 root         root 8552634 Sep 20 12:29 uninstall-xdbreplicationserver

-rw——-. 1 root         root   21641 Sep 20 12:29 uninstall-xdbreplicationserver.dat

-bash-4.2$ pwd

/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/lib/jdbc

-bash-4.2$ ls -lrt

total 2552

-rw-r–r–. 1 root root 1040162 Mar 31 21:25 postgresql-42.3.2.jar

-rw-r–r–. 1 root root  317816 Mar 31 21:25 jtds-1.3.1.jar

-rw-r–r–. 1 root root 1250289 Mar 31 21:25 edb-jdbc18.jar

The jdbc file for oracle is missing so we need to add the file.

[[email protected] ~]# cd /u01/app/oracle/product/12.2.0/dbhome_1/jdbc/lib

[[email protected] lib]# ls

ojdbc8dms_g.jar  ojdbc8dms.jar  ojdbc8_g.jar  ojdbc8.jar  simplefan.jar

[[email protected] lib]# cp -rf ojdbc8d.jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/lib/jdbc/

“similary copy the ojdbc6.jar  ojdbc7.jar  also to the same location”

Then we need to restart edb-xdbpubserver and edb-xdbsubserver

[[email protected] ~]# systemctl restart edb-xdbpubserver

[[email protected] ~]# systemctl restart edb-xdbsubserver

Now if we try to register publication server and test, it will be success

Click on the added DB and click on “create publication”

Enter details and choose the created table as below

Now again Rightclick on replication servers and choose Register Subscription server

Now the subscription server is added, right click on the same and choose “add databse”

Add below details

Click on test

Once test is success, click on save

Now right click on the added server 🡪create subscription

Click on load and choose the publication server and click on create

Now if we login to EDB database and check we can see that the related schema “muthu” is created

-bash-4.2$ psql -d postgres

psql.bin (11.17.28)

Type “help” for help.

postgres=# \dn

          List of schemas

        Name         |    Owner     

———————+————–

 _edb_replicator_sub | enterprisedb

 muthu               | enterprisedb

 public              | enterprisedb

postgres=# set search_path to muthu;

SET

postgres=# \dt

          List of relations

 Schema | Name | Type  |    Owner     

——–+——+——-+————–

 muthu  | emp  | table | enterprisedb

(1 row)

postgres=# select * from emp;

 id | name 

—-+——

(0 rows)

Above we can see that, only table is created and it does not have any values. In order to populate the values we to synchronise

Right click on “sub” and we can see few options, initially we need to compulsorily do “snapshot” for the first time.

Now if we check . the table will be populated

postgres=# select * from emp;

 id | name 

—-+——

  1 | a

  2 | b

  3 | c

  4 | d

  5 | e

 (5 rows)

Now add more values to the table from the oracle database(publication server)

SQL> select * from emp;

ID NAME

———- ——————–

1 a

2 b

3 c

4 d

5 e

6 f

7 g

7 rows selected.

Right click on “sub” and choose “synchronize”

Now check in Postgresdb(subscription server), data will be synchronised.

postgres=# select * from emp;

 id | name 

—-+——

  1 | a

  2 | b

  3 | c

  4 | d

  5 | e

  6 | f

  7 | g

(7 rows)

EDB-XDB replication done successfully.