PostgreSQL Point-in-Time Recovery Playbook

By Kokil Thapa | Last reviewed: August 2026

Data loss events rarely announce themselves. A rogue UPDATE without a WHERE clause, a failed migration, or application-level corruption can wipe hours of transactional data before anyone notices. This PostgreSQL Point-in-Time Recovery playbook gives you the exact configuration, commands, and verification steps needed to restore a production database to any specific second. Whether you are running legal-tech portals handling sensitive case files or high-volume eCommerce platforms, having a tested PITR strategy is the difference between a minor incident and a catastrophic business failure.

I have implemented this exact architecture for client projects ranging from law firm document management systems to multi-vendor marketplaces. In my experience working on production Laravel applications backed by PostgreSQL, the gap between "theoretically recoverable" and "actually recovered under pressure" is closed only by rigorous testing and precise configuration. For teams evaluating their broader infrastructure resilience, understanding these database fundamentals complements the server security hardening practices that protect against the incidents necessitating recovery in the first place.

How does PostgreSQL Point-in-Time Recovery work?

PostgreSQL Point-in-Time Recovery combines two distinct data streams: a physical base backup and a continuous sequence of Write-Ahead Log (WAL) files. The base backup provides a consistent snapshot of the data directory at a specific moment, while WAL files record every subsequent change to the database. During recovery, PostgreSQL replays WAL records sequentially from the base backup's checkpoint until it reaches your specified target timestamp, then stops.

This mechanism differs fundamentally from logical dumps produced by pg_dump. Logical dumps capture data state at export time but cannot reconstruct intermediate states. PITR preserves the complete transaction timeline, allowing recovery to any point covered by retained WAL segments. For applications where audit trails matter — such as legal case management systems or financial transaction ledgers — this temporal precision is non-negotiable.

Understanding WAL segment lifecycle

Each WAL segment is 16 MB by default and named with a timeline, log, and segment identifier (e.g., 0000000100000000000000A3). PostgreSQL creates new segments as transactions commit and recycles old ones after checkpoints. Archiving intercepts segments before recycling, copying them to durable external storage. The archive must maintain an unbroken chain from your oldest required base backup through the present moment; any gap renders PITR impossible for timestamps within that gap.

How do you configure WAL archiving for PostgreSQL PITR?

WAL archiving configuration lives in postgresql.conf. On Ubuntu 24.04 with PostgreSQL 17, this file typically resides at /etc/postgresql/17/main/postgresql.conf. The following settings establish continuous archiving to a local directory; adapt the path for S3, NFS, or other remote storage.

# postgresql.conf — WAL archiving for PITR
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
max_wal_senders = 10
wal_keep_size = 1GB

Each parameter serves a specific function:

• wal_level = replica: Generates sufficient WAL detail for recovery and replication. The default replica level suffices for PITR; logical adds overhead only needed for logical replication.
• archive_mode = on: Enables the archiving subsystem. Requires server restart to activate.
• archive_command: Shell command executed for each completed WAL segment. The %p placeholder expands to the source path; %f expands to the filename. The test ! -f guard prevents overwriting existing archives, which would corrupt the recovery chain.
• archive_timeout = 300: Forces archiving of incomplete segments after 300 seconds of inactivity. This bounds maximum data loss during low-traffic periods to five minutes.
• wal_keep_size = 1GB: Retains recent WAL segments locally for streaming replicas. Separate from archive retention.

Validating archive integrity

After configuring archiving, verify operation immediately. Force a WAL switch and confirm the segment appears in your archive directory:

-- Force immediate WAL segment switch
SELECT pg_switch_wal();

-- Verify archive status
SELECT * FROM pg_stat_archiver;

The pg_stat_archiver view reports archived_count, failed_count, and last_failed_time. Any non-zero failed_count indicates misconfiguration, permission errors, or storage exhaustion. Monitor this view continuously; silent archive failures are the most common cause of PITR disasters. On production systems I maintain, alerting triggers when failed_count increments or when last_archived_time exceeds archive_timeout by more than 60 seconds.

Remote archive destinations

Local archives protect against application corruption but not disk failure or server loss. Production deployments should archive to geographically separate storage. For S3-compatible targets, replace archive_command with a wrapper script using the AWS CLI or wal-g:

archive_command = '/usr/local/bin/wal-g wal-push %p'

wal-g compresses and encrypts segments before upload, reducing storage costs by 70–80% compared to raw archives. It also handles multipart uploads for large segments and retries transient network failures. For Nepal-based clients with limited international bandwidth, consider regional object storage endpoints to reduce latency and egress costs.

How do you create and manage base backups for PostgreSQL recovery?

Base backups anchor your recovery timeline. Without a valid base backup, WAL archives alone cannot reconstruct a database. Use pg_basebackup for consistent physical backups that respect running transactions.

# Create compressed base backup with manifest
pg_basebackup \
  -D /var/backups/postgresql/base_$(date +%Y%m%d_%H%M%S) \
  -Ft \
  -z \
  -Xs \
  -P \
  --manifest-checksums=SHA256 \
  --checkpoint=fast

Key flags explained:

• -Ft: Tar format output. Produces base.tar.gz and pg_wal.tar.gz, simplifying storage and transfer.
• -z: Gzip compression. Reduces backup size significantly; CPU cost is negligible on modern hardware.
• -Xs: Stream WAL during backup. Ensures all WAL generated during the backup window is captured alongside the base backup, eliminating dependency on archive for the backup period itself.
• --manifest-checksums=SHA256: Generates a backup manifest with per-file checksums. Enables validation before restore attempts.
• --checkpoint=fast: Initiates immediate checkpoint rather than waiting for next scheduled one. Reduces backup start delay at the cost of temporary I/O spike.

Scheduling and retention policies

Automate base backups via cron or systemd timers. A typical production schedule retains daily backups for seven days, weekly backups for four weeks, and monthly backups for twelve months. Align retention with your Recovery Point Objective (RPO) and regulatory requirements. Legal-tech applications often mandate longer retention; eCommerce systems may prioritize frequent backups during peak sales periods.

# /etc/cron.d/postgresql-backup
# Daily base backup at 02:00 NPT
0 2 * * * postgres /usr/local/bin/pg_backup.sh daily >> /var/log/pg_backup.log 2>&1

# Weekly base backup Sunday 03:00 NPT
0 3 * * 0 postgres /usr/local/bin/pg_backup.sh weekly >> /var/log/pg_backup.log 2>&1

Your backup script should handle rotation, checksum verification, and offsite replication. Never trust backups you haven't restored. Schedule quarterly restore drills on isolated hardware to validate both procedure and timing. Teams integrating PostgreSQL with Laravel applications should coordinate backup windows with maintenance schedules documented in their API operational runbooks to avoid conflicting with deployment pipelines.

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

When disaster strikes, execute recovery methodically. Rushing causes compounding errors. This procedure assumes PostgreSQL 17 on Ubuntu 24.04 with archives stored at /var/lib/postgresql/wal_archive.

1. Stop the PostgreSQL service: Prevent further writes to the corrupted cluster.

   sudo systemctl stop postgresql@17-main

2. Preserve the corrupted cluster: Rename, don't delete. Forensic analysis may be needed later.

   sudo mv /var/lib/postgresql/17/main /var/lib/postgresql/17/main_corrupted_$(date +%Y%m%d_%H%M%S)

3. Restore the base backup: Extract the appropriate base backup to the data directory.

   sudo mkdir -p /var/lib/postgresql/17/main
   sudo tar xzf /var/backups/postgresql/base_20260820_020000/base.tar.gz \
     -C /var/lib/postgresql/17/main
   sudo chown -R postgres:postgres /var/lib/postgresql/17/main
   sudo chmod 700 /var/lib/postgresql/17/main

4. Configure recovery parameters: Create recovery.signal and set target in postgresql.conf.

   # Create recovery signal file
   sudo touch /var/lib/postgresql/17/main/recovery.signal
   sudo chown postgres:postgres /var/lib/postgresql/17/main/recovery.signal
   
   # Add to postgresql.conf
   restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
   recovery_target_time = '2026-08-21 14:30:00+05:45'
   recovery_target_action = 'promote'

5. Start PostgreSQL in recovery mode: The server detects recovery.signal and enters recovery automatically.

   sudo systemctl start postgresql@17-main

6. Monitor recovery progress: Watch logs for WAL replay activity and completion signals.

   sudo tail -f /var/log/postgresql/postgresql-17-main.log | grep -E "(recovery|redo|consistent)"

7. Validate recovered state: Connect read-only initially, verify critical tables and row counts match expectations for the target timestamp.
8. Promote to primary: Once validated, promote if recovery_target_action = 'pause' was used instead of promote.

   sudo -u postgres pg_ctl promote -D /var/lib/postgresql/17/main

Timezone considerations for Nepal deployments

Always specify timezone offsets explicitly in recovery_target_time. Nepal Standard Time is UTC+5:45, an unusual offset that many tools mishandle. Using '2026-08-21 14:30:00+05:45' eliminates ambiguity. If your postgresql.conf sets timezone = 'Asia/Kathmandu', bare timestamps interpret correctly, but explicit offsets remain safer across environment variations.

How do you test and validate PostgreSQL PITR procedures?

Untested recovery procedures fail. Quarterly restore drills are mandatory for any system where data loss carries business or legal consequences. Testing reveals configuration drift, permission issues, and timing problems that only surface during actual recovery.

Document every drill: date, personnel, target timestamp, actual recovery duration, issues encountered, and remediation actions. This documentation satisfies compliance audits and accelerates future recoveries. For teams managing multiple client databases, consider tooling like wal-g's built-in fetch-and-restore commands or commercial solutions that automate drill execution. Developers building custom Laravel administration panels can integrate backup status dashboards using patterns from the Filament admin panel tutorial to surface backup health directly in operational interfaces.

Common PITR pitfalls and prevention

Certain failure modes recur across deployments. Archive command permissions rank highest; the postgres user must write to the archive directory, and SELinux/AppArmor policies often block access silently. Test archive writes immediately after configuration changes. Second, timezone confusion causes recovery to wrong timestamps; always use explicit offsets. Third, insufficient archive retention deletes WAL segments needed for older base backups; tie archive cleanup to base backup rotation, never to calendar schedules. Fourth, recovery to promoted standby creates timeline divergences; track timeline IDs when chaining recoveries.

Implementing Reliable PostgreSQL Point-in-Time Recovery

PostgreSQL Point-in-Time Recovery transforms catastrophic data loss into manageable operational incidents, but only when configured correctly and tested regularly. The combination of continuous WAL archiving, disciplined base backup scheduling, and validated restore procedures forms a safety net that protects real businesses and real users. Start with the configuration templates above, adapt retention policies to your specific RPO requirements, and schedule your first restore drill within 30 days. If your team needs hands-on implementation support or infrastructure review for production PostgreSQL deployments, reach out to discuss your recovery architecture.