Maintaining Postgres copies of fdw tables from Oracle

Question

I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses, so as not to overload the production with analytics queries.

I have succesfully mirrored the neccessary Oracle views with FDW wrapper, so relevant Oracle structures are accessible, and I have a couple of functions and queries I run regularly, to copy oracle_fdw view data to Postgres tables.

As Oracle is used for a business system, which is maintained by external support company, they are occasionally tweaking some of the views I copy, in order to enable new functionality in the business system. For example, they are adding columns to Oracle tables and views, and/or extending some column sizes.

When that happens, the Oracle fdw views in Postgres need to be reimported, or else they fail when trying to fetch a column, in which place there is another column now, or the column sizes don't match anymore, or there are more columns in the actual Oracle structs than in the fdw imported view.

Reimport, however, still breaks the queries and/or functions which use constructs like:

INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;

because as most of Oracle structures I copy, are actually views, the new Oracle columns can and do sometimes appear in middle of others, instead of at end, like:

oracle_fdw_table: col1 new_col3 col2

which means even if I add column to my_pg_mirror_table_archive the above query will fail, as it will expect the column order col1 col2 new_col3.

I can circumvent that by manually specifying column order instead of SELECT * , however, the more tables the more possibility of mistakes and manual work.

In addition there are views on the pg_mirror_table_archive, which depend on it and need to be recreated after every such column addition/change.

Are there any tools or best practices that can automagically or semi-automatically maintain such relationship(s) and do the behind scenes work, so that my postgres tables always match the oracle ones?

Answer 1

I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses ...

Why move the data from Oracle to Postgres at all?

Why not use a Data Guard based solution to populate your Data Warehouse?

Three possibilities:

Logical Standby:

Data Guard applies SQL on the Standby (instead of the more efficient, Redo vectors).
Logical Standbys can be used to load-balance near real-time, read-only activity away from the Primary.
The Logical Standby database can be structurally different (i.e. different indexes, etc.) from the Primary ... and therein lies your problem if the vendor goes and changes stuff - this will not get carried through into the Logical Standby automatically. You would have to find out the changes and re-make them in the Standby.
Alternatively, you could rebuild the Logical Standby database after each Product release or build a new Logical Standby, route Users to that one and throw the old one away).

Active Data Guard:

This is a "cost option", but it might be your simplest solution.

Using Active Data Guard, your Physical Standby database becomes readable and is [very] near real-time up-to-date with the Primary.

The Standby database is structurally identical to the Primary.

Snapshot Standby:

Spends some of its life as an inaccessible, Physical Standby database (unless you pay for Active Data Guard), but when you want to use it, you "unplug" it from the Data Guard configuration and open it as a database in its own right, with Data as of when it was "unplugged". Each night, you "plug" it back into the Data Guard configuration and redo (held on the Standby's host) is re-applied, bringing the database back "up-to-date" with the Primary.
The Standby database is structurally identical to the Primary unless you choose to add extra indexes, etc., after the "unplugging" activity.

Reimport ... still breaks ... constructs like:

INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;

And this is just one of the reasons why should you never use "select *" in Production Code.