Oracle -> Postgresql Log-Based replication

Question

(I do not code on my own, to make things clear)

I am looking for a solution that would allow to replicate data between a, master, Oracle 11g DB and a new PostgreSQL DB. Those are 2 different applications but the need to exchange data in real-time. There are some trigger-based ways but there is quite a big concern that this can affect the master DB efficiency - which we can't do. I have also come across some log-based solutions, like HVR, but the cost is way too high for 500MB of data to be replicated. Maybe anyone of You had a similar issue and found a way to deal with it?

Any kind of tips and help will be really appreciated as I am quite short on time

You could create a foreign data wrapper in Postgres that reads and writes to the tables in Oracle — , Feb 10 '17 at 10:19
Another option would be http://ha-jdbc.org/ if your applications are using JDBC/Java — , Feb 10 '17 at 11:02
I have talked to my Devs about the FDW way, but they say this will be very inefficient dut to views that have joined tables :/ — czuczer, Feb 10 '17 at 12:45

Vao Tsun · Answer 1 · 2017-02-10T10:46:36.370

Oracle Archive Logs have different format than Postgres Write Ahead Logs. Despite the general similarity in concept of Oracle Streams, SQL Log Shipping, Postgres Streaming Replication etc, transaction logs <> redo logs <> xlogs and you can't use one provider logs to roll on the other provider engine.

Moreover you can't roll logs over same DB provider different version because of difference in binary format.

Something alike logical replication you can get with Postgres Logical Decoding, Oracle GoldenGate, Heterogeneous Database Replication, AWS DMS. But none of above gives you "Log-Based replication" between different db vendors

I think I should also add that the replication has to take place from Oracle to Postgres not the other way around. — czuczer, Feb 10 '17 at 10:39

score 0 · Answer 2 · answered Oct 04 '18 at 00:56

You can use a product that specializes in change data capture based data integration. Striim, GoldenGate, Attunity allow you to do CDC from Oracle. Striim also allows you to do CDC from PostgreSQL and write to Oracle as well.

https://striim.com

https://attunity.com

Oracle -> Postgresql Log-Based replication

2 Answers2