PITR Best Practices

Align WAL retention with legal/compliance windows. These rules keep point-in-time recovery dependable when someone drops the wrong table.

How to Use This List

• Apply when enabling archive_mode or choosing cloud backup retention.
• Review before major migrations (create restore point checklist).
• Pair with quarterly PITR Drill Runbook.
• Share retention section with legal/compliance stakeholders.

A - Archiving

• Enable archive_mode on primary from day one. Not after first incident.
• Monitor pg_stat_archiver.failed_count with alert threshold 1. Any failure widens RPO.
• Idempotent archive_command (test ! -f or pgBackRest). Retries must succeed.
• Replicate WAL archive to second region/account. Region loss should not break chain.
• Set archive_timeout appropriate for low-traffic DBs. Avoid stale RPO on idle systems.

B - Retention and Compliance

• WAL retention >= legal hold minimum + ops buffer. Legal 90d means archive at least 90d plus margin.
• Document RPO as last successful archive, not backup window label. Honest stakeholder comms.
• Extend retention for litigation hold without deleting keys. Immutable storage or object lock.
• Cross-check cloud console retention vs compliance spreadsheet. RDS 7-day default is not enough for many regimes.
• Tier retention by database classification. PII cluster longer than internal analytics.

C - Restore Points and Targets

• pg_create_restore_point before high-risk DDL. Mandatory in change tickets.
• Use explicit timezone in recovery_target_time. UTC with offset in runbooks.
• Prefer recovery_target_action = pause for first attempt. Validate before promote.
• Never PITR in-place over production data_dir. Restore to scratch; swap after proof.
• Record which target type was used in incident ticket. Aids postmortem and audit.

D - Drills and Verification

• Quarterly PITR drill to scratch PostgreSQL 18.4 host. Log PITR_RTO_SECONDS.
• Validate row counts and business checksums after drill. Not only pg_is_in_recovery() = false.
• Failed drill = treat backups as untrusted until fixed. Sev-2 data risk.
• Include pgBackRest or cloud restore path in rotation. Do not only test shell cp archives.
• App smoke test on restored instance before declaring pass. DB up != app whole.

E - Tooling Integration

• Patroni: document bootstrap from backup for new cluster PITR. Not failover in place.
• pgBackRest: verify stanza and archive-push in same job monitoring. Unified chain.
• Encrypt archives at rest (S3 SSE-KMS, gpg). WAL contains all data.
• Restrict restore IAM to break-glass roles. Prevent accidental prod overwrite.
• Map cloud PITR UI steps to DIY runbook equivalents. Hybrid teams stay aligned.

FAQs

Related

• PITR Basics - architecture
• archive_mode & archive_command - shipping
• Recovery Target Options - stop conditions
• Backup Best Practices - complementary backups

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.