15

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?


How exactly (what parameters) was the backup taken and how exactly are you restoring?

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

1
  • The comments you indicated should be an answer have been moved to an answer (with attribution to the original authors as required).
    – Paul White
    Apr 12, 2023 at 10:28

4 Answers 4

18

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.

2
  • 6
    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. 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

When creating a backup with pg_dumpall or pg_dump it will create a plain sql backup and use the COPY command to inject the data into the relevant tables.

pg_dumpall -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql 
pg_dump -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql 

The COPY command is set to read from stdin with all the data being written to sdtin in one go.

COPY public.enduser (id, name) FROM stdin;
f659b7f9-813a-436b-89e4-e39abcf9fee7    asdf
35239537-e1f2-4cd5-8ef2-4dca5a94985c    ghij
e48f6e42-10e7-4805-a22d-693c39c56900    zxcv
39609d84-fb0f-4acf-adc6-1753f50e905d    yuiop
\.

If any of the entries fed to the single COPY command fails, all of them fail. A failure occurs if a given entry already exists in the given table.

If you manually edit the backup sql file and duplicate the COPY command, along with the delimeter for the input stream, for each entry it will succeed despite some entries already existing in the table. This is obviously impractical.

COPY public.enduser (id, name) FROM stdin;
f659b7f9-813a-436b-89e4-e39abcf9fee7    asdf
\.
COPY public.enduser (id, name) FROM stdin;
35239537-e1f2-4cd5-8ef2-4dca5a94985c    ghij
\.
COPY public.enduser (id, name) FROM stdin;
e48f6e42-10e7-4805-a22d-693c39c56900    zxcv
\.
COPY public.enduser (id, name) FROM stdin;
39609d84-fb0f-4acf-adc6-1753f50e905d    yuiop
\.

A practical solution is to use either of the --inserts or --column-inserts flags with pg_dumpall or pg_dump. In this an INSERT sql query is created for each entry, instead of a single COPY command with all the entries. This is slower (INSERT needs to pass by the parser, planner, etc.), but will work regardless of whether your tables already contain some of the data in them.

pg_dumpall --inserts -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql 
pg_dump --inserts -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql 
INSERT INTO public.enduser VALUES ('f659b7f9-813a-436b-89e4-e39abcf9fee7', 'asdf');
INSERT INTO public.enduser VALUES ('35239537-e1f2-4cd5-8ef2-4dca5a94985c', 'ghij');
INSERT INTO public.enduser VALUES ('e48f6e42-10e7-4805-a22d-693c39c56900', 'zxcv');
INSERT INTO public.enduser VALUES ('39609d84-fb0f-4acf-adc6-1753f50e905d', 'yuiop');
pg_dumpall --column-inserts -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql 
pg_dump --column-inserts -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql 
INSERT INTO public.enduser (id, name) VALUES ('f659b7f9-813a-436b-89e4-e39abcf9fee7', 'asdf');
INSERT INTO public.enduser (id, name) VALUES ('35239537-e1f2-4cd5-8ef2-4dca5a94985c', 'ghij');
INSERT INTO public.enduser (id, name) VALUES ('e48f6e42-10e7-4805-a22d-693c39c56900', 'zxcv');
INSERT INTO public.enduser (id, name) VALUES ('39609d84-fb0f-4acf-adc6-1753f50e905d', 'yuiop');

This backup is also better for restoring data to relational databases other than Postgres since the COPY command is unique to Postgres.

Another thing to consider is that pg_dumpall and pg_dump will, by default, create backups that do NOT respect foreign-key constraints since it only applies these constrainst (primary and foreign) in the backup sql script after creating the table without constraints, and copying or inserting the data. This can lead to an issue where, when applying a backup to a database where the table already exists with it's contstraints in place, the foreign-key constraint is not met. This will cause the same error you've mentioned.

To avoid this issue, rather create a data-only backup using the -a flag with pg_dumpall or pg_dump. This will respect foreign-key constraints, and will succeed, even if some of the data is already present in the given tables, if used alongside either the --inserts or --column-inserts flags. To get a structure-only (schema-only) backup, use the -s flag instead of the -a flag.

pg_dumpall -a --inserts -h HNAME -p PNUMBER -U UNAME > cluster_backup.sql 
pg_dump -a --inserts -h HNAME -p PNUMBER -d DBNAME -U UNAME > db_backup.sql 

Yet another solution is to first clean the given tables of all their data before you attempt to write the given data. Practically, this will require backing up the data with the -Fd or -Fc flags withpg_dump (pg_dumpall doesn't support this) for a directory-format backup or a custom-format backup respectively, and then restoring it with pg_restore using the -c (--clean) flag.

0

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.

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. - user993

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. - user6219

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. - user7788

-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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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