Postgres and Migrating Databases

So, it's doable to transfer all data from an Oracle database structure into a similar (hopefully exactly the same) database structure in Postgres.

One of the problems you encounter, is the Foreign Keys. These will get triggered when inserting data .

Now, in a perfect scenario, you know that the Oracle database structure, which also has the same Foreign Keys, is fine, so we won't need to check the Foreign Keys in Postgres when inserting data.

You cannot turn off Foreign Keys in Postgres, however, Postgres automatically creates system triggers for Foreign Keys that check if referential integrity is maintained.

So it simply comes down to temporarily disabling those triggers.

ALTER TABLE some_table DISABLE TRIGGER ALL;

And afterwards, enable them again.

ALTER TABLE some_table ENABLE TRIGGER ALL;

Problem

But what is not all data was successfully transferred? (due to network problems for instance). Now you have a Postgres database that no longer has referential integrity in all cases and you might not find out about this until God knows when.

So, you need to check afterwards if all the Foreign Keys are accounted for, i.e. does every value for a foreign key have an equivalent row in the related table?

I found a script for this at [1].

References

[1] Github.com - enrico-lt/ForeignKeyCheck

https://github.com/enrico-lt/ForeignKeyCheck