Pinning Extension Versions in Deployment Manifests

How to write a deployment manifest that pins each extension to an exact version and artifact checksum, so every environment installs a byte-identical build and a drifted node is caught before it ever serves a promotion.

Up: Extension Registry Mapping — this page is the manifest format that mapping produces and consumes, under the PostgreSQL Extension Architecture & Lifecycle Fundamentals area of the site.

Context & When This Applies

A deployment manifest is the declaration of what should be installed: for each extension, the exact version, the artifact checksum of its shared object, and the schema it belongs in. Without pinning, “install the latest PostGIS” resolves differently depending on when and where the package feed is read, and two nodes that were meant to be identical quietly diverge. Pinning to a version and a checksum makes the manifest the single source of truth a node is reconciled against — the declared set the compatibility matrix intersects with observed catalog and binary state. This applies to any fleet on PostgreSQL 12–17 where reproducibility and audit readiness are requirements, not aspirations.

The manifest is what lets a pre-flight gate assert strict environment parity: the version that was validated in staging is provably the version that ships to production, because both resolve the same pinned checksum. It is the deployment-time counterpart to the source-tracking discipline in a Git-based workflow for extension control files.

A pinned manifest reconciled against each node's observed state A deployment manifest pins each extension to an exact version, an artifact checksum, and a target schema. For every node, the reconciler compares the manifest's pinned tuple against the node's observed catalog version and its on-disk shared-object checksum. A node whose version and checksum both match the manifest is in parity and eligible for promotion. A node whose version or checksum differs is drifted and blocked until reconciled, so a mismatched build can never serve a promotion. Pinned manifest version + checksum per extension Reconcile manifest vs node Parity → eligible version + checksum match promotion allowed Drift → blocked version or checksum differs reconcile first

Concept: A Version Alone Does Not Pin a Build

Pinning a version number is necessary but not sufficient, because the same version string can correspond to different shared objects: a rebuild against a newer libc, a distro backport, or a re-tagged artifact all keep the version but change the binary. Two nodes reporting postgis 3.4.2 can therefore be running materially different .so files, one of which fails ldd. The manifest closes that gap by pinning the artifact checksum alongside the version — the same loadable-set identity the compatibility matrix synchronization ABI probe verifies. A node is in parity only when both the catalog version and the on-disk checksum match the manifest.

The second concept is fail-closed reconciliation. The manifest is authoritative, so any node whose observed state diverges from it is blocked from promotion until reconciled — never the other way around. This is what makes the manifest a gate rather than mere documentation: it converts “we think every node runs the same build” into a checkable assertion, and it feeds the same declared-set intersection the compatibility validation pipeline performs before it approves any tuple.

Runnable Implementation

The reconciler below loads a pinned manifest and checks a node’s catalog version and on-disk shared-object checksum against it, returning a per-extension parity verdict.

#!/usr/bin/env python3
"""Reconcile a node against a pinned extension manifest. Requires PyYAML, psycopg2."""
import hashlib
import pathlib
import yaml
import psycopg2


def sha256_file(path: str) -> str | None:
    p = pathlib.Path(path)
    if not p.is_file():
        return None
    return hashlib.sha256(p.read_bytes()).hexdigest()[:16]


def reconcile(conn, manifest_path: str, pkglibdir: str) -> dict:
    manifest = yaml.safe_load(pathlib.Path(manifest_path).read_text())
    results = []
    with conn.cursor() as cur:
        for name, pin in sorted(manifest["extensions"].items()):
            cur.execute("SELECT extversion FROM pg_extension WHERE extname = %s",
                        (name,))
            row = cur.fetchone()
            observed_version = row[0] if row else None
            observed_checksum = sha256_file(f"{pkglibdir}/{pin.get('so', name)}.so")

            in_parity = (observed_version == pin["version"]
                         and observed_checksum == pin["checksum"])
            results.append({
                "extension": name,
                "pinned": {"version": pin["version"], "checksum": pin["checksum"]},
                "observed": {"version": observed_version,
                             "checksum": observed_checksum},
                "status": "PARITY" if in_parity else "DRIFT",
            })
    blocked = [r["extension"] for r in results if r["status"] == "DRIFT"]
    return {"node_eligible": not blocked, "drift": blocked, "detail": results}


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    import json
    print(json.dumps(reconcile(conn, "manifest.yaml", "/usr/lib/postgresql/16/lib"),
                     indent=2))

A sample manifest pins both facets per extension:

# manifest.yaml — the declared, pinned state every node is reconciled against.
extensions:
  postgis:
    version: "3.4.2"
    checksum: "a1b2c3d4e5f60718"   # sha256(so)[:16]
    so: "postgis-3"                # module_pathname basename
    schema: "ext"
  pg_stat_statements:
    version: "1.10"
    checksum: "9f8e7d6c5b4a3021"
    schema: "public"

Feed a node_eligible: false result into the pre-flight gate so a drifted node is excluded from the promotion, and drive the corrective install through ALTER EXTENSION automation.

Expected Output & Verification

The reconciler returns a per-extension verdict and the node’s overall eligibility:

{
  "node_eligible": false,
  "drift": ["postgis"],
  "detail": [
    {
      "extension": "postgis",
      "pinned": {"version": "3.4.2", "checksum": "a1b2c3d4e5f60718"},
      "observed": {"version": "3.4.2", "checksum": "00ff11ee22dd33cc"},
      "status": "DRIFT"
    }
  ]
}

This is the instructive case: the version matches but the checksum does not — a re-tagged or rebuilt .so. Confirm the on-disk artifact directly before trusting the verdict:

-- The catalog agrees on the version; only the binary checksum reveals the drift.
SELECT extname, extversion FROM pg_extension WHERE extname = 'postgis';

Record every reconciliation result with the deploy so parity is auditable, and keep the manifest itself under version control and branching so a version bump is a reviewed diff.

Edge Cases & Gotchas

A matching version with a mismatched checksum is the dangerous case. This is the failure pinning exists to catch: two builds sharing a version string but differing in binary. Never pin version alone — always pin the checksum, and treat a checksum-only drift as a hard block, not a warning.

The .so basename can differ from the extension name. PostGIS loads postgis-3.so, not postgis.so; resolve the real filename from the control file’s module_pathname (captured as so in the manifest) or the checksum probe reads the wrong file and reports a spurious miss.

A pinned version with no reachable path on a node is a different failure. Pinning declares intent; it does not create reachability. If the pinned version is unreachable on a node (has no update path), that is a DEPENDENCY_BLOCK for the error categorization framework, handled separately from a checksum drift.

Manifests must be environment-agnostic, not environment-specific. The whole point is that staging and production resolve the same pin. Avoid per-environment version overrides; if an environment genuinely needs a different version, that is a separate manifest with its own review, not an inline exception that defeats parity.