Lorixen.
EN

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, not pg_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 with pg_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_dump at 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 --version and pg_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_dump only dumps a single database. The documentation points out that cluster-wide objects such as roles and tablespaces need pg_dumpall instead.
  • If you don't need to keep the original owners, use --no-owner on restore. The documentation explains that by default pg_restore issues ALTER OWNER or SET SESSION AUTHORIZATION commands to match the original database; --no-owner skips 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: --clean drops objects before recreating them. The documentation notes that this can report ignorable errors for objects that don't exist yet, unless --if-exists is also used.
  • Create the database as part of the restore with --create.

Speed and safety options

  • Parallel restore: --jobs runs the slowest steps, loading data, building indexes and creating constraints, concurrently. It works with custom and directory archives.
  • All-or-nothing: --single-transaction makes 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-error saves you from scrolling past a cascade of follow-on errors.
  • Restore a subset: --list prints the archive's table of contents, which you can edit and pass back with -L to 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 ANALYZE so 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.

Sources

WhatsAppBook a call