Building a Dynamic Compatibility Matrix for PostGIS and pgvector #

This page shows how to generate a checksummed compatibility matrix for the two hardest extensions to keep aligned during an upgrade — PostGIS and pgvector — by resolving their versioned shared objects, sampling the live catalog, and probing each ABI before a cell is ever trusted.

Up: Compatibility Matrix Synchronization — the reconciliation model this page specializes. The generic synchronizer treats every extension as a single <name>.so; PostGIS and pgvector break that assumption, and this page is the extension-family-specific parsing the synchronizer defers to.

Context & When This Applies #

Reach for this when a single cluster runs geospatial and vector-search workloads side by side — the increasingly common pattern where PostGIS answers spatial joins and pgvector backs similarity search over embeddings — and you need one authoritative version-to-server mapping before promoting either. The technique targets PostgreSQL 14 through 17, where pg_available_extension_versions is present and pg_upgrade is the migration path across majors.

The two extensions defeat a naive matrix for different reasons. PostGIS ships a versioned shared object (postgis-3.so, not postgis.so) and pulls in three sibling extensions — postgis_topology, postgis_raster, postgis_sfcgal — plus system libraries (GEOS, PROJ, GDAL) that live entirely outside the PostgreSQL catalog. pgvector registers under the extension name vector, exposes a bespoke vector type whose OID must survive pg_upgrade, and changes its on-disk index format across releases. A matrix that records only (server_major → extension → version) cannot express any of that, so it ships combinations that are unreachable, unlinkable, or OID-incompatible on the target server. This is the parsing detail the fleet-wide compatibility matrix synchronization pass cannot handle generically and hands off here.

Concept: Three Axes, One Resolvable Cell #

A PostGIS/pgvector cell is not a version string — it is a resolved tuple across three independent axes that must all agree before the cell is publishable:

1. Catalog axis — what the server says it can install. pg_available_extension_versions computes installable versions from the control files physically present in $sharedir/extension. It never opens the shared object, so it will happily advertise a version whose binary is broken.
2. Binary axis — what actually links. The real .so on disk must resolve every dependency. For PostGIS this means postgis-3.so finding libgeos_c.so.1, libproj.so, and libgdal.so; for pgvector it means vector.so resolving libc/libm. A control file can advertise a version the loader will reject with a FATAL at first use.
3. Type-stability axis — what survives the upgrade. pg_upgrade copies the old catalog’s type OIDs forward. The vector type OID must match between source and target, and PostGIS’s own type set (geometry, geography, raster) must be present under the same OIDs, or a pg_restore of dependent columns fails.

The resolved value of a cell is written only when all three axes agree; any disagreement is drift the promotion must not ship. This mirrors the set-intersection model formalized in the parent synchronizer, with the added wrinkle that the binary axis has to resolve a versioned filename from the control file’s module_pathname rather than assume <name>.so. Where a PostGIS build depends on a co-installed sibling, the ordering that decides which sibling upgrades first comes from dependency tree analysis, and the declared versions themselves must originate from an auditable extension registry mapping rather than whatever a package mirror serves that hour.

Baseline inventory #

Seed the matrix from the live catalog. This query captures both installed and merely-available versions for the two extension names, so the generator sees the full installable surface on a node:

-- Baseline inventory: installed + available versions for the two families.
SELECT
  e.extname,
  e.extversion               AS installed_version,
  a.default_version
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
WHERE e.extname IN ('postgis', 'vector')
UNION ALL
SELECT
  name                       AS extname,
  NULL                       AS installed_version,
  default_version
FROM pg_available_extensions
WHERE name IN ('postgis', 'vector')
  AND name NOT IN (SELECT extname FROM pg_extension);

postgis_full_version() returns the exact GEOS/PROJ/GDAL build strings that the catalog alone will not tell you, and SELECT extversion FROM pg_extension WHERE extname = 'vector' pins the vector build. Both feed the binary axis below.

Runnable Implementation #

The generator below resolves one (server_major, PostGIS, pgvector) cell end to end. It samples the catalog with a read-only role, resolves each extension’s true shared-object filename from pg_config --pkglibdir, runs the ABI probe, and emits a machine-readable verdict. It issues no DDL, so it is safe to run against production replicas; reserve write access for the artifact publish handled upstream.

#!/usr/bin/env python3
"""
Resolve a PostGIS + pgvector compatibility cell across all three axes:
catalog (installable), binary (linkable), and type-stability (OID present).
Emits a JSON verdict per (server_major, extension) with a resolved version
or a BLOCKED reason. Read-only; issues no DDL.
Requires: psycopg2-binary
"""
import json
import subprocess
import sys

import psycopg2

# PostGIS ships a *versioned* object; pgvector registers as "vector".
# Map the catalog extname -> the on-disk shared object basename.
SO_BASENAME = {
    "postgis": "postgis-3.so",
    "vector": "vector.so",
}

# Types whose OID must be present (and survive pg_upgrade) for the cell
# to be restore-safe. PostGIS registers geometry/geography; pgvector, vector.
REQUIRED_TYPES = {
    "postgis": ("geometry", "geography"),
    "vector": ("vector",),
}


def server_major(cur) -> str:
    cur.execute("SHOW server_version_num;")          # e.g. 160004
    return f"pg{int(cur.fetchone()[0]) // 10000}"    # -> pg16


def installable_versions(cur, extname: str) -> set:
    cur.execute(
        "SELECT version FROM pg_available_extension_versions WHERE name = %s;",
        (extname,),
    )
    return {row[0] for row in cur.fetchall()}


def types_present(cur, extname: str) -> bool:
    # Every declared type OID must exist; a missing OID means a restore of
    # dependent columns will fail after pg_upgrade.
    cur.execute(
        "SELECT typname FROM pg_type WHERE typname = ANY(%s);",
        (list(REQUIRED_TYPES[extname]),),
    )
    found = {row[0] for row in cur.fetchall()}
    return found == set(REQUIRED_TYPES[extname])


def abi_resolves(pkglibdir: str, extname: str) -> tuple:
    """Return (ok, detail). ok=False on a missing/unresolved shared object."""
    so_path = f"{pkglibdir}/{SO_BASENAME[extname]}"
    proc = subprocess.run(
        ["ldd", so_path], capture_output=True, text=True
    )
    if proc.returncode != 0:
        return False, f"ldd failed for {so_path}: {proc.stderr.strip()}"
    missing = [ln.strip() for ln in proc.stdout.splitlines() if "not found" in ln]
    if missing:
        return False, f"unresolved: {'; '.join(missing)}"
    return True, "linked"


def resolve_cell(dsn: str, declared: dict) -> dict:
    """declared = {'postgis': '3.4.1', 'vector': '0.7.0'} from the registry."""
    conn = psycopg2.connect(dsn, connect_timeout=5)
    try:
        pkglibdir = subprocess.check_output(
            ["pg_config", "--pkglibdir"], text=True
        ).strip()
        with conn.cursor() as cur:
            major = server_major(cur)
            cell = {}
            for ext, want in declared.items():
                installable = installable_versions(cur, ext)
                abi_ok, abi_detail = abi_resolves(pkglibdir, ext)
                if want not in installable:
                    cell[ext] = {"status": "BLOCKED",
                                 "reason": "not_installable",
                                 "declared": want}
                elif not abi_ok:
                    cell[ext] = {"status": "BLOCKED",
                                 "reason": "abi_unresolved",
                                 "detail": abi_detail}
                elif not types_present(cur, ext):
                    cell[ext] = {"status": "BLOCKED",
                                 "reason": "type_oid_missing"}
                else:
                    cell[ext] = {"status": "OK", "version": want}
            return {"server_major": major, "cell": cell}
    finally:
        conn.close()


if __name__ == "__main__":
    # In production, load `declared` from the registry rather than hard-coding.
    declared_cell = {"postgis": "3.4.1", "vector": "0.7.0"}
    dsn = sys.argv[1] if len(sys.argv) > 1 else "dbname=postgres"
    print(json.dumps(resolve_cell(dsn, declared_cell), indent=2))

The three axes are enforced in the order that fails cheapest first: the catalog query is one round trip, the ABI probe shells out to ldd, and the type check reads pg_type. Swapping psycopg2 for asyncpg to sample a large fleet concurrently is a mechanical change; the driver trade-offs are covered under ALTER EXTENSION automation.

For the binary axis, the raw pre-flight check that the script automates is worth keeping in your runbook:

# Confirm the versioned PostGIS object and the vector object both link.
pg_config --version
ldd "$(pg_config --pkglibdir)/postgis-3.so" | grep -E "libgeos|libproj|libgdal"
ldd "$(pg_config --pkglibdir)/vector.so"    | grep -E "libm|libc"

Expected Output & Verification #

A fully resolved cell — all three axes agree for both families — serializes like this:

{
  "server_major": "pg16",
  "cell": {
    "postgis": { "status": "OK", "version": "3.4.1" },
    "vector":  { "status": "OK", "version": "0.7.0" }
  }
}

A blocked cell names the axis that failed, which is exactly what a promotion gate keys on:

{
  "server_major": "pg15",
  "cell": {
    "postgis": { "status": "BLOCKED", "reason": "abi_unresolved",
                 "detail": "unresolved: libgeos_c.so.1 => not found" },
    "vector":  { "status": "OK", "version": "0.6.0" }
  }
}

Confirm the type-stability axis independently after any pg_upgrade by checking the OIDs are present and stable:

SELECT postgis_full_version();                                  -- GEOS/PROJ/GDAL build
SELECT extversion FROM pg_extension WHERE extname = 'vector';   -- vector build
SELECT typname, oid FROM pg_type
WHERE typname IN ('geometry', 'geography', 'vector')
ORDER BY typname;

If any OID differs from the source cluster’s recorded value, the cell is in a drift state and must not be promoted until the type registration is reconciled. As a working baseline for the two supported ranges:

Edge Cases & Gotchas #

Four failure signatures dominate PostGIS/pgvector matrix builds. Each has exact error text and a precise resolution.

1. Missing system library on the binary axis. The server logs:

ERROR:  could not load library "/usr/lib/postgresql/15/lib/postgis-3.so":
        libgeos_c.so.1: cannot open shared object file: No such file or directory

pg_available_extension_versions still lists the version, so only the ABI probe catches it. Resolve by installing the matching GEOS runtime (apt install libgeos-c1v5 or the distro equivalent) before the version reaches a promotion, then re-run the generator so the cell flips to OK.

2. pgvector control/version mismatch. ALTER EXTENSION vector UPDATE raises:

ERROR:  extension "vector" has no installation script nor update path
        for version "0.7.0"

The .so is newer than the control files (or vice versa) in $sharedir/extension. Inspect with SELECT * FROM pg_available_extension_versions WHERE name = 'vector' ORDER BY version DESC; and align the package so the control file and binary ship the same version.

3. vector type OID drift after pg_upgrade. A pg_restore of a table with a vector column fails:

ERROR:  type "vector" does not exist

The target cluster never registered the type, so pg_upgrade had no OID to carry forward. Recreate the extension on the target before the data restore (CREATE EXTENSION vector;), or when the OID itself diverged, pg_dump --schema-only the source, recreate the target database, and restore to force a clean type registration. Recovery of an unrecoverable node routes through snapshot & point-in-time recovery rather than hand-editing $sharedir.

4. Sibling extension version skew. PostGIS core upgrades but postgis_topology lags:

WARNING:  extension "postgis_topology" is not up to date;
          run ALTER EXTENSION "postgis_topology" UPDATE

Upgrade the family in dependency order in a single transaction so no window exists where core and siblings disagree:

ALTER EXTENSION postgis           UPDATE TO '3.4.1';
ALTER EXTENSION postgis_topology  UPDATE TO '3.4.1';
ALTER EXTENSION postgis_raster    UPDATE TO '3.4.1';
ALTER EXTENSION vector            UPDATE TO '0.7.0';

If post-upgrade validation fails, revert to the last matrix-validated state: downgrade via ALTER EXTENSION ... UPDATE TO '<previous>' where a downgrade script exists, then run REINDEX EXTENSION <name> to clear stale index caches. Keep every published matrix under version control & branching so each checksum is a reviewable commit and every rollback is a clean revert. When these signatures recur, route them into error categorization frameworks instead of reading logs by hand.

For authoritative version and OID-stability references, consult the official PostgreSQL documentation on extension development, the PostGIS manual release sections, and the pgvector repository.

FAQ #

Related Pages #

• Up: Compatibility Matrix Synchronization
• Extension Upgrade Planning & Compatibility Validation
• Simulating Async Extension Upgrades with pg_upgrade
• Categorizing Extension Upgrade Errors for Automated Triage
• Extension Registry Mapping