A PostgreSQL restore fails on foreign key constraints
violates foreign key constraintShort answer
Foreign keys fail on restore when rows load before the rows they point to, or when the dump never contained the parent tables. Restore the full archive so constraints are created after the data, use --disable-triggers for a data-only restore, or re-dump with the parent tables included.
Fix it fast
1Find out which cause you have
Find out what kind of dump you hold, because the cause depends on it. pg_restore --list prints the table of contents of a custom, directory or tar archive, and shows which sections and tables it holds.
pg_restore --list mydb.dump | head -602Match what you see, then jump to its fix
- 1
The dump was made with --data-only, or you restore with -a, into a schema that was created earlier with its foreign keysA data-only restore into tables that already have their constraintsFix - 2
You restore in several steps or restore plain SQL files by hand, and constraints come before the data they checkSchema, data and constraints were not loaded in the right orderFix - 3
pg_dump was run with -t for one table, and the restore into a clean database failsThe dump holds only some tables, and the parent tables are missingFix
- 1
What does the error look like?
violates foreign key constraintdata-only restore into a schema that already has foreign keys failsrestore of a dump made with pg_dump -t fails because a referenced table does not exist
Cause 1 of 3
A data-only restore into tables that already have their constraints
1Check
In a normal restore, constraints belong to the post-data section and are created after the table data is loaded. A data-only restore loads rows into tables whose foreign keys already exist, so the load order matters.
pg_restore --list mydb.dump | head -602Fix
Disable triggers during the data load. This needs superuser, and the manual warns that stopping the restore in the middle can leave the system catalogs in the wrong state, so let it finish.
pg_restore --data-only --disable-triggers -S postgres -d newdb mydb.dump3Verify
The restore finishes with no foreign key errors, and the row counts match the source.
psql -d newdb -c "select count(*) from orders"Confirmed by postgresql.org
Cause 2 of 3
Schema, data and constraints were not loaded in the right order
1Check
A dump has three sections: pre-data (definitions), data, and post-data (indexes, triggers, rules and constraints). Foreign keys sit in the last one.
pg_restore --list mydb.dump | head -402Fix
Restore the sections one after another, so the constraints are created after the rows exist.
pg_restore --section=pre-data -d newdb mydb.dump
pg_restore --section=data -d newdb mydb.dump
pg_restore --section=post-data -d newdb mydb.dump3Verify
The third command creates the foreign keys without errors.
psql -d newdb -c "\d orders"Confirmed by postgresql.org
Cause 3 of 3
The dump holds only some tables, and the parent tables are missing
1Check
When -t is used, pg_dump does not dump other objects the chosen tables depend on, so a specific-table dump is not guaranteed to restore by itself into a clean database.
pg_restore --list mydb.dump | head -602Fix
Dump again with the parent tables included, or dump the whole database.
pg_dump -Fc -t orders -t customers -d mydb -f mydb.dump3Verify
Restore into a new, empty database with a clean run.
psql -c "CREATE DATABASE newdb WITH TEMPLATE template0"
pg_restore -d newdb mydb.dumpConfirmed by postgresql.org
How do I stop it coming back?
- Restore a full archive into an empty database created from template0, instead of loading data into a half-built schema.
- Test every backup by restoring it into a scratch database on a schedule.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
pg_restore refuses a dump file as not a valid archive
pg_restore: [archiver] input file does not appear to be a valid archive
pg_restore only reads archives made by pg_dump in custom, tar or directory format.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix