Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A PostgreSQL restore fails on foreign key constraints

Errorviolates foreign key constraint

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

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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 -60
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2You 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. 3pg_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

What does the error look like?

  • violates foreign key constraint
  • data-only restore into a schema that already has foreign keys fails
  • restore 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 -60

2Fix

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

3Verify

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

Did this fix it?

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

2Fix

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

3Verify

The third command creates the foreign keys without errors.

psql -d newdb -c "\d orders"

Confirmed by postgresql.org

Did this fix it?

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

2Fix

Dump again with the parent tables included, or dump the whole database.

pg_dump -Fc -t orders -t customers -d mydb -f mydb.dump

3Verify

Restore into a new, empty database with a clean run.

psql -c "CREATE DATABASE newdb WITH TEMPLATE template0"
pg_restore -d newdb mydb.dump

Confirmed by postgresql.org

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.