Is there a way to replicate data(like triggers or jobs) from oracle tables to postgres tables and vice versa(for different set of tables) without using external tools? Just one way replication for both the scenarios.
5
-
1You could use a foreign data wrapper to access Oracle tables from within Postgresuser330315– user3303152019-11-14 11:55:25 +00:00Commented Nov 14, 2019 at 11:55
-
@a_horse_with_no_name Thanks but I need the tables in both oracle and postgres as oracle tables will no longer be used after few months.Mano– Mano2019-11-14 12:03:04 +00:00Commented Nov 14, 2019 at 12:03
-
1You can write triggers that push changes from Postgres into the Oracle tables. The other direction will be a bit complicated. Oracle has something similar to Postgres foreign data wrappers, called "Heterogeneous Services" - maybe you can get that to connect to Postgres through ODBC. jameshuangsj.wordpress.com/2019/05/10/…user330315– user3303152019-11-14 12:09:10 +00:00Commented Nov 14, 2019 at 12:09
-
Do you need to write to both systems? Or are you only writing to one of them and reading from the other?user330315– user3303152019-11-14 12:13:21 +00:00Commented Nov 14, 2019 at 12:13
-
@a_horse_with_no_name for 20 tables I need to replicate data from oracle to postgres. For 40 different tables, I need to replicate from postgres to oracle. So it's just one way replication but for both ora to pg & pg to oraMano– Mano2019-11-14 12:20:34 +00:00Commented Nov 14, 2019 at 12:20
Add a comment
|
2 Answers
Just a hint:
You can think of create a DB link from Oracle to Postgres which is called heterogeneous connectivity which makes it possible to select data from Postgres with a select statement in Oracle.
Then use materialized views to schedule and store the results of those selects.
As you don't want to use any external tool otherwise the solution should have been much simpler
2 Comments
Mano
Can you please suggest some open source tools to do this?
Marcus
If you want open source platform tool then you can use symmetricdsa or EDB Postgres which are quiet good and easy method for replication.. symmetricds.org ... enterprisedb.com/enterprise-postgres/… ........please mark the answer as acceptance if you think its answered
for 20 tables I need to replicate data from oracle to postgres. For 40 different tables, I need to replicate from postgres to oracle.
I could imagine the following setup:
For the Oracles tables that need to be accessible from Postgres, simply create foreign tables inside the Postgres server. They appear to be "local" tables in the Postgres server, but the FDW ("foreign data wrapper") will forward any request to the Oracle server. So no replication required. Whether or not this will be fast enough depends on how you access the tables. Some operations (WHERE clause, ORDER BY etc) can be pushed down to the Oracle server, some will be done by the Postgres server after all the rows have been fechted.
For the Postgres tables that need to be replicated to Oracle you could have a similar setup: create a foreign table that points to the target table in Oracle. Then create triggers on the Postgres table that will update the foreign table, thus sending the changes to Oracle.
This could all be managed on the Postgres side.
1 Comment
Mano
Thanks for the suggestion. But oracle_fdw is not supported in RDS Postgres which I use. Is there any alternate available?