LEARN. PRACTICE. GROW.Compare values and invariants
The first check reads identifiers, references and units on the target and compares them with the exported point. A count of two rows would be compatible with incorrect values or swapped references, so it cannot serve as the only criterion. The lab confirms A=40 and B=60 before performing new acceptance writes. Define which invariants matter to the service: totals, relationships, states or allowed ranges. Distinguish recovered data from data created by subsequent tests so the experiment itself does not appear as divergence. In production, functional reconciliation needs an owner and agreed criteria.
Test the runtime path and owner
After full restoration, the table belongs to deploy_owner and an actual runtime connection can read total 100. The same identity inserts a new reference with RETURNING id. These operations exercise schema, table, sequence and identifier reading, going beyond an administrative query. In another database, --no-owner --no-acl restores the same values, but ownership becomes dr_lab and runtime receives 42501. The contrast demonstrates that data portability and authorization preservation are distinct objectives. If target remapping is intentional, define the new grants and repeat testing with the intended identities.
Preserve sequence semantics
At the source, two rows have identifiers 1 and 2. An additional nextval call and an insertion later rolled back advanced the sequence to 4. The dump preserves that state; the first runtime insertion on the target receives 5. This is neither a counting error nor a row lost during restoration. Sequences can have gaps and do not act as gapless transactional counters. Avoid replacing recovered state with max(id) without analyzing external contracts, calls already made and reuse risk. The lab demonstrates this controlled sequence without concurrency or a guarantee of business-transaction time ordering.
Confirm rejection and integrity
Usable recovery should also preserve operations that are not permitted. After a valid insertion, the experiment attempts negative units and a duplicate A reference. Restored constraints reject the operations with 23514 and 23505; the count remains three rows. Record the structured diagnostic and confirm state instead of treating any error message as a successful negative test. This set does not cover every possible constraint, trigger or function in another application. The real matrix must derive from the service schema and invariants, including external effects that SQL rollback cannot undo.
Distinguish structure and data in partial recovery
In a third target database, --data-only fails because application objects do not yet exist. The experiment then applies --schema-only, confirms that ledger exists empty and applies data from the same archive. It recovers two rows, total 100 and next sequence value 5. This order demonstrates one compatible composition; it does not prove that every partial dump is independent. When selecting tables or schemas, inventory dependencies and supporting objects that filtering may exclude. Do not combine one version’s structure with another version’s data without explicitly validating columns, constraints, types and required transformations.
Close acceptance with clear limits
After recovery, ANALYZE updates information used by the planner; the experiment confirms an estimate of two rows. That result does not demonstrate acceptable latency, throughput or RTO compliance. For the actual service, measure the complete path: obtaining the backup, preparing the target, restoration, reconciliation, application and the resume decision. Also identify how RPO will be met if changes occurred after the recovered point. PITR, crash, remote restore, pooling, load and a human workshop were not executed here. The report should retain what passed and turn unexercised areas into acceptance work without presenting the lab as completed production recovery.
# Target database and required roles already prepared in the owned lab.
pg_restore -h /owned/target/socket -U dr_lab -d restored --single-transaction original.dump
# Connect as runtime for application-path acceptance:
# SELECT id, ref, units FROM app.ledger ORDER BY id;
# INSERT INTO app.ledger(ref, units) VALUES ('recovered',7) RETURNING id;
# ANALYZE is performed by the administrator after recovery.The recovered table has ids 1 and 2, but the first runtime insert returns id 5 because sequence state was preserved.
Common pitfalls
Accepting by count alone; ignoring grants; resetting sequences from max(id); treating ANALYZE as SLA evidence.
Related topics: Logical recovery and dependencies · Operational acceptance and evidence
Usable recovery requires checking operations the service needs, with experiment limits and gaps recorded.
Reference: Archive restoration and error handling · PostgreSQL 18 reference semantics;18.6 current stable at review