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?