11

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?

Improve this question
6
  • 1
    pg_dump usually takes care of this. How exactly (what parameters) was the backup taken and how exactly are you restoring?
    – ypercubeᵀᴹ
    Oct 15, 2015 at 10:04
  • Up to now I've been successfully using a syntactic schema such as pg_dump app_environment > /archive/yymmdd.sql then on restore psql app_environment < /archive/yymmdd.sql
    – Jerome
    Oct 15, 2015 at 10:18
  • 4
    I'm not sure about the default settings of 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 with pg_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.
    – ypercubeᵀᴹ
    Oct 15, 2015 at 10:28
  • 2
    @ypercube is right. You have the option he mentions, or you can remove the foreign key constraint before restoring and recreate it afterwards. In a full dump, these are being created after the data is loaded into the tables.
    – dezso
    Oct 15, 2015 at 10:38
  • 2
    Also, pg_dump -Fc and pg_restore are almost always the way to go in preference to using SQL dumps and psql. That way you can specify things like --clean at load-time.
    – Craig Ringer
    Oct 15, 2015 at 11:28

2 Answers 2

Reset to default
10

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.

Improve this answer
3
  • 4
    Danger, Will Robinson! This setting is designed for very low-level tools that need to be able to disable system rules, triggers and constraints in Postgres while they do their work. End users should never use this.
    – Colin 't Hart
    May 24, 2018 at 8:35
  • 6
    Yes, there is danger associated with this setting. But is is there for replication purposes and that IMHO includes restoration of a DB. Therefore I don't see this as an abuse of the setting.
    – achimh
    May 25, 2018 at 9:20
  • 2
    @Colin'tHart - it's terribly unhelpful when you point out a problem and don't offer an alternative solution or workaround. Did you have a suggested solution if this one is dangerous?
    – karns
    Jan 21, 2020 at 20:21
-1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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