Point-in-Time Recovery Targeting

Point-in-time recovery (PITR) targeting is the temporal control plane inside Restore Drill Orchestration & Environment Isolation: the component that decides which data state a drill materializes, proves that state is reachable, and hands a verifiably-consistent database to downstream validation. The operational gap it closes is deterministic reachability. A static snapshot restore answers “can we restore the last backup?” — PITR targeting answers the harder audit question, “can we restore to 14:32:07 UTC last Tuesday, the instant before the erroneous DELETE, and prove the result is transactionally consistent?” That distinction is what separates a passing recovery drill from a compliance artefact.

This page is written for DBAs, SREs, and Python automation engineers who already own an RTO/RPO budget and need to turn a wall-clock recovery request into a pipeline step. Targeting sits between two other concerns in this section: it consumes an isolated environment produced by sandbox provisioning, and its recovery objectives are inherited from the RTO/RPO mapping frameworks defined during architecture design. Everything below treats time as a first-class pipeline parameter — normalized, validated against the retention window, and translated into engine-native coordinates before a single byte is restored.

Architecture and execution workflow

Targeting executes as a state-aware, multi-stage workflow that fails fast the moment the requested epoch is unreachable, rather than discovering the gap halfway through a multi-hour replay. The pipeline resolves a timestamp, cross-references the backup catalog for snapshot-plus-log continuity, provisions isolation, restores the base image, replays logs to the exact epoch, then stops in read-only mode for handoff.

Figure. Multi stage targeting workflow resolving a timestamp, validating catalog continuity, then restoring and replaying logs to the exact recovery epoch.

The invariant that governs the whole flow is that no irreversible or expensive step runs until the cheap validations have passed. Timestamp resolution and catalog cross-reference cost milliseconds; base restore and log replay cost minutes to hours and consume sandbox capacity. A target outside retention, or one that intersects a known log gap, must be rejected before provisioning — otherwise the drill burns its time budget producing a corrupt dataset that the validation stage then has to catch.

Phase-by-phase breakdown

Target timestamp resolution

Resolution converts an ambiguous, human-supplied instant into an unambiguous UTC coordinate. The input arrives from a compliance window (“restore to end-of-quarter”), an incident ticket payload, or a synthetic drill schedule, and it is almost never clean: it may carry a local timezone, omit an offset entirely, or land on a daylight-saving discontinuity. The resolver normalizes every request to a timezone-aware UTC datetime, rejects any instant in the future, and accounts for clock skew across replica members before it trusts the value. Skipping normalization is the most common cause of an “off by one hour” recovery that passes every technical check yet restores the wrong business state.

Backup catalog cross-reference

Once the instant is canonical, the engine cross-references it against the backup catalog to confirm the recovery point is physically reachable. Three conditions must hold simultaneously: a base backup exists whose stop-time precedes the target, every intermediate log segment between that base and the target is present and uncorrupted, and the storage tier can deliver the base image within the drill’s I/O budget. A single missing WAL segment or a truncated oplog window makes the target unreachable even though the timestamp is perfectly valid. When any condition fails, targeting does not improvise — it routes to the fallback chain configuration to select the nearest consistent checkpoint or escalate.

Isolated environment provisioning

Only after catalog validation succeeds does the pipeline claim a sandbox. Isolation is non-negotiable: a replayed transaction stream must never touch production routing tables, fire real webhooks, or write through to a shared cache. Provisioning enforces network segmentation, ephemeral storage quotas, and a default-deny egress policy before the restore begins, so that even a misconfigured connection string cannot leak recovered data outward. This phase is where targeting depends on the environment guarantees described under sandbox provisioning automation.

Base restore and sequential log replay

With an isolated, health-checked target in place, the pipeline restores the base image and then replays logs sequentially toward the epoch. For PostgreSQL this is a base backup plus WAL replay driven by recovery_target_time; for MySQL it is a physical snapshot plus binlog application to a target datetime; for document stores it is a snapshot plus oplog application to a BSON timestamp. Replay is the long pole, so the orchestrator polls recovery status, applies exponential backoff to transient I/O throttling, and injects checkpoint markers so an interrupted job can resume from the last verified position instead of restarting from the base image.

Figure. The replayable window opens at the base backup; replay applies contiguous log segments up to the requested epoch, then stops exactly on the target coordinate. Segments before the retention floor are unreachable, and those after the target are recorded but never applied.

Consistency stop and read-only handoff

Replay must stop exactly at the target — not one transaction early, not one late. When the engine reaches the coordinate it issues a controlled stop, flushes pending work, and transitions the instance to read-only. Read-only mode is what makes downstream validation trustworthy: the state under test cannot drift while smoke test routing logic directs synthetic queries and referential-integrity scans against it. Targeting’s contract ends the instant it hands off a frozen, consistent, epoch-accurate database.

Python implementation patterns

Treat targeting as a state machine with one pluggable resolver per engine, not a linear script. An abstract TargetResolver isolates the engine-specific translation (ISO instant → native coordinate, instant → base backup id) behind a stable interface, so the orchestrator never branches on engine type. The resolve() template method owns the cross-cutting rules — UTC normalization, future-timestamp rejection, retention checks — that must be identical for every engine.

from __future__ import annotations

import abc
from dataclasses import dataclass
from datetime import datetime, timezone
from enum import Enum


class Engine(str, Enum):
    POSTGRESQL = "postgresql"
    MYSQL = "mysql"
    MONGODB = "mongodb"


@dataclass(frozen=True)
class RecoveryTarget:
    """A validated recovery point in both human and engine coordinates."""
    requested_at: datetime      # tz-aware UTC instant the caller asked for
    engine: Engine
    native_coordinate: str      # e.g. a PostgreSQL recovery_target_time
    base_backup_id: str         # catalog id of the base snapshot to restore first
    within_retention: bool


class TargetResolver(abc.ABC):
    """Per-engine strategy mapping a wall-clock instant to a restore coordinate."""

    @property
    @abc.abstractmethod
    def engine(self) -> Engine:
        raise NotImplementedError

    @abc.abstractmethod
    def to_native(self, instant: datetime) -> str:
        """Translate a tz-aware UTC instant into an engine-native coordinate."""
        raise NotImplementedError

    @abc.abstractmethod
    def base_backup_for(self, instant: datetime) -> str:
        """Return the id of the newest base backup taken at or before instant."""
        raise NotImplementedError

    def resolve(self, raw_timestamp: str, retention_floor: datetime) -> RecoveryTarget:
        # One normalization path for every engine: parse, force UTC, reject futures.
        instant = datetime.fromisoformat(raw_timestamp)
        if instant.tzinfo is None:
            raise ValueError(f"timestamp {raw_timestamp!r} has no timezone offset")
        instant = instant.astimezone(timezone.utc)
        if instant > datetime.now(timezone.utc):
            raise ValueError(f"recovery target {instant.isoformat()} is in the future")
        return RecoveryTarget(
            requested_at=instant,
            engine=self.engine,
            native_coordinate=self.to_native(instant),
            base_backup_id=self.base_backup_for(instant),
            within_retention=instant >= retention_floor,
        )


class PostgresResolver(TargetResolver):
    """Maps an instant to a PostgreSQL recovery_target_time string."""

    def __init__(self, catalog):
        self._catalog = catalog

    @property
    def engine(self) -> Engine:
        return Engine.POSTGRESQL

    def to_native(self, instant: datetime) -> str:
        # PostgreSQL expects 'YYYY-MM-DD HH:MM:SS+00' in postgresql.auto.conf.
        return instant.strftime("%Y-%m-%d %H:%M:%S%z")

    def base_backup_for(self, instant: datetime) -> str:
        candidates = [b for b in self._catalog.base_backups(self.engine)
                      if b["stop_time"] <= instant]
        if not candidates:
            raise LookupError(f"no base backup precedes {instant.isoformat()}")
        return max(candidates, key=lambda b: b["stop_time"])["backup_id"]

The orchestration layer that drives resolvers is I/O-bound — it waits on catalog lookups, storage reads, and replay-status polls — so asyncio keeps concurrent drills from blocking one another. The gating entrypoint below returns explicit POSIX exit codes so an Airflow BashOperator, a Celery task wrapper, or a cron guard can branch on the result: 0 for a reachable, in-retention target, 1 for an unreachable target that must divert to the fallback chain, and 2 for an operator or input error.

from __future__ import annotations

import asyncio
import logging
import sys
from datetime import datetime, timedelta, timezone

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("pitr.targeting")

EXIT_OK = 0            # target reachable and in retention
EXIT_UNREACHABLE = 1   # divert to the fallback chain
EXIT_OPERATOR = 2      # bad input or resolver error


async def gate_target(resolver, raw_timestamp: str, retention_days: int) -> int:
    """Resolve and validate a recovery target; return a POSIX exit code."""
    retention_floor = datetime.now(timezone.utc) - timedelta(days=retention_days)
    try:
        target = await asyncio.to_thread(resolver.resolve, raw_timestamp, retention_floor)
    except (ValueError, LookupError) as exc:
        log.error("targeting rejected %r: %s", raw_timestamp, exc)
        return EXIT_OPERATOR
    if not target.within_retention:
        log.warning("target %s precedes retention floor %s; divert to fallback",
                    target.requested_at.isoformat(), retention_floor.isoformat())
        return EXIT_UNREACHABLE
    log.info("target reachable: engine=%s coordinate=%s base=%s",
             target.engine.value, target.native_coordinate, target.base_backup_id)
    return EXIT_OK


class _InMemoryCatalog:
    """Minimal catalog stub so this module runs standalone in a drill harness."""

    def base_backups(self, engine):
        anchor = datetime.now(timezone.utc) - timedelta(hours=6)
        return [{"backup_id": "base-000042", "stop_time": anchor}]


def main(argv: list[str]) -> int:
    if len(argv) != 2:
        log.error("usage: pitr_target.py <iso-8601-timestamp>")
        return EXIT_OPERATOR
    resolver = PostgresResolver(_InMemoryCatalog())
    return asyncio.run(gate_target(resolver, argv[1], retention_days=35))


if __name__ == "__main__":
    sys.exit(main(sys.argv))

The pattern generalizes cleanly: adding MySQL or MongoDB support means writing one more TargetResolver subclass, not editing the orchestrator. MongoDB in particular needs per-shard boundary resolution and BSON-timestamp arithmetic — that specialization is covered in Point-in-Time Targeting for MongoDB Backups.

Integration with DR drill orchestration

Targeting is a gate, not a leaf. Its exit code determines what the orchestrator does next, and its read-only handoff determines what the next stage receives. Three adjacent pipelines consume or backstop it:

• Fallback routing. An EXIT_UNREACHABLE result diverts to the fallback chain configuration, which selects the nearest consistent checkpoint or escalates to the incident queue rather than failing the drill outright.
• Environment isolation. A successful resolution triggers sandbox provisioning automation to stand up a segmented target with default-deny egress before any restore writes to disk.
• Validation. The read-only instance is handed to smoke test routing logic, which proves the epoch produced the expected business data — not merely a technically successful replay.

Targeting also inherits objectives from upstream architecture: the retention floor and the acceptable recovery granularity trace directly back to the RTO/RPO mapping frameworks that fixed the recovery budget in the first place. Wiring these boundaries as explicit inputs — rather than hard-coded constants — is what lets the same targeting code run across engines and environments.

Error classification and threshold management

Targeting failures are not equal, and collapsing them into a single “restore failed” alert produces fatigue that hides the failures that matter. Classify by reachability and blast radius, and let severity drive routing.

Threshold management hinges on tolerance windows. A replay that lands within a few hundred milliseconds of a target with no committed transactions in that gap is not a failure; a replay that stops a full transaction short is. Encode the acceptable drift explicitly so alerts fire on real RPO misses rather than on sub-second rounding. This taxonomy shares the same severity spine as the broader error categorization frameworks used elsewhere on the site, so a targeting S2 and an integrity-check S2 mean the same thing to the on-call engineer.

Telemetry and compliance output

Every drill must emit structured, queryable evidence — both operational metrics for capacity planning and an audit trail for regulators. Export the operational signals as Prometheus metrics so time-to-verify trends are visible across runs:

from prometheus_client import Counter, Gauge, Histogram

TARGET_RESOLUTION_SECONDS = Histogram(
    "pitr_target_resolution_seconds",
    "Wall-clock time to resolve and validate a recovery target",
    ["engine"],
)
REPLAY_LAG_SECONDS = Gauge(
    "pitr_replay_lag_seconds",
    "Difference between the achieved stop point and the requested epoch",
    ["engine", "drill_id"],
)
TARGET_OUTCOME_TOTAL = Counter(
    "pitr_target_outcome_total",
    "Targeting outcomes by exit class",
    ["engine", "outcome"],   # outcome in {ok, unreachable, operator}
)


def record_outcome(engine: str, drill_id: str, exit_code: int, lag_seconds: float) -> None:
    outcome = {0: "ok", 1: "unreachable", 2: "operator"}.get(exit_code, "unknown")
    TARGET_OUTCOME_TOTAL.labels(engine=engine, outcome=outcome).inc()
    REPLAY_LAG_SECONDS.labels(engine=engine, drill_id=drill_id).set(lag_seconds)

The audit trail is a separate, append-only artefact. Each drill writes one record capturing the requested epoch, the resolved native coordinate, the base backup id, the achieved stop point, the replay lag, and the operator or pipeline that triggered it. That record maps cleanly onto regulatory controls: NIST SP 800-34 contingency-plan testing (CP-4) expects documented, repeatable recovery exercises; SOC 2 availability criteria expect evidence that recovery objectives are met on a schedule; and ISO 22301 expects business-continuity exercises with retained results. Because targeting records the exact epoch reached and the drift from the request, a single JSON line satisfies all three: proof that the drill ran, proof of what it recovered, and proof of how close it came to the objective.

Operational best practices

Harden targeting with the same rigor as any production write path:

• Store and compare timestamps in UTC only. Convert at the edge, reject timezone-naive input at the gate, and never let a local offset survive into a native coordinate.
• Validate retention before provisioning. Cheap checks first — an unreachable target must never consume sandbox capacity or storage bandwidth.
• Make replay resumable. Persist checkpoint metadata so an interrupted job resumes from the last verified position instead of restarting from the base image.
• Enforce default-deny egress on the target. A recovered instance that can reach production is a data-leak and split-brain risk; isolation is a precondition of targeting, not an afterthought.
• Assert the stop point. After replay, confirm the achieved coordinate equals the requested epoch within the tolerance window and fail the drill if it does not.
• Keep engine parity. Match base-backup and replay-log versions to the target engine version to avoid replay incompatibilities that surface only mid-drill.
• Emit exit codes and metrics on every path. A drill that succeeds silently is indistinguishable from one that never ran; structured telemetry is what turns a restore into an audit artefact.

Related

• Restore Drill Orchestration & Environment Isolation — the parent section this targeting workflow belongs to.
• Sandbox Provisioning Automation — the isolated environment targeting restores into.
• Fallback Chain Configuration — where unreachable targets divert.
• Smoke Test Routing Logic — the validation stage that consumes the read-only recovered instance.
• Point-in-Time Targeting for MongoDB Backups — engine-specific boundary resolution for sharded document stores.

This targeting workflow is one component of the broader Restore Drill Orchestration & Environment Isolation discipline that proves recovery objectives are met before a real incident forces the issue.