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?
Best 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 https://www.endpoint.com/blog/2015/01/28/postgres-sessionreplication-role for a deeper discussion of the pros and cons of this approach.