PostGIS Extension Upgrade Quirks and Catalog Pitfalls

A field reference for upgrading PostGIS across minor and major PostgreSQL versions without stranding dependent views, orphaning the sub-extensions, or leaving the catalog reporting a library version that no longer matches the loaded .so.

Up: Compatibility Matrix Synchronization — this page is the PostGIS-specific cell of that matrix, and it feeds the wider Extension Upgrade Planning & Compatibility Validation pipeline that gates every promotion.

Context & When This Applies

PostGIS is the single most catalog-invasive extension most fleets run: a CREATE EXTENSION postgis registers hundreds of functions, several composite and base types (geometry, geography, raster), operator classes for GiST and SP-GiST, and a data table (spatial_ref_sys). It also ships as a family — postgis_raster, postgis_topology, postgis_sfcgal, and postgis_tiger_geocoder are separate extensions that each carry their own version. That structure means a PostGIS upgrade is never a single ALTER EXTENSION UPDATE; it is a coordinated bump of every installed member plus a catalog-repair step, and this reference applies whenever you are moving PostGIS 3.x to a newer 3.x, or carrying it across a PostgreSQL major with pg_upgrade vs a logical replication upgrade path.

The technique targets PostgreSQL 12–17 with PostGIS 3.0 or newer, where the dedicated postgis_extensions_upgrade() helper exists. On older 2.x installs the same catalog pitfalls apply but the helper is absent, so the manual ALTER EXTENSION sequence below is mandatory. Validate the whole sequence in an async upgrade simulation before it touches production, because the failure modes here surface at call time against real spatial data, not at install time.

PostGIS upgrade order across the extension family A PostGIS upgrade proceeds in three ordered phases. Phase one swaps the shared library on disk from the old to the new build. Phase two runs postgis_extensions_upgrade, which updates postgis and cascades to postgis_raster, postgis_topology, and postgis_sfcgal, each a separate extension with its own version. Phase three revalidates dependent objects: views and materialized views built on geometry columns, plus spatial indexes that may need a REINDEX. Only after all three does the catalog version match the loaded library. 1 · Swap library new postgis-3.so on disk, no restart 2 · postgis_extensions_upgrade() postgis postgis_raster postgis_topology postgis_sfcgal 3 · Revalidate dependents views on geometry columns REINDEX spatial GiST catalog version now matches the loaded library

Concept: Library Version vs Catalog Version

PostGIS carries two version numbers, and every catalog pitfall stems from them disagreeing. The catalog version is what pg_extension.extversion records — the SQL-level definitions installed by the last CREATE/ALTER EXTENSION. The library version is what the loaded postgis-3.so actually implements, reported by postgis_lib_version(). Swapping the package on disk changes the library immediately; it does not change the catalog. Until you run the upgrade SQL, postgis_full_version() prints a procs need upgrade warning and some functions resolve against new C symbols while their SQL wrappers still describe the old signature — the classic source of an ABI_MISMATCH surfaced during error categorization.

The second concept is cascade ownership. postgis_extensions_upgrade() is a PostGIS-provided function that inspects which family members are installed and issues the correct ALTER EXTENSION ... UPDATE for each, in dependency order, so postgis_topology is never updated before the postgis it requires. Calling it is strictly safer than hand-writing the ALTER sequence because it reads the same on-disk reachability the dependency tree analysis pre-flight uses, rather than assuming a member is present.

Runnable Implementation

The procedure below performs a minor-version PostGIS upgrade end to end: confirm the library moved, run the family upgrade, and repair the catalog. Run it as a single session so the loader picks up the new .so before the SQL executes.

-- 1. Confirm the on-disk library is NEWER than the installed catalog version.
--    If these already match, there is nothing to upgrade.
SELECT postgis_lib_version()  AS library_on_disk,
       extversion             AS catalog_version
FROM   pg_extension
WHERE  extname = 'postgis';

-- 2. Upgrade the whole PostGIS family in dependency order. This helper issues
--    ALTER EXTENSION ... UPDATE for postgis and every installed sub-extension.
--    Safe to run twice; the second call is a no-op once versions align.
SELECT postgis_extensions_upgrade();

-- 3. Verify the catalog now matches the library and no procs remain stale.
--    A clean result contains no "procs need upgrade" or "procs need rebuild".
SELECT postgis_full_version();

When a dependent view blocks step 2 (see the gotchas), drop and recreate the view around the upgrade rather than forcing it. Automate the whole block from Python with the transactional-safety rules developed in ALTER EXTENSION automation, and keep a pre-upgrade checkpoint via snapshot and point-in-time recovery so a half-cascaded family can be rewound cleanly.

Expected Output & Verification

A successful upgrade leaves postgis_full_version() reporting a single, consistent version with no upgrade warning:

POSTGIS="3.4.2 c19ce56" [EXTENSION] PGSQL="160" GEOS="3.12.1"
PROJ="9.3.1" LIBXML="2.9.14" LIBJSON="0.17" LIBPROTOBUF="1.4.1"
WAGYU="0.5.0 (Internal)"

The absence of a trailing (procs need upgrade ...) clause is the pass signal. Cross-check that every family member advanced together — a member left behind is the most common half-upgrade:

-- All installed PostGIS-family members should report the same target version.
SELECT extname, extversion
FROM   pg_extension
WHERE  extname LIKE 'postgis%'
ORDER  BY extname;

Feed the observed versions back into compatibility matrix synchronization so the published matrix records the exact family tuple that shipped, not just the postgis cell.

Edge Cases & Gotchas

A view built on a geometry column blocks the type update. When an update script needs to redefine geometry, any view or materialized view that references it raises:

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view vw_parcels depends on column "geom"

Capture the dependent view definitions, drop them, run postgis_extensions_upgrade(), then recreate them. Track the drop/recreate pair as one reversible unit under version control and branching so the recovery is a reviewed revert, not an improvised fix.

postgis_extensions_upgrade() sometimes must run twice. Crossing certain versions it upgrades the postgis extension first, then reports that postgis_raster still needs a second pass. This is expected — the function is idempotent, so a second call finishes the cascade. Treat a lingering procs need upgrade after one call as a signal to re-run, not as a failure.

A stale spatial index survives the upgrade silently. GiST operator classes occasionally change across versions; the index keeps answering queries with the old operator strategy and returns subtly wrong results rather than erroring. After any major PostGIS bump, REINDEX INDEX CONCURRENTLY every spatial index and budget the lock window through threshold tuning for downtime windows.

Crossing a PostgreSQL major with pg_upgrade requires the matching PostGIS build on both clusters. pg_upgrade copies the catalog verbatim, so the new cluster must have a PostGIS package whose library version is greater than or equal to the old one, or the first postgis-3.so load raises could not access file "$libdir/postgis-3": No such file or directory. Install the matching (or newer) PostGIS on the target before running pg_upgrade --check.