By using AWS re:Post, you agree to the Terms of Use
/Aurora Postgres upgrade from 11.13 to 12.8 failing - I assume due to PostGis/

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

0

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-12.8.12.8.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-12.8.12.8.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.

Thanks

Thomas

2 Answers
0

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

answered 3 months ago
  • 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.

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');
answered 3 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions