Aurora Postgres upgrade from 11.13 to 12.8 failing - I assume due to PostGis
Trying to upgrade our Aurora Clusters finally. Got them recently updated to 11.13, but every attempt I make to upgrade to 12.8 fails with "Database cluster is in a state that cannot be upgraded: Postgres cluster is in a state where pg_upgrade can not be completed successfully."
Here are the logs which I think point to the culprit: 2022-02-11 22:37:53.514 GMT 5276 ERROR: could not access file "$libdir/postgis-2.4": No such file or directory 2022-02-11 22:37:53.514 GMT 5276 STATEMENT: LOAD '$libdir/postgis-2.4' 2022-02-11 22:37:53.515 GMT 5276 ERROR: could not access file "$libdir/rtpostgis-2.4": No such file or directory 2022-02-11 22:37:53.515 GMT 5276 STATEMENT: LOAD '$libdir/rtpostgis-2.4' command: "/rdsdbbin/aurora-18.104.22.168.0.5790.0/bin/pg_ctl" -w -D "/rdsdbdata/db" -o "--config_file=/rdsdbdata/config_new/postgresql.conf --survivable_cache_mode=off" -m fast stop >> "pg_upgrade_server.log" 2>&1 waiting for server to shut down....2022-02-11 22:37:53.541 GMT 5185 LOG: received fast shutdown request 2022-02-11 22:37:53.541 GMT 5185 LOG: aborting any active transactions 2022-02-11 22:37:53.542 GMT 5237 LOG: shutting down ................sh: /rdsdbbin/aurora-22.214.171.124.0.5790.0/bin/curl: /apollo/sbin/envroot: bad interpreter: No such file or directory 2022-02-11 22:38:10.305 GMT 5185 FATAL: Can't handle storage runtime process crash 2022-02-11 22:38:10.305 GMT 5185 LOG: database system is shut down
I found several other articles that point to issues with Postgis, so I followed what they suggest, but no luck. First our cluster is running Postgis 2.4.4. So I went ahead and updated this to 3.1.4, tried the approach to restart the instance and validate its really using Postgis 3 and that all looks fine. Nothing helps though.
If anyone has suggestions, I am happy to try.
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
Looking at the output of these queries is it possible that you have one or more other databases where postgis is installed on this RDS DB instance?
I believe this is the case as you can see in the upgrade error you reported references to "$libdir/postgis-2.4" but in the query results for the database you ran we can only see "$libdir/postgis-3" and the other results show postgis 3.1.4 is installed correctly in that database at least.
It's fairly common to miss another database which might not be the "main" database but still exists on the RDS instance with postgis installed but not in a clean state, sometimes I need to check many databases to pinpoint which one is causing the trouble.
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');
Missing Aurora Function after upgrade from aurora pg 12 to pg 13asked 3 months ago
Upgrade for Amazon Aurora Serverless v1 PostgreSQL-compatible edition 10.x end of support is January 31, 2023asked a month ago
Aurora Postgres cluster failed to upgrade from 13.4 to 13.4.2 at maintenance windowasked a month ago
How to failback to Original Writer instance after Upgradeasked 2 months ago
Aurora Postgres upgrade from 11.13 to 12.8 failing - I assume due to PostGisasked 3 months ago
How to debug In-place upgrade of the engine to the new target version 8.0.mysql_aurora.3.01.0 is not supported.asked 3 months ago
Upgrade RDS Postgres engine from 9.6.11 to 10.6 - Migrate to AuroraAccepted Answerasked 3 years ago
Upgrade to 13.2 and PostGISAccepted Answerasked a year ago
RDS upgrade from PG 12.7 to 13.3 fails due to missing librariesasked a year ago
Major Version Upgrade Aurora PostgreSQL 11asked 2 years ago