Thread: Materialized view from PG to Oracle?
Good day. We are transitioning over one database to Postgres as a test but retain an Oracle presence. The PG db in question is (it is currently still running Oracle) feeds a couple other Oracle dbs via materialized view logs and materialized views. Is it possible to achieve the same MV functionality in PG (i.e., have it feed Oracle via MVs)?
Thank you
Thank you
2014-03-12 12:00 GMT+09:00 Roy Anderson <roy.anderson@gmail.com>: > Good day. We are transitioning over one database to Postgres as a test but > retain an Oracle presence. The PG db in question is (it is currently still > running Oracle) feeds a couple other Oracle dbs via materialized view logs > and materialized views. Is it possible to achieve the same MV functionality > in PG (i.e., have it feed Oracle via MVs)? Not directly. The reverse would be possible at SQL level with the Oracle FDW (see: http://pgxn.org/dist/oracle_fdw/ ) but otherwise you'd need some kind of custom script/cronjob which reads the Postgres materialised view and imports it to Oracle. Regards Ian Barwick
Thank you for the info Ian! Maybe I'm looking at this from the wrong perspective... Please review below.
My Current process:
Oracle DB1
|--->materialized view log (records changes to table X)
Oracle DB2
|--->materialized view (pulls data from mv log every 3 minutes)
My Future process:
PostgreSQL DB1
|---> "something" records changes to table X
Oracle DB2
|---> "something" pulls data every 3 minutes
Why I really like the current process is because it is so light-weight in terms of system resources. Do you (or anyone else) have a recommendation for my situation?
Thank you for your time. I sincerely appreciate it.
-Roy Anderson
On Wed, Mar 12, 2014 at 9:28 AM, Ian Lawrence Barwick <barwick@gmail.com> wrote:
2014-03-12 12:00 GMT+09:00 Roy Anderson <roy.anderson@gmail.com>:Not directly. The reverse would be possible at SQL level with the Oracle FDW> Good day. We are transitioning over one database to Postgres as a test but
> retain an Oracle presence. The PG db in question is (it is currently still
> running Oracle) feeds a couple other Oracle dbs via materialized view logs
> and materialized views. Is it possible to achieve the same MV functionality
> in PG (i.e., have it feed Oracle via MVs)?
(see: http://pgxn.org/dist/oracle_fdw/ ) but otherwise you'd need some kind
of custom script/cronjob which reads the Postgres materialised view and
imports it to Oracle.
Regards
Ian Barwick