Compatibility Matrix Synchronization for PostgreSQL Extension Lifecycles #

A compatibility matrix is only useful if it matches reality on every node. The moment a replica receives a partial package upgrade, an artifact store publishes a new build, or a .so is rebuilt against a different libc, a statically-authored matrix starts lying — and the pipeline that trusts it will happily promote an extension version that is unreachable, unlinkable, or unsupported on the target server major. Compatibility matrix synchronization is the discipline of continuously reconciling three independent sources of truth — the declared artifact registry, the live catalog on each node, and the on-disk shared libraries — into a single deterministic, checksummed artifact that upgrade automation can gate on without guessing. This page is for platform engineers and database SREs who need the version-to-server mapping to be a verifiable build output, not tribal knowledge in a spreadsheet.

Up: Extension Upgrade Planning & Compatibility Validation — the staged validation pipeline that consumes this matrix as its first gate is described there, and the four compatibility surfaces it defines are the surfaces this matrix has to keep in sync.

Synchronization Flow #

Synchronization is a reconciliation pass, not a one-way export. The generator pulls the declared state from the registry, samples the observed state from every node, intersects them, then fails closed on any divergence before publishing a new immutable matrix.

Every arrow into the merge box is a source that can drift independently, and the divergence gate is the whole point: a matrix is only published when all three agree, so a downstream promotion never runs against a version tuple that some node cannot actually satisfy.

Prerequisites #

The synchronizer fails closed: if any source is unreachable or ambiguous, it keeps the last-good matrix rather than publishing a partial one. Confirm the environment meets these assumptions before wiring it into a pipeline.

• PostgreSQL version: 9.6 or newer on every sampled node. The catalog sample reads pg_available_extension_versions (present since 9.6) and pg_extension; the reconciliation logic treats any node that predates the view as an unresolvable source and halts rather than assuming compatibility.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary) for catalog sampling and PyYAML (pip install pyyaml) for the matrix artifact. Only the standard library plus these two is required. Async fleets can port the sampler to asyncpg; the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: A read-only role is sufficient for every catalog sample and every validation query — synchronization never issues DDL. The role only needs CONNECT and SELECT on the system catalogs, which every login role has by default. Scope the artifact-store credentials separately and keep them least-privilege per Security Boundaries & Permissions.
• Catalog state: The declared version-to-artifact mapping must come from an authoritative store, not a transient upstream mirror. Keep it current with Extension Registry Mapping so the matrix is built from signed, auditable releases rather than whatever a package feed happens to serve that hour.
• Shared-library reachability: The synchronizer must be able to reach a representative node’s $libdir (or a mirror of it) to run the ABI probe. A version declared in the registry but whose .so is missing on a node is exactly the drift this process exists to catch.

Core Concept: Reconciliation as a Set Intersection #

Synchronization treats the matrix as the intersection of three sets, computed per (server_major, extension) cell:

1. Declared set — the registry. What the artifact store says exists and is approved: a mapping from PostgreSQL major to extension to the versions published and signed for it. This is the intended state.
2. Observed catalog set — pg_available_extension_versions. What each running node reports it can actually install or update to, computed by the server from control files present in $sharedir/extension on that node. This is the installable state.
3. Observed binary set — the ABI probe. What each .so on disk actually links against. A control file can advertise a version whose shared object is missing a symbol or built against the wrong libc. This is the loadable state.

A cell is only written to the published matrix when a version appears in all three sets. The declared-minus-observed difference is drift the pipeline must not ship; the observed-minus-declared difference is unapproved state a node is carrying — either an unsanctioned manual install or a stale package that reconciliation should flag. Because the matrix drives promotion, both differences are treated as hard failures rather than warnings.

Determinism is the second half of the concept. The synchronizer sorts every key, serializes with fixed separators, and hashes the result, so identical inputs always yield an identical checksum. That checksum is the matrix’s identity: downstream stages pin to it, and a changed checksum is the only thing that invalidates a cached compatibility decision. This mirrors the same immutable-artifact model that Test Environment Routing relies on to guarantee staging and production evaluate byte-identical version tuples. Where transitive requires chains complicate a cell — an extension whose compatibility depends on a co-installed dependency — the ordering comes from Dependency Tree Analysis, which the synchronizer consumes rather than re-implements.

Step-by-Step Implementation #

The following procedure turns the reconciliation model into a runnable synchronizer. Each code block is complete and copy-pasteable; run every step with a read-only role and reserve write access for the artifact publish in step 4.

Step 1 — Generate the deterministic declared matrix #

Start from the registry. Normalize it into a sorted, checksummed structure so the artifact is reproducible and diffable across runs. The --dry-run flag validates and prints the checksum without writing, making this safe to run as a pre-merge check.

#!/usr/bin/env python3
"""
Deterministic compatibility matrix generator.
Idempotent, CI-ready, and strictly side-effect free in dry-run mode.
Requires: PyYAML (pip install pyyaml)
"""
import argparse
import hashlib
import json
import sys
from pathlib import Path

import yaml


def compute_checksum(matrix: dict) -> str:
    """Deterministic SHA-256 of the sorted, compactly-serialized matrix."""
    serialized = json.dumps(matrix, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(serialized.encode("utf-8")).hexdigest()


def fetch_registry_metadata() -> dict:
    """Replace with real artifact-store calls (Artifactory, Nexus, PGDG)."""
    return {
        "postgresql_15": {"postgis": "3.4.1", "pgvector": "0.5.1"},
        "postgresql_16": {"postgis": "3.5.0", "pgvector": "0.6.0"},
    }


def build_matrix(dry_run: bool = False,
                 output_path: str = "compatibility_matrix.yaml") -> dict:
    raw = fetch_registry_metadata()
    # Enforce deterministic ordering so identical inputs hash identically.
    matrix = {k: dict(sorted(v.items())) for k, v in sorted(raw.items())}
    checksum = compute_checksum(matrix)

    if dry_run:
        print(json.dumps({"status": "VALIDATED",
                          "checksum": checksum,
                          "cells": sum(len(v) for v in matrix.values())}))
        return matrix

    target = Path(output_path)
    if target.exists() and yaml.safe_load(target.read_text()) == matrix:
        print(json.dumps({"status": "UNCHANGED", "checksum": checksum}))
        return matrix

    target.write_text(yaml.dump(matrix, default_flow_style=False, sort_keys=True))
    print(json.dumps({"status": "WRITTEN",
                      "output": output_path,
                      "checksum": checksum}))
    return matrix


if __name__ == "__main__":
    p = argparse.ArgumentParser(description="Generate the declared compatibility matrix")
    p.add_argument("--dry-run", action="store_true", help="Validate without writing")
    p.add_argument("--output", default="compatibility_matrix.yaml")
    args = p.parse_args()
    try:
        build_matrix(dry_run=args.dry_run, output_path=args.output)
    except Exception as exc:  # noqa: BLE001 — top-level CI boundary
        print(json.dumps({"status": "FAILURE",
                          "error": str(exc),
                          "phase": "matrix_generation"}), file=sys.stderr)
        sys.exit(1)

Step 2 — Sample the live catalog on every node #

The declared matrix is intent; the catalog is fact. Query pg_available_extension_versions on each node and fold the results into an observed set. Any node the sampler cannot reach is a hard stop, not a silent skip.

#!/usr/bin/env python3
"""
Sample pg_available_extension_versions across a fleet and return the
observed installable set as {server_major: {ext: {versions}}}.
Requires: psycopg2-binary
"""
import json
import sys
import psycopg2


CATALOG_SQL = """
    SELECT name, version
    FROM pg_available_extension_versions
    WHERE name = ANY(%s)
    ORDER BY name, version;
"""


def server_major(conn) -> str:
    with conn.cursor() as cur:
        cur.execute("SHOW server_version_num;")           # e.g. 160004
        num = int(cur.fetchone()[0])
    return f"postgresql_{num // 10000}"                    # -> postgresql_16


def sample_node(dsn: str, extensions: list) -> tuple:
    conn = psycopg2.connect(dsn)
    try:
        major = server_major(conn)
        observed: dict = {}
        with conn.cursor() as cur:
            cur.execute(CATALOG_SQL, (extensions,))
            for name, version in cur.fetchall():
                observed.setdefault(name, set()).add(version)
        return major, observed
    finally:
        conn.close()


def sample_fleet(dsns: list, extensions: list) -> dict:
    fleet: dict = {}
    for dsn in dsns:
        try:
            major, observed = sample_node(dsn, extensions)
        except psycopg2.Error as exc:
            # Unreachable/unsampleable node -> fail closed.
            print(json.dumps({"status": "FAILURE",
                              "phase": "catalog_sample",
                              "dsn_host": dsn.split("@")[-1],
                              "error": str(exc).strip()}), file=sys.stderr)
            sys.exit(1)
        bucket = fleet.setdefault(major, {})
        for ext, versions in observed.items():
            # Intersect across nodes of the same major: a version only counts
            # as installable if EVERY node of that major reports it.
            bucket[ext] = bucket[ext] & versions if ext in bucket else set(versions)
    return fleet

Step 3 — Probe the ABI of each declared shared object #

A control file can advertise a version whose binary is unlinkable. Cross-reference the declared cells against the real .so on disk and reject any with unresolved symbols. Resolve the true filename from the control file’s module_pathname when it differs from the extension name (for example postgis-3.so).

#!/usr/bin/env bash
# ABI probe for the shared objects backing a declared matrix.
# Exits non-zero (fail closed) on the first unresolved shared library.
set -euo pipefail

MATRIX_FILE="${1:-compatibility_matrix.yaml}"
PG_CONFIG="${2:-pg_config}"

if [[ ! -f "$MATRIX_FILE" ]]; then
    echo '{"status":"FAILURE","phase":"abi_probe","error":"matrix file missing"}' >&2
    exit 1
fi

PKGLIBDIR="$("$PG_CONFIG" --pkglibdir)"

# Emit one .so path per declared extension cell.
while IFS= read -r so_path; do
    if [[ ! -f "$so_path" ]]; then
        echo "{\"status\":\"FAILURE\",\"phase\":\"abi_probe\",\"error\":\"missing binary: ${so_path}\"}" >&2
        exit 1
    fi
    if ldd "$so_path" 2>/dev/null | grep -q 'not found'; then
        missing="$(ldd "$so_path" | grep 'not found' | tr -d '\t' | paste -sd';' -)"
        echo "{\"status\":\"FAILURE\",\"phase\":\"abi_probe\",\"error\":\"unresolved: ${so_path} -> ${missing}\"}" >&2
        exit 1
    fi
done < <(python3 - "$MATRIX_FILE" "$PKGLIBDIR" <<'PY'
import sys, yaml
matrix_file, pkglibdir = sys.argv[1], sys.argv[2]
with open(matrix_file) as fh:
    matrix = yaml.safe_load(fh) or {}
for _major, exts in matrix.items():
    for ext_name in exts:
        # module_pathname may point at a versioned object; resolve it there
        # in production. Default: $pkglibdir/<ext_name>.so
        print(f"{pkglibdir}/{ext_name}.so")
PY
)

echo '{"status":"SUCCESS","phase":"abi_probe","message":"all declared shared objects resolve"}'

Step 4 — Reconcile, checksum, and publish #

Intersect the declared set (step 1) with the observed catalog set (step 2), having gated on the ABI probe (step 3). Only cells present in both sets survive; anything declared-but-unobserved or observed-but-undeclared is reported as drift and blocks the publish.

#!/usr/bin/env python3
"""Reconcile declared vs observed sets and publish an immutable matrix."""
import hashlib
import json
import sys


def reconcile(declared: dict, observed: dict) -> tuple:
    """Return (matrix, drift). matrix = intersected cells; drift = divergences."""
    matrix: dict = {}
    drift: list = []
    majors = set(declared) | set(observed)
    for major in sorted(majors):
        dec = declared.get(major, {})
        obs = observed.get(major, {})
        for ext in sorted(set(dec) | set(obs)):
            declared_ver = dec.get(ext)
            installable = obs.get(ext, set())
            if declared_ver is None:
                drift.append({"cell": f"{major}/{ext}", "kind": "undeclared_on_node"})
            elif declared_ver not in installable:
                drift.append({"cell": f"{major}/{ext}",
                              "kind": "declared_not_installable",
                              "declared": declared_ver})
            else:
                matrix.setdefault(major, {})[ext] = declared_ver
    return matrix, drift


def checksum(matrix: dict) -> str:
    return hashlib.sha256(
        json.dumps(matrix, sort_keys=True, separators=(",", ":")).encode()
    ).hexdigest()


def publish(declared: dict, observed: dict) -> int:
    matrix, drift = reconcile(declared, observed)
    if drift:
        print(json.dumps({"status": "DRIFT",
                          "phase": "reconcile",
                          "divergences": drift}), file=sys.stderr)
        return 1                      # keep last-good; do not overwrite
    print(json.dumps({"status": "PUBLISHED",
                      "checksum": checksum(matrix),
                      "matrix": matrix}))
    return 0


if __name__ == "__main__":
    # In production, load these from steps 1 and 2 rather than hard-coding.
    declared_example = {"postgresql_16": {"postgis": "3.5.0", "pgvector": "0.6.0"}}
    observed_example = {"postgresql_16": {"postgis": {"3.5.0"}, "pgvector": {"0.6.0"}}}
    sys.exit(publish(declared_example, observed_example))

Wire steps 1–4 as a single pre-merge gate. If any step exits non-zero — a generation failure, an unreachable node, an unresolved .so, or a reconciliation drift — the pipeline halts and the previously published matrix (and its checksum) remains the source of truth. Extension-family-specific parsing quirks — versioned shared objects, requires chains, control-file directives — are worked end-to-end in Building a Dynamic Compatibility Matrix for PostGIS and pgvector.

Dry-Run & Validation Gate #

The dry-run is the safety interlock: it runs generation, sampling, and reconciliation without publishing, and emits a machine-readable verdict your pipeline archives as a deploy artifact. A clean run looks like this:

{
  "status": "PUBLISHED",
  "checksum": "9f2b7c1e5a08d4c3b6e1f0a92d7c4b8e5f3a1c0d9e8b7a6f5c4d3e2b1a0f9e8d",
  "matrix": {
    "postgresql_16": { "postgis": "3.5.0", "pgvector": "0.6.0" }
  }
}

Gate the committing publish on three conditions before it becomes the fleet’s source of truth:

• status is PUBLISHED, not DRIFT. A DRIFT verdict carries a divergences array naming every major/ext cell that failed reconciliation; the pipeline must block and route those cells to triage rather than shipping a partial matrix.
• The ABI probe exited 0. A version whose .so fails ldd will still appear in pg_available_extension_versions and pass the catalog sample, so the binary probe is a separate, mandatory gate that must run before the publish.
• The checksum changed intentionally. A new checksum invalidates every cached compatibility decision downstream. If the checksum moved without a corresponding registry change, treat it as unexplained drift and investigate before promoting anything against it.

Feed the published checksum forward: Async Upgrade Simulation consumes the exact tuples in the matrix to model ALTER EXTENSION UPDATE timelines against production-sized data, so a matrix that has not passed this gate must never reach simulation.

Failure Modes & Error Taxonomy #

Synchronization fails in a small, well-defined set of ways. Each has a distinctive signal — an SQLSTATE, a log line, or a reconciliation verdict — and each maps to a specific recovery action.

When these divergences recur across runs, route them into a structured classifier rather than reading logs by hand — Error Categorization Frameworks turns these SQLSTATEs and reconciliation verdicts into automated triage signals.

Rollback & Recovery Path #

Synchronization never mutates the database, so recovery is about the artifact, not the catalog: a bad publish is undone by reverting to the last-good matrix and its checksum. Because the matrix is immutable and checksummed, rollback is deterministic.

1. Pin the last-good checksum. Every published matrix is stored with its SHA-256. On a drift or a mis-publish, downstream gates re-pin to the previous checksum — the version tuples they were already validating against remain in force.
2. Never overwrite in place. The reconciler exits non-zero on drift precisely so the previous artifact is preserved. Publishing writes a new file keyed by checksum; the “current” pointer only advances on a clean run.
3. Quarantine the diverged cells. Remove the major/ext cells named in the divergences array from the promotion queue so the rest of the fleet keeps upgrading while the diverged nodes are reconciled.
4. Reconcile the source, then re-run. Fix the underlying drift — restore the missing package, rebuild the .so, or add the unsanctioned install to the registry — then re-run steps 1–4. A clean pass republishes and advances the pointer.
5. Restore node state when a package cannot be re-fetched. If a node’s on-disk state is unrecoverable, drive its restore through Snapshot & Point-in-Time Recovery rather than hand-editing $sharedir.

Pair every published matrix with Version Control & Branching so each checksum is a reviewable commit, and every rollback is a reproducible revert rather than an improvised fix.

Performance & Scale Considerations #

The reconciliation logic is cheap; the cost lives in fleet-wide sampling and the ABI probes.

• Sampling fan-out. Each node contributes one short read-only query against pg_available_extension_versions. On a large fleet, sample nodes concurrently (a thread or async pool) and bound each connection with a connect_timeout so one hung node cannot stall the whole synchronization run.
• Representative sampling vs. full sweep. Sampling every node of a given server major is the safe default, but on fleets of hundreds of identical replicas you can sample a representative quorum per package generation and reconcile the remainder against their known package manifest — accepting that a divergent replica outside the quorum is caught at Test Environment Routing instead.
• ABI probe caching. ldd output is stable for a given .so inode. Cache probe results keyed by the file’s checksum so a matrix rebuild that touches only one extension does not re-probe every unchanged binary.
• Publish cadence. Synchronization is safe to run on every registry change and on a schedule, but couple the promotion it gates to your maintenance windows — a fresh matrix does not itself apply anything, so downtime budgeting stays the concern of Threshold Tuning for Downtime Windows.

FAQ #

Related Pages #

• Up: Extension Upgrade Planning & Compatibility Validation
• Building a Dynamic Compatibility Matrix for PostGIS and pgvector
• Test Environment Routing
• Async Upgrade Simulation
• Error Categorization Frameworks
• Extension Registry Mapping