Understanding pg_available_extensions vs Installed Extensions: Catalog State vs Runtime Reality

The distinction between pg_available_extensions and pg_extension defines the operational boundary between filesystem availability and database runtime state. Misinterpreting these system catalogs triggers silent CI/CD pipeline failures, broken dependency chains, and unsafe in-place upgrades. This reference isolates exact diagnostic patterns, version reconciliation workflows, and production-safe automation strategies for PostgreSQL DBAs, platform engineers, and database SREs.

Catalog vs. Runtime at a Glance

pg_available_extensions reflects what can be installed (filesystem candidates), while pg_extension reflects what is installed (runtime state).

flowchart LR
    subgraph FS["Filesystem candidates"]
      A1["control files<br/>pg_config --sharedir/extension"] --> A[("pg_available_extensions")]
    end
    subgraph RT["Database runtime state"]
      B[("pg_extension")] --> B1["loaded library<br/>+ catalog objects"]
    end
    A -- "CREATE EXTENSION" --> B
    B -. "reconcile / drift check" .-> A

The Operational Boundary: Filesystem Candidates vs. Database Reality

pg_available_extensions is a read-only system view that parses the directory returned by pg_config --sharedir/extension/ at server startup or on-demand. It reads .control files to report candidate versions, relocatability flags, default schemas, and installation comments. The view is populated dynamically and does not require a restart when new .control files are dropped into the directory. For a deeper breakdown of how PostgreSQL resolves these control files during initialization, see PostgreSQL Extension Architecture & Lifecycle Fundamentals.

pg_extension is a persistent system catalog that tracks extensions actively loaded in the current database. It binds each extension to a specific schema, owner, exact version string, and configuration parameters. When an extension is created, PostgreSQL records its state here and loads the corresponding shared library from the directory returned by pg_config --pkglibdir. Conflating these two catalogs during deployment orchestration produces false-positive assertions, unmet runtime dependencies, and phantom upgrade paths.

Diagnostic Patterns & State Reconciliation

To audit the delta between available and installed states without relying on external tooling, execute a targeted reconciliation query. The following pattern surfaces version drift, missing installations, and alignment status in a single pass:

SELECT
    a.name AS available_ext,
    a.default_version AS available_ver,
    e.extname AS installed_ext,
    e.extversion AS installed_ver,
    CASE
        WHEN e.extname IS NULL THEN 'NOT_INSTALLED'
        WHEN a.default_version != e.extversion THEN 'VERSION_DRIFT'
        ELSE 'ALIGNED'
    END AS state
FROM pg_available_extensions a
LEFT JOIN pg_extension e ON a.name = e.extname
ORDER BY a.name;

This query surfaces version drift before it triggers CREATE EXTENSION or ALTER EXTENSION UPDATE failures. For dependency-heavy stacks, cross-referencing this output against Dependency Tree Analysis prevents cascading ERROR: extension "X" requires extension "Y" during automated provisioning.

Critical Implementation Notes:

  • PostgreSQL treats version strings as text. Lexicographical comparison (!=) works for aligned versions but fails for semantic versioning edge cases (e.g., 1.10.0 vs 1.9.0). Precise semver comparison requires application-level parsing or the pg_semver extension.
  • pg_available_extensions is cluster-wide in availability but database-scoped in installation. An extension available in the catalog can be installed in multiple databases independently, each maintaining its own pg_extension row.
  • The official documentation for pg_available_extensions confirms that the view lists every extension whose .control file can be parsed; an extension whose control file is unreadable, malformed, or lacks a default_version is omitted.

Edge Cases & Step-by-Step Resolution

Missing .control Files

pg_available_extensions omits extensions even if shared libraries exist in the pkglibdir. This occurs when package managers install binaries but fail to deploy control files, or when custom extensions are compiled with incorrect PG_CONFIG paths.

Resolution:

  1. Verify filesystem state: ls -la "$(pg_config --sharedir)/extension/"*.control
  2. Confirm the running server’s pg_config --sharedir matches the package manager’s installation prefix.
  3. If files exist but are ignored, check permissions (chmod 644) and ensure the .control file contains valid comment, default_version, and module_pathname keys.

Version Pinning Drift

If pg_extension.extversion shows 1.2.1 but pg_available_extensions.default_version shows 1.3.0, automatic upgrade commands will fail if the intermediate migration script (extension--1.2.1--1.3.0.sql) is absent.

Resolution:

  1. Query pg_available_extension_versions to inspect the exact upgrade path:
    SELECT version, superuser, relocatable, schema, requires
FROM pg_available_extension_versions
WHERE name = 'extension_name'
ORDER BY version;
  2. Apply explicit version targeting: ALTER EXTENSION name UPDATE TO '1.3.0';
  3. If the path is broken, manually execute missing SQL scripts within a transaction, then run ALTER EXTENSION name UPDATE TO '1.3.0'; to sync the catalog. Refer to ALTER EXTENSION for transactional safety guarantees.

Schema Relocation Traps

Extensions marked relocatable = true in their .control file can be moved post-installation. However, pg_extension.extnamespace locks the schema at runtime. Attempting ALTER EXTENSION SET SCHEMA fails if the extension contains non-relocatable objects (e.g., hardcoded schema references in SQL functions).

Resolution:

  1. Check relocatability: SELECT default_version, relocatable FROM pg_available_extensions WHERE name = 'ext_name';
  2. If relocatable = false, schema relocation requires a full DROP EXTENSION followed by CREATE EXTENSION ... SCHEMA new_schema;. Ensure dependent objects are dropped first or use pg_dump/pg_restore with schema remapping.

Production-Safe Automation Strategies

Automating extension lifecycle management requires pre-flight validation, idempotent execution, and strict error boundaries.

Pre-Flight Validation Block

Wrap extension provisioning in a DO $$ ... $$ block that validates state before execution:

DO $$
DECLARE
    v_available_ver TEXT;
    v_installed_ver TEXT;
BEGIN
    SELECT default_version INTO v_available_ver
    FROM pg_available_extensions WHERE name = 'my_extension';

    SELECT extversion INTO v_installed_ver
    FROM pg_extension WHERE extname = 'my_extension';

    IF v_available_ver IS NULL THEN
        RAISE EXCEPTION 'Extension my_extension not found in filesystem catalog';
    ELSIF v_installed_ver IS NULL THEN
        EXECUTE format('CREATE EXTENSION my_extension VERSION %L', v_available_ver);
    ELSIF v_installed_ver != v_available_ver THEN
        EXECUTE format('ALTER EXTENSION my_extension UPDATE TO %L', v_available_ver);
    END IF;
END $$;

Idempotent CI/CD Integration

  • State-First Approach: Always query pg_extension before attempting CREATE or UPDATE. Never assume filesystem presence implies runtime readiness.
  • Transaction Wrapping: Execute all extension operations inside explicit transactions. PostgreSQL rolls back catalog changes on failure, leaving the database in a consistent state.
  • Version Pinning in IaC: Terraform, Ansible, or Kubernetes operators should pin exact versions (VERSION '1.3.0') rather than relying on default_version. This prevents uncontrolled upgrades during package manager updates.
  • Audit Logging: Capture pg_available_extensions snapshots pre-deployment and pg_extension states post-deployment. Diff outputs serve as immutable proof of compliance and rollback baselines.

Conclusion

The boundary between pg_available_extensions and pg_extension is not merely academic; it is the fault line between predictable deployments and production incidents. By treating filesystem availability as a candidate state and runtime catalogs as the source of truth, teams can eliminate false-positive assertions, resolve version drift deterministically, and automate extension lifecycles with surgical precision. Rigorous pre-flight validation, explicit version targeting, and strict adherence to PostgreSQL’s transactional guarantees form the foundation of resilient database platform engineering.