Tuning Maintenance Windows for High-Availability Clusters

Size and enforce an ALTER EXTENSION UPDATE maintenance window on a streaming-replication or Patroni-managed PostgreSQL cluster so the catalog rewrite completes inside the stall budget your SLO allows — and fails fast to rollback if it cannot.

Up: Threshold Tuning for Downtime Windows — the gate that converts a downtime SLO into enforced pipeline ceilings; this page is the high-availability specialization of that gate, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline.

Context & When This Applies

Reach for this technique when the target of an extension upgrade is not a single node but a replicated topology — a primary with one or more streaming standbys, usually fronted by orchestration tooling such as Patroni or repmgr — and the maintenance-window contract is measured in single-digit seconds. It applies to PostgreSQL 12 and newer, where pg_stat_replication.replay_lag is exposed as an interval and lock_timeout/statement_timeout can bound a single DDL statement precisely.

The scenario is most acute for compiled extensions that ship a shared object and, in many cases, register into shared_preload_libraries — PostGIS, timescaledb, pgvector, pg_partman. For those, a maintenance window has two independent risks that a single-node runbook never sees: the primary can acquire an AccessExclusiveLock on pg_extension and stall every dependent query, and a standby can carry a mismatched .so that only surfaces as a FATAL at promotion time. A window that ignores either one turns a two-second catalog update into a failed failover. This page assumes you have already proven the version tuple is reachable on every node — that check belongs to the compatibility matrix and to async simulation with pg_upgrade --check — and focuses only on sizing and enforcing the live window.

Concept: Why Streaming Replication Does Not Do This For You

The load-bearing fact is that streaming replication ships WAL records, not DDL intent. When ALTER EXTENSION ... UPDATE runs on the primary, its catalog mutations to pg_extension, pg_proc, and pg_depend are written to WAL and replayed on every standby — but the files the new version depends on are not. The .so in $libdir, the new SQL function bodies, and any shared_preload_libraries change are all out-of-band: they must already be present on each node’s filesystem before the WAL that references them arrives, or replay stalls and a subsequent promotion aborts.

That splits the window into three measurable cost dimensions, each of which must fit under the SLO stall budget:

  • Lock acquisition wait — how long the update backend queues behind existing transactions before it can take its AccessExclusiveLock. Bounded with lock_timeout.
  • Catalog rewrite + plan invalidation — the exclusive-lock hold time itself, during which dependent queries block. Bounded with statement_timeout.
  • Replica apply lag — how far behind the busiest standby is when the DDL transaction reaches it, which sets how long the whole cluster is version-skewed. Read from pg_stat_replication.

Static windows fail because all three scale with live load, so the window must be derived from real-time telemetry at the moment of promotion, not from a historical average. The safe execution order on a Patroni-managed topology is a controlled switchover sequence:

Controlled switchover sequence for an ALTER EXTENSION UPDATE on a Patroni cluster Three lifelines run top to bottom: the Orchestrator on the left, the Primary in the middle, and the Standby on the right. Seven ordered messages flow between them. Step one, the Orchestrator tells the Standby to pause WAL replay. Step two, it issues ALTER EXTENSION UPDATE on the Primary. Step three, the Primary returns catalog updated. Step four, the Orchestrator tells the Standby to resume replication. Step five, the Standby returns that its replay lag is within threshold. Step six, the Orchestrator asks the Primary to validate function signatures. Step seven, it asks the Standby to validate its shared libraries. Solid arrows are commands and dashed arrows are returned confirmations. Orchestrator Primary Standby Orchestrator Primary Standby 1 · pause WAL replay 2 · ALTER EXTENSION UPDATE O catalog updated --> catalog updated 4 · resume replication O replay lag within threshold --> replay lag within threshold 6 · validate function signatures 7 · validate shared libraries The three cost dimensions of a maintenance window laid under the SLO stall budget A horizontal time axis runs from zero to eight seconds, the full SLO stall budget shown as a bracket across the top. Three stacked bars run left to right in sequence. The first bar is lock acquisition wait, bounded by the lock_timeout ceiling at three seconds. The second bar is the AccessExclusiveLock hold, covering catalog rewrite and plan invalidation, bounded by the statement_timeout ceiling at six seconds. The third bar is standby replay lag, during which the whole cluster is version-skewed; version skew clears at two point three seconds when the busiest standby replays the DDL. Every phase finishes well inside its ceiling, so the whole window fits comfortably under the eight-second budget with headroom to spare. SLO STALL BUDGET · 8s Lock acquisition wait bounded by lock_timeout AccessExclusiveLock hold catalog rewrite + plan invalidation · bounded by statement_timeout Standby replay lag cluster version-skewed until replay 0s version skew clears · 2.3s lock_timeout · 3s statement_timeout · 6s 8s Every phase completes inside its ceiling — the whole window clears well under the 8s budget.

Runnable Implementation

The gate below runs from the promotion pipeline. It refuses to open the window unless every standby can already reach the target version on disk, snapshots live replica lag against a ceiling, bounds the DDL with both lock_timeout and statement_timeout derived from the stall budget, applies the update on the primary, then confirms every standby replayed the catalog change. The actual ALTER EXTENSION call is the same one owned by ALTER EXTENSION automation; here it is wrapped in the high-availability guards.

#!/usr/bin/env python3
"""Size and gate an ALTER EXTENSION UPDATE window on an HA PostgreSQL cluster.

Confirms every standby can reach the target version on disk, gates on live
replica lag, bounds the DDL with timeouts derived from the SLO stall budget,
promotes on the primary, then verifies replay on every standby.
"""
from __future__ import annotations

import sys
import time
from dataclasses import dataclass

import psycopg  # psycopg 3; the same flow works on psycopg2 with minor edits

# --- Tunables derived from the maintenance-window SLO ----------------------
SLO_STALL_BUDGET_SEC = 8.0   # total stall the window contract permits
MAX_REPLAY_LAG_SEC = 5.0     # abort if any standby lags more than this
LOCK_TIMEOUT_MS = 3000       # hard cap on the AccessExclusiveLock wait
SAFETY_MARGIN = 0.75         # spend at most 75% of the budget on the DDL


@dataclass
class NodeState:
    addr: str
    state: str
    replay_lag_sec: float


def standby_lag(conn) -> list[NodeState]:
    """Per-standby replay lag, measured on the primary."""
    rows = conn.execute(
        """
        SELECT client_addr,
               state,
               COALESCE(EXTRACT(EPOCH FROM replay_lag), 0) AS replay_lag_sec
        FROM pg_stat_replication
        WHERE state = 'streaming'
        """
    ).fetchall()
    return [NodeState(str(a), s, float(lag)) for a, s, lag in rows]


def can_reach(conn, extname: str, target: str) -> bool:
    """True only if `extname` is installed AND its on-disk default is `target`,
    i.e. the node already carries the .so and control file for the new version."""
    row = conn.execute(
        """
        SELECT a.default_version, e.extversion
        FROM pg_available_extensions a
        LEFT JOIN pg_extension e ON e.extname = a.name
        WHERE a.name = %s
        """,
        (extname,),
    ).fetchone()
    if row is None:
        return False
    default_version, installed = row
    return installed is not None and default_version == target


def promote(primary_dsn: str, standby_dsns: list[str],
            extname: str, target: str) -> None:
    with psycopg.connect(primary_dsn, autocommit=True) as pconn:
        # 1. Pre-flight: every standby must already carry the target files,
        #    or its promotion will FATAL on a missing library later.
        for dsn in standby_dsns:
            with psycopg.connect(dsn) as sconn:
                if not can_reach(sconn, extname, target):
                    sys.exit(f"ABORT: {dsn} cannot reach {extname} {target}")

        # 2. Gate on live lag before opening the window.
        worst = max((n.replay_lag_sec for n in standby_lag(pconn)), default=0.0)
        if worst > MAX_REPLAY_LAG_SEC:
            sys.exit(f"ABORT: replica lag {worst:.1f}s exceeds ceiling")

        # 3. Bound the DDL so it can never blow the stall budget. SET takes
        #    integer literals only, so format the computed millisecond values.
        budget_ms = int(SLO_STALL_BUDGET_SEC * SAFETY_MARGIN * 1000)
        pconn.execute(f"SET lock_timeout = {min(LOCK_TIMEOUT_MS, budget_ms)}")
        pconn.execute(f"SET statement_timeout = {budget_ms}")

        # 4. Apply on the primary; WAL carries the catalog change to standbys.
        #    extname is an identifier (trusted config, not user input); the
        #    version is a string literal and is safely parameterized.
        started = time.monotonic()
        pconn.execute(f"ALTER EXTENSION {extname} UPDATE TO %s", (target,))
        elapsed = time.monotonic() - started

        # 5. Confirm every standby replayed the DDL transaction.
        for dsn in standby_dsns:
            with psycopg.connect(dsn) as sconn:
                if not can_reach(sconn, extname, target):
                    sys.exit(f"ROLLBACK NEEDED: {dsn} did not replay {target}")

    print(f"OK {extname} -> {target} in {elapsed:.2f}s "
          f"(budget {SLO_STALL_BUDGET_SEC}s, worst lag {worst:.1f}s)")


if __name__ == "__main__":
    promote(
        primary_dsn="host=pg-primary dbname=app",
        standby_dsns=["host=pg-standby-1 dbname=app",
                      "host=pg-standby-2 dbname=app"],
        extname="postgis",
        target="3.4.1",
    )

Expected Output & Verification

A clean promotion prints a single structured line the pipeline can capture and fold back into its ceiling estimate:

OK postgis -> 3.4.1 in 0.42s (budget 8.0s, worst lag 0.3s)

Confirm the window behaved as measured, don’t just trust the exit code. First, verify every standby has consumed the DDL transaction by comparing the current primary LSN against each standby’s replay_lsn:

-- Zero (or a tiny) diff means the standby has replayed the catalog update.
SELECT client_addr,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind,
       EXTRACT(EPOCH FROM replay_lag) AS replay_lag_sec
FROM pg_stat_replication
WHERE state = 'streaming';

Then confirm the extension metadata and function signatures resolved identically on every node — a version match with a missing function row means the catalog replayed but the library did not load:

SELECT e.extname, e.extversion,
       count(p.oid) AS resolved_functions
FROM pg_extension e
LEFT JOIN pg_depend d ON d.refobjid = e.oid AND d.deptype = 'e'
LEFT JOIN pg_proc p ON p.oid = d.objid
WHERE e.extname = 'postgis'
GROUP BY e.extname, e.extversion;

When integrating with Patroni, drive the switchover through its REST API only after these checks return clean, and consult the PostgreSQL monitoring statistics reference for exact pg_stat_replication lag semantics. If a check fails, hold a snapshot and point-in-time recovery checkpoint ready so a mid-flight abort has a clean restore target.

Edge Cases & Gotchas

A standby is missing the shared library and only fails at promotion. Because the catalog change replays but the .so does not travel with it, a standby that was never patched looks healthy until it is promoted, then dies with:

FATAL:  could not load library "$libdir/postgis-3": No such file or directory

The pre-flight can_reach check in step 1 is exactly what prevents this — it rejects any standby whose on-disk default_version is not the target before the window opens. Treat a failed pre-flight as a packaging problem, cross-referenced against the dependency tree analysis pre-flight, never as something to retry.

The DDL blows past the lock ceiling under load. When a long-running transaction holds a conflicting lock, the bounded update self-cancels rather than piling up:

ERROR:  canceling statement due to lock timeout

This is the intended, safe outcome: the window closed itself instead of stalling every dependent query. Route the failure through error categorization, which marks LOCK_CONTENTION as retryable, and reschedule into a quieter window instead of forcing it.

shared_preload_libraries changes need a restart the window never budgeted for. An extension that must be added to shared_preload_libraries cannot activate with pg_reload_conf() alone; the new setting sits pending until a full restart. Verify before promoting:

-- A row with pending_restart = true means this node still needs a restart
-- cycle scheduled inside the maintenance window.
SELECT name, setting, pending_restart
FROM pg_settings
WHERE name = 'shared_preload_libraries';

Size the window to include a rolling restart of each standby, and stage that restart on the same topology your staging environment already mirrors — see routing staging upgrades to mirror production topology.

Replica lag is low at snapshot time but spikes mid-window. The gate reads lag once, before the DDL. On a write-heavy primary a checkpoint or vacuum storm can push a standby past the ceiling during the window. Keep MAX_REPLAY_LAG_SEC conservative relative to the budget, and if promotions run during unpredictable load, re-read pg_stat_replication immediately after step 4 and abort to rollback if the worst standby has drifted beyond the ceiling. Installing the extension as a superuser here also widens the blast radius, so enforce least privilege per Security Boundaries & Permissions.