PostgreSQL Point-in-Time Recovery: A Production Architecture Guide

A nightly pg_dump can only take you back to last night. If someone runs a bad migration at 14:31, you want the database as it was at 14:30. That is what PostgreSQL point-in-time recovery (PITR) does. This guide covers how to build it as a system: the architecture, archiving that reports failures honestly, a backup repository that is hard to destroy, a restore path you have practiced, and monitoring that warns you before you need a restore. For the business side (the 3-2-1 rule, RPO, RTO and who owns the plan), read our small business data backup plan guide. This article covers the engineering.

How PostgreSQL point-in-time recovery works

PITR combines two things: a physical base backup of the data directory, and a continuous archive of the write-ahead log (WAL) segments created after it. To recover, you restore the base backup and replay archived WAL until you reach a chosen target: a timestamp, a transaction ID, an LSN or a named restore point. The official documentation, Continuous Archiving and Point-in-Time Recovery, explains how this works. Read it once from start to finish before you choose a tool.

Three facts from that documentation shape the whole design:

• To enable archiving, you set wal_level to replica or higher, set archive_mode to on, and configure an archive command.
• The archive command only runs on completed WAL segments. A transaction committed a minute ago may still sit in a segment that has not been archived yet.
• In rare cases, PostgreSQL may try to archive a WAL file it has already archived, so your archiver must handle duplicates safely.

Choosing an approach: the trade-offs

• Logical dumps (pg_dump): portable across major versions and easy to restore one table at a time. They are only snapshots, though: your recovery point is the last dump, and on large databases both dumping and restoring are slow. Keep them in addition to PITR, not instead of it.
• Streaming replicas: they give you fast failover, but a replica is not a backup. A DROP TABLE reaches the replica within moments. A delayed replica (recovery_min_apply_delay) helps, but it does not replace an off-host archive.
• Managed database PITR (RDS, Cloud SQL, Azure, and others): the least work to run. Still check how long the provider keeps backups, whether backups survive if the instance or account is deleted, and whether you can export them somewhere else.
• Self-managed PITR with a dedicated tool: pgBackRest, WAL-G and Barman handle parallel transfer, compression, encryption, retention and verification. Unless you have an unusual constraint, pick one of these instead of writing your own archive_command script.

The rest of this guide shows the plain mechanics, so you understand what those tools do and can judge them.

Reference architecture

1. Primary database with WAL archiving enabled.
2. Archiver: the archive command or archive library, running on the database host.
3. Repository: object storage in a separate account or project. The database host's credentials can write to it but cannot delete from it.
4. Base backup job: a scheduled job, usually running from a backup host instead of the primary.
5. Restore worker: a disposable machine or container that rebuilds the database from the repository on a schedule.
6. Monitor: checks archive lag, archive failures, base backup age and the result of the last restore test.

Step 1: WAL archiving that reports failures honestly

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = '/usr/local/bin/archive_wal.sh %p %f'
archive_timeout = 60   # force a segment switch on quiet databases

PostgreSQL treats a zero exit code as "archived". If the script returns 0 before the data is durable in the repository, you have an unrecoverable gap and nothing tells you. The core logic, in pseudo-bash:

#!/usr/bin/env bash
# archive_wal.sh %p %f  (pseudo-code: put_object/head_sha256 wrap your storage CLI)
set -euo pipefail
src="$1"; name="$2"
key="pg-main/wal/$name"
local_sum=$(sha256sum "$src" | cut -d' ' -f1)

if remote_sum=$(head_sha256 "$key" 2>/dev/null); then
  # Already archived: success only if the content is identical
  [ "$remote_sum" = "$local_sum" ] && exit 0
  echo "refusing to overwrite $key with different content" >&2
  exit 1
fi

encrypt "$src" | put_object "$key" --checksum-sha256 "$local_sum"
[ "$(head_sha256 "$key")" = "$local_sum" ] || exit 1

Design notes:

• Duplicates are safe, overwrites are not. An identical file already in the archive counts as success. A different file under the same name is an incident, not something to overwrite silently.
• RPO is set by segment switching. Because only completed segments are archived, a quiet database can keep a partial segment open for a long time. archive_timeout limits that window, and you can force a switch with pg_switch_wal(). If you need an RPO close to zero, stream WAL continuously (pg_receivewal or your tool's async/streaming mode) instead of shortening the timeout.
• A failing archiver fills the disk. WAL segments that have not been archived stay in pg_wal. A broken credential can therefore turn into a full disk on the primary. Alert on this long before it happens.

Step 2: Base backups and retention

dir=/backups/base/$(date -u +%Y%m%dT%H%M%SZ)
pg_basebackup -h primary -U backup -D "$dir" -Fp -X stream -c fast
pg_verifybackup "$dir"          # checks files against the backup manifest
encrypt_and_upload "$dir"        # then remove the local copy

The documentation says to take file-system-level base backups for PITR, not pg_dump, and you do not need to stop the database to take them. The main trade-off is how often you take them versus how long a restore takes. Recovery replays every WAL segment between the base backup and the target. On a database with heavy writes, a week-old base backup can mean hours of replay. Take full backups more often, or use incremental backups (pgBackRest and WAL-G support them, and recent PostgreSQL releases include native incremental backup), until your measured restore time fits your RTO.

Retention rule: never delete WAL that is newer than your oldest kept base backup. If you keep base backups for 14 days, you must keep all WAL from the oldest one onward, or that backup cannot be recovered past its end point. Dedicated tools enforce this for you. Hand-written lifecycle rules usually don't.

Step 3: Make the repository hard to destroy

• Separate the blast radius. Keep the repository in a different cloud account or project. If the database host is compromised, the attacker should get credentials that can only upload, not delete or change lifecycle rules.
• Immutability. Object-level write-once retention, such as Amazon S3 Object Lock or the equivalents from other providers, stops deletion until the retention period ends. Set the retention period to at least your backup retention window.
• Client-side encryption. Encrypt before upload and store the key outside the repository account. Test that the restore worker can get the key without access to production.
• A second copy. Replicate the repository to another region or provider. This is the "1 off-site" part of 3-2-1, applied to WAL.

Step 4: The restore path

Since PostgreSQL 12, recovery settings go in postgresql.conf (or postgresql.auto.conf), and you trigger recovery by creating a recovery.signal file in the data directory. See the WAL configuration reference.

# after restoring the base backup into $PGDATA (owned by postgres, not root)
restore_command = '/usr/local/bin/fetch_wal.sh %f %p'
recovery_target_time = '2025-03-14 14:30:00+00'   # example target
recovery_target_action = 'pause'

• Set only one recovery target. Recent versions raise an error if you set more than one.
• Use pause for incident restores. Run read-only queries to confirm the bad change is not there. If you need to go further, set a later target and restart; when the state is right, promote with pg_promote().
• Always write timestamps with an explicit time zone. Converting "14:30 local" in a hurry during an incident is a common source of mistakes.
• Restore to a new instance. Don't overwrite the damaged primary, which you may still need for forensics or to copy rows the restore does not include.
• Fix ownership and permissions after copying files. The docs point out that files restored as root are a common mistake.

If you know a risky operation is coming, such as a big migration or a bulk import, create a named point first with SELECT pg_create_restore_point('before_migration_42'); and target it with recovery_target_name.

Step 5: Automate restore tests

Until you have restored a backup, you don't know it works. Run this on a schedule on the restore worker:

start=$(date +%s)
base=$(latest_base_backup)
fetch_and_decrypt "$base" /restore/data
chown -R postgres:postgres /restore/data && chmod 700 /restore/data
touch /restore/data/recovery.signal
cat >> /restore/data/postgresql.auto.conf <<'EOF'
restore_command = '/usr/local/bin/fetch_wal.sh %f %p'
recovery_target_action = 'promote'
EOF
sudo -u postgres pg_ctl -D /restore/data -w -t 7200 start

# Business-level checks, not just "it started"
psql -U postgres -d app -Atc "SELECT max(created_at) FROM orders"   # freshness
psql -U postgres -d app -Atc "SELECT count(*) FROM customers"        # sanity
report_metric restore_seconds $(( $(date +%s) - start ))
report_metric restore_ok 1

Without a target, recovery replays to the end of the archive. The gap between max(created_at) and the start of the test is your RPO as it actually happened. The elapsed time is your actual RTO. Store both values over time. A test that has not run is a failure, so alert when the last successful run is too old, not only when a run fails.

Monitoring that warns you before you need a restore

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_failed_wal,
       last_failed_time,
       now() - last_archived_time AS since_last_archive
FROM pg_stat_archiver;

Alert on these:

• since_last_archive above a few multiples of archive_timeout.
• last_failed_time newer than last_archived_time, meaning the archiver is failing right now.
• Disk usage of pg_wal growing steadily.
• Age of the newest verified base backup.
• Age and result of the last restore test.

Common mistakes

• Treating a replica or a disk snapshot as the backup.
• An archive command that returns 0 when the upload fails, for example a pipeline without pipefail.
• Lifecycle rules that delete WAL still needed by the oldest base backup.
• Database host credentials that can delete the repository.
• Encryption keys stored only on the server you are trying to recover.
• Never measuring replay time, so the RTO is a guess.
• Restore runbooks written once and never run.

Checklist

• Archiving on; archive command handles duplicates safely and checks uploads.
• archive_timeout or WAL streaming set to match your RPO.
• Base backups verified with pg_verifybackup or your tool's verify command.
• WAL retention covers the oldest base backup.
• Repository in a separate account, with write-only credentials, object lock and a second region.
• Client-side encryption; key stored and tested outside production.
• Scheduled automated restore with business-level checks and recorded RTO/RPO.
• Alerts on archive lag, archive failures, pg_wal growth, backup age and restore test age.

If your product runs on PostgreSQL and nobody owns this pipeline, that is a gap worth closing. It is the kind of infrastructure we design and build as part of custom software development.

Frequently asked questions

Is PITR possible on a managed database?

Usually yes, and it is usually turned on with a retention setting. Check how far back you can restore, whether backups survive if the instance is deleted, and whether you can export to storage you control.

Should I still keep pg_dump backups?

Yes, as an extra layer. Logical dumps work across major versions and make it easy to restore a single table, which physical PITR cannot do directly.

How often should I test restores?

Often enough that a broken backup cannot go unnoticed longer than you could tolerate. Automate the test, and alert when it fails or has not run.