Restoring from pg_dump with foreign key constraints

Posted on

Question :

In restoring a database from a pg_dump, a number of errors are being generated and the whole table is subsequently being ignored. An example:

ERROR:  insert or update on table "channelproducts" violates foreign key constraint "fk_rails_dfaae373a5"
DETAIL:  Key (channel_id)=(1) is not present in table "channels".

Interestingly enough, I’ve noted that all these instances are popping up because of the sequence of loading. channels is after channelproducts both alphabetically and in the file and thus I can understand why postgres complains about having to create a child without a parent.

caveat: the foreign key is being generated automatically by rails 4.2: I could remove the problem at the source but that still does not really solve the problem…

version: PostgreSQL 9.4.4.

How can one then restore from psql with cases of foreign key constraints, if the database tables and columns are already created?

Answer :

You can put SET session_replication_role = replica;at the top of your SQL file. This will ignore constraints during data insertion As the setting is session-based, constraints will continue to work outside this script.

But beware: if you create inconsistent data while this setting is active, postgres will keep them. Constraints are only ever checked at insert/update time.

See for a deeper discussion of the pros and cons of this approach.

Try creating new database with empty table with in it and restore the db. The problem probably can be with the existing relation between table.

Leave a Reply

Your email address will not be published. Required fields are marked *