DEV Community

Cover image for PostGIS After pg_upgrade: Fixing Version Mismatches and Broken Functions
Philip McClarence
Philip McClarence

Posted on

PostGIS After pg_upgrade: Fixing Version Mismatches and Broken Functions

#postgres #tutorial #devops #database

PostgreSQL major version upgrades via pg_upgrade are well-documented, well-tested, and generally smooth. But if you run PostGIS, there is a post-upgrade step that the documentation buries in a single sentence -- and skipping it breaks every spatial function in your database.

What pg_upgrade Does (and Doesn't Do)

pg_upgrade migrates the data directory: system catalogs, user tables, indexes, and extension metadata. It copies the catalog entry that says "PostGIS 3.4.0 is installed in this database" to the new cluster. What it does not do is install PostGIS 3.4.0 in the new cluster's library directory.

The old .so files were compiled against PG 15's internal ABI. When PG 16 tries to load them, one of two things happens:

  1. The file doesn't exist. The new PG 16 installation has a clean lib/ directory. The catalog references $libdir/postgis-3, but that file is only in PG 15's lib path. Error: could not access file "$libdir/postgis-3": No such file or directory.

  2. The file exists but is incompatible. If PostGIS packages for PG 16 were installed before the upgrade, postgis-3.so exists but is a newer version. The catalog expects function signatures from 3.4.0, but the library provides 3.5.0. This sometimes works, sometimes produces undefined symbol errors for functions whose C signatures changed.

The Partial Failure Trap

This is where it gets dangerous. Not all PostGIS functions are C functions. Some are pure SQL wrappers or PL/pgSQL. These work fine regardless of the shared library state. PostGIS_Full_Version() reads from catalog tables -- it succeeds. ST_SetSRID() is a thin SQL wrapper -- it succeeds. But ST_Contains(), ST_Intersects(), ST_Buffer(), and ST_DWithin() call into the C library and fail.

A smoke test that runs SELECT PostGIS_Full_Version() passes. The application starts. The map loads. The first user who triggers a proximity search gets an internal server error.

Detection

Check installed vs. available versions for all PostGIS extensions:

SELECT name, default_version, installed_version,
    CASE WHEN default_version != installed_version
         THEN 'MISMATCH'
         ELSE 'OK'
    END AS status
FROM pg_available_extensions
WHERE name LIKE 'postgis%'
ORDER BY name;
Enter fullscreen mode Exit fullscreen mode

Test that the C library actually loads:

-- If this fails, the shared library is broken
SELECT ST_AsText(ST_MakePoint(0, 0));
Enter fullscreen mode Exit fullscreen mode

Check the full version report:

SELECT PostGIS_Full_Version();
Enter fullscreen mode Exit fullscreen mode

If the "POSTGIS" version and the "POSTGIS_LIB" version differ in the output, you have a mismatch.

The Fix

If PostGIS is functional but outdated (version mismatch), update in place:

ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_raster UPDATE;
SELECT PostGIS_Full_Version();
Enter fullscreen mode Exit fullscreen mode

ALTER EXTENSION ... UPDATE runs the migration scripts that ship with the PostGIS package. These scripts handle function signature changes, add new functions, and update catalog entries to match the library.

If the shared library is completely broken and ALTER EXTENSION UPDATE itself fails:

-- Last resort: drops all dependent objects (views, indexes, geography columns)
DROP EXTENSION postgis CASCADE;
CREATE EXTENSION postgis;
Enter fullscreen mode Exit fullscreen mode

Before running DROP CASCADE, inventory what will be destroyed:

SELECT classid::regclass, objid, deptype
FROM pg_depend
WHERE refclassid = 'pg_extension'::regclass
  AND refobjid = (SELECT oid FROM pg_extension WHERE extname = 'postgis');
Enter fullscreen mode Exit fullscreen mode

Critical: run the update in every database in the cluster, not just the primary application database. pg_upgrade migrates all databases, so every one has stale PostGIS catalog entries.

The Post-Upgrade Checklist

Add this to your upgrade automation, after pg_upgrade completes and before traffic is restored:

# 1. Install PostGIS for the new PG version
# Debian/Ubuntu:
apt install postgresql-16-postgis-3

# 2. Update extensions in every database
for db in $(psql -t -c "SELECT datname FROM pg_database WHERE datallowconn AND datname NOT IN ('template0')"); do
    psql -d "$db" -c "ALTER EXTENSION postgis UPDATE;"
    psql -d "$db" -c "ALTER EXTENSION postgis_topology UPDATE;" 2>/dev/null
    psql -d "$db" -c "ALTER EXTENSION postgis_raster UPDATE;" 2>/dev/null
done

# 3. Verify
psql -c "SELECT PostGIS_Full_Version();"
psql -c "SELECT ST_AsText(ST_Buffer(ST_MakePoint(0,0)::geography, 100));"
Enter fullscreen mode Exit fullscreen mode

The verification step tests a C-implemented function (ST_Buffer) that calls into GEOS. If this returns a polygon, PostGIS is fully functional -- not just catalog-present.

Why This Keeps Catching People

The PostgreSQL pg_upgrade documentation says, in a single line: "you should also run ALTER EXTENSION ... UPDATE for each extension." It does not explain what happens if you skip it, does not mention it breaks C functions while leaving SQL functions working, and does not emphasize that it must run in every database. For extensions like pgcrypto or uuid-ossp, skipping the update is usually harmless -- the function signatures rarely change between minor versions. For PostGIS, with hundreds of C functions and a complex dependency chain on GEOS, PROJ, and GDAL, skipping it is almost guaranteed to cause problems.

The fix takes 30 seconds. The debugging, when you discover the issue at 2 AM because proximity searches are failing in production, takes considerably longer.

Full detection queries, monitoring setup, and prevention strategies: mydba.dev/blog/postgis-upgrade-version-mismatch

Top comments (0)