By using AWS re:Post, you agree to the Terms of Use

Oracle materialized view replication to postgres using DMS CDC

0

I am migrating data from Oracle materialized view to PostgreSQL using DMS CDC. Initial load completed successfully. I do not see the changes are replicating. Is any one has performed this type of migration? If Yes please share the details/document.

2 Answers
0

Dear Customer - Thanks for reaching out to us via AWS re:Post.

To begin with, I would like to inform you that AWS DMS supports only the migration (fullLoad) of materialized views to the target, the materialized views are migrated and created as "base tables" in the DMS target instance.

However, if you like to replicate the REFRESH of materialized views on source to target then it causes issues during CDC, as DMS currently does not support the on-going replication of materialized views.

As a way forward, you can use the base tables of the materialized views for on-going replication of changes to the target Oracle instance. Then if you need to use the materialized views on the Oracle target, you can recreate them using the migrated base tables.

Also, kindly note that AWS DMS doesn't support the ROWID data type or materialized views based on a ROWID column.

[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html#CHAP_Source.Oracle.Limitations

I hope that I was able to address your concerns

answered 25 days ago
0

instead of migrating materialized view from oracle to postgres target I would suggest create dms task only for underlying tables.

on target postgresql you can create materialized view so that it would have required data coming from underlying tables to populate. https://www.postgresql.org/docs/current/rules-materializedviews.html

If you migrate using DMS it will convert materialized view to table in postgresql and also it wont support migrating changes for this view.

answered 25 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions