Upgrading Aurora PostgreSQL can sometimes feel like navigating a minefield, especially when extensions like PostGIS are involved. Here’s a real-world example of how we resolved an upgrade from PostgreSQL 13.20 → 15.12 → 17.4 that was blocked by PostGIS extension issues.

---

The Problem

We attempted to upgrade an Aurora PostgreSQL cluster from 13.20 to 15.12, but the upgrade failed during prechecks. Aurora’s logs gave this error:

The cluster could not be upgraded because the PostGIS extension and its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology) installation in one or more databases is not compatible with your desired upgrade path.

Running a PostGIS version check returned:

SELECT postgis_full_version();

[58P01] ERROR: could not access file "$libdir/postgis-2.5": No such file or directory

This indicated that the database had old PostGIS 2.5 library references, which were breaking the Aurora cluster upgrade.

Step 1: Initial Fix – Reinstall PostGIS

Our first attempt was to drop and recreate PostGIS and its dependent extensions:

DROP EXTENSION IF EXISTS postgis_tiger_geocoder CASCADE;
DROP EXTENSION IF EXISTS address_standardizer_data_us CASCADE;
DROP EXTENSION IF EXISTS address_standardizer CASCADE;
DROP EXTENSION IF EXISTS postgis_topology CASCADE;
DROP EXTENSION IF EXISTS postgis CASCADE;

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;
CREATE EXTENSION postgis_tiger_geocoder;

SELECT postgis_extensions_upgrade();

After this, running:

SELECT postgis_full_version();

showed PostGIS 3.5.1, but still contained a warning:

POSTGIS="3.5.1 0" ... TOPOLOGY (raster procs from "2.5.2 r0" need upgrade)

The problem: artifacts from the old raster extension remained. PostGIS 3.x no longer includes raster as part of the main extension, but remnants of old raster functions were still in the database.

Further reading: https://postgis.net/documentation/tips/tip-upgrading-raster-from-2-3/?utm_source=chatgpt.com

Step 2a: Remove leftover Raster Artifacts

We identified and removed orphaned raster functions:

-- Identify
SELECT n.nspname, p.proname
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname ILIKE '%raster%'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_depend d
    WHERE d.objid = p.oid
      AND d.deptype = 'e'
  );

-- Remove
DO $$
DECLARE r RECORD;
BEGIN
FOR r IN
SELECT n.nspname, p.proname,
       pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname ILIKE '%raster%'
  AND NOT EXISTS (
      SELECT 1
      FROM pg_depend d
      WHERE d.objid = p.oid
        AND d.deptype = 'e'
  )
LOOP
EXECUTE format(
'DROP FUNCTION %I.%I(%s) CASCADE;',
      r.nspname, r.proname, r.args
    );
END LOOP;
END$$;

After this cleanup, SELECT postgis_full_version(); returned a clean output with no raster warnings.

Step 2b: Remove Orphaned PostGIS 2.5 Functions

Some functions were still linked to the PostGIS 2.5 library (rtpostgis-2.5). These leftover references can block the upgrade.

To identify them, we ran:

SELECT proname, probin
FROM pg_proc
WHERE probin LIKE '%rtpostgis-2.5%';

This returned a list of functions still pointing to the old PostGIS 2.5 binary. To drop them safely, we generated DROP FUNCTION statements:

SELECT 'DROP FUNCTION ' || oid::regprocedure || ';'
FROM pg_proc
WHERE probin LIKE '%rtpostgis-2.5%';

Then we executed these statements, which cleared all PostGIS 2.5 function remnants. After this step, the database no longer had any invalid PostGIS library references, and the upgrade to PostgreSQL 15 could proceed without errors.

Why Old PostGIS 2.5 Functions Were Left Behind

These leftovers exist in PostgreSQL’s system catalog pg_proc, which stores metadata about every function or procedure:

• proname → function name

• prosrc → function source code

• probin → associated binary/library file (used by compiled C functions like PostGIS)

• pronamespace → schema the function belongs to

Even when you drop and reinstall extensions, functions can persist if:

1. They are C-language functions linked to old shared libraries.

2. They are orphaned raster functions no longer tracked by the extension.

3. They were originally installed outside the extension framework.

Step 3: Check All Databases

Aurora performs cluster-wide checks, so we needed to verify all non-template databases:

SELECT datname FROM pg_database WHERE datistemplate=false;

For each database we:

1. Verified PostGIS and dependent extensions.

2. Applied the same drop/recreate and raster cleanup (Steps 2a and 2b).

Step 4: Upgrade the Cluster

Once all databases were validated:

aws rds modify-db-cluster \
  --db-cluster-identifier <cluster_identifier> \
  --engine-version 15.12 \
  --allow-major-version-upgrade \
  --db-cluster-parameter-group-name default.aurora-postgresql15 \
  --db-instance-parameter-group-name default.aurora-postgresql15 \
  --apply-immediately \
  --profile <profile>

aws rds modify-db-cluster \
  --db-cluster-identifier ra-dev-v-test-cluster \
  --engine-version 17.4 \
  --allow-major-version-upgrade \
  --db-cluster-parameter-group-name default.aurora-postgresql17 \
  --db-instance-parameter-group-name default.aurora-postgresql17 \
  --apply-immediately \
  --profile rsquare-dev

The upgrade then completed successfully.

After fixing the issue of upgrading from 13.20 to 15.12, upgrading from 15.12 to 17.4 went smoothly.

Key Takeaways

1. Broken extensions block Aurora upgrades: Even one database with an invalid PostGIS library prevents a major version upgrade.

2. Old artifacts may linger: PostGIS 3.x separates raster functionality, so leftover functions from older versions must be removed.

3. Cluster-wide validation matters: Aurora checks all non-template databases, not just the main user database.