Extension Registry Mapping for PostgreSQL Extension Fleets #

Extension registry mapping is the practice of building a single authoritative map from each extension’s on-disk artifacts — its .control manifest, its versioned .sql scripts, and its compiled shared library — to the runtime state a PostgreSQL server can actually resolve. Database SREs, platform engineers, and DevOps teams running multi-version fleets need this map because the two things that must agree during any upgrade — what the filesystem ships and what the catalog reports — drift independently. A package upgraded on the primary but not on a replica, a module_pathname that points at a library the target node never installed, or a control file whose default_version has no matching .so all produce the same symptom: CREATE EXTENSION and ALTER EXTENSION UPDATE fail at the worst possible moment, mid-deploy, on one node out of many. This page shows how to make that mapping explicit, versioned, and enforceable inside a pipeline.

This work sits inside the broader PostgreSQL Extension Architecture & Lifecycle Fundamentals, which defines how control files, shared library loading, and catalog registration behave; those mechanics are the substrate the mapper reconciles against, so treat that reference as prerequisite reading.

Mapping Pipeline at a Glance #

Registry mapping runs as a read-only reconciliation stage: it scans the on-disk package layout, cross-checks each artifact against the catalog the server exposes, and emits a canonical manifest that every downstream gate diffs against. It never mutates the database.

Every artifact the scan touches becomes a row in the manifest: extension name, default_version, the library the control file expects, whether that library is present on disk, and the transitive requires edges. Downstream stages — dependency resolution, compatibility validation, the apply itself — all consume that manifest instead of re-deriving state ad hoc, which is what keeps a fleet reproducible.

Prerequisites #

The mapper is deliberately conservative: it treats any surface it cannot read as a mapping failure rather than assuming a benign default.

• PostgreSQL version: 9.6 or newer on every node. pg_available_extensions and pg_available_extension_versions (the catalog side of the map) have existed since 9.6; the pg_config binary that reports the on-disk paths ships with every server package. The reconciliation logic itself is version-agnostic, but the manifest is only meaningful when compared across nodes running catalog-identical package sets.
• Python packages: Python 3.8+. The scanner needs only the standard library (pathlib, subprocess, json, argparse). If you also reconcile against a live catalog rather than the filesystem alone, add psycopg2-binary (pip install psycopg2-binary); the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required privileges: Filesystem read access to pg_config --sharedir and pg_config --pkglibdir is enough for the on-disk scan and dry-run. Querying the catalog needs only a read-only role. Nothing in the mapping stage requires SUPERUSER; keep the privileged role reserved for the guarded apply step and scope it per Security Boundaries & Permissions.
• Catalog state: The baseline manifest you diff against must come from a node you trust — usually production or a golden image. Comparing a fresh scan against that baseline is how drift surfaces before it reaches an ALTER EXTENSION UPDATE, and it pairs directly with the DAG produced by Dependency Tree Analysis.

Core Concept: Reconciling Three Surfaces Into One Map #

PostgreSQL never stores “the truth” about an extension in a single place. An extension exists as three separate surfaces that the server joins only at CREATE EXTENSION time, and registry mapping is the act of pre-joining them so failures surface during a scan instead of during a deploy.

The control manifest is the anchor. Its module_pathname — typically $libdir/<library> — names the shared object that must exist in pkglibdir, and its default_version names the .sql script that must exist alongside it. A registry map is correct only when, for every extension the fleet intends to install, all three on-disk surfaces resolve and the catalog agrees. The single most common production incident is the fourth-column drift: an operator upgrades the OS package but never reloads or restarts, so the filesystem shows the new default_version while pg_available_extensions still reports the old one. The mapper catches this by reading both sides and refusing to promote when they disagree.

Critically, requires edges are read from the catalog array pg_available_extension_versions.requires, keyed by version — not from the free-text comment in the control file. Building the map off the control file’s requires line is acceptable for an offline scan, but the authoritative edge set is the per-version catalog array, which is why the mapper output is designed to feed straight into Dependency Tree Analysis rather than duplicating its topological sort.

Step-by-Step Implementation #

The procedure below turns the three-surface model into a runnable, dry-run-capable mapper suitable for a pipeline gate. Each block is complete and copy-pasteable.

Step 1 — Locate the on-disk package layout #

Never hard-code paths; ask the server binary where its artifacts live. On a node with multiple PostgreSQL majors installed, run the pg_config that matches the target cluster you are mapping:

# Resolve the two directories every extension artifact lives under.
SHAREDIR="$(pg_config --sharedir)/extension"
PKGLIBDIR="$(pg_config --pkglibdir)"

echo "control files : $SHAREDIR"
echo "shared objects: $PKGLIBDIR"
ls "$SHAREDIR"/*.control | head

If pg_config is not on PATH, invoke it by absolute path (for example /usr/lib/postgresql/16/bin/pg_config) so you map the intended major version rather than whichever binary the shell finds first.

Step 2 — Confirm what the catalog resolves #

The catalog is the runtime half of the map. Capture it on the same node so the scan can be diffed against it:

-- Runtime view: what THIS server can actually resolve right now.
SELECT e.name,
       e.default_version,
       e.installed_version,
       v.requires AS requires_array
FROM pg_available_extensions e
LEFT JOIN pg_available_extension_versions v
  ON v.name = e.name
 AND v.version = e.default_version
ORDER BY e.name;

A row where default_version differs from what Step 1’s control file reports is drift the mapper must flag before any deploy proceeds.

Step 3 — Scan artifacts and build the canonical manifest #

This scanner parses every control file, resolves the library each one points at, verifies the .so and the version .sql scripts exist, and emits a machine-readable registry manifest. It performs no writes and needs no database connection.

#!/usr/bin/env python3
"""
extension_registry_mapper.py
Reconcile PostgreSQL extension artifacts (control file, version scripts,
shared library) into a single canonical registry manifest.

Read-only. Suitable for CI/CD pre-flight gates and cross-node drift diffs.
Requires: Python 3.8+ standard library only.
"""

import argparse
import json
import subprocess
import sys
from pathlib import Path
from typing import Dict, List, Optional


def pg_config(setting: str, pg_config_bin: str) -> Path:
    """Ask the server binary for an on-disk directory (e.g. --sharedir)."""
    out = subprocess.run(
        [pg_config_bin, setting],
        check=True, capture_output=True, text=True
    ).stdout.strip()
    return Path(out)


def parse_control_file(path: Path) -> Dict[str, str]:
    """Parse a .control file: key = 'value' pairs, '#' starts a comment."""
    meta: Dict[str, str] = {}
    with open(path, "r", encoding="utf-8") as fh:
        for line in fh:
            line = line.split("#", 1)[0].strip()
            if "=" in line:
                key, value = line.split("=", 1)
                meta[key.strip()] = value.strip().strip("'\"")
    return meta


def resolve_library(meta: Dict[str, str], ext_name: str, pkglibdir: Path) -> Optional[Path]:
    """
    Turn a module_pathname (default '$libdir/<ext_name>') into a concrete
    shared-object path under pkglibdir. Returns the expected path even when
    the file is absent, so callers can report 'declared but missing'.
    """
    module = meta.get("module_pathname", f"$libdir/{ext_name}")
    lib_stem = module.replace("$libdir/", "").strip()
    # PostgreSQL appends the platform suffix; .so covers Linux/BSD builds.
    return (pkglibdir / f"{lib_stem}.so")


def scan_registry(sharedir: Path, pkglibdir: Path) -> Dict[str, dict]:
    """Build the canonical map: one entry per extension found on disk."""
    ext_dir = sharedir / "extension"
    registry: Dict[str, dict] = {}

    for ctrl in sorted(ext_dir.glob("*.control")):
        ext_name = ctrl.stem
        meta = parse_control_file(ctrl)
        default_version = meta.get("default_version", "")
        requires = [r.strip() for r in meta.get("requires", "").split(",") if r.strip()]

        lib_path = resolve_library(meta, ext_name, pkglibdir)
        install_sql = ext_dir / f"{ext_name}--{default_version}.sql"

        # Enumerate every update-path script this extension ships.
        update_scripts = sorted(
            p.name for p in ext_dir.glob(f"{ext_name}--*--*.sql")
        )

        registry[ext_name] = {
            "default_version": default_version,
            "requires": requires,
            "relocatable": meta.get("relocatable", "false") == "true",
            "superuser": meta.get("superuser", "true") == "true",
            "library": lib_path.name,
            "library_present": lib_path.is_file(),
            "install_script_present": install_sql.is_file(),
            "update_scripts": update_scripts,
            "control_path": str(ctrl),
        }
    return registry


def validate_registry(registry: Dict[str, dict]) -> List[str]:
    """Return a list of mapping violations; empty means the map is coherent."""
    violations: List[str] = []
    for name, entry in registry.items():
        if not entry["library_present"]:
            violations.append(
                f"{name}: declared library '{entry['library']}' missing from pkglibdir"
            )
        if not entry["install_script_present"]:
            violations.append(
                f"{name}: install script for version "
                f"'{entry['default_version']}' not found"
            )
        for dep in entry["requires"]:
            if dep not in registry:
                violations.append(
                    f"{name}: requires '{dep}', which is not present on this node"
                )
    return violations


def main() -> int:
    parser = argparse.ArgumentParser(description="PostgreSQL extension registry mapper")
    parser.add_argument("--pg-config", default="pg_config",
                        help="Path to the pg_config binary for the target major version")
    parser.add_argument("--baseline", type=Path,
                        help="Baseline manifest JSON to diff this scan against")
    parser.add_argument("--dry-run", action="store_true",
                        help="Scan and validate without writing a manifest file")
    parser.add_argument("--out", type=Path, default=Path("registry_manifest.json"),
                        help="Where to write the canonical manifest")
    args = parser.parse_args()

    try:
        sharedir = pg_config("--sharedir", args.pg_config)
        pkglibdir = pg_config("--pkglibdir", args.pg_config)
    except (subprocess.CalledProcessError, FileNotFoundError) as exc:
        print(f"ERROR: cannot run pg_config: {exc}", file=sys.stderr)
        return 2

    registry = scan_registry(sharedir, pkglibdir)
    violations = validate_registry(registry)

    result = {
        "sharedir": str(sharedir),
        "pkglibdir": str(pkglibdir),
        "extension_count": len(registry),
        "violations": violations,
        "registry": registry,
    }

    if args.dry_run:
        print(json.dumps({"status": "dry_run", **result}, indent=2))
        return 1 if violations else 0

    args.out.write_text(json.dumps(result, indent=2), encoding="utf-8")
    print(f"Manifest written to {args.out} ({len(registry)} extensions)")

    if violations:
        for v in violations:
            print(f"VIOLATION: {v}", file=sys.stderr)
        return 1
    return 0


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

Step 4 — Diff a scan against the trusted baseline #

Mapping is only useful when it is comparative: a manifest on its own says a node is internally consistent, but a diff against production is what proves two nodes will behave identically. This helper reports every extension whose mapped version or library changed:

#!/usr/bin/env python3
"""Diff two registry manifests to detect cross-node or cross-version drift."""

import json
import sys
from pathlib import Path


def load(path: str) -> dict:
    return json.loads(Path(path).read_text(encoding="utf-8"))["registry"]


def diff(baseline: dict, candidate: dict) -> list:
    drift = []
    for name in sorted(set(baseline) | set(candidate)):
        b = baseline.get(name)
        c = candidate.get(name)
        if b is None:
            drift.append(f"ADDED   {name} (not in baseline)")
        elif c is None:
            drift.append(f"REMOVED {name} (missing on candidate)")
        elif b["default_version"] != c["default_version"]:
            drift.append(
                f"VERSION {name}: {b['default_version']} -> {c['default_version']}"
            )
        elif b["library"] != c["library"]:
            drift.append(f"LIBRARY {name}: {b['library']} -> {c['library']}")
    return drift


if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("usage: diff_registry.py baseline.json candidate.json", file=sys.stderr)
        sys.exit(2)
    drift = diff(load(sys.argv[1]), load(sys.argv[2]))
    for line in drift:
        print(line)
    sys.exit(1 if drift else 0)

Step 5 — Wire both stages into the pipeline #

Run the scan on every node, then diff each against the golden baseline. The gate blocks promotion the instant a node’s map diverges:

# 1. Map the candidate node (read-only; no DB connection needed).
python3 extension_registry_mapper.py \
  --pg-config /usr/lib/postgresql/16/bin/pg_config \
  --out candidate_manifest.json

# 2. Fail the deploy on any version/library drift from the trusted baseline.
python3 diff_registry.py production_manifest.json candidate_manifest.json

Dry-Run & Validation Gate #

The --dry-run flag is the interlock: it performs the full scan and validation, prints the manifest to stdout, and writes nothing to disk or database. A coherent node emits an empty violations array, which is the only state allowed to promote:

{
  "status": "dry_run",
  "sharedir": "/usr/share/postgresql/16",
  "pkglibdir": "/usr/lib/postgresql/16/lib",
  "extension_count": 42,
  "violations": [],
  "registry": {
    "postgis": {
      "default_version": "3.4.2",
      "requires": [],
      "relocatable": false,
      "superuser": true,
      "library": "postgis-3.so",
      "library_present": true,
      "install_script_present": true,
      "update_scripts": ["postgis--3.4.1--3.4.2.sql"],
      "control_path": "/usr/share/postgresql/16/extension/postgis.control"
    },
    "postgis_topology": {
      "default_version": "3.4.2",
      "requires": ["postgis"],
      "relocatable": false,
      "superuser": true,
      "library": "postgis_topology-3.so",
      "library_present": true,
      "install_script_present": true,
      "update_scripts": ["postgis_topology--3.4.1--3.4.2.sql"],
      "control_path": "/usr/share/postgresql/16/extension/postgis_topology.control"
    }
  }
}

Gate the downstream apply on three conditions:

• Exit code is 0. 1 means the scan found violations (a missing library, a missing install script, or a requires target absent on this node) or the diff detected drift. 2 means pg_config could not be run at all. Only 0 proceeds.
• violations is empty. A non-empty array is never a warning here — every entry is a concrete artifact the server will fail to resolve.
• The diff is clean. Any VERSION or LIBRARY line from diff_registry.py means the candidate does not match production. Reconcile it against the authoritative source before promoting, ideally the same source of truth used for Compatibility Matrix Synchronization.

Failure Modes & Error Taxonomy #

Registry-mapping failures have distinctive filesystem states, log signatures, and SQLSTATEs. Each maps to a specific recovery action.

When these cluster during a batch rollout, feed them into a structured classifier rather than reading logs by hand — the taxonomy in Error Categorization Frameworks turns each SQLSTATE into an automated triage signal.

Rollback & Recovery Path #

The mapping stage itself is read-only, so a failed scan mutates nothing — you simply fix the node and re-scan. The recovery that matters is for the case where a map passed on staging but the corresponding apply failed on production because the node’s artifacts had drifted after the scan. The deterministic sequence is:

1. Halt dependent workloads so no session writes against a half-mapped extension whose library failed to load.
2. Re-scan the failing node immediately with --dry-run to capture the exact violation — a missing .so, an absent install script, or a default_version the catalog has not picked up.
3. Restore the prior package set at the OS layer so the filesystem again matches the last-good manifest, then reload or restart the node so pg_available_extensions re-reads sharedir.
4. Downgrade or restore data state only if an ALTER EXTENSION UPDATE had already partially applied. For catalog-only updates this is a shipped downgrade script; where none exists, restore from a verified pre-upgrade snapshot through Snapshot & Point-in-Time Recovery, and route the automated recovery through Fallback Routing Strategies.

Because a corrected map is worthless if it is not reproducible, commit every promoted registry_manifest.json as a build artifact. Pairing that artifact with Version Control & Branching makes the last-good map an auditable, revertible object rather than tribal knowledge.

Performance & Scale Considerations #

The scan is cheap — it is a directory walk plus a handful of stat calls per extension, linear in the number of control files, and a real node rarely carries more than a few dozen. The cost and risk live in the fleet dimension, not the single-node one.

• Scan concurrency: Because the mapper is read-only and side-effect-free, run it on every node in parallel. There is no lock contention and no transaction, so a thousand-node fleet maps in the time the slowest node takes to walk its extension directory.
• Diff volume: The bottleneck at scale is triaging diffs, not producing them. Diff each candidate against one golden baseline rather than pairwise across the fleet, so review effort grows linearly with nodes instead of quadratically.
• Catalog reads: If you extend the mapper to reconcile against a live catalog, that query is a single indexed scan of pg_available_extensions and adds negligible load; still route it through a read replica to keep the primary untouched.
• Staging fidelity: The largest scale risk is a baseline that no longer mirrors production topology. Regenerate the golden manifest from a node validated through Test Environment Routing, and budget any resulting apply against Threshold Tuning for Downtime Windows.

FAQ #

Related Pages #

• Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals
• How to Map PostgreSQL Extension Dependencies Across Major Versions
• Dependency Tree Analysis
• Security Boundaries & Permissions
• Fallback Routing Strategies
• Version Control & Branching