Fix common pg_dump and pg_restore errors when moving a database
Short answer: Most failed PostgreSQL moves come down to a handful of causes: the dump format doesn't match the restore tool, the client tools are older than the server you dumped, the target database lacks the roles that own objects, extensions aren't installed on the new server, or objects already exist. Dump with the newest pg_dump you'll use, in custom format (-Fc), restore with pg_restore using --no-owner when users differ, create needed extensions first, and use --clean --if-exists only when you mean to overwrite.
The usual situation
You're moving a business app's database: from one host to another, from a developer's laptop to production, into a staging copy, or onto a managed database service. You run pg_dump, copy the file, run a restore, and get a screen of errors. Some are harmless; some mean your data didn't arrive. Knowing which is which saves hours.
Choose the right format
The PostgreSQL documentation explains that pg_dump writes either script files or archive files.
- Plain-text script dumps are SQL commands that rebuild the database. You restore them with
psql, notpg_restore. - Custom (
-Fc) and directory (-Fd) archives are described as the most flexible formats: they allow selecting and reordering items, support parallel restoration, and are compressed by default. You restore them withpg_restore.
Mixing these up causes the classic early error: pg_restore complaining that the input file looks like a text dump, or psql printing garbage when fed an archive. For most moves, custom format is the best choice.
Version mismatches
The documentation notes that because pg_dump is used to transfer data to newer versions of PostgreSQL, its output can be expected to load into servers newer than the pg_dump version. The reverse isn't promised. Practical rules:
- Use a
pg_dumpat least as new as the source server. An older client may refuse to dump a newer server. - Restoring into an older server than the one you dumped is not something to rely on. Upgrade the target, or use a plain dump and fix incompatible statements by hand as a last resort.
- Check your tool versions with
pg_dump --versionandpg_restore --version, especially on laptops and CI images that lag behind servers.
Ownership and role errors
Errors such as "role does not exist" appear when the dump says objects belong to a user that doesn't exist on the new server. This is common when moving to a managed service, where you get one admin user with different names.
pg_dumponly dumps a single database. The documentation points out that cluster-wide objects such as roles and tablespaces needpg_dumpallinstead.- If you don't need to keep the original owners, use
--no-owneron restore. The documentation explains that by defaultpg_restoreissuesALTER OWNERorSET SESSION AUTHORIZATIONcommands to match the original database;--no-ownerskips those so objects belong to the user running the restore. - Use
--no-privileges(or-x) too if the old grants refer to roles that won't exist, then set permissions fresh on the new server.
Extensions
If the app uses extensions, such as ones for UUIDs, full-text features or geography, the dump contains commands to create them. On a new server the extension package may not be installed, or a managed service may only allow certain ones. Install or enable required extensions on the target before restoring, and check that versions are compatible.
"Already exists" errors
Restoring into a database that already has tables produces "relation already exists" errors and can leave mixed old and new data. Choose deliberately:
- Restore into a fresh, empty database. Simplest and safest.
- Overwrite an existing one:
--cleandrops objects before recreating them. The documentation notes that this can report ignorable errors for objects that don't exist yet, unless--if-existsis also used. - Create the database as part of the restore with
--create.
Speed and safety options
- Parallel restore:
--jobsruns the slowest steps, loading data, building indexes and creating constraints, concurrently. It works with custom and directory archives. - All-or-nothing:
--single-transactionmakes the restore succeed or fail as a whole, though the documentation notes it can't be combined with multiple jobs. - Stop on first error:
--exit-on-errorsaves you from scrolling past a cascade of follow-on errors. - Restore a subset:
--listprints the archive's table of contents, which you can edit and pass back with-Lto restore selected items.
After the restore
- Compare row counts on key tables between source and target.
- Run the app's test suite or smoke tests against the restored database.
- Check sequences produce IDs above existing ones before taking new writes.
- Run
ANALYZEso the planner has fresh statistics.
A worked example
Consider a hypothetical Nelson winery whose online shop database was moving from an old virtual server to a managed PostgreSQL service. The first restore failed with dozens of "role does not exist" errors and an extension error. Their developer installed the same major version of the client tools as the new server, dumped in custom format, enabled the extension on the target, and restored with --no-owner --no-privileges --exit-on-error into a new empty database. Row counts matched, the app's smoke tests passed, and the switch happened during a quiet evening.
FAQ
Can I ignore all restore errors?
No. Some are harmless notices, but others mean tables or data are missing. Use --exit-on-error and fix causes.
Do I need pg_dumpall?
Only if you need roles, tablespaces or every database in a cluster. For one app database, pg_dump plus fresh roles is usually cleaner.
How do I move a very large database faster?
Directory format with parallel dump and restore jobs helps; for big moves, replication-based approaches may be better.
Will the app need changes?
Only if usernames, connection details or extension availability differ on the new server.
If a database move is coming up and you want it to go right first time, tell us where it's going.