Infrastructure at your Service

Daniel Westermann

Replicating from Oracle to PostgreSQL with EDB Replication Server – 1 – A simple replication setup

When you want to offload reporting stuff from Oracle to PostgreSQL there are several solutions you can go for. There most widely known probably are Oracle GoldenGate and SharePlex. EnterpriseDB has its own solution for this which is called EDB Replication Server. As soon as you have one subscription from EnterpriseDB this tool is included and can be used to offload from Oracle to PostgreSQL. Lets do a simple replication setup and see how it works.

For this little demo I have to boxes: One running Oracle (12.1.0.2) and the EDB Replication Server publisher and the other one running PostgreSQL 9.6.2 and the EDB Replication server subscriber and the console.

The installation is quite simple: Download the software, unzip and execute as root:

[root@pgxdb ~]$ cd /var/tmp/
[root@pgxdb tmp]$ ls -la xdbreplicationserver-6.1.0-1-linux-x64.run 
-rw-r--r--. 1 root root 26934145 May 19 09:01 xdbreplicationserver-6.1.0-1-linux-x64.run
[root@pgxdb tmp]$ chmod +x xdbreplicationserver-6.1.0-1-linux-x64.run 
[root@pgxdb tmp]$ ./xdbreplicationserver-6.1.0-1-linux-x64.run 

These are the screenshots for the PostgreSQL box:
xdb_1
xdb_2
xdb_3
xdb_4
xdb_5
xdb_6
xdb_7
xdb_8
xdb_9
xdb_10
xdb_11
xdb_12
xdb_13

Once the installation completed you should be able to see what was installed:

[root@pgxdb tmp]$ su - postgres
Last failed login: Sun May 15 06:35:25 CEST 2016 from 192.168.22.1 on ssh:notty
There were 6 failed login attempts since the last successful login.
-bash-4.2$ ls -la
total 7928
drwxr-xr-x. 7 root root    4096 May 19 09:15 .
drwxr-xr-x. 3 root root      46 May 19 09:15 ..
drwxrwxr-x. 2 root root    4096 May 19 09:15 bin
drwxrwxr-x. 3 root root      78 May 19 09:15 etc
drwxrwxr-x. 4 root root      43 May 19 09:15 installer
drwxrwxr-x. 5 root root    4096 May 19 09:15 lib
drwxrwxr-x. 4 root root      44 May 19 09:15 scripts
-rwx------. 1 root root 7967605 May 19 09:15 uninstall-xdbreplicationserver
-rw-------. 1 root root   26455 May 19 09:15 uninstall-xdbreplicationserver.dat
-r--r--r--. 1 root root   64033 Feb 27 12:31 xdb_3rd_party_licenses.txt
-r--r--r--. 1 root root   15216 Feb 27 12:30 xdb_license.txt

Two new processes are running, which are basically the subscriber:

-bash-4.2$ ps -ef | grep postgres
postgres 13019     1  0 09:15 ?        00:00:00 /bin/bash -c cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin; ./runSubServer.sh  >> /var/log/edb/xdbsubserver/edb-xdbsubserver.log 2>&1 &
postgres 13044 13019  0 09:15 ?        00:00:00 /usr/bin/java -XX:-UsePerfData -XX:ErrorFile=/var/log/xdb-6.1/subserver_pid_%p.log -Djava.awt.headless=true -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repserver.jar subserver 9052

For the Oracle box I did exactly the same except for this:

xdb_14
xdb_15

Same picture, two new processes which are the publisher:

oracle   29542     1  0 09:48 ?        00:00:00 /bin/bash -c cd /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin; ./runPubServer.sh  >> /var/log/edb/xdbpubserver/edb-xdbpubserver.log 2>&1 &
oracle   29567 29542  0 09:48 ?        00:00:00 /usr/bin/java -XX:-UsePerfData -Xms256m -Xmx1536m -XX:ErrorFile=/var/log/xdb-6.1/pubserver_pid_%p.log -Djava.library.path=/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin -Djava.awt.headless=true -jar /opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/bin/edb-repserver.jar pubserver 9051

Lets start the console to bring up the graphical user interface:

-bash-4.2$ bin/runRepConsole.sh

Register a new subscription server (PostgreSQL box):

xdb_16
xdb_17

Register a new publication server (Oracle box):

xdb_18
xdb_19

Before proceeding from here you should download the Oracle JDBC driver and put it to both boxes:

-bash-4.2$ pwd
/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/lib/jdbc
-bash-4.2$ ls -l ojdbc*.jar
-rw-r--r--. 1 root root 3698857 May 19 10:45 ojdbc7.jar

Remember to restart the EDB Replication Server services:

sudo systemctl restart edb-xdbpubserver.service
sudo systemctl restart edb-xdbsubserver.service

After that add the Oracle database (I am using the scott/tiger sample schema here):
xdb_20
xdb_21
xdb_22

Ok, easy to fix:

grant connect,resource,create trigger to scott;
Grant succeeded.

Once again:
xdb_23

The next thing is to create the publication itself:
xdb_24
xdb_25

xdb_26

Ok, lets fix it:

alter table scott.bonus add constraint bonus_pk primary key ( ename,job);
Table altered.

xdb_27

Ok, lets fix it:

alter table scott.salgrade add constraint salgrade_pk primary key (grade,losal,hisal);
Table altered.

xdb_28
xdb_29

Done.

Lets create the subscription, but before doing that make sure that you put the details of the controller database (The Oracle one in my case) to the EDB Replication Server configuration file:

postgres@pgxdb:/opt/PostgreSQL/EnterpriseDB-xDBReplicationServer/ [xdbrepl] cat /etc/edb-repl.conf
admin_password=VbjDPk5zwu/naXI2fpEbBQ\=\=
user=scott
port=1521
password=H4rzJCNHH/Q\=
type=oracle
admin_user=admin
host=192.168.22.242
database=servicename\=DB4.it.dbi-services.com

Proceed with creating the subscription database:
xdb_30
xdb_31
xdb_32
xdb_33
xdb_34

All the table are there now in the PostgreSQL instance but empty:

pgxdb/postgres MASTER (postgres@5432) # \d SCOTT.*
Table "scott.bonus"
┌────────┬───────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
├────────┼───────────────────────┼───────────┤
│ ename │ character varying(10) │ not null │
│ job │ character varying(9) │ not null │
│ sal │ numeric │ │
│ comm │ numeric │ │
└────────┴───────────────────────┴───────────┘
Indexes:
"bonus_pk" PRIMARY KEY, btree (ename, job)

Index "scott.bonus_pk"
┌────────┬───────────────────────┬────────────┐
│ Column │ Type │ Definition │
├────────┼───────────────────────┼────────────┤
│ ename │ character varying(10) │ ename │
│ job │ character varying(9) │ job │
└────────┴───────────────────────┴────────────┘
primary key, btree, for table "scott.bonus"

Table "scott.dept"
┌────────┬───────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
├────────┼───────────────────────┼───────────┤
│ deptno │ numeric(2,0) │ not null │
│ dname │ character varying(14) │ │
│ loc │ character varying(13) │ │
└────────┴───────────────────────┴───────────┘
Indexes:
"pk_dept" PRIMARY KEY, btree (deptno)

Table "scott.emp"
┌──────────┬───────────────────────┬───────────┐
│ Column │ Type │ Modifiers │
├──────────┼───────────────────────┼───────────┤
│ empno │ numeric(4,0) │ not null │
│ ename │ character varying(10) │ │
│ job │ character varying(9) │ │
│ mgr │ numeric(4,0) │ │
│ hiredate │ date │ │
│ sal │ numeric(7,2) │ │
│ comm │ numeric(7,2) │ │
│ deptno │ numeric(2,0) │ │
└──────────┴───────────────────────┴───────────┘
Indexes:
"pk_emp" PRIMARY KEY, btree (empno)

Index "scott.pk_dept"
┌────────┬──────────────┬────────────┐
│ Column │ Type │ Definition │
├────────┼──────────────┼────────────┤
│ deptno │ numeric(2,0) │ deptno │
└────────┴──────────────┴────────────┘
primary key, btree, for table "scott.dept"

Index "scott.pk_emp"
┌────────┬──────────────┬────────────┐
│ Column │ Type │ Definition │
├────────┼──────────────┼────────────┤
│ empno │ numeric(4,0) │ empno │
└────────┴──────────────┴────────────┘
primary key, btree, for table "scott.emp"

Table "scott.salgrade"
┌────────┬─────────┬───────────┐
│ Column │ Type │ Modifiers │
├────────┼─────────┼───────────┤
│ grade │ numeric │ not null │
│ losal │ numeric │ not null │
│ hisal │ numeric │ not null │
└────────┴─────────┴───────────┘
Indexes:
"salgrade_pk" PRIMARY KEY, btree (grade, losal, hisal)

Index "scott.salgrade_pk"
┌────────┬─────────┬────────────┐
│ Column │ Type │ Definition │
├────────┼─────────┼────────────┤
│ grade │ numeric │ grade │
│ losal │ numeric │ losal │
│ hisal │ numeric │ hisal │
└────────┴─────────┴────────────┘
primary key, btree, for table "scott.salgrade"

pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.salgrade;
┌───────┐
│ count │
├───────┤
│ 0 │
└───────┘
(1 row)

Time: 0.443 ms
pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.depts;
ERROR: relation "scott.depts" does not exist
LINE 1: select count(*) from SCOTT.depts;
^
Time: 0.554 ms
pgxdb/postgres MASTER (postgres@5432) # select count(*) from SCOTT.dept;
┌───────┐
│ count │
├───────┤
│ 0 │
└───────┘
(1 row)

Time: 0.298 ms

To do the initial synchronization do a Snapshot:

xdb_35
xdb_36
xdb_37

All the data is there:

pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.dept;
┌────────┬────────────┬──────────┐
│ deptno │ dname │ loc │
├────────┼────────────┼──────────┤
│ 10 │ ACCOUNTING │ NEW YORK │
│ 20 │ RESEARCH │ DALLAS │
│ 30 │ SALES │ CHICAGO │
│ 40 │ OPERATIONS │ BOSTON │
└────────┴────────────┴──────────┘
(4 rows)

Time: 0.435 ms
pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.emp;
┌───────┬────────┬───────────┬──────┬────────────┬─────────┬─────────┬────────┐
│ empno │ ename │ job │ mgr │ hiredate │ sal │ comm │ deptno │
├───────┼────────┼───────────┼──────┼────────────┼─────────┼─────────┼────────┤
│ 7369 │ SMITH │ CLERK │ 7902 │ 1980-12-17 │ 800.00 │ NULL │ 20 │
│ 7499 │ ALLEN │ SALESMAN │ 7698 │ 1981-02-20 │ 1600.00 │ 300.00 │ 30 │
│ 7521 │ WARD │ SALESMAN │ 7698 │ 1981-02-22 │ 1250.00 │ 500.00 │ 30 │
│ 7566 │ JONES │ MANAGER │ 7839 │ 1981-04-02 │ 2975.00 │ NULL │ 20 │
│ 7654 │ MARTIN │ SALESMAN │ 7698 │ 1981-09-28 │ 1250.00 │ 1400.00 │ 30 │
│ 7698 │ BLAKE │ MANAGER │ 7839 │ 1981-05-01 │ 2850.00 │ NULL │ 30 │
│ 7782 │ CLARK │ MANAGER │ 7839 │ 1981-06-09 │ 2450.00 │ NULL │ 10 │
│ 7788 │ SCOTT │ ANALYST │ 7566 │ 1987-04-19 │ 3000.00 │ NULL │ 20 │
│ 7839 │ KING │ PRESIDENT │ NULL │ 1981-11-17 │ 5000.00 │ NULL │ 10 │
│ 7844 │ TURNER │ SALESMAN │ 7698 │ 1981-09-08 │ 1500.00 │ 0.00 │ 30 │
│ 7876 │ ADAMS │ CLERK │ 7788 │ 1987-05-23 │ 1100.00 │ NULL │ 20 │
│ 7900 │ JAMES │ CLERK │ 7698 │ 1981-12-03 │ 950.00 │ NULL │ 30 │
│ 7902 │ FORD │ ANALYST │ 7566 │ 1981-12-03 │ 3000.00 │ NULL │ 20 │
│ 7934 │ MILLER │ CLERK │ 7782 │ 1982-01-23 │ 1300.00 │ NULL │ 10 │
└───────┴────────┴───────────┴──────┴────────────┴─────────┴─────────┴────────┘
(14 rows)

To synchronize changes:
xdb_38

xdb_39

To schedule synchronization:

xdb_40
xdb_41
xdb_42
xdb_43

Test it, in Oracle:

insert into scott.dept values ( 50,'My_Dept','Home');
1 row created.
commit;
Commit complete.

PostgreSQL:

pgxdb/postgres MASTER (postgres@5432) # select * from SCOTT.dept;
┌────────┬────────────┬──────────┐
│ deptno │ dname │ loc │
├────────┼────────────┼──────────┤
│ 10 │ ACCOUNTING │ NEW YORK │
│ 20 │ RESEARCH │ DALLAS │
│ 30 │ SALES │ CHICAGO │
│ 40 │ OPERATIONS │ BOSTON │
│ 50 │ My_Dept │ Home │
└────────┴────────────┴──────────┘
(5 rows)

Quite easy to setup and to do an initial simple replication. In a future post we’ll look at more specific use cases. Hope this helps to get you started.

 

2 Comments

Leave a Reply


6 × = fifty four

Daniel Westermann
Daniel Westermann

Senior Consultant and Technology Leader Open Infrastructure