A PostgreSQL logical dump can contain the database objects and data while omitting the cluster-wide roles or installed extension files needed to use them. Treat recovery as a set of dependencies: the dump, appropriate global definitions, compatible tools/server, extension support files and an explicit ownership model.1, 2, 4
A restore that reports errors and leaves some tables visible is not a completed recovery. Inspect the archive, preserve every error and validate application behaviour before enabling the destination.
This guide uses PostgreSQL 18 documentation. It concerns logical dumps, not physical base backups or WAL point-in-time recovery. No dump was restored, package installed or role changed for this article.
Identify what was actually saved
Record source server version, dump-tool version, dump format and the exact options used. pg_dump exports one database. pg_dumpall can export cluster-wide objects such as roles and tablespaces that pg_dump does not save.1, 2
Plain SQL scripts and custom/directory/tar archives have different restore paths. The former are executed as SQL scripts; the latter are processed by pg_restore.1, 3 A filename ending in .backup or .sql is not sufficient evidence of format or completeness.
Review whether the dump was filtered by schema, table, extension or data-only options. A valid partial dump is not automatically a complete application recovery set. It may rely on objects deliberately excluded at capture time.
Keep source warnings and exit status with the saved artifact. PostgreSQL's documentation advises inspecting warnings on standard error. A file can exist even when the capture had a problem, so file presence and nonzero size do not establish a successful backup.1
Check tool and server compatibility
Use a supported client/server combination for the actual versions. PostgreSQL documents that pg_dump refuses to dump a server newer than its own major version and that output is not guaranteed to load into an older major server.1 Do not infer backward compatibility from the fact that the original database once ran on that older release.
Record the intended destination version and extension versions before restoring. A newer engine may support a migration path, but extension SQL and binary compatibility remain separate requirements. Retain the original dump unchanged so later review can distinguish capture content from any reviewed conversion.
This guide does not prescribe editing a dump until it stops reporting errors. If a version conversion is required, make the transformation explicit, preserve the original and validate the changed semantics. A discarded statement may represent an important ownership, security or application dependency.
Inspect an archive without connecting to a database
For a trusted custom, directory or tar archive, the documented pg_restore --list option lists its table of contents. This illustrative command only reads the supplied archive and writes a listing to standard output; it does not include a database connection or perform a restore:3
pg_restore --list '/approved/path/recovery.dump'
Replace the path with the intended existing archive in a controlled review environment. The command is source-checked but unexecuted here. Archive metadata may contain sensitive object names, so handle the listing with the same access discipline as the backup.
Look for the expected schemas, tables, data entries, sequences, functions, extensions and ownership/privilege-related entries. A table-of-contents listing shows available archive entries; it does not validate data contents, extension binaries or successful execution of every entry.
If a selected restore list is proposed, document each omission and dependency. PostgreSQL allows list-based selection and ordering, but that flexibility can produce an incomplete application if used casually.3 Do not remove a failing object from the list merely to obtain a zero-error run.
Reconcile roles and ownership
Roles are cluster-wide objects. A database archive can refer to owners or grantees that do not exist on the destination. Decide which identities should be preserved and which should be deliberately mapped before changing restore options.1, 2
Missing roles are not a reason to grant every application user superuser access. Have the DBA review the minimum role definitions and memberships needed for the intended environment. A test restore may require a different login policy from production while still preserving the ownership semantics necessary to validate the application.
The --no-owner option suppresses ownership restoration, but that changes who owns the resulting objects. For archive formats, ownership handling is applied through pg_restore, rather than assuming a dump-time option will determine the later result.1, 3 Treat that as an explicit design choice, not an error-suppression trick.
Likewise, skipping privilege restoration does not demonstrate that permissions are correct. After recovery, test both required access and denied access with the intended application role. A successful administrator query proves little about a restricted application's behaviour.
Global exports can include sensitive role information.
If pg_dumpall --no-role-passwords is used, restored roles have null passwords and cannot use password authentication until credentials are provisioned through the approved process. The option avoids exporting passwords; it does not create a working replacement authentication arrangement.2 Keep them in controlled backup custody and do not paste them into a review ticket. Record which global objects are required and who can restore them without copying secrets into the article or incident notes.
Provide extension support, not just its name
An extension's SQL definition is not a copy of all files needed by the destination server. PostgreSQL requires an extension's supporting files to be installed before CREATE EXTENSION can load it. Available extensions and versions can be inspected through the documented availability views.4
Map each required extension to its supported destination version, package source and installation method. Confirm that the operator is allowed to install it and that the platform supports it. A managed database service may expose a narrower extension set than a self-managed server; no generic package command is appropriate for both.
Extension installation runs extension scripts and has privilege requirements. Some trusted extensions can be installed by a user with database CREATE; others require higher privileges. Do not assume all extensions are trusted or that an existing extension of the same name is the required version/content.4
If an extension is unavailable, record the specific blocker. Replacing its types, functions or indexes with guessed substitutes changes application semantics. That is a migration project requiring its own design, not completion of the original restore.
Preserve restore errors and control side effects
The pg_restore default is to continue after errors and report a count at the end. Its --exit-on-error option changes that behaviour.3 The restore plan must define how failures are captured and whether partial state is acceptable for diagnosis; a completed process alone is not proof that every object loaded.
Do not rerun an entire restore repeatedly into an unknown partially populated destination. Preserve the first error and determine whether later errors are consequences of it. A fresh disposable destination may be the appropriate rehearsal route, but removal of any existing database remains a separately authorised destructive action.
PostgreSQL warns that restoring a dump can execute arbitrary code chosen by source superusers, and that a partial restore does not remove that risk.1, 2, 3 Review untrusted sources in an isolated environment and inspect their SQL rather than executing them on a privileged production connection.
Keep application integrations, foreign connections and background processing controlled during recovery. Restoring data is not permission to begin sending production work from the new copy.
Verify a usable database
Acceptance should cover expected schema and object definitions, complete data, relevant sequence state, extension versions, ownership and privileges. Use independent synthetic reference records where possible, and exercise the application's required functions through its intended role.
Check a write path as well as a read path in an authorised test environment. A database can answer a SELECT while later inserts fail because a sequence, default, permission or extension function was not restored correctly. Also test a representative denied operation so overly broad permissions do not hide an incomplete mapping.
Record the exact dump, tool versions, reviewed options, error count and application results. A no-error restore is stronger than file presence, but it is still one part of application acceptance. No such result is claimed here.
Learning notes
- A database dump does not automatically include cluster-wide identities or installed extension files.
- Ownership overrides alter the recovered security model.
- Restore completion, zero errors and usable application behaviour are separate acceptance stages.
Acceptance checklist
- [ ] Dump/tool versions and required roles, extensions and supporting files are inventoried.
- [ ] Restore errors and intended ownership/privilege changes are accounted for.
- [ ] Application-role reads, writes, sequences and expected denied operations pass isolated checks.
- [ ] Authentication provisioning and external integrations are controlled before activation.
References
- PostgreSQL: Documentation: 18: pg_dump. Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: pg_dumpall. Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: pg_restore. Accessed 2026-11-01.
- PostgreSQL: Documentation: 18: CREATE EXTENSION. Accessed 2026-11-01.