I'm using AWS Aurora Postgres and using DMS to migrate from RDS postgres to Aurora PG. In order to perform the FULL LOAD I want to disable foreign key constraints and triggers on all the objects. I'm able to disable triggers but couldn't find a way to disable constraints.
Below doesn't work:
ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey;
It throws:
ERROR: syntax error at or near "CONSTRAINT" LINE 1: ALTER TABLE so_items DISABLE CONSTRAINT so_items_so_id_fkey; ^ SQL state: 42601 Character: 30
Setting "session_replication_role" = "replica" in the parameter group didn't work. While the DMS task tries to truncate the table part of preparation it still fails with foreign key violation errors.
Please advise any workarounds.
Note: I couldn't do below since in RDS I do not have permissions to do so even with master account:
alter table so_items disable trigger ALL;
ERROR: permission denied: "RI_ConstraintTrigger_c_16520" is a system trigger SQL state: 42501
You cannot disable constraints. This would make no sense.
You may DROP CONSTRAINT
s
ALTER TABLE so_items DROP CONSTRAINT so_items_so_id_fkey;
which will delete it permanently, or defer the constraint check to the end of transactions:
ALTER TABLE so_items ALTER CONSTRAINT so_items_so_id_fkey DEFERRABLE INITIALLY DEFERRED;
With that modification the constraint is evaluated after a modification at the end of the current transaction. This will allow you to break the constraint inside of an transaction.
You shouldn't modify the triggers a Postgres constraint relies on. This is an implementation detail for which you shouldn't care about.
Edit: It is also possible to disable the triggers which also affects the foreign key constraints of the table
ALTER TABLE so_items DISABLE TRIGGER ALL;
But when you are re-enabling the triggers afterwards, the foreign keys are not checked. This might lead to invalid / inconsistent foreign keys in the database.