Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
Cross-posted here, in case anyone finds this later: stackoverflow.com/questions/12093654/… –  Craig Ringer Aug 24 '12 at 12:54

1 Answer 1

up vote 9 down vote accepted

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.

share|improve this answer
    
And it works like a charm... –  Stephane Rolland Aug 23 '12 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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