Verify a backup by restoring it into an isolated destination and checking that the application can use the recovered state. A successful backup command, a nonempty file, and a matching checksum are useful evidence about creation and storage. None of them alone proves recovery.
For PostgreSQL, choose the recovery question first: restore a database snapshot, recover an entire cluster, or stop at a particular point in time. This guide uses a small logical dump and a real local restore rehearsal. It does not test a production system, a blog backup, or disaster-recovery timing.
Decide what “restored” must mean
Write down the target before running commands. For a synthetic order service, the target might be: the expected customers and orders exist, every order still refers to a customer, the order total is correct, and the next order can be inserted without an identity collision.
For a real service, that target also includes ownership, access privileges, extensions, application compatibility, and external objects such as uploaded files. A database that accepts connections may still be unusable because the application role cannot read its tables or its object storage was never recovered.
Separate two objectives. Recovery point is how much recent data loss is acceptable. Recovery time is how long the service may remain unavailable. Define when the recovery clock starts and ends: retrieving a key, locating the archive, provisioning a destination, restoring data, and validating the application can all matter. A stopwatch around pg_restore measures only one part.
Match the backup method to the failure
PostgreSQL 18's SQL-dump documentation explains logical snapshots and their restore paths: plain SQL is loaded with psql, while archive formats use pg_restore. A logical database dump is not a complete replacement for every cluster-level object or external dependency.
If the requirement is to recover immediately before an accidental deletion, a daily logical snapshot may leave too large a gap. PostgreSQL's point-in-time recovery documentation describes a different design based on a base backup and retained write-ahead log records. That procedure needs its own rehearsal; restoring a custom-format dump does not test WAL continuity or a recovery target.
The choice follows the failure you need to survive. Losing the database host, deleting one table, losing an encryption key, and compromising the administrative account are different scenarios. Document which copies and credentials remain available in each one before assuming that another copy on the same machine is sufficient.
The local rehearsal
We used an already available PostgreSQL 18.6 container image, with networking disabled, no published ports, no host data directory mounted, and a temporary database filesystem. The container was removed afterward. All data was synthetic; no external database was contacted.
The source database contained two customers and three orders totaling 4,200 integer cents. An order's customer reference had a foreign-key constraint. The order identifier used a generated identity sequence.
After making a custom-format dump, we inserted a fourth order into the source. The restore went into a separate, empty database created from template0. The following commands show the database operations inside the disposable environment; connection settings and authentication must identify your own isolated test instance.
pg_dump --format=custom --file=fixture.dump source_lab
createdb --template=template0 restored_lab
pg_restore --exit-on-error --single-transaction \
--no-owner --no-acl --dbname=restored_lab fixture.dumpThe PostgreSQL 18 pg_restore reference documents these options. This rehearsal intentionally omitted original ownership and access-control restoration. That simplifies a portable data exercise, but it also means the result provides no evidence that production roles or permissions would recover correctly.
The observed checks were:
| Check on restored data | Result |
|---|---|
| Order count before adding a new test row | 3 |
| Total before adding a new test row | 4,200 cents |
| Orders without a matching customer | 0 |
| Next generated order identifier | 4 |
| Fourth source order inserted after the dump | Absent from the restored snapshot, as expected |
The archive was 3,527 bytes. Those tiny inputs are useful for checking mechanics and assertions, not for estimating recovery time, compression ratio, or throughput for an application database.
Check invariants as well as counts
A row count can match while important values are wrong. Choose checks that express the service's contracts. For this fixture, the aggregate and reference check were:
SELECT count(*), sum(cents) FROM orders;
SELECT count(*)
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;The final insert checked that the restored identity state could support a new write. In an application rehearsal, also run a narrow read-and-write workflow using the application's ordinary role. Keep outbound email, webhooks, payments, and scheduled jobs disabled or redirected to controlled test services so that recovery validation does not replay real side effects.
If a check fails, retain the error and identify whether the archive, restore command, destination prerequisites, or expected value is wrong. Do not silently accept partial data because the restore process produced some tables. A nonzero restore result is evidence to investigate, not a cosmetic warning to omit from the report.
Make the result repeatable by another operator
Keep a sanitized record of the source and destination versions, backup method, creation time, artifact identity, retrieval path, restore command, validation queries, results, and missing prerequisites. Keep the actual backup and sensitive connection details in their protected storage, outside the source repository.
Rehearse with the access that would remain during the intended incident. A procedure that works only because its author has an unrecorded key or an existing administrator session has an untested dependency. Include retrieval and decryption in the exercise when those are part of the real recovery path.
Repeat the rehearsal after changes to schema, extensions, PostgreSQL versions, backup tooling, credentials, retention, or storage. Periodic runs also catch dependencies that drift without an application release. The schedule should follow the service's recovery needs and rate of change.
This local exercise established that one synthetic logical snapshot restored its expected data and identity state. A production recovery claim requires the same kind of observable acceptance criteria at the scope and scale of the actual service.
