Topic: PostgreSQL

PostgreSQL backups: what pg_dump, WAL, and PITR actually do

Use an accidental order deletion to understand RPO and RTO, SQL versus custom archive restore tools, forward WAL replay, and PITR stopping points and restore checks.

Suppose a deletion without a WHERE clause commits at nine in the morning. Orders disappear, and the replica applies the deletion too. A backup folder contains several files. Which one should you open? Can you recover to 8:59? Will that also discard legitimate orders created afterward?

I would first ask which data point to recover and how soon the service needs to return, then choose the backup tools. Crunchy Data’s introduction to Postgres backups is a readable overview. Tool behavior below follows the PostgreSQL 18 documentation.

RPO measures data loss; RTO measures service interruption

Recovery Point Objective is the acceptable data-loss window. Recovery Time Objective is the acceptable service interruption. Both are targets set in advance. A drill tells you whether the system meets them.

Suppose a service fails at 20:00, recovered data reaches only 19:52, and core functionality returns at 20:35. The observed data gap is eight minutes and downtime is 35 minutes. With an RPO of five minutes and an RTO of 60 minutes, only the latter passes. A fast restore cannot recover missing data, and a recent backup does not guarantee a fast service recovery. Microsoft Learn explains the definitions and tradeoffs.

Define the end of the timer too. Fixing privileges, reconnecting file storage, and checking sign-in and order creation can all add time after the database finishes loading.

pg_dump exports logical database contents

A logical backup contains definitions and data needed to rebuild a database. A physical backup contains database-cluster files. Here, a PostgreSQL cluster means the databases managed by one server, not necessarily a group of machines.

pg_dump belongs to the first category and normally handles one database. It uses a consistent snapshot. If the snapshot is established at 10:00 and export finishes at 10:08, an order committed at 10:05 is not automatically included. Record the data cutoff separately from export completion.

The scope can change. --schema-only exports structure; --data-only exports data. The former helps inspect a schema, and the latter can support loading into compatible existing structures. Neither flag makes a complete recovery plan. Functions or settings in a schema can also be sensitive, so excluding rows does not automatically make an export safe to publish.

Normal reads and writes can run alongside pg_dump, although the export consumes resources. Its ACCESS SHARE locks conflict with operations requiring ACCESS EXCLUSIVE. Some migrations may therefore wait, or make the dump wait. Different ALTER TABLE operations require different locks. Check the lock modes before scheduling them together.

Choose the restore tool by format, not filename

Check how the file was produced and what it contains:

Renaming a custom archive from lesson.dump to lesson.sql changes none of its contents. A file named .backup may also contain plain SQL. The SQL Dump guide describes both restore paths.

For a known custom archive, this inspection does not connect to a database to write data:

pg_restore --list lesson.dump

It reads the archive’s table of contents. It does not prove that every object can be restored. The pg_restore documentation separates listing from loading. ANALYZE collects database statistics for the query planner. It does not inspect backup files.

The following examples assume an already-created, empty, disposable database named restore_lab. Choose the command for the actual format. Do not run them against production:

# Plain SQL
psql -X --set=ON_ERROR_STOP=on --dbname=restore_lab --file=lesson.sql

# Custom archive
pg_restore --exit-on-error --dbname=restore_lab lesson.dump

Stopping on an error does not roll back everything. Earlier successful commands may already have committed. Plan transactions, cleanup, and retries separately, and check versions and dependencies. Restore only trusted backups: a restore can execute code supplied by the source. These commands were not executed against a production database for this article.

WAL records changes that PostgreSQL can replay

WAL stands for Write-Ahead Logging. In simplified terms, PostgreSQL writes the relevant change records before flushing modified data pages to durable storage. After a crash, it can replay the necessary records to recover a consistent state. Configuration determines which durability stage a commit must reach before reporting success. The WAL introduction and commit settings explain the details.

WAL is not a SQL history file that psql can execute, nor a general undo log. Finding a pg_wal directory does not prove that weeks of history exist. Old segments may be recycled. Historical recovery needs a usable base backup, continuous retention of the required WAL, and monitoring.

PITR replays forward from an earlier state

Point-in-Time Recovery starts with a suitable physical base backup, then replays WAL from the same recovery chain to a chosen stopping point. pg_basebackup can create a physical base backup. A logical pg_dump export cannot serve as the starting point for this WAL recovery.

Consider an abstract timeline. Assume the base backup can reach a consistent 08:00 state and the subsequent WAL is continuous and matches it:

08:00 usable base state
  → replay legitimate orders from 08:30
  → stop before the erroneous transaction commits at 09:00
  × do not replay the committed deletion

A base state from 12:00 cannot be rewound to 08:59 by replaying WAL. The target must fall within the interval that the backup and WAL chain can actually recover. The continuous archiving guide also explains that this restores a whole cluster, not a single production table in place.

Check the stopping boundary separately. If an incident log records only whole seconds, “recover to nine” may include the bad transaction. recovery_target_inclusive controls whether a commit exactly at the target is included, and defaults to on. Verify commit timing, timezone, and inclusion behavior, then inspect the isolated restored database. Replaying to the newest WAL may replay the unwanted deletion too. Use the Recovery Target reference to check these settings.

A replica can replicate the mistake

If a bad DELETE has committed and the replica has applied it, promoting that replica will not recover the old rows. Replicas and historical recovery serve different needs. A late discovery also requires a retention window long enough to cover the original incident.

Recovering old rows does not justify overwriting the live table. Legitimate orders or updates may have arrived afterward. Restore in isolation, compare identifiers, differences, foreign-key relationships, and newer data, then plan a merge. Rewinding the whole production database also discards legitimate changes after that point.

Test whether the application can actually use the restored database

In a restore drill, I would check:

• The backup is readable, there are no unhandled restore errors, and important data and constraints are correct. Total row counts alone are insufficient.
• The target has the required roles, owners, grants, and extensions. A single-database dump does not create cluster-wide roles. Extensions may require compatible support files on the host.
• Application-role reads, writes, and core flows work with the required configuration and file storage. Disable jobs that could send email, charge payments, or call production webhooks in the isolated drill.
• Measure backup retrieval, loading, replay, dependency repair, and functional checks separately, then compare the result with RPO and RTO. If recovery takes three hours against a one-hour target, find the bottleneck, change the plan, and retest.
• Check shared failure risks. A different storage location offers limited isolation when the same credentials can delete every copy.

The privileges guide covers ownership and object access. CREATE EXTENSION explains host-side requirements. Finishing a database restore completes only part of service recovery.

What I learned

• I would check the recovered data point and service interruption separately against RPO and RTO before choosing backup frequency and tools.
• I would choose psql or pg_restore by contents. Listing an archive starts an inspection; it is not evidence of successful recovery.
• I understand PITR as forward replay from a base state. Recovering deleted data requires a verified stopping boundary and a plan to preserve legitimate later changes.
• I would include roles, dependencies, and application behavior in a drill. These concepts still need practical measurement before I can claim a system meets its targets.

External references

• PostgreSQL 18: Backup and Restore, for choosing a method.
• PostgreSQL 18: pg_dump, pg_restore, and psql, for formats, options, and error handling.
• PostgreSQL 18: Continuous Archiving and PITR, for the base-backup and WAL relationship.

Further learning

• Philip Hurst, Crunchy Data: Introduction to Postgres Backups, for a readable overview before checking implementation details.
• Sonia Valeja, PGConf India 2025: pgBackRest, for how tooling manages backup and restore. The original Percona event page identifies the speaker and session. Watch after building the basic concepts.