pg_restore duplicate key errors: fix schema restore fails

Quick answer

Drop the target schema (or run pg_restore --clean --if-exists) before restoring, because the error means your target database already contains rows that conflict with the unique constraints in the dump.

What's actually happening here is that pg_restore tries to insert data into tables that already hold records. The unique constraint fires, PostgreSQL aborts that single command, and you see the dreaded duplicate key value violates unique constraint error. Most people hit this when they restore a dump into a database that isn't empty — maybe you ran a partial restore earlier, or the target schema was created by something like an ORM migration that seeded default rows. The dump itself isn't corrupt; the target just isn't in the state the restore expects.

This error also shows up when you restore a full database dump (with CREATE TABLE and data) into a schema that already has the tables and data. The CREATE TABLE statements fail silently or are skipped, but the INSERT statements run anyway, and that's where the duplicate key hits.

Fix steps

1. Identify the target schema state. Run this on the target database to see if there's existing data:

\dt public.*

If you see tables with rows, that's your problem. If the tables exist but are empty, then the duplicate key is coming from something else — like a sequence that's out of sync, but that's rare for key constraints.

2. Drop the target schema entirely. The cleanest fix is to remove the schema and recreate it. You can do that with:

DROP SCHEMA public CASCADE;

Then recreate it:

CREATE SCHEMA public;

Or, if you're restoring into a dedicated schema, drop that one instead.

3. Restore with the --clean flag. If you can't drop the schema manually (maybe you don't have superuser rights), pg_restore can do it for you:

pg_restore --clean --if-exists --dbname=mydb dump.dump

The --clean flag drops database objects before recreating them, and --if-exists prevents errors if an object doesn't exist. This is the most direct fix and works in almost every case.

4. Restore only the schema, then only the data. If you're dealing with a huge dump and want to avoid the drop, you can split the restore. First, restore just the schema (no data):

pg_restore --schema-only --dbname=mydb dump.dump

Then truncate all tables in the target schema, then restore data:

pg_restore --data-only --dbname=mydb dump.dump

But honestly, step 2 or 3 is simpler. Splitting this way only makes sense if you have a reason to preserve the existing schema objects.

Alternative fixes if the main one fails

What if you can't drop the schema because it's referenced by other databases or you're sharing it? Then the real issue might be that you restored a partial backup earlier, and your dump actually contains conflicting rows across multiple tables. In that case, you need to find the offending row.

Run the restore with verbose output to see the exact constraint and table:

pg_restore --verbose --clean --if-exists --dbname=mydb dump.dump 2>&1 | grep -i 'duplicate key'

That will show you the table and the constraint name. Then you can query that table in the target database to see the existing rows. For example:

SELECT * FROM users WHERE email = 'existing@example.com';

Then you decide: delete the conflicting row, or skip that table in the restore. Skipping a table is awkward with pg_restore because you can't easily restore all but one table without listing every other table. You can use --table to restore specific tables, but that's tedious for many tables.

Another alternative: restore to a fresh database first, then copy the data over. This is a solid workaround if the target database is shared and you can't touch its schema. Create a new database, restore the dump there, then use something like pg_dump or COPY to move data into the target. It's more work, but it guarantees a clean state.

Prevention tip

The root cause is always the same: you're restoring into a database that isn't empty. The fix is to make your restore idempotent from the start.

• Always use --clean --if-exists when restoring into a database that might already have objects.
• Or, make it a habit to drop and recreate the target schema before any restore, even in development.
• If you're automating restores (like in CI/CD), add a step that wipes the schema first. One command: psql -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;' before running pg_restore.

One more thing: don't confuse this with a primary key sequence issue. If you see duplicate key errors on a column that's supposed to be auto-incrementing, that's a different problem related to sequence values not matching after a data-only restore. But in a schema restore, the duplicate key error almost always means existing data is in the way.