Contents

Recovery Target LsnEdit

Recovery Target LSN is a PostgreSQL recovery configuration parameter used to designate the exact WAL (Write-Ahead Log) position at which replay should stop during a restore or standby operation. It is a fundamental tool for precision recovery, enabling administrators to bring a database to a known, consistent state at a specific point in time or sequence.

Note: This article describes a technical concept and is intended to be neutral and informative about database engineering practices. It does not advocate any political viewpoint.

Overview

  • Definition and purpose: The recovery target is expressed as a log sequence number (LSN), a marker inside the WAL stream that records every modification to the database. By setting recovery_target_lsn, a DBA can instruct the server to halt WAL replay once the specified LSN is reached, supporting precise recovery scenarios such as testing, forensics, or restoring to a known-good point after an incident. See Recovery Target LSN for the canonical name of the feature.
  • Relationship to other recovery targets: PostgreSQL also supports alternative recovery targets, such as a specific Point-in-Time Recovery or a particular recovery_target_xid. The LSN target is used when a precise WAL location is known or when the archived WAL stream must be consumed to reach a defined state.
  • How LSN relates to durability: The LSN identifies a position in the Write-Ahead Logging stream, which is the bedrock of PostgreSQL’s durability and crash-safety guarantees. Recovery relies on replaying WAL from a base backup or from a streaming replica until the target is met or until the end of available WAL is reached.

Usage and configuration

  • Basic concept: During recovery, PostgreSQL replays WAL records from a base backup. The recovery_target_lsn parameter tells the system to stop replay at a specific LSN. If recovery_target_lsn is set and the WAL stream contains that LSN, the server will pause recovery at that point (depending on recovery_target_inclusive). If the LSN is reached and the system proceeds to normal operation, you’ll have a database state that reflects all committed changes up to that WAL position.
  • Where to configure: In traditional setups, recovery_target_lsn is specified in the recovery configuration, typically in a file such as recovery.conf (older setups) or via equivalent settings in postgresql.conf in modern versions. The exact syntax commonly resembles:
    • recovery_target_lsn = '0/3000000'
    • recovery_target_inclusive = true
  • How it interacts with WAL archiving and streaming: For a successful recovery to a target LSN, you must have access to all WAL segments up to that LSN. This usually means you have:
    • Active WAL archiving enabled, storing segments in a known archive, or
    • A streaming replica that has replayed WAL up to the target. If the necessary WAL segments are missing, recovery cannot proceed to the target and will fail or fall back to an earlier, available point.
  • Example workflow:
    • Take a base backup from a running cluster.
    • Configure restore_command to fetch WAL segments from the archive as needed.
    • Set recovery_target_lsn to the desired LSN and, if applicable, recovery_target_inclusive.
    • Start the server to begin recovery; once the target is reached, PostgreSQL can complete the recovery and begin normal operation, or you can promote a standby if running in a replica role.
  • Interaction with other recovery targets: If you specify a time-based target (recovery_target_time) or a transaction ID target (recovery_target_xid), PostgreSQL may choose a different path through the WAL stream. The LSN target provides exact control over the final point of replay, which can be more precise than time-based targets in environments with irregular workload or WAL lag.

Practical considerations and pitfalls

  • Availability of WAL: The entire recovery hinges on having WAL segments up to the target LSN available. If archiving is misconfigured or WAL is purged before the target can be reached, recovery will fail to reach the specified LSN.
  • Inclusive vs. exclusive targets: The recovery_target_inclusive setting determines whether the LSN itself is included in the recovered state. This can affect whether the recovered database includes the effects of the transaction at that LSN.
  • Consistency concerns: When recovering to an LSN, ensure that the resulting state will be usable for the intended purpose. For example, recovering to a mid-transaction point may yield an inconsistent view if a long-running transaction spans the LSN boundary.
  • Promotions and standby behavior: In a replication setup, recovering to a specific LSN on a standby and then promoting it to a primary can be a deliberate strategy for controlled failover or testing. It is important to understand how the chosen target affects replication slots, archived WAL, and downstream subscribers.
  • Version differences: The exact syntax and recommended practices for configuring recovery_target_lsn can vary between PostgreSQL releases. Always consult the documentation matching your version to verify supported options and defaults.

Alternatives and related techniques

  • Time-based PITR: Instead of targeting a specific LSN, you can recover to a particular timestamp using recovery_target_time. This approach is useful when you know the event time you want to restore to, but it may require more WAL history and can be less precise if clocks or timing are uncertain.
  • Transaction-based recovery: recovery_target_xid allows you to restore to a particular transaction ID, which can be useful when you need to revert to the point just after a known commit.
  • Base backup strategies: The effectiveness of recovery_target_lsn is closely tied to base backup frequency and the quality of WAL archives. Regular, verified base backups combined with robust WAL archiving are essential for reliable recovery.
  • Replication options: For ongoing availability, many deployments rely on streaming replication with hot standby nodes rather than relying solely on PITR techniques. This approach reduces the need to restore from backups and allows near-zero downtime in failover scenarios.

See also