Fix

pg_restore: relation "orders" already exists

The database you are restoring into is not empty. pg_restore will tell you so a few hundred times, keep going anyway, and hand you a database that is neither the old one nor the new one.

The error

pg_restore: error: could not execute query: ERROR:  relation "orders" already exists
pg_restore: error: Command was: CREATE TABLE public.orders (
    id bigint NOT NULL,
    ...

From a plain SQL dump replayed with psql, the same cause reads:

ERROR:  relation "orders" already exists

And once the schema statements have failed, the data statements often fail too, with a second error that looks unrelated:

ERROR:  duplicate key value violates unique constraint "orders_pkey"

Why it happens

pg_restore does not clean anything by default. It replays CREATE statements, and CREATE TABLE on an existing table is an error. Three situations produce it:

• A nightly "refresh staging from prod" job that restores into the same database every night. It worked the first night.
• A second attempt after a failed restore. The first run created half the objects before dying; the retry trips on those.
• Restoring into a database a framework already migrated. The tables exist because your migrations created them at boot.

The dangerous part is what happens next. Unless you tell it otherwise, pg_restore logs each error and continues to the end, finishing with a count of how many it ignored. A cron line that redirects output to /dev/null sees a job that ran. What it actually produced is a database where the schema is whatever was there before — old column types, missing new columns — and the data is yesterday's rows plus today's, either duplicated outright or partially rejected by the constraints that happened to be in the way.

Fix 1 — restore into an empty database (do this one)

The error is not really a flag problem. A restore is only meaningful if the target starts empty, because that is the only way the result is a function of the backup alone:

dropdb --if-exists appdb_restore
createdb appdb_restore
pg_restore --exit-on-error -j 4 -d appdb_restore appdb.dump

For a recurring refresh, restore into a new database and swap names at the end, so there is no window where the target is half-populated:

dropdb --if-exists staging_new
createdb staging_new
pg_restore --exit-on-error -d staging_new appdb.dump

psql -d postgres -c 'ALTER DATABASE staging RENAME TO staging_old;'
psql -d postgres -c 'ALTER DATABASE staging_new RENAME TO staging;'
dropdb staging_old

A database cannot be renamed or dropped while sessions are connected to it, so point your app at the new name or disconnect it first.

Fix 2 — --clean --if-exists, and what it does not do

If you must reuse the same database, have the restore drop each object before recreating it:

pg_restore --clean --if-exists --exit-on-error -d appdb appdb.dump

Use both flags. --clean alone emits bare DROP statements, which fail noisily for every object that is not there yet — the exact noise you were trying to get rid of. --if-exists turns them into DROP ... IF EXISTS. It is not valid on its own: passing --if-exists without --clean is rejected outright.

Now the limitation that matters. --clean only drops objects the dump knows about. A table someone created by hand last month, a leftover from a migration you rolled back, an index added directly in production — none of that is in the archive, so nothing drops it, and it survives every "clean" restore you will ever run. The database drifts further from the backup each time, and the drift is invisible until something depends on it.

Fix 3 — --create --clean, to drop the database itself

This is the version that has no leftovers, because it replaces the whole database:

pg_restore --create --clean -d postgres appdb.dump

Read the -d carefully: with --create, that database is only the one pg_restore connects to in order to issue DROP DATABASE and CREATE DATABASE. The data lands in the database name recorded inside the archive. Pointing -d at the database you want to overwrite does not work — you cannot drop the database you are connected to.

Two caveats. The drop fails if anything is still connected. And the database is recreated from the properties stored in the archive, which are only there if the dump was taken with --create as well.

On managed Postgres, where you cannot drop the database

Supabase, Neon, RDS and friends hand you one database you do not own at the cluster level. The equivalent move is to empty the schema instead:

psql -d "$TARGET_URL" -c 'DROP SCHEMA public CASCADE; CREATE SCHEMA public;'
pg_restore --no-owner --no-acl -d "$TARGET_URL" appdb.dump

Three things to check before you run that. CASCADE removes everything in the schema, including extensions installed there — on Supabase that is a real risk, since parts of the platform live in their own schemas and some extensions are placed outside public. Dropping only public leaves those other schemas untouched, so a dump containing them still collides. And on Postgres 15 and later the shipped public schema is owned by pg_database_owner without CREATE for PUBLIC; recreating it by hand gives you a schema owned by whoever ran the command, with different default privileges.

If the target is a live database with anything you care about in it, do not do this at all — restore into a separate project or a local container and copy what you need across.

Data-only restores are a different problem

If the schema is already correct and you only want the rows back, the flag is --data-only — but it appends, so an existing table gives you duplicates or unique violations. Truncate first, and be aware that foreign keys are checked during a data-only load in an order the dump does not control:

psql -d appdb -c 'TRUNCATE orders, order_items RESTART IDENTITY CASCADE;'
pg_restore --data-only --disable-triggers -d appdb -t orders -t order_items appdb.dump

--disable-triggers is only meaningful with --data-only, and it requires superuser rights — which is precisely what you do not have on managed Postgres. There, restore into an empty database instead of fighting the constraint order.

Make the failure loud

Whatever you choose above, the flag that changes the outcome is the one that stops the run:

# stop at the first error instead of limping to the end
pg_restore --exit-on-error --clean --if-exists -d appdb appdb.dump

# all-or-nothing: implies --exit-on-error, rolls back on failure
pg_restore --single-transaction -d appdb appdb.dump

# plain SQL equivalent
psql -v ON_ERROR_STOP=1 --single-transaction -d appdb -f dump.sql

--single-transaction is the strongest form: either the restore completes or the database is untouched, with no half-restored state to debug at 3 a.m. It cannot be combined with parallel restore (-j), so on a large database you trade restore time for atomicity.

The part worth keeping

"Relation already exists" is not really an error about a flag. It is the database telling you that this restore's result depends on what was in the target beforehand — which means it proves nothing about your backup. A restore that only works into a database that already looks right is not a tested backup. The drill starts from an empty container for exactly this reason.

Related fixes: pg_restore: role "…" does not exist pg_restore: unsupported version in file header pg_dumpall vs pg_dump: what a per-database dump leaves behind pg_dump formats: custom, plain, directory or tar? How to actually test a Postgres backup