Understanding pg_available_extensions vs Installed Extensions: Catalog State vs Runtime Reality #

This page shows exactly how to tell what a PostgreSQL server could install from disk apart from what a specific database has installed, and how to reconcile the two safely in automation.

Context & When This Applies #

Reach for this technique whenever a provisioning script, CI/CD job, or drift check needs a truthful answer to “is extension X installed, and at which version?” It applies to every supported PostgreSQL release (pg_available_extensions has existed since 9.1, and pg_available_extension_versions since 9.1 as well), across managed platforms (RDS, Cloud SQL, Azure) and self-hosted clusters alike. The distinction becomes load-bearing in three concrete situations: an idempotent CREATE EXTENSION step that must not assume filesystem presence means runtime readiness; a fleet-wide audit that compares primary, standby, and read-replica nodes; and an upgrade gate that must catch version drift before it aborts a maintenance window. If you are resolving transitive requires chains rather than a single extension, pair this with Tactical Dependency Tree Analysis for PostgreSQL Extension Upgrades, the parent topic this page sits under.

Concept: The Operational Boundary Between Catalog and Runtime #

pg_available_extensions reflects what can be installed (filesystem candidates), while pg_extension reflects what is installed (runtime state). Treating the first as proof of the second is the single most common source of false-positive provisioning assertions.

pg_available_extensions is a read-only system view that parses the directory returned by pg_config --sharedir/extension/ on demand. It reads each .control file 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. Two properties make the boundary asymmetric and easy to misread:

• Availability is cluster-wide; installation is database-scoped. A single .control file makes an extension available to every database across the whole cluster, but each database maintains its own pg_extension row (or none). The absence of a row in one database says nothing about the others.
• Version strings are opaque text. PostgreSQL never parses them as semver. 1.10.0 sorts before 1.9.0 under a plain !=/< comparison, so naive drift checks can silently misclassify a newer build as older.

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 entirely.

Runnable Implementation: One-Pass State Reconciliation #

The following query joins the availability view against the runtime catalog and classifies every extension into a single state column. It surfaces version drift, missing installations, and aligned rows in one pass, with no external tooling. Run it against the specific database you are provisioning — remember that pg_extension is database-scoped.

SELECT
    a.name              AS available_ext,     -- filesystem candidate (from .control)
    a.default_version   AS available_ver,     -- version the control file would install
    e.extname           AS installed_ext,     -- NULL when not installed in THIS database
    e.extversion        AS installed_ver,     -- exact version recorded at CREATE/ALTER time
    n.nspname           AS installed_schema,  -- schema the runtime catalog locked it into
    CASE
        WHEN e.extname IS NULL                       THEN 'NOT_INSTALLED'
        WHEN a.default_version IS DISTINCT FROM
             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      -- LEFT JOIN keeps candidates with no runtime row
LEFT JOIN pg_namespace n   ON e.extnamespace = n.oid  -- resolve the locked schema OID to a name
ORDER BY state, a.name;

Two deliberate choices matter here. The LEFT JOIN (not an inner join) is what lets NOT_INSTALLED rows appear at all — an inner join would hide exactly the candidates you most need to see. IS DISTINCT FROM (not !=) makes the comparison NULL-safe, so a candidate with a NULL default_version does not collapse the CASE into an unexpected branch.

Expected Output & Verification #

On a database where pg_stat_statements is installed and current, pgcrypto is installed but behind its filesystem candidate, and postgis is available but never created, the query returns:

Read the result as a state machine, not a report:

• ALIGNED — no action; the runtime version matches the candidate the control file would install.
• VERSION_DRIFT — the filesystem package moved ahead of the installed catalog. A CREATE EXTENSION here is a no-op, but an unqualified ALTER EXTENSION ... UPDATE will jump to available_ver and fail if the intermediate upgrade script is missing (see gotchas below).
• NOT_INSTALLED — the candidate exists on disk but no pg_extension row exists in this database. Safe to CREATE EXTENSION, provided its requires chain is satisfied.

To confirm a row’s runtime side independently, the catalog must agree with what the server actually loaded:

-- Ground-truth check for a single extension in the current database.
SELECT extname, extversion, extnamespace::regnamespace AS schema
FROM pg_extension
WHERE extname = 'pgcrypto';

If this returns zero rows while your reconciliation query showed anything other than NOT_INSTALLED, you are querying a different database than you think — the most common cause of “it says installed but the function is missing.”

Edge Cases & Gotchas #

1. A shared library exists but the extension is absent from the view #

pg_available_extensions omits an extension even when its .so is present in the pkglibdir, because the view keys on the .control file, not the binary. Package managers occasionally deploy the library and skip the control file, or a source build uses the wrong PG_CONFIG and installs the control file into a different prefix than the running server. The symptom is a CREATE EXTENSION that fails with:

ERROR:  could not open extension control file "/usr/share/postgresql/16/extension/foo.control": No such file or directory

Resolution:

1. Confirm the running server’s share path: SHOW data_directory; then compare pg_config --sharedir against the package manager’s install prefix — they must match.
2. List what the server can actually see: ls -la "$(pg_config --sharedir)/extension/"*.control.
3. If the file exists but is ignored, fix permissions (chmod 644) and verify it contains valid comment, default_version, and module_pathname keys.

2. VERSION_DRIFT with a broken upgrade path #

If installed_ver is 1.2.1 and available_ver is 1.3.0, an update will fail when the intermediate migration script extension--1.2.1--1.3.0.sql is not shipped in the target release’s share directory:

ERROR:  extension "foo" has no update path from version "1.2.1" to version "1.3.0"

Resolution:

1. Inspect the exact transition chain the server can offer:

   SELECT version, superuser, relocatable, schema, requires
   FROM pg_available_extension_versions
   WHERE name = 'foo'
   ORDER BY version;

2. Apply explicit version targeting rather than defaulting: ALTER EXTENSION foo UPDATE TO '1.3.0';.
3. If no chain exists, install the package build that ships the missing script, or chain incremental updates through the intervening versions. Wrapping the operation in an explicit transaction — as covered in ALTER EXTENSION Automation — keeps a failed jump from leaving the catalog half-migrated.

3. Lexicographic version comparison misfires #

Because extversion is text, a build at 1.10.0 compared against a candidate at 1.9.0 classifies as drift in the wrong direction under a raw string comparison. The reconciliation query above avoids a false equality verdict, but any logic that decides upgrade direction from the raw strings is unsafe. Parse versions in the application layer, or install the semver type and cast both sides before comparing. Never let a >/< on raw extversion gate an automated upgrade.

4. Schema relocation blocked by non-relocatable objects #

An extension marked relocatable = true in its control file can be moved after installation, but pg_extension.extnamespace locks the schema at runtime and individual objects may hardcode a schema. ALTER EXTENSION ... SET SCHEMA then fails with:

ERROR:  cannot SET SCHEMA on extension "foo" because it contains non-relocatable objects

Resolution: check SELECT relocatable FROM pg_available_extensions WHERE name = 'foo';. If it is false, a move requires DROP EXTENSION followed by CREATE EXTENSION foo SCHEMA new_schema;, dropping dependent objects first or remapping schemas through pg_dump/pg_restore. Because dropping an extension cascades to dependents, validate the impact against Tactical Dependency Tree Analysis for PostgreSQL Extension Upgrades before you run it, and enforce the privilege checks in Security Boundaries & Permissions if the operation needs SUPERUSER.

Related Pages #

• Up one level: Tactical Dependency Tree Analysis for PostgreSQL Extension Upgrades — the resolution algorithms this reconciliation feeds into.
• How to Map PostgreSQL Extension Dependencies Across Major Versions — extend catalog interrogation across a pg_upgrade boundary.
• Tracking Extension Lifecycle States in Production — persist these ALIGNED/DRIFT verdicts as auditable state over time.
• Security Implications of Superuser Extension Installation — the privilege model behind CREATE/ALTER EXTENSION.