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
[root@krishna sf_shared]# cp xdbreplicationserver-6.2.18-1-linux-x64\ \(1\).run /var/lib/edb/tool
[root@krishna sf_shared]# cd /var/lib/edb/tool
[root@krishna tool]# ls
xdbreplicationserver-6.2.18-1-linux-x64 (1).run
[root@krishna 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.
[root@krishna ~]# cd /u01/app/oracle/product/12.2.0/dbhome_1/jdbc/lib
[root@krishna lib]# ls
ojdbc8dms_g.jar ojdbc8dms.jar ojdbc8_g.jar ojdbc8.jar simplefan.jar
[root@krishna 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
[root@sdbt ~]# systemctl restart edb-xdbpubserver
[root@sdbt ~]# 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.
Leave A Comment