Back to Glossary

PostgreSQL Recovery Controls

Recovery Target

Learn how PostgreSQL recovery targets work and how to choose between time-based and LSN-based replay stopping points.

Definition

A recovery stop point defined by time, XID, name, or LSN to which PostgreSQL will recover during PITR.

What a Recovery Target Controls

A recovery target determines exactly where WAL replay stops during PITR, allowing recovery to a known-good state.

This is essential when the latest timeline already includes bad writes, accidental deletes, or damaging schema operations.

Target Types and When to Use Them

PostgreSQL supports recovery targets by time, LSN, transaction ID, and named restore points. The right choice depends on incident precision requirements.

  • Use recovery_target_time for most operator-error incidents
  • Use recovery_target_lsn for exact ordering boundaries
  • Use restore points for planned cutovers and releases

Execution Guidance

Always rehearse target selection in non-production environments. Incorrect settings can replay beyond the safe boundary or stop too early.

Incident runbooks should include target selection logic, validation queries, and application-level correctness checks.

  • Capture precise event timeline during incident response
  • Choose target type based on precision needed
  • Validate restored state before promotion

Common Recovery Target Mistakes

  • Relying on approximate timestamps without timezone clarity
  • Skipping data validation after replay
  • No predefined restore points for risky migrations
  • No rehearsal for cross-team incident handoff

Related Glossary Topics

Replication Restore restore_command Replication Slot Real-time Analytics ACID Atomicity Archiving (WAL Archiving)

Frequently Asked Questions

What is a recovery target in PostgreSQL?
It is the configured stop point for WAL replay during recovery, defined by time, LSN, transaction, or named restore point.
Should I use time or LSN recovery targets?
Use time for routine incidents and LSN when precision is critical or event order must be exact.
Can wrong recovery targets cause data issues?
Yes. If set incorrectly, recovery can replay too far or stop too early. Rehearsal testing is essential.
Is recovery_target_time enough for all incidents?
Not always. For high-precision events, LSN-based recovery is often safer.
How do teams validate recovery targets?
Practice drills with known timestamps/LSNs, then confirm restored data and application behavior before production promotion.