Aurora Postgres upgrade from 11.13 to 12.8 failing - I assume due to PostGis

We are seeing the same error trying to upgrade to PG 12. The issue is that even after completely removing postgis from a PG 10 Aurora cluster (where postgis 3.1.7 is the available version) we have 98 rows in pg_proc for functions with $libdir/rtpostgis-2.4 in the probin column. So when we try to upgrade we get a similar message as the op

2023-04-05 18:14:41.359 GMT [31870] ERROR: could not access file "$libdir/rtpostgis-2.4": No such file or directory
2023-04-05 18:14:41.359 GMT [31870] STATEMENT: LOAD '$libdir/rtpostgis-2.4'

I assume that at some point in the past postgis 2.4 was installed, but it's not there any more and we just need to have those references to it in pg_proc cleaned out but of course as it is a system table we can't do that so how do we proceed with the upgrade?

Thank you very much for your response. I would like to try to get this resolved without involving AWS support as it will be tedious with all the environments that have to go through this, potentially multiple times. Here is the output to the queries you gave me, sorry about the bad formatting, I hope its readable.

SELECT SUBSTR(version(),1,18),postgis_full_version(); | PostgreSQL 11.13 o | POSTGIS=3.1.4 ded6c34 [EXTENSION] PGSQL=110 GEOS=3.7.3-CAPI-1.11.3 b50468f PROJ=6.2.1 LIBXML=2.9.9 LIBJSON=0.12.99 LIBPROTOBUF=1.3.0 WAGYU=0.5.0 (Internal)

SELECT extname,extversion,extnamespace::regnamespace,extowner::regrole FROM pg_catalog.pg_extension WHERE extname LIKE 'postgis%'; | postgis | 3.1.4 | public | rdsadmin

SELECT probin,count(*) FROM pg_catalog.pg_proc WHERE probin LIKE '%postgis%' GROUP BY 1 ORDER BY 1; | $libdir/postgis-3 | 515

SELECT name,version,installed FROM pg_catalog.pg_available_extension_versions WHERE name LIKE 'postgis%' order by 2,1; | postgis, 3.1.4, true | postgis_raster, 3.1.4, false | postgis_tiger_geocoder3.1.4, false | postgis_topology, 3.1.4, false | postgis, 3.1.4next, false | postgis_raster, 3.1.4next, false | postgis_tiger_geocoder, 3.1.4next, false | postgis_topology, 3.1.4next, false | postgis, unpackaged, false | postgis_raster, unpackaged, false | postgis_topology, unpackaged, false

SELECT count(typname) AS "number of installed raster types in pg_type" FROM pg_type WHERE typname IN ('raster', 'rastbandarg', 'geomval', 'addbandarg', 'summarystats', 'agg_count', 'reclassarg', 'agg_samealignment', 'unionarg'); | 0

Hi Thomas,

Depending on the current installed state of postgis you may require assistance from AWS Support, however the following meta-data for the database which is triggering this error. You may need to check this data on multiple databases, the following SQL will pull data related to the postgis state which may help provide the next steps to resolve this issue.

Can you share the information from the SQL below? If you open a support case this information will help the AWS Support team also.

SELECT SUBSTR(version(),1,18),postgis_full_version();

SELECT extname,extversion,extnamespace::regnamespace,extowner::regrole FROM pg_catalog.pg_extension WHERE extname LIKE 'postgis%';

SELECT probin,count(*) FROM pg_catalog.pg_proc WHERE probin LIKE '%postgis%' GROUP BY 1 ORDER BY 1;

SELECT name,version,installed FROM pg_catalog.pg_available_extension_versions WHERE name LIKE 'postgis%' order by 2,1;

SELECT count(typname) AS "number of installed raster types in pg_type" FROM pg_type WHERE typname IN ('raster', 'rastbandarg', 'geomval', 'addbandarg', 'summarystats', 'agg_count', 'reclassarg', 'agg_samealignment', 'unionarg');

If your problem is the same as mine, then its all about by default "hidden objects/databases". I use pgadmin and there is a setting to "Show System Objects". turn this on and you will be surprised how many more databases are there. For me postgis was installed in some system database too, so before I could upgrade the cluster I either had to remove postgis from the system databases or update postgis (the same way like you did in your database).