14

When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:

ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"

This is obviously due to the fact that I've emptied the data base before restoring it from the dump file (this file comes from a production database)... then of course no foreign keys constraint can be ok if one referenced table is empty...

Is there a way to disable the constraints and all foreign keys, for all tables, before I call pg_restore.exe, and afterwards, re-enable the constraints and foreign keys.

In SO I found something interesting: deferring constraint checking to commit time. But I don't think I can call pg_restore.exe from inside psql.exe after deferring the constraints.

There is also this post, dating back 10 years ago, suggesting to drop then re-add the constraints. Or to change the value of pg_class reltriggers to 0 and that would also be possible for constraints... but I'm afraid it is more hacking than good practice...

What do you advise, what is the best practice in this case ? Is using pg_dump.exe with the -clean flag creates a dump that bypasses the constraints checking when restoring the database ?

26

Have you tried the --disable-triggers option to pg_restore?

Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.