Runbook: Take a Logical Backup and Prove It Restores

1 · Prerequisites

Confirm every item is in place before any state change.

• The database to dump, and a written statement of what the backup is for: migration, pre-change safety copy, or an ongoing recovery capability
• A role with read access to everything that must be captured, and superuser or pg_read_all_data if the dump must include objects owned by others
• Storage for the dump with enough free space, measured rather than assumed, and somewhere to restore it that is not production
• Knowledge of whether the estate also takes physical backups, because a logical dump is not a substitute for one
• A maintenance consideration: a long dump holds a snapshot open, which stops vacuum removing dead rows cluster-wide for its duration
• The PostgreSQL version of the source and of the intended restore target, because pg_dump can write forward but not backward

2 · Pre-checks

Read-only diagnostic commands. If any of these don't match expected output, stop and investigate further.

• · Measure the space you need. SELECT pg_size_pretty(pg_database_size(:db)); gives the on-disk size; a custom-format dump is usually smaller and a plain-text one is often larger. Check the destination has room with df -h rather than assuming.
• · Confirm the client version is at least the server version. pg_dump --version and SELECT version();. A newer pg_dump can dump an older server; the reverse fails, and it fails after the dump has been running for a while.
• · Estimate how long the dump will hold a snapshot. pg_dump runs in a single repeatable-read transaction, so for its entire duration vacuum cannot remove dead rows anywhere in the cluster. On a large database this is a real cost, and it belongs in the change note.
• · Confirm what the dump will and will not contain. A database-level pg_dump excludes roles, tablespace definitions and other cluster-wide objects. Those come from pg_dumpall --globals-only, and a restore without them fails on missing roles.
• · Check for large objects and extensions. SELECT count(*) FROM pg_largeobject_metadata; and SELECT extname, extversion FROM pg_extension;. Extensions are dumped as CREATE EXTENSION, so the target must have the extension available — the dump does not carry its code.
• · Confirm the restore target exists and is not production. A restore into a live database is the fastest way to turn a verification into an incident.
• · Decide the format before starting. Custom (-Fc) or directory (-Fd) formats support selective restore and parallel restore; plain SQL does not. Directory format additionally supports parallel dump with -j.

3 · Procedure

Execute each step in order. Verify the expected output of a step before moving to the next.

1. 1Dump the globals first, separately. pg_dumpall --globals-only -f globals.sql. Roles, role memberships and tablespaces live here and nowhere else. A restore that skips this step fails with role "app_ro" does not exist at the first GRANT.
2. 2Dump the database in a restorable format. pg_dump -Fc -d orders -f orders.pgdump for custom format, or pg_dump -Fd -j 4 -d orders -f orders.dir/ for a directory-format parallel dump.
3. 3Capture the exit status without a pipe. pg_dump -Fc -d orders -f orders.pgdump; echo "exit=$?". In a shell pipeline $? reports the status of the last command, so pg_dump ... | tee log reports tee's success and discards pg_dump's failure.
4. 4**If you need the output piped, set pipefail or read PIPESTATUS.** set -o pipefail before the pipeline, or echo "${PIPESTATUS[0]}" after it. Whichever you choose, put it in the script rather than trusting the operator to remember.
5. 5Record the dump's size and duration. Both belong in the change note, because the restore time is what the recovery objective is measured against and the dump time is what the snapshot cost.
6. 6List the dump's contents without restoring it. pg_restore -l orders.pgdump | head -40. This reads the table of contents and proves the file is a readable archive, which is a cheap first check on a file you have not tested.
7. 7Create a clean restore target. createdb orders_verify on a non-production cluster.
8. 8Restore the globals into the target cluster if the roles are not already present: psql -f globals.sql. Expect errors for roles that already exist; those are benign, and any other error is not.
9. 9Restore the dump, capturing the exit status the same way. pg_restore -d orders_verify -j 4 orders.pgdump; echo "exit=$?". Parallel restore with -j is substantially faster on a multi-core host and works only with custom or directory format.
10. 10Read the restore output for errors, not only the exit status. pg_restore continues past some errors by default and still exits non-zero; --exit-on-error makes it stop at the first one, which is usually what you want during a verification.
11. 11Count rows in the restored database and compare against the source. For the tables that matter: SELECT count(*) FROM orders; on both. A restore that produced a schema and no data exits zero and looks identical to success in every other respect.
12. 12Compare object counts as well as row counts. SELECT count(*) FROM pg_class WHERE relkind IN ('r','i','S','v','m'); on source and target. A missing index or sequence is invisible in a row count and expensive later.
13. 13Record the verification: dump size, dump duration, restore duration, the row counts compared, and the object count comparison.

4 · Verification

Confirm the procedure actually fixed the problem.

• ✓pg_dump exited zero, read without a pipe or read from PIPESTATUS[0].
• ✓pg_restore -l lists the expected schemas and tables, from the file you intend to keep rather than from a copy.
• ✓pg_restore completed and its output contains no errors, checked line by line rather than by exit status alone.
• ✓Row counts for the significant tables match the source, taken at a point that accounts for writes since the dump began.
• ✓Object counts by relkind match between source and restored database — tables, indexes, sequences, views and materialised views.
• ✓Sequences are at sensible values: SELECT last_value FROM <sequence>; on both. A restored sequence behind its table produces duplicate key errors on the first insert.
• ✓The restore duration is recorded and compared against the recovery time objective. A backup that restores in eleven hours does not meet a four-hour objective, and the only way to know that is to have restored it.

5 · Rollback

If verification fails, undo the procedure in reverse order.

• ↶Taking a dump changes nothing on the source, so there is nothing to roll back from the backup itself. The rollback section here covers the verification, which does write.
• ↶Drop the verification database when finished: dropdb orders_verify. Leaving it behind is how a stale copy of production data ends up in an inventory nobody expected.
• ↶If globals were restored into a shared non-production cluster, review what was created. pg_dumpall --globals-only includes every role in the source cluster, and restoring it can add roles that do not belong on the target.
• ↶If a dump was accidentally restored into a live database, stop immediately and treat it as a data incident rather than continuing. pg_restore into a populated database can duplicate rows, fail partway, and leave the schema half-modified.
• ↶If the dump file is being discarded, delete it from every location it was copied to. A dump is a complete copy of the data with none of the database's access controls.
• ↶If the dump was taken to enable a change and the change is being abandoned, keep the dump until the decision is final, then remove it deliberately with a note saying so.

6 · Escalation

When the runbook isn't enough, contact:

• · pg_dump fails partway through with a permission error: escalate rather than re-running as a superuser by reflex. The set of objects the dumping role cannot read is information about the schema's ownership, and a superuser dump conceals it.
• · The dump duration is long enough that the held snapshot is causing bloat on the source: escalate to whoever owns the maintenance window. The answer may be to dump from a standby instead, which removes the cost from the primary entirely.
• · Row counts do not match and the difference is not explained by writes during the dump: escalate. A dump that is missing data is a more serious finding than a dump that fails.
• · The restore requires an extension the target cluster does not have: escalate to the platform owner. The dump carries CREATE EXTENSION, not the extension itself, and this failure appears only at restore time.
• · The restore time exceeds the recovery time objective: escalate to the service owner with the measured number. This is a capability gap, not a database fault, and it is usually solved with physical backups rather than by making the dump faster.
• · This logical dump is the estate's only backup: escalate. A logical dump cannot perform point-in-time recovery, takes longer to restore than a physical backup, and captures a single moment; an estate relying on one alone has a recovery objective nobody has agreed to.

References

1. PostgreSQL 18 documentation, SQL Dump
2. PostgreSQL 18 documentation, pg_dump
3. PostgreSQL 18 documentation, pg_restore
4. PostgreSQL 18 documentation, pg_dumpall