Automating PITR Restore-Target Selection After a Failed Upgrade

How to choose the exact recovery target — a named restore point, a timestamp, or an LSN — automatically after a failed extension upgrade, so a rewind lands one moment before the damage and not a minute of good data too early or too late.

Up: Snapshot & Point-in-Time Recovery — this page is the target-selection logic behind an automated PITR, under the Automated Execution & Rollback Workflows area of the site.

Context & When This Applies

Once WAL archiving makes recovery possible, the hard operational question is where to stop replay. Stop too early and you discard good transactions committed before the upgrade; stop too late and you replay the very damage you are trying to undo. This page applies to PostgreSQL 12–17 wherever automation, not a human, must pick the recovery target after a failed extension upgrade — which is exactly when a full rewind is the chosen route per PITR restore vs schema-level rollback.

The reliable answer is to not guess a timestamp at all. If the automation created a named restore point immediately before the upgrade, that name is an exact, unambiguous target — far safer than a clock time that can be off by clock skew or a fraction of a second. Automating target selection is therefore mostly about preferring the strongest available marker and falling back gracefully when it is absent.

Preference order for choosing a PITR recovery target Automated target selection follows a preference order. First choice: the named restore point created immediately before the upgrade, an exact and unambiguous target. If no named point exists, second choice: the LSN captured just before the upgrade began. If neither is available, last resort: a timestamp set to just before the upgrade start, which is vulnerable to clock skew. Whichever target is chosen, recovery pauses at the target rather than promoting immediately, so the state can be verified before the server is opened for writes. 1 · named restore point exact, skew-proof — prefer this 2 · LSN before upgrade exact position, if captured 3 · timestamp (fallback) clock-skew risk — last resort recovery_target action = pause Verify, then promote

Concept: Prefer the Strongest Marker, Pause Before Promoting

Three target types exist, and they are not equally trustworthy. A named restore point (recovery_target_name) points at an exact WAL position the automation deliberately marked before the upgrade — it cannot drift, and it names intent, so it is always the first choice. An LSN (recovery_target_lsn) is equally exact if the automation captured the pre-upgrade LSN, and is the natural second choice. A timestamp (recovery_target_time) is the fallback: it is human-readable but vulnerable to clock skew and sub-second ambiguity, so it stops “about” where you meant, which for an upgrade rewind is a real risk. Automating selection means walking this preference order and using the strongest marker that exists.

The second half is pause, do not auto-promote. Setting recovery_target_action = 'pause' makes recovery replay to the target and then wait, opening the database read-only so the automation (or an operator) can confirm the extension state is the pre-upgrade one before promoting to read-write. If verification fails — the target was wrong — you can adjust and replay again without having committed to a bad rewind. This mirrors the health-gate discipline of the execution envelope: never make an irreversible move until a check passes.

Runnable Implementation

The selector below inspects what markers are available and emits the correct recovery settings, always preferring the named restore point and always pausing at the target.

#!/usr/bin/env python3
"""Choose the strongest available PITR recovery target for a failed upgrade."""
from dataclasses import dataclass


@dataclass
class UpgradeMarkers:
    restore_point_name: str | None   # e.g. "before_postgis_3.4.2_upgrade"
    pre_upgrade_lsn: str | None      # e.g. "0/9A3F1B8"
    upgrade_start_time: str | None   # ISO8601, fallback only


def select_recovery_target(m: UpgradeMarkers) -> dict:
    if m.restore_point_name:
        settings = {"recovery_target_name": m.restore_point_name}
        chosen = "named_restore_point"
    elif m.pre_upgrade_lsn:
        settings = {"recovery_target_lsn": m.pre_upgrade_lsn}
        chosen = "lsn"
    elif m.upgrade_start_time:
        # Fallback: stop just BEFORE the upgrade started. recovery_target_inclusive
        # = false so the target transaction itself is NOT replayed.
        settings = {"recovery_target_time": m.upgrade_start_time,
                    "recovery_target_inclusive": "false"}
        chosen = "timestamp_fallback"
    else:
        raise ValueError("no recovery marker available; cannot target a rewind")

    # Always pause at the target so state is verified before promotion.
    settings["recovery_target_action"] = "pause"
    return {"target_type": chosen, "settings": settings}


if __name__ == "__main__":
    import json
    m = UpgradeMarkers(restore_point_name="before_postgis_3.4.2_upgrade",
                       pre_upgrade_lsn="0/9A3F1B8",
                       upgrade_start_time="2026-07-18T02:00:00Z")
    print(json.dumps(select_recovery_target(m), indent=2))

Write the emitted settings into the recovery configuration, restore the base backup, and let replay reach the paused target; then verify before running pg_wal_replay_resume() / promotion, driving the whole flow from the same automation that owns ALTER EXTENSION Automation.

Expected Output & Verification

The selector emits the strongest target and the pause action:

{
  "target_type": "named_restore_point",
  "settings": {
    "recovery_target_name": "before_postgis_3.4.2_upgrade",
    "recovery_target_action": "pause"
  }
}

After recovery reaches the paused target, verify the recovered server landed before the upgrade before promoting it:

-- On the paused, read-only recovered cluster: the extension version must be
-- the PRE-upgrade one, and recovery must be paused (not yet promoted).
SELECT extname, extversion FROM pg_extension WHERE extname = 'postgis';
SELECT pg_is_in_recovery() AS still_recovering,
       pg_last_wal_replay_lsn() AS stopped_at;

extversion must equal the pre-upgrade value and still_recovering must be true. Only then resume/promote. Record the chosen target and the verification result under Version Control & Branching so the rewind is auditable.

Edge Cases & Gotchas

A timestamp target can land on the wrong side of the upgrade. Clock skew between the app that logged upgrade_start_time and the database can put a recovery_target_time after the upgrade began, replaying the damage. Always set recovery_target_inclusive = false on a timestamp fallback, and prefer the named restore point precisely to avoid this class of error.

A named restore point that never reached the archive is unusable. If the pre-upgrade pg_create_restore_point was not followed by a WAL switch, its segment may be missing from the archive and recovery cannot find the name. This is why WAL archiving setup forces the segment out at marking time — verify the name resolves before relying on it.

Auto-promotion destroys your second chance. With recovery_target_action = 'promote', a wrong target is committed the instant replay stops — the server opens read-write and generates a new timeline. pause keeps the option to re-target open; only promote after verification.

Multiple restore points with similar names invite the wrong pick. If several upgrades ran, before_postgis_upgrade alone is ambiguous. Name restore points with the exact target version and a timestamp (before_postgis_3.4.2_upgrade_20260718T0200Z) so the selector picks the intended one unambiguously.