Guides

Why my database backup won't restore — three causes and the fix

Illustration: a sealed crate and a key that won't fit the lock

The backup file exists. It's three hundred megabytes. You confidently start the restore — and get an error within the first second.

Bad news: people usually discover this on the worst possible day. Good news: it's almost always one of three things, and none of them means the data is lost.

Let's go through them, most common first.

Symptom: the restore fails immediately, or quietly does the wrong thing

What it looks like:

  • the command aborts with a message about the server version;
  • syntax error pours into the console from line one, as if the file weren't SQL;
  • the script runs through, but ends with dozens of must be owner of or permission denied;
  • the database is created, but the tables are empty.

Each variant points at a different cause. Don't just retry "with sudo" — first read what the command actually said.

Cause 1. The versions don't match (the most common)

You took the dump with a local pg_dump, and the cloud server is newer. Or the reverse — the dump came from a newer version and you're restoring into an older one.

The PostgreSQL docs state the rule plainly: pg_dump output is expected to load into servers newer than pg_dump's own version. Dumping from an older server is fine too. The other direction is not: pg_dump "cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump."

How to check. Compare two numbers:

pg_dump --version
psql -h your-host -U postgres -c "SHOW server_version;"

How to fix. Use a tool at least as new as the server. On managed databases (Supabase, Neon, RDS) the server is upgraded without you, so it's usually the local client that's stale. Update it, or take the dump through the provider's own panel.

And remember the direction: restoring into a newer version is fine, into an older one isn't. You can't downgrade a database with a dump.

Cause 2. The file isn't the format you think

pg_dump can emit four formats, and they aren't interchangeable. Plain text SQL is only one of them. The other three, in the docs' words, are "suitable for input into pg_restore": custom, directory and tar.

Hence the classic: the dump was taken in custom format (the -Fc flag) and someone restores it with psql. It dutifully tries to read a binary file as text and throws syntax error on line one.

How to check. Peek at the start of the file:

head -c 100 backup.dump

See PGDMP and gibberish — it's an archive, you need pg_restore. See -- PostgreSQL database dump and readable SQL — it's text, you need psql.

How to fix. Just pick the right tool:

# plain text dump
psql -d mydb -f backup.sql

# custom or directory archive
pg_restore -d mydb backup.dump

Only one direction is a dead end: you can turn an archive into SQL (pg_restore -f out.sql), but not text into an archive.

Cause 3. You aren't who the owner was

The script reaches the end, but half the commands failed with must be owner of relation.

By default pg_dump writes ownership commands into the dump. The docs warn: those statements "will fail when the script is run unless it is started by a superuser (or the same user that owns all of the objects in the script)."

On your own machine you are the superuser, so there's no problem. On a managed database you aren't — you get an ordinary role. And roles like postgres or admin_prod from the old dump may not exist on the new server at all.

How to check. Look for ownership lines in the dump:

grep -c "ALTER TABLE.*OWNER TO" backup.sql

How to fix. Take the dump without owners and without access privileges:

pg_dump -O -x -d mydb -f backup.sql

The -O flag is exactly the one the docs describe: to make a script that any user can restore, taking ownership of all objects. If the dump already exists, the same switches work on the restore side: pg_restore --no-owner --no-acl.

Two smaller traps

Extensions. If your database uses extensions (pgvector, postgis), installing them requires privileges, and the object isn't captured whole. The docs warn honestly that with a specific-extension dump "there is no guarantee that the results ... can be successfully restored by themselves into a clean database." The fix is to run CREATE EXTENSION on the new database first.

A truncated file. The dump was downloaded over the network and the connection dropped. On the surface the file is there. Check its size and its tail: a plain-text dump ends with a completion comment. No comment, no complete file — nothing to fix, take a fresh dump.

Does table order matter? I'm getting foreign key errors

On a normal full restore, no: the dump orders objects correctly and adds constraints after the data. Foreign key errors usually mean the data was loaded selectively, one table at a time. Then either respect "parents before children", or restore the whole database.

How do I know the backup works before disaster strikes?

The only honest way is to restore it. Keep an empty test database, load a fresh dump into it monthly and run a couple of queries. This works nicely as a scheduled task.

The dump restored, but yesterday's data is missing

Then the dump is older than you thought: it holds the state at the moment it was taken, not at the moment things broke. The gap between your last backup and the failure is what you lose. It only shrinks with more frequent dumps — or with the write-ahead logs that managed databases keep for you.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →