PostgreSQL

Postgres Backup and Restore With pg_dump, Common Errors

Most pg_dump and pg_restore failures come down to a small set of recurring causes — version mismatches between the tools and the server, missing permissions on the target database, or role/ownership differences between the source and destination environments.

The Problem

A backup or restore operation that should be routine fails with one of several common errors:

pg_dump: error: server version: 16.1; pg_dump version: 14.9
pg_dump: error: aborting because of server version mismatch

pg_restore: error: could not execute query: ERROR: role "produser" does not exist

pg_restore: error: [archiver] unsupported version (1.16) in file header

Why It Happens

Each of these represents a slightly different underlying mismatch between the environment the dump was taken from and the environment it's being applied to:

  • Version mismatch: pg_dump generally requires being the same version as, or newer than, the PostgreSQL server it's dumping from — an older pg_dump client against a newer server is explicitly blocked, since it may not understand newer server-side features
  • Missing roles: the dump references database roles/users (as owners of tables or grantees of permissions) that exist in the source environment but were never created in the destination, so pg_restore fails trying to apply ownership or grants referencing a role it can't find
  • Corrupted or incompatible archive format: attempting to restore a custom-format dump with a pg_restore version too different from the one that created it, or the dump file itself was truncated during transfer
  • Permission issues on the destination database, where the connecting user doesn't have sufficient privileges to create the objects the dump defines

The Fix

For version mismatches, use a pg_dump binary matching (or newer than) the source server's version. If you have multiple PostgreSQL versions installed, reference the correct one explicitly rather than relying on whatever's first in your system PATH:

/usr/lib/postgresql/16/bin/pg_dump -U user -d mydb -F c -f backup.dump

For missing role errors during restore, create the required roles in the destination database first, matching the names referenced in the dump, before attempting the restore:

CREATE ROLE produser WITH LOGIN;

Alternatively, if ownership details don't matter for your use case (like restoring into a fresh local development database), use the --no-owner flag to skip ownership commands entirely during restore, letting the connecting user own everything instead:

pg_restore --no-owner -d mydb backup.dump

Similarly, --no-acl skips restoring access privilege (grant/revoke) statements, useful in the same scenario where matching the exact permission structure of the source isn't necessary.

Use the custom format (-F c) rather than plain SQL for anything beyond a simple, small database — it supports parallel restore, selective table restore, and is generally more robust against partial corruption than a plain .sql text dump:

pg_dump -U user -d mydb -F c -f backup.dump
pg_restore -U user -d newdb backup.dump

If the dump file might have been corrupted during transfer (a common cause of "unsupported version" or unexpected parsing errors), verify its integrity before assuming the dump itself is the problem:

pg_restore --list backup.dump

If this command itself fails to even list the contents, the file is likely corrupted or incomplete, rather than the restore process having a genuine configuration issue — re-transferring or re-generating the dump is the fix in that case, not adjusting restore flags.

Still Not Working?

If a restore partially succeeds but specific tables or functions fail individually, restore with verbose output to see exactly which statements are failing and why, rather than only seeing a final summary:

pg_restore -v -d mydb backup.dump 2> restore_errors.log

Reviewing this log line by line, rather than the terminal's final pass/fail summary, usually reveals whether the remaining failures are genuinely blocking (missing extensions, incompatible data types) or safe to ignore (harmless warnings about objects that already existed).

You might also like