Module 9 · Operations

Backup, Restore, and Point-in-Time Recovery

Lesson 25 of 30 12 min read Updated

Backups answer a simple operational question: after a disk failure, bad deployment, dropped table, corrupted filesystem, or mistaken update, what exact PostgreSQL state can you recover, how long will it take, and how much committed work might be lost? Restore and point-in-time recovery are the proof. A backup strategy that has never been restored is only a hope with a timestamp.

In this PostgreSQL operations lesson, the outcome is concrete: you should be able to choose between logical and physical backup methods, explain how write-ahead log archiving makes point-in-time recovery possible, run a restore into a separate cluster, and diagnose common failures before they become outages.

How PostgreSQL Recovery Works

PostgreSQL durability is built around the write-ahead log, usually called WAL. Before PostgreSQL considers a transaction durable, the change is represented in WAL records that can be replayed later. Data files are written lazily by background processes and checkpoints, so the files under the data directory are not a complete history by themselves. WAL is the ordered stream that lets PostgreSQL bring data files from one consistent point to a later consistent point.

A physical backup copies the database cluster at the file level: relation files, visibility maps, catalogs, configuration-adjacent state, and control files. Because the server continues changing files while the copy runs, a valid physical backup also needs the WAL generated during the backup window. PostgreSQL records backup start and end information so recovery can replay WAL until the copied files reach a consistent state.

A logical backup, normally produced with pg_dump or pg_dumpall, exports SQL-level objects and data. It is excellent for migrations, selective restores, upgrades across major versions, and human inspection. It does not preserve physical details such as heap layout, index files, or WAL position. Logical backups restore by executing SQL, so they are slower for very large databases but more flexible when you need a subset or a transformed copy.

Point-in-time recovery, or PITR, combines a physical base backup with archived WAL. You restore the base backup into a new data directory, configure PostgreSQL to fetch WAL files from an archive, create a recovery signal, and optionally set a recovery target. PostgreSQL replays WAL until it reaches the chosen transaction ID, timestamp, named restore point, log sequence number, or immediate consistency point. The recovered cluster then promotes into normal read-write service.

Backup Anatomy

The main moving parts are the base backup, WAL archive, restore command, recovery target, and validation process. The base backup gives PostgreSQL a starting filesystem image. The WAL archive stores completed WAL segment files outside the primary data directory. The restore command tells a recovering server how to retrieve a requested WAL segment. The recovery target tells replay where to stop. Validation proves the result is usable, complete enough, and isolated from production.

Backup choice starts with recovery objectives. Recovery point objective, or RPO, is the amount of data you can afford to lose. Recovery time objective, or RTO, is the time you can afford to spend restoring. A nightly logical dump may satisfy a small internal tool with a one-day RPO. A revenue database usually needs continuous WAL archiving, frequent base backups, monitored archive lag, and rehearsed failover or restore steps.

Example 1: Logical Dump and Restore

A logical dump is the most direct way to capture database contents in a portable form. The custom format is usually preferable to plain SQL because pg_restore can list objects, restore selected objects, and use parallel workers for suitable restore phases.

pg_dump --format=custom --file=/tmp/appdb.dump appdb
createdb appdb_restore
pg_restore --dbname=appdb_restore --clean --if-exists /tmp/appdb.dump
psql --dbname=appdb_restore --command="SELECT count(*) AS orders FROM orders;"

The first command writes a custom-format dump of appdb. The restore goes into appdb_restore, not back into the source database, which keeps verification safe. The final query is a simple deterministic check when the expected order count is known from production monitoring or a pre-backup query. If the source had 1842 rows in orders, the restored database should report 1842. A mismatch means the dump, restore, or validation query is not describing the same scope.

Logical dumps are not continuous recovery. If the dump started at 01:00 and a table was dropped at 15:00, the dump alone cannot recover the database to 14:59. It can restore the 01:00 state or individual objects from that snapshot.

Example 2: Physical Base Backup with WAL Archiving

Physical recovery needs WAL outside the primary data directory. The archive command runs when PostgreSQL finishes a WAL segment and copies it to durable storage. In a real system the archive destination is often object storage, a backup appliance, or another host with retention controls.

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/wal_archive/%f && cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 300

The archive command must be idempotent in the practical sense: if PostgreSQL retries it, an already archived segment should not be overwritten with different content. PostgreSQL treats exit status zero as success. If the command fails, WAL accumulates in pg_wal, and the server can eventually fill the disk. The archive_timeout setting bounds how long a low-write system waits before closing a segment, trading extra archive files for lower potential data loss.

pg_basebackup --pgdata=/backups/base_2026_09_06 --format=plain --write-recovery-conf --checkpoint=fast --progress

This produces a file-level copy that can become a standby or a restore source. The expected behavior is that the command prints progress and exits successfully after copying the cluster. The backup is not complete operationally until you know the required WAL segments are archived and retained. Losing a WAL segment between the base backup start and recovery target can make the backup unusable beyond the last available consistent point.

Example 3: Recover to Before a Mistake

Assume an operator accidentally ran DELETE FROM invoices; at 2026-09-06 14:17:30 UTC. If you have a base backup from earlier that day and complete WAL archives, recover into a new data directory and stop before the mistake.

restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-09-06 14:17:20+00'
recovery_target_action = 'promote'
touch /restore/pgdata/recovery.signal
pg_ctl --pgdata=/restore/pgdata start
psql --dbname=appdb --command="SELECT count(*) AS invoices FROM invoices;"

During startup, PostgreSQL sees recovery.signal, enters archive recovery, asks the restore command for WAL files, and replays records until the target time. With recovery_target_action = 'promote', it stops recovery and opens as a normal primary. The invoice count should match the last valid count before the bad delete. You would usually keep this recovered cluster isolated, inspect it, and then decide whether to export missing rows, replace the original primary, or perform an application-level repair.

Design Choices and Trade-offs

Logical backups are flexible and easy to inspect, but they are not enough for low RPO recovery. They also require replaying SQL, rebuilding indexes, and respecting dependencies during restore. Physical backups are faster for whole-cluster recovery and support PITR, but they are tied to PostgreSQL storage format, architecture assumptions, and major-version compatibility rules.

Full backups are simple to reason about but consume storage and network bandwidth. Incremental or differential approaches reduce copy volume when provided by external tooling, but they add catalog, chain, and validation complexity. Compression saves storage and transfer time at the cost of CPU. Encryption protects backup media, but key loss is data loss, so key rotation and escrow need the same seriousness as the backup files.

Retention should be policy-driven. Keeping seven daily base backups is not meaningful if WAL is retained for only two days and your target restore point is five days old. Conversely, keeping old WAL without a compatible base backup only burns storage. Design retention around restore windows, audit needs, legal holds, and the time required to discover human errors.

Failure Modes and Troubleshooting

Symptom: pg_wal grows until the primary disk is nearly full. Cause: the archive command is failing, so PostgreSQL cannot recycle WAL segments. Diagnose: check server logs for archive failures, run SELECT * FROM pg_stat_archiver;, and verify destination permissions and free space. Correct: fix the archive destination, rerun a safe copy for missing segments if available, and alert on failed archive count and archive lag.

Symptom: recovery stops with a missing WAL file message. Cause: the archive does not contain a segment required after the base backup checkpoint. Diagnose: identify the requested WAL file in the log, compare it with archive inventory, and confirm the base backup label or manifest. Correct: use an older base backup with a complete WAL chain, restore only to the last available point, or recover from another replica or storage snapshot.

Symptom: a restored logical dump fails with ownership or extension errors. Cause: roles, privileges, extensions, or target database settings differ from the source. Diagnose: run pg_restore --list, inspect the first failing object, and compare installed extensions and roles. Correct: pre-create required roles and extensions, use restore options such as owner suppression when appropriate, and document the bootstrap sequence.

Symptom: PITR succeeds but contains the bad data change. Cause: the recovery target was after the mistake, the server used local WAL beyond the intended point, or the timestamp was interpreted differently than expected. Diagnose: compare database logs, application audit records, transaction timestamps, and timezone offsets. Correct: restore again to an earlier target or use a named restore point created before risky maintenance.

Security, Performance, and Reliability

Backups contain the database, not a harmless copy. They often include personal data, password hashes, API tokens stored by applications, and deleted rows still present at the logical time of the backup. Encrypt backups in transit and at rest, restrict restore privileges, audit access to archives, and test key recovery. A developer convenience dump from production can violate the same data rules as direct production access.

Performance impact depends on method. Logical dumps read tables and can stress I/O, caches, and long-running snapshot behavior. Physical base backups stream data files and WAL, so they can compete with normal workload for disk and network. Fast checkpoints shorten backup startup but can create write spikes. Schedule backups with workload patterns in mind, throttle where tooling allows it, and monitor replication slots or archive retention so backup consumers do not accidentally retain unlimited WAL.

Hands-on Lab

Prerequisites: a disposable PostgreSQL instance, permission to create databases, enough local disk for a dump and restore, and a shell with PostgreSQL client tools. Do not run this lab against a production database.

  1. Create a small database with two tables and several rows.
  2. Take a custom-format logical backup.
  3. Restore it into a different database name.
  4. Verify row counts and a representative constraint.
  5. Drop the restored database as cleanup.
createdb pitr_lab
psql --dbname=pitr_lab --command="CREATE TABLE accounts (id integer PRIMARY KEY, email text NOT NULL UNIQUE);"
psql --dbname=pitr_lab --command="INSERT INTO accounts VALUES (1, 'a@example.com'), (2, 'b@example.com');"
pg_dump --format=custom --file=/tmp/pitr_lab.dump pitr_lab
createdb pitr_lab_restore
pg_restore --dbname=pitr_lab_restore /tmp/pitr_lab.dump
psql --dbname=pitr_lab_restore --command="SELECT count(*) AS restored_accounts FROM accounts;"
psql --dbname=pitr_lab_restore --command="INSERT INTO accounts VALUES (3, 'a@example.com');"
dropdb pitr_lab_restore
dropdb pitr_lab

Verification has two parts. The count query should return 2, proving the rows restored. The final insert should fail with a duplicate key violation, proving the unique constraint restored as well as the table data. Cleanup removes only the lab databases; the dump file can be deleted separately after inspection.

Assessment Exercises

  1. A database takes nightly logical dumps at midnight. A migration corrupts data at 16:00 and the problem is discovered at 18:00. What state can be restored from the dump alone, and what data is at risk?
  2. You have a base backup from Monday and WAL archives for Wednesday and Thursday only. Explain why Thursday PITR may still fail.
  3. Design a restore drill for a database with a four-hour RTO. What measurements prove the drill met the objective?
  4. An archive command copies WAL to object storage but sometimes overwrites existing objects. Why is this dangerous, and how would you change the command or storage policy?
  5. During PITR, why might a named restore point be safer than a wall-clock timestamp before a risky maintenance operation?

Summary

PostgreSQL backup strategy is a chain: a usable backup method, complete WAL where PITR is required, restore commands that can fetch the right files, recovery targets that stop at the intended state, and verification that proves the result. Logical dumps are portable and selective. Physical base backups plus WAL archiving provide whole-cluster recovery and point-in-time control. The operational discipline is to restore regularly, monitor archive health, protect backup data like production data, and make every recovery objective measurable.

Progress is saved in this browser.