Back up, restore, and test a PostgreSQL database
Produce a custom-format pg_dump, restore into isolation, compare invariants, and retain recovery evidence.
Continuously prove that PostgreSQL logical backups contain the required schema and data.
- PostgreSQL 16, 17, 18
- Compatible client Use pg_dump from the same or newer major version than the server.
- Protected credentials Use .pgpass or another protected provider.
- Isolated target Restore into a non-production database.
OneLiners never runs these steps or stores secrets. Review placeholders, versions, current state, and change-control requirements before using a command.
Full guide
What you will build
- A PostgreSQL custom-format database archive plus a separate globals-only script for cluster roles and tablespace metadata.
- A checksum manifest and archive inventory that can be reviewed before restoration.
- A clean template0-based test database restored with explicit ownership policy, refreshed optimizer statistics, and representative structural and application checks.
- pg_dump and pg_dumpall finish without unresolved warnings, and the custom archive lists the required schemas, data, functions, extensions, and large objects.
- The retained checksums match after transfer to an independent location.
- pg_restore exits on the first error and the isolated database passes object, extension, row, sequence, and application invariants.
- The measured restore defines a defensible recovery-time estimate and identifies the roles, tablespaces, extensions, configuration, WAL, and secrets outside the single-database archive.
Architecture
How the parts fit together
pg_dump takes a transactionally consistent snapshot of one database without blocking ordinary readers or writers and stores it in a portable custom archive. pg_dumpall --globals-only records cluster-wide roles and tablespaces separately. pg_restore reads the archive into a clean template0 database, after which ANALYZE and application checks prove usability.
- Source identity, version, size, schema, extensions, and invariants are recorded.
- pg_dump writes one database to custom format while pg_dumpall writes cluster globals to a reviewable SQL file.
- pg_restore --list and SHA-256 checks validate the artifacts before transfer and again at the restore location.
- createdb makes a clean database from template0 on an isolated server.
- pg_restore loads the archive under an explicit ownership policy and stops on errors.
- vacuumdb refreshes statistics; SQL and application checks compare the result; the disposable database is then removed.
Assumptions
- The pg_dump client is the same major version as the source or newer and is not asked to dump a newer server than it supports.
- The target version is compatible with the source objects, extensions, collations, and SQL; upgrades are tested separately.
- The procedure tests one database. Physical recovery, WAL archiving, point-in-time recovery, replication slots, server configuration, and operating-system files are separate concerns.
- Required extension packages exist on the target before restore, and external objects such as foreign servers are inventoried.
- The artifact comes from trusted source administrators. PostgreSQL warns that restoring a dump can execute arbitrary code chosen by source superusers.
- The isolated target has capacity for archive, restored data, indexes, temporary files, WAL growth, and concurrent restore jobs.
Key concepts
- Custom format
- A compressed pg_dump archive catalog that supports pg_restore inspection, selection, ordering, and parallel restoration.
- Database globals
- Cluster-wide roles and tablespaces that pg_dump does not include and pg_dumpall --globals-only can export.
- template0
- The pristine database template used to avoid inheriting local additions from a customized template1.
- Ownership policy
- The decision to recreate source owners or intentionally restore objects under a target identity with --no-owner.
- ANALYZE
- Statistics collection that gives the query planner useful data after a bulk restore.
- Archive table of contents
- The ordered object catalog printed by pg_restore --list and used to verify scope before execution.
Before you copy
Values used in this guide
{{sourceUrl}}Protected libpq connection URI or service reference for the source database.
Example: postgresql://backup@db.internal/appdb{{sourceAdminUrl}}Protected source-cluster connection used only to export roles and tablespace definitions.
Example: postgresql://backup_admin@db.internal/postgres{{adminUrl}}Maintenance connection for creating and dropping a test database on the isolated target.
Example: postgresql://restore_admin@restore.internal/postgres{{restoreUrl}}Connection URI for the exact disposable target database.
Example: postgresql://restore_admin@restore.internal/restore_test_20260725{{restoreDatabase}}Unique disposable database name with an unmistakable test prefix.
Example: restore_test_20260725{{backup}}Absolute path of the custom-format database archive.
Example: /srv/backups/postgresql/appdb-2026-07-25.dump{{globalsBackup}}Absolute path of the globals-only SQL artifact.
Example: /srv/backups/postgresql/globals-2026-07-25.sql{{criticalTable}}Representative restored table used by a stable invariant.
Example: ordersSecurity and production boundaries
- Use a protected password file or secret provider. PostgreSQL ignores a Unix .pgpass file if group or other permissions are present, so enforce mode 0600.
- A source superuser can place executable code in database objects. Inspect artifacts from untrusted sources before restoration and never load them into a privileged network.
- Globals scripts can contain role definitions and password verifiers. Restrict and encrypt them like the database archive.
- A custom archive is not human-readable, but pg_restore --file can render SQL for security review without executing it.
- Use a dedicated isolated target and outbound network controls when restoring content for investigation.
Stop before continuing if
- Stop if client/server versions, extensions, collation providers, or target architecture are not compatible with the intended restore.
- Stop if pg_dump or pg_dumpall emits an unresolved warning, the archive list omits required objects, or checksums differ.
- Stop if the restore connection could reach production or the target database name is not uniquely disposable.
- Stop on the first pg_restore error; do not certify a partially loaded database.
- Stop if extensions, ownership policy, tablespaces, or roles required by the application are unresolved.
- Do not remove the previous verified generation until the new archive has passed restore and application checks.
command
Record source invariants
Capture server version, size, schemas, and representative row counts.
Why this step matters
A restore can only be compared with a known source, so record server and client versions, database identity, size, schemas, extensions, ownership, and domain invariants before the archive is created.
What to understand
version() identifies the server build while pg_dump --version records the client. The client must not be older than a server catalog it cannot understand.
Record pg_database_size, schema and table counts, extension versions, important sequence values, and one or more application invariants.
Use --no-psqlrc or a controlled service definition in automation so personal startup files cannot alter evidence formatting or behavior.
System changes
- No database change; reads source catalog and business invariants and records them as recovery evidence.
Syntax explained
--dbname={{sourceUrl}}- Connects to the exact protected source service or URI.
--no-align- Uses unaligned output that is stable in logs.
--tuples-only- Suppresses headings and row-count decoration.
--command- Executes the quoted read-only SQL and exits.
Values stay on this page and are never sent or saved.
psql --dbname={{sourceUrl}} --no-align --tuples-only --command="SELECT version(); SELECT pg_size_pretty(pg_database_size(current_database()));"PostgreSQL 17.2 ... 248 MB
Checkpoint: Identify the recovery source
psql --dbname=postgresql://backup@db.internal/appdb --no-psqlrc --tuples-only --command="SELECT current_database(), current_user, version(), pg_database_size(current_database());"Continue whenThe intended database, approved backup user, server version, and plausible nonzero size are recorded.
Stop whenThe source, client compatibility, extension inventory, or expected invariant cannot be established.
If this step fails
The connection reaches a database with the expected name on the wrong cluster.
Likely causeDNS, service configuration, or a copied URI points to another environment.
psql --dbname='postgresql://backup@db.internal/appdb' --tuples-only --command="SELECT inet_server_addr(),inet_server_port(),current_setting('data_directory');"
ResolutionCorrect the protected connection definition and record a cluster-specific identity before proceeding.
Security notes
- Do not include passwords or sensitive row samples in the evidence.
Alternatives
- Use a monitored read replica when its replay state and lag meet the declared recovery point.
Stop conditions
- Do not start a backup against an ambiguously identified cluster.
command
Create a custom-format backup
Custom format supports selective and parallel restore.
Why this step matters
Custom format preserves a rich object catalog, compresses content, and gives pg_restore the ordering and selection controls needed for a realistic isolated rehearsal.
What to understand
pg_dump creates a consistent database snapshot while ordinary readers and writers continue. It backs up one database, not roles, tablespaces, configuration, or WAL.
--format=custom produces a portable archive compressed by default. Directory format is the alternative when parallel dump is required.
--verbose diagnostics belong in a protected operation log and must be reviewed. A file existing after an interrupted command is not a successful backup.
System changes
- Creates a custom-format archive on the backup host; source objects and rows remain unchanged.
Syntax explained
--format=custom- Creates a compressed pg_restore archive with a selectable table of contents.
--file={{backup}}- Writes to the explicit protected artifact path instead of standard output.
--verbose- Emits detailed progress and warnings to standard error for the operation log.
--dbname={{sourceUrl}}- Pins the export to the reviewed source connection.
Values stay on this page and are never sent or saved.
pg_dump --dbname={{sourceUrl}} --format=custom --file={{backup}} --verbosepg_dump: saving database definition pg_dump: dumping contents of table "public.orders"
Checkpoint: Require a complete custom archive
test -s /srv/backups/postgresql/appdb-2026-07-25.dump && pg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump | headContinue whenpg_dump exits zero and pg_restore reads a non-empty archive header and catalog.
Stop whenThe command reports a warning or error, the file is empty, or the catalog cannot be read.
If this step fails
pg_dump reports permission denied for a sequence or table.
Likely causeThe backup role lacks privileges across the complete selected schema.
psql --dbname='postgresql://backup@db.internal/appdb' --command='\dp' --command='\dn+'
ResolutionGrant the smallest required read access through a reviewed role and repeat the entire dump.
Security notes
- Run with a restrictive umask and protect verbose logs that reveal object names.
Alternatives
- Use directory format and a measured --jobs value when backup time is the limiting objective.
Stop conditions
- Never promote an archive from a nonzero pg_dump exit or unresolved diagnostic.
command
Export cluster roles and tablespaces
Record cluster-wide dependencies that pg_dump intentionally omits, but review them separately before any privileged restore.
Why this step matters
One database archive omits cluster-wide roles and tablespaces, so a separate globals export is necessary to understand ownership and permissions during full recovery.
What to understand
pg_dumpall --globals-only writes role and tablespace definitions without database rows. It may include password verifiers and must be protected as sensitive.
Restoring globals generally requires elevated privileges and tablespace directories prepared on the destination. This tutorial records and reviews them but does not apply them to a shared server.
The globals snapshot is taken near the database dump but is not transactionally synchronized with it; coordinate role changes during the backup window.
System changes
- Creates a plain SQL globals artifact containing cluster role and tablespace definitions.
Syntax explained
pg_dumpall- Connects at cluster scope rather than exporting only one database.
--globals-only- Includes roles and tablespaces without dumping database contents.
--file={{globalsBackup}}- Writes the sensitive SQL to the explicit protected path.
Values stay on this page and are never sent or saved.
pg_dumpall --dbname={{sourceAdminUrl}} --globals-only --file={{globalsBackup}} --verbosepg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false);
Globals written to globals-2026-07-25.sqlCheckpoint: Review global dependencies
grep -E '^(CREATE|ALTER) (ROLE|TABLESPACE)' /srv/backups/postgresql/globals-2026-07-25.sql | head -n 20Continue whenExpected application owners and tablespaces are represented and the file is mode 600.
Stop whenRequired roles are absent, unexpected privileged roles appear, or the artifact is broadly readable.
If this step fails
pg_dumpall repeatedly prompts for a password.
Likely causeThe protected passfile does not match the host, port, database, and user used for each connection.
stat -c '%a %n' ~/.pgpasssed -E 's/:[^:]*$/:REDACTED/' ~/.pgpass
ResolutionCreate a specific mode-0600 password-file entry or approved service definition; do not place a password in argv.
Security notes
- Password verifiers in role SQL are sensitive authentication material.
Alternatives
- Manage roles and tablespaces declaratively and export the reviewed desired state alongside the backup.
Stop conditions
- Do not apply globals automatically to an existing cluster.
verification
Inspect and checksum
List archive contents and verify critical objects before moving it.
Why this step matters
The archive catalog and checksums detect wrong scope, corruption, and transfer changes before a restore consumes time or executes database code.
What to understand
pg_restore --list reads the table of contents without executing it. Review schemas, tables, TABLE DATA, functions, extensions, ACLs, and large objects expected by the application.
SHA-256 identifies both the custom archive and globals script. Verify the manifest at the independent destination, not only beside the source files.
For untrusted sources, use pg_restore --file to render SQL for security inspection in an isolated environment.
System changes
- Creates a checksum manifest and recovery evidence; does not connect to or modify the target database.
Syntax explained
pg_restore --list- Prints archive catalog entries without restoring them.
sha256sum- Calculates a content digest for artifact identity and transfer verification.
head -n 30- Limits the quick console preview; the full catalog still belongs in evidence.
Values stay on this page and are never sent or saved.
pg_restore --list {{backup}} | head -n 30 && sha256sum {{backup}} {{globalsBackup}} | tee {{backup}}.sha256TABLE public orders postgres TABLE DATA public orders postgres 9f7c... appdb-2026-07-25.dump 71b2... globals-2026-07-25.sql
Checkpoint: Approve archive scope and identity
sha256sum --check /srv/backups/postgresql/SHA256SUMS && pg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump >/dev/nullContinue whenEvery artifact reports OK and the archive catalog contains the expected critical objects.
Stop whenA checksum differs, the archive is unreadable, or any required schema, data, extension, function, or large object is absent.
If this step fails
The archive lists schema but no TABLE DATA entries.
Likely causeA schema-only option or selection filter was used unintentionally.
pg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump | grep 'TABLE DATA' | headpg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump | grep -E 'SCHEMA|TABLE ' | head
ResolutionReview the pg_dump invocation and create a new complete archive; do not treat the schema-only artifact as a data backup.
Security notes
- Catalog and globals inspection reveals internal names and identities; keep evidence restricted.
Alternatives
- Sign the checksum manifest when independent provenance verification is required.
Stop conditions
- Do not restore a catalog whose scope has not been reviewed.
command
Create an isolated target
Create a clean test database from template0.
Why this step matters
A database created from template0 gives the archive a predictable empty destination and an unmistakable disposable name, avoiding local additions inherited from template1.
What to understand
Connect through adminUrl to an isolated cluster and record server address, port, version, data directory, and system identifier before creation.
--template=template0 avoids languages or objects added locally to template1 that pg_dump may also carry, reducing duplicate-object conflicts.
Use a unique restore_test name and fail closed if it already exists. Existing content is evidence to inspect, not something to overwrite.
System changes
- Creates one empty disposable PostgreSQL database on the isolated target cluster.
Syntax explained
--maintenance-db={{adminUrl}}- Uses the reviewed maintenance connection to issue CREATE DATABASE.
--template=template0- Creates from PostgreSQL's pristine template rather than customized template1.
{{restoreDatabase}}- Names the uniquely disposable target.
Values stay on this page and are never sent or saved.
createdb --maintenance-db={{adminUrl}} --template=template0 {{restoreDatabase}}Database restore_test_20260724 created
Checkpoint: Prove a clean isolated target
psql --dbname=postgresql://restore_admin@restore.internal/postgres --tuples-only --command="SELECT datname FROM pg_database WHERE datname='restore_test_20260725';"Continue whenExactly the reviewed disposable database exists on the isolated cluster and has no user objects.
Stop whenThe connection resolves to production, the name is ambiguous, or the target already contains objects.
If this step fails
createdb reports a collation or locale mismatch.
Likely causeThe target operating system or collation provider cannot reproduce source database properties.
psql --dbname='postgresql://backup@db.internal/appdb' --command="SELECT datcollate,datctype FROM pg_database WHERE datname=current_database();"locale -a
ResolutionProvision a compatible isolated target or explicitly test a reviewed locale migration before restoring.
Security notes
- The maintenance identity should exist only on the isolated recovery environment where possible.
Alternatives
- Create a disposable container or VM cluster with the exact target major version and locale.
Stop conditions
- Never reuse or clean an existing database to save setup time.
command
Restore with ownership controls
Use --no-owner when target roles differ and stop on the first error.
Why this step matters
A complete pg_restore with exit-on-error is the central proof that catalog ordering, extensions, definitions, rows, indexes, constraints, and ownership policy can be reconstructed.
What to understand
--no-owner deliberately assigns created objects to the restore identity. This proves application data and schema recovery but not source ownership and ACL behavior.
--exit-on-error stops immediately instead of continuing after SQL failures. Capture stderr and timing; inspect every warning even when the process exits zero.
Parallel --jobs can reduce time for custom archives, but concurrency increases CPU, memory, connections, I/O, temporary space, and WAL. Establish a safe value through measurement.
Treat the archive as executable code and restore it only inside the trusted isolated boundary.
System changes
- Creates database objects, loads rows, builds indexes and constraints, and changes storage usage in the disposable target.
Syntax explained
--dbname={{restoreUrl}}- Pins the restore to the exact disposable database connection.
--no-owner- Skips restoration of original object ownership for this first isolated data test.
--exit-on-error- Stops at the first SQL execution error.
--verbose- Records detailed object progress and diagnostics.
Values stay on this page and are never sent or saved.
pg_restore --dbname={{restoreUrl}} --no-owner --exit-on-error --verbose {{backup}}pg_restore: creating TABLE "public.orders" pg_restore: processing data for table "public.orders"
Checkpoint: Require an error-free restore
psql --dbname=postgresql://restore_admin@restore.internal/restore_test_20260725 --tuples-only --command="SELECT count(*) FROM pg_class WHERE relkind IN ('r','p','v','m','S');"Continue whenpg_restore exits zero and the target contains the expected nonzero object inventory.
Stop whenAny error occurs, the target fills, an extension is missing, or the connection identity changes.
If this step fails
pg_restore fails while creating an extension.
Likely causeThe target lacks the extension package, trusted control file, or required privilege.
pg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump | grep EXTENSIONpsql --dbname='postgresql://restore_admin@restore.internal/postgres' --command="SELECT name,default_version,installed_version FROM pg_available_extensions ORDER BY name;"
ResolutionInstall the official compatible package and recreate the clean database before repeating the restore.
Security notes
- Use network isolation and avoid restoring untrusted procedural code with superuser privileges.
Alternatives
- Render archive SQL with pg_restore --file for review before execution.
Stop conditions
- Never validate or reuse a partially restored database after an error.
command
Refresh planner statistics
Run staged ANALYZE before representative performance checks.
Why this step matters
Bulk restore does not preserve ordinary planner statistics, so representative queries can be misleading or dangerously slow until statistics are collected.
What to understand
vacuumdb --analyze-in-stages first creates minimal statistics quickly, then progressively improves them for useful planning during a large recovery.
Statistics collection changes metadata and consumes CPU and I/O but not application row values. Measure it as part of total recovery time.
Custom extended statistics objects are schema objects, while their collected values may still need ANALYZE after restore.
System changes
- Updates optimizer statistics throughout the disposable restored database.
Syntax explained
vacuumdb- Runs maintenance commands through a PostgreSQL client.
--analyze-in-stages- Collects progressively richer statistics in multiple passes after a restore.
--dbname={{restoreUrl}}- Limits statistics work to the reviewed disposable database.
Values stay on this page and are never sent or saved.
vacuumdb --dbname={{restoreUrl}} --analyze-in-stagesGenerating minimal optimizer statistics
Checkpoint: Confirm usable planner statistics
psql --dbname=postgresql://restore_admin@restore.internal/restore_test_20260725 --command="SELECT count(*) FILTER (WHERE last_analyze IS NOT NULL OR last_autoanalyze IS NOT NULL) AS analyzed, count(*) AS tables FROM pg_stat_user_tables;"Continue whenEvery expected user table has a recent analyze timestamp or documented reason.
Stop whenANALYZE errors, target capacity is exhausted, or critical tables remain without statistics.
If this step fails
ANALYZE is much slower than the recovery window permits.
Likely causeTarget I/O, CPU, maintenance settings, or data volume differ from rehearsal assumptions.
SELECT pid,datname,relid::regclass,phase,sample_blks_scanned,sample_blks_total FROM pg_stat_progress_analyze;iostat -xz 1 5
ResolutionRecord the bottleneck, tune the isolated rehearsal safely, and revise recovery objectives or capacity before production need.
Security notes
- Run only on the isolated target and avoid logging sensitive query constants.
Alternatives
- Run plain ANALYZE or targeted ANALYZE for critical tables when a staged plan is not supported.
Stop conditions
- Do not run performance acceptance tests on an unanalyzed restore.
verification
Compare restored invariants
Check object counts, extensions, functions, and critical data.
Why this step matters
Catalog reconstruction is not sufficient evidence; extensions, functions, sequences, permissions, domain invariants, and representative application reads determine whether recovery is usable.
What to understand
Compare table and schema counts, extension versions, function inventory, sequence states, constraints, and critical row invariants with the source evidence.
Run a read-only application smoke path through the same driver settings the service uses. --no-owner means ownership and ACL checks must be tested separately.
Record expected and actual values, restore and analyze duration, archive checksum, target version, and every accepted discrepancy.
System changes
- No intended persistent change; reads restored objects and creates an external recovery evidence record.
Syntax explained
information_schema.tables- Provides portable table and view inventory for the public schema.
public.{{criticalTable}}- Queries the explicitly chosen representative restored table.
--tuples-only --no-align- Produces compact, stable evidence output.
Values stay on this page and are never sent or saved.
psql --dbname={{restoreUrl}} --no-align --tuples-only --command="SELECT count(*) FROM information_schema.tables WHERE table_schema='public'; SELECT count(*) FROM public.{{criticalTable}};"32 94120
Checkpoint: Approve restored behavior
psql --dbname=postgresql://restore_admin@restore.internal/restore_test_20260725 --no-psqlrc --tuples-only --command="SELECT count(*) FROM public.orders; SELECT extname,extversion FROM pg_extension ORDER BY extname;"Continue whenObject inventory, extensions, row invariants, sequence checks, and application reads match the approved source evidence.
Stop whenAny critical object, extension, invariant, permission path, or application read differs unexpectedly.
If this step fails
Rows match but the application receives permission denied.
Likely cause--no-owner intentionally omitted source owners and ACL dependencies, or the application role was not provisioned.
psql --dbname='postgresql://restore_admin@restore.internal/restore_test_20260725' --command='\dp' --command='\dn+'grep -E '^(CREATE|ALTER) ROLE' /srv/backups/postgresql/globals-2026-07-25.sql | head
ResolutionRehearse globals, ownership, and ACL restoration in a separate disposable cluster with reviewed roles; do not weaken public privileges.
Security notes
- Redact row values and credentials from evidence; prefer aggregate invariants.
Alternatives
- Run the application's automated read-only acceptance suite against the isolated connection.
Stop conditions
- Do not certify recovery from object counts alone.
command
Drop only the test target
Remove the isolated database after retaining results.
Why this step matters
Dropping the rehearsal database is irreversible and must occur only after the exact isolated cluster, target name, retained artifacts, logs, timings, and validation evidence are confirmed.
What to understand
Use the maintenance connection to display server identity and target sessions immediately before dropdb. Unexpected users or writes invalidate the cleanup assumption.
Terminate only known rehearsal sessions if necessary. Never pattern-match broadly across database names or clusters.
Retain archive, globals file, checksum manifest, operation logs, catalog, comparison evidence, and remediation notes according to policy.
System changes
- Permanently drops the exact disposable database and all restored objects inside it.
Syntax explained
dropdb- Issues DROP DATABASE for one exact database name.
--maintenance-db={{adminUrl}}- Uses the reviewed isolated maintenance connection.
{{restoreDatabase}}- Specifies the uniquely named disposable target.
Values stay on this page and are never sent or saved.
dropdb --maintenance-db={{adminUrl}} {{restoreDatabase}}Database restore_test_20260724 dropped
Checkpoint: Confirm destructive boundary
psql --dbname=postgresql://restore_admin@restore.internal/postgres --tuples-only --command="SELECT datname,numbackends FROM pg_stat_database WHERE datname='restore_test_20260725';"Continue whenOnly the exact disposable database is selected, expected sessions are closed, and recovery evidence is retained.
Stop whenThe server identity or name is unexpected, sessions are unexplained, or any evidence has not been exported.
If this step fails
dropdb fails because other sessions are connected.
Likely causeA test process, monitoring agent, or accidentally configured application still uses the target.
psql --dbname='postgresql://restore_admin@restore.internal/postgres' --command="SELECT pid,usename,application_name,client_addr,state FROM pg_stat_activity WHERE datname='restore_test_20260725';"
ResolutionIdentify every session and stop its owning test component; do not force-drop a database with unexplained use.
Security notes
- Log the exact server identity and target name for destructive-action review.
Alternatives
- Destroy a uniquely identified disposable VM or container after exporting evidence.
Stop conditions
- Never drop a database from an empty variable, wildcard, reused name, or unverified cluster.
Finish line
Verification checklist
pg_restore --list backup.dumpContains expected schema and TABLE DATA entries.psql --dbname=restore_test --command='SELECT 1'Returns one row.psql --dbname=restore_test --command='SELECT count(*) FROM critical_table'Matches source invariant.Recovery guidance
Common problems and safe checks
pg_dump refuses because the server is newer than the client.
Likely causeThe backup host uses an older pg_dump major version that cannot understand the source catalog.
pg_dump --versionpsql --dbname='postgresql://backup@db.internal/appdb' --tuples-only --command='SHOW server_version;'
ResolutionInstall a compatible current client and repeat the dump; never force an unsupported version mismatch.
pg_restore reports that a role or extension does not exist.
Likely causeCluster globals or extension packages were not provisioned on the isolated target.
pg_restore --list /srv/backups/postgresql/appdb-2026-07-25.dump | grep -E 'EXTENSION|ACL'psql --dbname='postgresql://restore_admin@restore.internal/postgres' --command='\du+' --command='\dx'
ResolutionInstall the trusted extension package and apply a reviewed ownership strategy in isolation, then recreate the clean target and restore from the beginning.
Parallel restore exhausts disk, I/O, memory, or connection capacity.
Likely causeThe chosen job count creates concurrent data, index, constraint, WAL, and temporary-file pressure beyond target limits.
df -h /var/lib/postgresql /srv/backupsfree -hiostat -xz 1 5psql --dbname='postgresql://restore_admin@restore.internal/postgres' --command='SHOW max_connections;'
ResolutionPreserve evidence, drop the failed disposable target, reduce jobs or increase isolated capacity, and repeat the full timed rehearsal.
The database restores but queries are unexpectedly slow.
Likely causePlanner statistics are absent after bulk load or target configuration differs materially.
vacuumdb --dbname='postgresql://restore_admin@restore.internal/restore_test_20260725' --analyze-in-stagespsql --dbname='postgresql://restore_admin@restore.internal/restore_test_20260725' --command='SELECT schemaname,relname,last_analyze FROM pg_stat_user_tables ORDER BY relname;'
ResolutionRefresh statistics, compare configuration and extensions, then rerun representative EXPLAIN and application reads before judging restore correctness.
Reference
Frequently asked questions
Does pg_dump block application writes?
It takes a consistent snapshot and does not block ordinary readers or writers, although it takes locks needed to prevent conflicting object drops and can add load.
Why save globals separately?
pg_dump covers one database and omits cluster-wide roles and tablespaces. pg_dumpall --globals-only records those dependencies, which require separate review and usually privileged restoration.
Why use --no-owner in the first rehearsal?
It isolates data and schema recovery from missing source roles. A separate disposable-cluster test must prove original owners, ACLs, tablespaces, and role memberships when those are recovery requirements.
Recovery
Rollback
The dump leaves the source unchanged; remove the isolated target.
- Confirm the database is the disposable target.
- Terminate only sessions connected to that target.
- Drop the test database.
- Retain archive, checksum, and evidence.
Evidence