How to upgrade AWS RDS Postgres instance from 14 to 15 with postgis?

Question

AWS added support for Postgres 15.2. Upgrading to this version with postgis extension enabled fails with this error message:

The instance could not be upgraded because there are one or more databases with an older version of PostGIS extension or its dependent extensions (address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster) installed. Please upgrade all installations of PostGIS and drop its dependent extensions and try again.

The RDS instance has postgis 3.1.7 installed, and there is no path to upgrade the extension. Running exact upgrade command ALTER EXTENSION postgis UPDATE TO "3.1.8"; results in

extension "postgis" has no update path from version "3.1.7" to version "3.1.8"

Running SELECT postgis_extensions_upgrade(); command which should upgrade the extension results in:

Upgrade completed, run SELECT postgis_full_version(); for details

, but the version after running SELECT postgis_full_version(); returns

POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="140" GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)"

The postgis_raster extension is broken, and we are not using this, nor any other postgis extension. Only the core postgis extension.

Is there a way to upgrade the RDS instance to postgres. 15 without dropping the postgis extension?

Tomcat

I'm in the same situation and my plan is to wait a while. It is a bit strange that this is missing though because AWS have a had a 'Preview' beta release of Postgres 15 available since October 2022. I am motivated by the new parallel SELECT DISTINCT - this has been a tricky bottleneck for me for years on one project so a few weeks more will not make much difference! – Atomizer

I verified this with a new, empty Postgres 14.7 RDS database with PostGIS enabled. The situation was exactly the same as with my production db - impossible to go to Postgres 15.2 via RDS console and impossible to upgrade PostGIS past 3.1.7. I submitted a bug report to AWS. While annoying, this is actually consistent with the Extensions help page as it lists support of PostGIS 3.3.2 in Postgres 15 but only up to 3.1.7 in Postgres 14. Presumably dump and load is an option. – Atomizer

@Atomizer a sub-production database upgrade with dump would not be the ideal, but it is an option I would like to avoid at all cost :D – Tomcat

i have a support case open for this, after a week the agent finally confirmed with product team that this is currently not possible and that "product team have been made aware of your customer request". :/ maybe they laid off their rds release testing team? – Deuteron

@Deuteron is the support ticket publicly available so we can see the status? It is still not resolved. – Tomcat

Support request closed with "your feature request has been forwarded to the internal product team", :) – Deuteron

Linking twin question on AWS re:Post: repost.aws/questions/QULdhIuoF8RI2zUn8MhTPpqA. A guy named 'Brandon' which username is tagged 'Support Engineer' commented he was able to replicate the potential bug and passed it along to the AWS dev team. – Predecease

Answer 1

Good news, the normal upgrade workflow is now working:

1. Upgrade Postgres to 14.8 (newly available)
2. Upgrade PostGIS to version 3.3.2 (SELECT postgis_extensions_upgrade();)
3. Upgrade Postgres to 15.3

Atomizer

Answer 2

You probably just need to wait for a few days. I'm affected by this exact problem today. The same problem happened to me a few years ago on RDS for an older version of postgres.

From what I remember, after some time AWS released a new PostGIS version, then I just had to run the commands that you quoted.

Note: PostgreSQL 15 on RDS was announced only a few days ago, on February 28. https://aws.amazon.com/about-aws/whats-new/2023/02/amazon-rds-postgresql-major-version-15/

Sclaff