LEARN. PRACTICE. GROW.Define the contract before exporting
A logical backup should answer a concrete need: migration, a diagnostic copy or object recovery. In the lab, the contract includes two rows, their values, constraints, sequence state, owner and runtime access. Two disposable PostgreSQL 18.6 clusters are used, both local with private sockets. Recovery time for a representative database is not measured, and WAL or PITR is not exercised. Before applying the example at work, identify volume, extensions, external dependencies and service recovery objectives. Success of this small contract does not by itself demonstrate that the strategy suits an entire production database.
Inspect a custom archive
pg_dump produces a custom archive, and pg_restore --list confirms table-data and sequence-state entries. Listing helps compare the expected inventory with the produced artifact. It does not execute commands on the target, check available roles or prove that the application can work afterward. The lab records the exit code and retains the archive throughout several restore attempts. A hash before and afterward confirms that those attempts did not alter its bytes. That equality does not demonstrate source authenticity, data freshness or functional correctness; these are different properties requiring their own evidence.
Compare against the exported point
Before the dump, A contains 40 units and B contains 60. After the command finishes, the source changes A to 999 and adds late with 5 units. The restored target contains A=40 and B=60 without late. Comparing it only with the source’s current total would produce a false diagnosis of incorrect restoration. Record the reference point and later changes requiring reconciliation. This experiment orders writes after the dump and does not demonstrate concurrency during export. It also does not allow choosing an arbitrary later time: recovering those changes requires another mechanism or authorized process.
Separate database data from global objects
The database archive references deploy_owner and runtime, but the new cluster does not contain those roles. The first attempt fails because the owner is missing. Separately, pg_dumpall --globals-only produces a script with global definitions and no ledger table. The script is inspected using --no-role-passwords, and only the two required fictional roles are created on the target. The exported global set is not executed. In real recovery, review identities, memberships, tablespaces and configuration against the authorized target. Blindly copying a global set can recreate capabilities that do not belong in the recovery environment.
Choose error behavior
The initial attempt uses --single-transaction. Missing deploy_owner causes an error exit; afterward, the target catalogue confirms that the app schema was not left behind. The restored database, prepared before the command, still exists. This difference defines what the restore transaction covers. Do not extrapolate the observation to every execution with different options: stopping at the first error is not equivalent to undoing already committed commands. Record options, diagnostic and final state. For larger volumes, also evaluate long-transaction cost, locks and incompatibility with parallelism before adopting the same mode.
Prepare a retry decision
After identifying the missing dependency, the lab creates target roles and reuses the same archive in the database left without application objects. The second execution succeeds. A real operation should first confirm the state left by the previous attempt and deliberately choose a clean target or controlled repair. Do not routinely add --clean against an unknown database, because it can remove objects. Keep source, target and parameters explicit in the runbook. The experiment uses no real credentials, connects to no external networks and removes only the temporary clusters and files it created itself.
# Only synthetic data in the disposable lab. Use owned socket paths.
pg_dump -h /owned/source/socket -U dr_lab -d original -Fc --compress=0 -f original.dump
pg_restore --list original.dump
pg_dumpall -h /owned/source/socket -U dr_lab --globals-only --no-role-passwords -f globals.sql
# The lab inspects globals.sql; it does not execute this script on the target.The archive contains two rows totaling 100. Later changes take the source to three rows totaling 1064 without modifying the backup.
Common pitfalls
Confusing listing with restoration; assuming pg_dump creates global roles; comparing the target with a source that has already changed.
Related topics: Logical recovery and dependencies · Operational acceptance and evidence
The archive is part of the plan: dependencies, options, data point and acceptance criteria must be explicit.
Reference: Logical database export · PostgreSQL 18 reference semantics;18.6 current stable at review