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?
pg_dump app_environment > /archive/yymmdd.sql
then on restorepsql app_environment < /archive/yymmdd.sql
pg_dump
and restore. But I think the errors are because you are not dropping and recreating the database on restore. But wait for more knowledgeable (in postgres) users to answer properly. I would try restoring withpg_restore
with the--clean
option (assuming you want to restore from the dump and not keep anything in the current db, where you are restoring to.pg_dump -Fc
andpg_restore
are almost always the way to go in preference to using SQL dumps andpsql
. That way you can specify things like--clean
at load-time.