disable constraints before using pg_restore.exe

Question

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 ?

Answer 1

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.