Postgresql MVIEW refresh from Oracle Materialized View Log


Is it possible for me to have Oracle MVIEW log in my source database and refresh a Postgresql database MVIEW using this MVIEW log? I know I can query the MVIEW log, I have done that. But since it is not a good practice to query a log, is it even possible to use an MVIEW log from Postgresql just like an Oracle MVIEW uses a log?

If that's not possible, is a dblink from Postgresql to Oracle possible?

Best Answer

You can use oracle_fdw to connect from PostgreSQL to Oracle and query a materialized view log. I have done that.

Back then I tried to implement replication that way. The problem that led me to give up the project was that the Oracle materialized view log is undocumented, and I could not get all the information I needed. But barring that, there should be no problem.