PostgreSQL Backup and Restore: pg_dump, PITR and Restore Testing

What a PostgreSQL backup captures: logical dumps vs physical base backups

A PostgreSQL backup comes in one of two families, and they protect against different failures.

A logical backup, made with pg_dump or pg_dumpall, is a description of your database written as SQL statements or as an archive that pg_restore can read. It is a consistent snapshot of the schema and data taken at the moment the dump began. Because it is rebuilt from the definitions in the dump, you can load it into a newer major version, onto a different CPU architecture, or into a cluster with a different on-disk layout. Index and table storage is not copied. Indexes are rebuilt from scratch during the load, so restoring a large database can be slow.

A physical backup copies the data directory itself. pg_basebackup takes the base copy, and continuous archiving keeps the write-ahead log (WAL) produced afterwards. When you restore, PostgreSQL replays the archived WAL on top of the base copy. You can stop that replay at any moment the archive covers. The files are tied to one major version and one platform.

Most production systems need physical backups for disaster recovery and point in time recovery, plus logical dumps for upgrades, migrations and pulling back a single table someone dropped. Neither kind of backup counts until you have restored it successfully.

Reference: PostgreSQL documentation, chapter 25 "Backup and Restore".

Choosing a design from RPO and RTO

Start with two numbers from the business. RPO (recovery point objective) is how much committed data you can afford to lose. RTO (recovery time objective) is how long the service can stay down while you restore. Ask the business for both, then check each design against them.

Illustrative estimate. These inputs are assumed, not measured: a 500 GB database, a pg_dump that starts at 01:00 every night, and restore throughput of about 100 GB/hour once index builds are counted.

A physical backup restores only to the same PostgreSQL major version, and only as a whole cluster. To get one table back from it, you restore the full cluster somewhere else and then extract the table. A logical dump handles both of those cases well.

A streaming replica is not a backup. A bad DELETE or DROP TABLE reaches the standby within seconds, so the replica keeps the damage and nothing else. Some alternatives that do protect you:

• Delayed standby: set recovery_min_apply_delay so you have a window to stop replay before the mistake is applied.
• PITR: restore from the WAL archive to a point just before the bad statement.
• Both families together: use physical backups plus WAL to meet the RPO, and periodic pg_dump output for upgrades, single-table restores and portability.

Using pg_dump, pg_dumpall and pg_restore

pg_dump writes one database in one of three formats, and the format determines what you can do at restore time:

• Plain SQL (the default) is a script you replay with psql. It is readable and diffable, but restore is all-or-nothing and single-threaded.
• Custom (-Fc) is a single compressed archive read by pg_restore. It supports selective and parallel restore.
• Directory (-Fd) writes one file per table plus a table of contents. It is the only format that supports a parallel dump with -j, which matters when a few large tables dominate the dump time.

Both archive formats support parallel restore with pg_restore -j. Index builds and constraint validation usually take most of the restore time, and those steps are where the extra workers help most. Run pg_restore -l to print an archive's contents. Restore a single table with -t or a single schema with -n. For finer control, edit the listing and pass it back with -L.

pg_dump gets its consistency from MVCC. It runs inside one transaction under a single snapshot and takes only ACCESS SHARE locks, so normal reads and writes continue. Only DDL such as ALTER TABLE or DROP waits. The cost is that the transaction stays open for the whole dump. On a busy primary, vacuum cannot remove rows deleted or updated after the snapshot was taken, so bloat grows. Moving the dump to a standby causes a different problem. The dump's queries conflict with WAL replay and can be cancelled. Turning on hot_standby_feedback prevents the cancellation, but the standby then reports the old snapshot to the primary and holds back vacuum there instead.

pg_dump does not include roles or tablespaces, because they are cluster-wide objects. Capture them with pg_dumpall --globals-only and restore that file first. Otherwise, a restore onto a fresh server fails on every OWNER TO and GRANT that refers to a missing role.

Copy#!/usr/bin/env bash
set -euo pipefail

# Placeholder connection settings; credentials come from ~/.pgpass, not this script.
export PGHOST=db-primary.internal PGUSER=backup_admin
STAMP=$(date +%Y%m%d)

# 1. Cluster-wide objects (roles, tablespaces) are not in pg_dump output.
pg_dumpall --globals-only -f "globals_${STAMP}.sql"

# 2. Directory format is required for a parallel dump (-j).
#    Each worker opens its own connection, all sharing one snapshot.
pg_dump -Fd -j 4 -f "appdb_${STAMP}.dir" appdb

# --- Restore onto a fresh server ---
export PGHOST=db-restore.internal

# 3. Globals first, so ownership and GRANTs in the dump resolve.
psql -X -v ON_ERROR_STOP=1 -d postgres -f "globals_${STAMP}.sql"
createdb -O app_owner appdb

# 4. Parallel restore. Workers mostly help with data loads and index builds.
pg_restore -j 4 -d appdb "appdb_${STAMP}.dir"

# 5. Inspect the archive's table of contents.
pg_restore -l "appdb_${STAMP}.dir" > toc.list

# 6. Selective restore: one schema (-n), or one table inside it (-t).
createdb scratch
pg_restore -d scratch -n billing "appdb_${STAMP}.dir"
pg_restore -d scratch -n sales -t orders "appdb_${STAMP}.dir"

# 7. Finer control: comment out entries in toc.list with ';', then restore that list.
#    Note that restoring into appdb again fails on objects that already exist.
pg_restore -d appdb -L toc.list "appdb_${STAMP}.dir"

This script captures globals with pg_dumpall --globals-only, takes a parallel directory-format dump, restores the globals and then the database in parallel onto a fresh server, and uses the archive listing for selective restores.

How continuous archiving works and how to set it up

A logical dump is a snapshot of one moment. Continuous archiving gives you every moment after a snapshot. PostgreSQL writes each change to the write-ahead log (WAL) in pg_wal before it applies the change to data files. If you copy each completed WAL segment off the host and keep a physical base backup, you can replay changes from that backup up to any later point.

Copy            primary
               │ writes
               ▼
            pg_wal/ ── archive_command / archive_library ──► off-host WAL archive
               │                                                   │
 pg_basebackup ┴──────────────────────► base backup storage        │
                                              │                    │
 restore:     copy base backup ──► new data dir ◄── restore_command┘ (replay WAL)

Three settings in postgresql.conf turn archiving on:

• wal_level = replica. This is the default, and it logs enough for archive recovery.
• archive_mode = on. Changing this setting requires a server restart.
• archive_command. A shell command that receives %p (the segment's path) and %f (its file name). It must exit zero only after the file is safely stored, and it should refuse to overwrite an existing file. Since PostgreSQL 15 you can set archive_library instead, which loads an archive module such as the bundled basic_archive.

Then run pg_basebackup with -X stream. This also captures the WAL generated while the backup runs, so the backup can be restored on its own.

There are two failure modes. The first is a gap in the archive: if one WAL segment is missing, replay stops at that point, and nothing after the gap can be recovered from that base backup. The second is a persistently failing archive command. PostgreSQL keeps every unarchived segment, so pg_wal grows until the disk fills and the primary stops. A broken archive is an availability problem as well as a backup problem. Alert on failed_count and last_failed_wal in pg_stat_archiver, and on free disk space for pg_wal.

Copy#!/usr/bin/env bash
# Enable WAL archiving, take a base backup, then check that archiving keeps working.
set -euo pipefail

ARCHIVE_DIR=/mnt/wal_archive        # placeholder: in production, an off-host mount
BACKUP_DIR=/backups/base/2026-10-19 # placeholder: one directory per base backup

# 1. Configure archiving (ALTER SYSTEM writes to postgresql.auto.conf).
#    'test ! -f' refuses to overwrite a segment that is already archived.
#    Note: plain cp does not fsync. Use a tool that does in production.
psql -U postgres -c "ALTER SYSTEM SET wal_level = 'replica';"
psql -U postgres -c "ALTER SYSTEM SET archive_mode = 'on';"
psql -U postgres -c "ALTER SYSTEM SET archive_command = 'test ! -f ${ARCHIVE_DIR}/%f && cp %p ${ARCHIVE_DIR}/%f';"

# 2. archive_mode (and a wal_level change) only take effect after a restart.
pg_ctl restart -D "$PGDATA"

# 3. Take a base backup. -X stream includes the WAL generated during the backup,
#    so the backup can be restored on its own. -c fast requests an immediate checkpoint.
pg_basebackup -U postgres -D "$BACKUP_DIR" -Ft -z -X stream -c fast -P

# 4. Force a segment switch so you can confirm right away that archiving works.
psql -U postgres -c "SELECT pg_switch_wal();"

# 5. Monitor regularly. A rising failed_count means segments are piling up in pg_wal.
psql -U postgres -x -c "SELECT archived_count, last_archived_wal, last_archived_time,
                               failed_count, last_failed_wal, last_failed_time
                        FROM pg_stat_archiver;"

# If archiving keeps failing, this directory grows until the disk fills.
du -sh "$PGDATA/pg_wal"

Enabling archive_mode with a non-overwriting archive_command, taking a self-contained base backup with pg_basebackup -X stream, and checking pg_stat_archiver and pg_wal size so that archive failures are noticed before the disk fills.

Running a point in time recovery

Before you overwrite anything, copy the current data directory somewhere safe, including pg_wal. Some WAL segments may not have reached the archive yet, and you might need them. Then stop the server and take these steps:

1. Restore the base backup into the empty data directory. Remove any files it brought in under pg_wal, since replay should read WAL from the archive.
2. Set restore_command in postgresql.conf so it fetches segments from the archive.
3. Set exactly one recovery target. recovery_target_time works when you know roughly when the change happened. recovery_target_xid and recovery_target_lsn stop replay at an exact point, which matters when many transactions committed in the same second. recovery_target_inclusive controls whether the target transaction itself is replayed.
4. Choose recovery_target_action:
   • pause is the default and the safest choice.
   • promote opens the server for writes immediately.
   • shutdown stops the server at the target, so you can copy the result elsewhere.
5. Create an empty recovery.signal file in the data directory and start the server.

With hot_standby on, a paused server accepts read-only queries. Use that time to check that the dropped table exists and that the most recent rows are the ones you expect. If you stopped too early, shut down, move the target later and replay again. You cannot move the target earlier without restoring the base backup again. Once the state looks right, call pg_wal_replay_resume() or pg_promote(). The server then starts a new timeline and accepts writes.

To find the target, start with application logs and the server log. Set log_statement = 'ddl' now, so the next DROP or TRUNCATE is logged with a timestamp. For an exact stop point, run pg_waldump over the archived segments from that period. It shows the transaction ID and LSN of the bad commit, and you can use either value as the target.

Copy#!/usr/bin/env bash
# PITR to just before a bad DROP TABLE. Run as the postgres OS user.
set -euo pipefail
PGDATA=/var/lib/postgresql/data
BASE=/backups/base/2026-10-18.tar.gz   # base backup taken before the incident
ARCHIVE=/mnt/wal_archive               # where archive_command copied WAL

# Stop the server, then keep a copy of the old cluster. Its pg_wal may hold
# segments that never reached the archive.
pg_ctl stop -D "$PGDATA" -m fast
mv "$PGDATA" "${PGDATA}.broken"

# Restore the base backup into an empty directory and drop any WAL it
# brought along, so replay reads WAL from the archive.
mkdir -m 700 "$PGDATA"
tar -xzf "$BASE" -C "$PGDATA"
rm -rf "${PGDATA:?}/pg_wal/"*

# Recovery settings: one target, stop just before it, then pause for review.
cat >> "$PGDATA/postgresql.conf" <<'EOF'
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-10-18 14:31:58+00'
recovery_target_inclusive = off
recovery_target_action = 'pause'
hot_standby = on
EOF

# The empty recovery.signal file starts targeted recovery on the next startup.
touch "$PGDATA/recovery.signal"
pg_ctl start -D "$PGDATA" -l /tmp/pitr.log

# Once replay stops at the target, the server is read-only and paused.
# Check that state before you commit to it.
psql -d appdb -c "SELECT pg_is_in_recovery(), pg_is_wal_replay_paused(), pg_last_wal_replay_lsn();"
psql -d appdb -c "SELECT max(created_at) FROM orders;"   # are the newest rows the ones you expect?

# When the data looks right, promote. This starts a new timeline and allows writes.
# psql -d appdb -c "SELECT pg_promote();"

A shell walkthrough of point-in-time recovery: keep a copy of the old cluster, restore the base backup, set restore_command and a time target that stops just before the bad change, create recovery.signal, then check the paused read-only state before promoting.

Backup testing as a scheduled job

A backup that has never been restored is an assumption. Treat restore testing as its own scheduled job, separate from the backup job, with its own success criteria and its own alerts.

What the job does

On a schedule, provision a scratch instance, restore the most recent backup into it, and run checks against the result. For physical backups, replay archived WAL up to a recent point so the test also covers the archive. The example script below follows this pattern. It runs three kinds of sanity queries:

• Row counts on critical tables, compared against a minimum or against counts from the previous run. A table with zero rows usually means an object was left out or a restore step failed silently.
• Recency checks, such as the newest created_at on a busy table. If the latest row is older than your RPO, the backup or the WAL archive is stale.
• Application smoke tests: point a read-only build of the service at the scratch instance and exercise a few real code paths. This catches missing roles, extensions and grants.

Record the wall-clock time from the start of the restore to passing checks, and compare it against your RTO. If restore time grows with data volume, you see it here before an incident does.

Alert when the restore test fails or does not run. An alert on the backup job alone only tells you that files were written.

Verifying physical backups

pg_basebackup writes a backup_manifest that lists every file with a checksum. pg_verifybackup compares the backup against that manifest and checks that the required WAL can be parsed. This is a fast way to catch corruption or missing files, so run it right after each backup. It does not start a server, replay WAL or run queries, so it does not replace an actual restore.

Copy#!/usr/bin/env bash
# Nightly restore test. Runs separately from the backup job; a non-zero exit fires the scheduler's alert.
set -euo pipefail
BACKUP=/backups/base/latest      # plain-format pg_basebackup output
WAL_ARCHIVE=/backups/wal         # uncompressed archived WAL
DATA=/srv/restore-test/data
PORT=55432; DB=appdb
RTO_SECONDS=3600; RPO_SECONDS=900; MIN_ORDERS=1000000
q() { psql -X -p "$PORT" -d "$DB" -Atqc "$1"; }
fail() { echo "RESTORE TEST FAILED: $1" >&2; exit 1; }
start=$(date +%s)

# 1. Integrity check against backup_manifest. Fast, but it does not start a server or run queries.
pg_verifybackup --wal-directory="$WAL_ARCHIVE" "$BACKUP" || fail "pg_verifybackup"

# 2. Restore into a scratch data directory and replay archived WAL.
rm -rf "$DATA" && cp -a "$BACKUP" "$DATA" && chmod 700 "$DATA"
echo "restore_command = 'cp $WAL_ARCHIVE/%f %p'" >> "$DATA/postgresql.auto.conf"
touch "$DATA/recovery.signal"
trap 'pg_ctl -D "$DATA" -m fast stop >/dev/null 2>&1 || true' EXIT
# archive_mode=off so the scratch instance never writes into the production archive.
pg_ctl -D "$DATA" -o "-p $PORT -c archive_mode=off" -w -t "$RTO_SECONDS" -l "$DATA/restore.log" start
# pg_ctl returns once the server accepts connections; wait until WAL replay ends and it promotes.
until [ "$(q 'SELECT pg_is_in_recovery()')" = f ]; do sleep 5; done

# 3. Sanity queries.
orders=$(q "SELECT count(*) FROM orders")
[ "$orders" -ge "$MIN_ORDERS" ] || fail "orders has $orders rows"
lag=$(q "SELECT extract(epoch FROM now() - max(created_at))::int FROM orders")
[ "$lag" -le "$RPO_SECONDS" ] || fail "newest order is ${lag}s old, RPO is ${RPO_SECONDS}s"
[ "$(q "SELECT count(*) FROM pg_extension WHERE extname = 'pgcrypto'")" = 1 ] || fail "pgcrypto missing"

# 4. Application smoke tests against the restored copy (read-only build of the service).
DATABASE_URL="postgresql://app_ro@localhost:$PORT/$DB" ./smoke-tests.sh || fail "smoke tests"

# 5. Compare wall-clock restore time against the RTO.
elapsed=$(( $(date +%s) - start ))
echo "restore test passed in ${elapsed}s (RTO ${RTO_SECONDS}s)"
[ "$elapsed" -le "$RTO_SECONDS" ] || fail "restore took ${elapsed}s, over RTO"

A scheduled restore test that checks a physical backup with pg_verifybackup, restores it and replays archived WAL into a scratch instance, runs row-count, recency, extension and smoke-test checks, and fails if the restore takes longer than the RTO.

Operational hygiene, cost and engineering time

Store copies elsewhere and restrict access

A backup on the database host goes down with the host. Copy every base backup and archived WAL segment to storage in a different location. Use credentials that can write new backups but cannot delete or overwrite existing ones. With that split, a compromised server cannot erase its own recovery path. Encrypt backups at rest and in transit. Limit read access to the few people and jobs that run restores, because a dump contains every row of production data.

Retention and archiver monitoring

Set one retention policy that covers base backups and WAL together. WAL is only useful if it reaches back to the oldest base backup you keep. Delete WAL only after the base backup that needs it has expired.

The archiver can fail without any visible effect, and failed segments then build up in pg_wal until the disk fills. Alert when failed_count rises or last_archived_time stops advancing:

SELECT archived_count, failed_count, last_failed_wal, last_failed_time, last_archived_time FROM pg_stat_archiver;

Cost

Illustrative estimate (assumed numbers, not a benchmark): storage = base size × backups retained + daily WAL × retention days = 200 GB × 4 + 20 GB × 28 = 1,360 GB.

Longer retention increases this total. It also enlarges the amount of sensitive data you have to secure. Restore tests add compute time and some engineering time each time they run.

That cost is worth paying. A tested restore has a known duration and a runbook that works. Teams that skip testing usually learn during an outage that a WAL segment is missing or a dump will not load, when it is too late to fix.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgresql backup restore, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.