PostgreSQL Point-in-Time Recovery Playbook

By Khimananda Oli | Last reviewed: August 2026

Data loss incidents rarely happen at convenient times, and standard daily dumps cannot recover transactions committed minutes before a failure. The PostgreSQL Point-in-Time Recovery Playbook solves this by combining continuous Write-Ahead Log (WAL) archiving with periodic base backups, allowing you to restore a database to any exact second. Whether you are recovering from accidental deletions, corrupted migrations, or ransomware, mastering this workflow is non-negotiable for production environments. Before attempting a live recovery, ensure your foundational backup strategy is sound by reviewing PostgreSQL backup and restore with pg_dump to understand the differences between logical and physical backups.

How do you configure WAL archiving for PostgreSQL Point-in-Time Recovery?

WAL archiving is the backbone of the PostgreSQL Point-in-Time Recovery Playbook. Without continuous log shipping, you can only restore to the last base backup, not to an arbitrary moment. Configuration must be done carefully because errors here silently break your recovery capability.

Enable archive mode and set the archive command

Edit your postgresql.conf file to enable archiving. In PostgreSQL 15 and later, some parameters have moved to postgresql.auto.conf when using ALTER SYSTEM, but direct editing remains valid for infrastructure-as-code workflows.

# postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /mnt/wal_archive/%f && cp %p /mnt/wal_archive/%f'
archive_timeout = 300

• wal_level = replica: Required for archiving and replication. Setting this to minimal disables PITR entirely.
• archive_command: This shell command runs every time a WAL segment completes (typically 16 MB). The %p expands to the full path of the WAL file, and %f expands to just the filename. The test ! -f guard prevents overwriting existing archives, which would corrupt your timeline.
• archive_timeout = 300: Forces a WAL segment switch every 5 minutes even if the segment isn't full. This caps your maximum data loss window during quiet periods. For high-value financial systems in Nepal or globally, reduce this to 60 seconds.

Validate archiving is working

Never assume archiving works because you configured it. Run this query to confirm:

SELECT * FROM pg_stat_archiver;

Check that archived_count is incrementing and failed_count remains zero. If failures appear, check permissions on the archive directory and verify the command syntax. A common mistake in my experience is forgetting that the postgres OS user needs write access to the archive path, especially when mounting NFS or S3-backed filesystems.

Archive to remote storage safely

Local archives defeat the purpose of disaster recovery. Ship WAL files to object storage or a separate server. For S3-compatible targets, use wal-g or pgbackrest instead of raw cp commands. These tools handle compression, encryption, and multipart uploads reliably. If you're managing replicas alongside PITR, review PostgreSQL replication and high availability to understand how streaming replication interacts with archive streams.

How do you take a proper base backup for PITR?

A base backup provides the starting point for recovery. Without it, WAL files alone are useless because they contain only changes, not the initial data state. The PostgreSQL Point-in-Time Recovery Playbook requires consistent, verified base backups taken without stopping the database.

Use pg_basebackup correctly

The built-in pg_basebackup utility creates a consistent snapshot while the server continues serving traffic:

pg_basebackup -D /mnt/backups/base_$(date +%Y%m%d_%H%M%S) \
  -Ft -z -Xs -P -R \
  -U replication_user \
  -h localhost

• -Ft: Tar format, easier to store and transfer than plain directory format.
• -z: Gzip compression, typically reducing backup size by 60–80%.
• -Xs: Stream WAL files generated during the backup into the same tarball. This guarantees you have all WAL needed to make the backup consistent, even if archive_command lags.
• -R: Creates standby.signal and appends connection info to postgresql.auto.conf. Essential if this backup might become a standby, harmless for pure PITR.
• -P: Shows progress, critical for multi-terabyte databases where backups run for hours.

Schedule and retain backups strategically

Daily base backups are standard, but retention depends on your WAL archive retention. If you keep 30 days of WAL but only 7 days of base backups, you cannot recover to day 15. Align both retention periods. For compliance-heavy environments following ISO 27001 or SOC 2, document your retention policy formally and test restoration quarterly. Automated evidence collection for audits should include backup verification logs.

What are the exact steps to perform PostgreSQL Point-in-Time Recovery?

When disaster strikes, precision matters more than speed. Follow this PostgreSQL Point-in-Time Recovery Playbook sequence exactly. Deviating risks restoring to the wrong moment or corrupting the cluster.

Step 1: Determine the exact recovery target

Identify the precise timestamp before the damaging event. Query application logs, audit trails, or transaction timestamps. If unsure, err on the side of earlier rather than later—you can always recover again to a later point, but you cannot undo a recovery that went too far.

Step 2: Stop PostgreSQL and preserve current state

sudo systemctl stop postgresql
# Optionally rename the corrupted data directory as forensic evidence
mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main_corrupted_$(date +%s)

Never overwrite the corrupted data directory until you've confirmed successful recovery. Storage is cheap; lost evidence is permanent.

Step 3: Restore the base backup

mkdir -p /var/lib/postgresql/16/main
tar xzf /mnt/backups/base_20260814_020000/base.tar.gz -C /var/lib/postgresql/16/main
chown -R postgres:postgres /var/lib/postgresql/16/main
chmod 700 /var/lib/postgresql/16/main

If you used plain format instead of tar, copy the directory directly. Ensure ownership and permissions match exactly—PostgreSQL refuses to start with incorrect permissions on the data directory.

Step 4: Configure recovery parameters

In PostgreSQL 12+, recovery configuration lives in postgresql.conf rather than a separate recovery.conf:

# postgresql.conf
restore_command = 'cp /mnt/wal_archive/%f %p'
recovery_target_time = '2026-08-14 14:23:00+05:45'
recovery_target_action = 'pause'

• restore_command: The inverse of archive_command. Fetches WAL segments from archive during replay.
• recovery_target_time: The exact moment to stop replay. Use ISO 8601 format with timezone offset. Nepal uses UTC+05:45—getting this wrong shifts your recovery by hours.
• recovery_target_action = pause: Stops at the target without promoting. Lets you verify data before making the instance writable. Change to promote only after validation.

Step 5: Create the recovery signal file

touch /var/lib/postgresql/16/main/recovery.signal
chown postgres:postgres /var/lib/postgresql/16/main/recovery.signal

This empty file tells PostgreSQL to enter recovery mode on startup. Without it, the server starts normally and ignores your recovery parameters.

Step 6: Start PostgreSQL and monitor replay

sudo systemctl start postgresql
tail -f /var/log/postgresql/postgresql-16-main.log

Watch for messages indicating WAL replay progress. Replay speed depends on archive retrieval latency and transaction volume. For large databases, this can take hours. Do not interrupt the process.

Step 7: Validate and promote

Once replay pauses at your target time, connect read-only and verify critical tables, row counts, and application state. If satisfied:

SELECT pg_wal_replay_resume();
-- Or promote directly:
SELECT pg_promote();

After promotion, remove recovery parameters from postgresql.conf to prevent accidental re-entry into recovery mode on next restart.

How does PostgreSQL PITR compare to other recovery methods?

Understanding trade-offs prevents choosing the wrong tool during an incident. The PostgreSQL Point-in-Time Recovery Playbook excels for granular recovery but carries operational complexity that simpler methods avoid.

A delayed replica deserves special attention. By configuring recovery_min_apply_delay on a standby, you maintain a copy that lags behind primary by a fixed interval (e.g., 1 hour). If someone runs DROP TABLE accidentally, the delayed replica hasn't executed it yet. This complements PITR rather than replacing it. For comprehensive administration patterns including delayed replicas, see PostgreSQL administration essentials.

What common mistakes break PostgreSQL Point-in-Time Recovery?

In 15 years of managing production databases, I've seen the same PITR failures repeatedly. Avoid these pitfalls to ensure your PostgreSQL Point-in-Time Recovery Playbook actually works when needed.

Untested archives are worthless

Archive commands fail silently when disk fills, permissions change, or network mounts disconnect. Implement automated verification: periodically restore a test instance from recent backups and replay WAL to a random timestamp. Log success metrics to your monitoring stack. If you're building observability around this, the four golden signals of monitoring apply directly to backup health.

Timezone confusion destroys precision

PostgreSQL stores timestamps in UTC internally but displays them according to session timezone. When specifying recovery_target_time, always include the explicit offset. Writing '2026-08-14 14:23:00' without offset uses the server's timezone setting, which may differ from your expectation. In Nepal, where systems might be configured to UTC, Asia/Kathmandu, or local offsets inconsistently, this causes recovery to the wrong hour.

Ignoring WAL retention alignment

If your base backup retention exceeds WAL archive retention, gaps emerge. You might have a base backup from 30 days ago but only 14 days of WAL, making recovery to day 20 impossible. Document and enforce matching retention policies. Automate cleanup scripts to delete base backups only after confirming corresponding WAL segments exist for the entire interval.

Recovering directly onto production

Always recover to a separate instance first. Validate data integrity, run application smoke tests, and confirm the correct timestamp before promoting. Only then export the needed data back to production via pg_dump/pg_restore or logical replication. Direct production recovery doubles your outage duration if anything goes wrong.

Implementing Your PostgreSQL Point-in-Time Recovery Playbook

The PostgreSQL Point-in-Time Recovery Playbook transforms catastrophic data loss into a manageable operational procedure. Success requires three elements working together: reliable WAL archiving configured and verified today, disciplined base backup scheduling aligned with archive retention, and rehearsed recovery procedures tested quarterly under realistic conditions. Document every step, automate verification, and treat untested backups as nonexistent. If your team needs hands-on guidance implementing PITR for compliance-sensitive environments or wants to audit existing configurations, reach out through my contact page to discuss your specific infrastructure requirements.