Logical Backups with pg_dump/pg_restore and Object-Level Recovery

Learning outcomes

A ServiceHub operator deletes a reporting view and two small reference tables. Restoring yesterday's whole PostgreSQL cluster would recover them, but it would also roll back unrelated production work. This is the recovery problem that logical backup tooling solves: reconstruct database objects and data at SQL level, often selectively, and often across machines or major versions.

pg_dump creates a transactionally consistent export of one database. It does not dump cluster-global roles or tablespaces; those are handled by pg_dumpall. Archive-format dumps are restored with pg_restore, while a plain SQL script is replayed with psql. The engineering goal is not “a dump file exists.” It is “the restore path has been proven against the object scope and recovery objective we actually care about.”

1. Logical backup formats are recovery interfaces

Plain output is an SQL script. It is readable and replayed with psql, but it lacks pg_restore's object-selection and reordering interface. Custom format is a single archive file that supports selective and parallel restore. Directory format stores a table-of-contents plus separate data files; it supports parallel dump as well as parallel restore. These are not merely compression choices.

SELECT version();SHOW server_version;SHOW server_version_num;

CREATE TABLE IF NOT EXISTS app.ch13_recovery_events (    event_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    recovery_key text NOT NULL UNIQUE,    work_order_id bigint NOT NULL,    state text NOT NULL,    happened_at timestamptz NOT NULL DEFAULT clock_timestamp());INSERT INTO app.ch13_recovery_events (recovery_key, work_order_id, state)VALUES  ('baseline-001', 1001, 'accepted'),  ('baseline-002', 1002, 'accepted')ON CONFLICT (recovery_key) DO NOTHING;

pg_dump --dbname="service=servicehub-lab-admin" \  --format=custom \  --file=servicehub_ch13.dump \  --verbose

pg_dump --dbname="service=servicehub-lab-admin" \  --format=directory \  --jobs=4 \  --file=servicehub_ch13_dir

The actual elapsed time and best job count depend on CPU, storage, compression, table count, and source workload. Measure locally; do not copy a universal worker count.

2. Inspect before restoring

An archive is executable database content, not an inert spreadsheet. PostgreSQL warns that restoring a dump from an untrusted source can execute code chosen by source superusers. Inspect archive contents and treat the source trust boundary seriously.

pg_restore --list servicehub_ch13.dump | less

pg_dumpall --dbname="host=localhost port=55432 user=postgres" \  --globals-only \  --file=servicehub_ch13_globals.sql

pg_dump covers one database. Roles and tablespaces are cluster-level objects, so a complete logical recovery design must decide whether they are recreated by infrastructure automation, pg_dumpall --globals-only, or another controlled source of truth. Role passwords and external secrets require special handling and may deliberately be excluded.

3. Restore into a clean target, not over the source

DROP DATABASE IF EXISTS servicehub_restore_lab;CREATE DATABASE servicehub_restore_lab TEMPLATE template0;

pg_restore --dbname="postgresql://localhost:55432/servicehub_restore_lab" \  --table=app.ch13_recovery_events \  --verbose \  servicehub_ch13.dump

A selected table restore can depend on its schema, types, extensions, sequences, or owner existing in the target. Object selection is powerful, but it does not magically eliminate dependencies. For a repeatable lab, either restore the required pre-data objects too or prepare a compatible clean target.

SELECT count(*) AS rows,       count(*) FILTER (WHERE state = 'accepted') AS accepted_rows,       min(event_id) AS min_id,       max(event_id) AS max_idFROM app.ch13_recovery_events;SELECT conname, contype, pg_get_constraintdef(oid)FROM pg_constraintWHERE conrelid = 'app.ch13_recovery_events'::regclass;SELECT tableownerFROM pg_tablesWHERE schemaname='app' AND tablename='ch13_recovery_events';

4. Ownership and ACL strategy must be explicit

A restore can fail because original owners do not exist in the target. --no-owner makes restored objects owned by the restore role; --no-acl omits GRANT/REVOKE state. These are migration tools, not security defaults. If production relies on least-privilege grants, omitting ACLs without recreating them elsewhere changes the security model.

pg_restore --dbname=servicehub_restore_lab \  --no-owner \  --role=servicehub_owner \  --verbose \  servicehub_ch13.dump

5. RPO and RTO for a logical dump

Recovery Point Objective (RPO) is the maximum tolerable data loss measured in time or business state. For a nightly dump with no intervening log-based recovery, the theoretical RPO can approach the dump interval. Recovery Time Objective (RTO) is the acceptable time to restore service. Measure RTO from the start of the restore procedure through validation and readiness, not merely the time spent by pg_restore.

SELECT clock_timestamp() AS drill_start \gset-- run pg_restore in the shell, reconnect, perform validationSELECT clock_timestamp() AS drill_end,       clock_timestamp() - :'drill_start'::timestamptz AS observed_drill_elapsed;

6. Parallelism is a recovery tradeoff, not a default

Directory format is the only pg_dump format that supports parallel dump; custom and directory archives support parallel restore. More workers can reduce elapsed time when CPU, storage, and table-level independence permit it, but they also increase connections and I/O pressure. A production drill should record the exact worker count, source/target storage, cache state, and competing load so the measured RTO remains reproducible.

Parallel dump uses synchronized snapshots so workers see one consistent database snapshot. That consistency is per database. A set of independent pg_dump operations across several databases is not automatically one cluster-wide synchronized point, and pg_dumpall's per-database work should not be described as a single cross-database transaction snapshot.

Authoritative references

Backup and recovery behavior is version-, topology-, privilege-, and storage-sensitive. These primary PostgreSQL sources define the mechanisms used in this lesson.

• PostgreSQL 18 — pg_dump
• PostgreSQL 18 — pg_restore
• PostgreSQL 18 — pg_dumpall
• PostgreSQL 18 — SQL Dump