By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Major version upgrade from 14.7 to 15.2 fails due to PostGIS Extension

3

This document indicates that PostGIS needs to be updated before a major version upgrade. And, this list shows the supported PostGIS version under PostgreSQL 15.2 is 3.3.2.

However, the latest PostGIS version available on RDS under PostgreSQL 14.7 appears to be 3.1.7. Indeed, after running this: SELECT postgis_extensions_upgrade(); Then, attempting the upgrade, I get the following:

------------------------------------------------------------------
Upgrade could not be run on ...
------------------------------------------------------------------
The instance could not be upgraded from 14.7.R1 to 15.2.R1 because of following reasons. Please take appropriate action on databases that have usages incompatible with requested major engine version upgrade and try again.
- Following usages in database 'postgres' need to be corrected before upgrade:
-- 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.
----------------------- END OF LOG ----------------------

Trying the specific target version also fails: ALTER EXTENSION postgis UPDATE TO '3.3.2';

ERROR:  extension "postgis" has no update path from version "3.1.7" to version "3.3.2"
SQL state: 22023

Any suggestions?

  • Brandon SUPPORT ENGINEER
    a year ago

    Hi there,

    Do you have any of the follow dependant extensions installed on your instance: address_standardizer, address_standardizer_data_us, postgis_tiger_geocoder, postgis_topology, postgis_raster?

    If so, can you ensure they are upgraded to the latest for 14.7, or drop these dependant extensions. Then try the upgrade again and see.

  • Andre
    a year ago

    @Brandon. I have the same issue as Original Poster. I don't have any of these dependent extensions installed on. It is indeed incompatible migrating from 14.7 to 15.2 because of PostGIS. This also came up in Stack Overflow: https://stackoverflow.com/questions/75612704/how-to-upgrade-aws-rds-postgres-instance-from-14-to-15-with-postgis

  • Brandon SUPPORT ENGINEER
    a year ago

    Hi Andre,

    I have replicated this situation in my environment. I've raised this with our dev team as a potential bug which they are now investigating. I'll let you know once that has been completed.

    Thanks

  • Andre
    a year ago

    Thank you Brandon. I will wait your reply, then. Looking forward to test PostgreSQL 15 in AWS RDS. Tks.

  • rePost-User-6605032
    a year ago

    Just want to note that we've also been awaiting this fix. It's not currently possible to upgrade to 15.2 from 14.7 without dumping and restoring which is not an option for us.

Topics
Database
Tags
PostgreSQLDatabase
Language
English
rePost-User-6165245
asked a year ago712 views
1 Answer
3
Accepted Answer

Hey guys,

Thanks heaps for your patience. A new version of 15.2 has been released named 15.2-R2 as well as 15.3-R1 which has the fix in it.

Please up vote this answer if it works for you.

Thanks :)

profile pictureAWS
SUPPORT ENGINEER
Brandon
answered a year ago
  • Andre
    10 months ago

    Thank you Brandon. After upgrading PostGIS with SELECT postgis_extensions_upgrade(); I get the following: "POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="140" (procs need upgrade for use with PostgreSQL "150") GEOS="3.11.2-CAPI-1.17.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)". What does "procs need upgrade for use with PostgreSQL "150"" mean?

  • Brandon SUPPORT ENGINEER
    10 months ago

    Hey Andre, Just to clarify, are you saying the extension upgraded successfully and then you saw that error message? Or did it fail? Can you run the upgrade again (or twice consecutively) and see what message you get and post here please. Can you also run select postgis_full_version(); and let me know the output.

    We may need to reinstall PGSQL, or it may be in the wrong schema.

  • Andre
    10 months ago

    Hi Brandon. Yes, the version now appears as 3.3.2 (updated successfully?). I am just not sure if it worked as expected (because of that part in the message which says: "procs need upgrade for use with PostgreSQL "150""). Here is what I did: 1. Upgraded PostgreSQL to version 14.8 (I was in 14.7). 2. Updated PostGIS with SELECT postgis_extensions_upgrade();. 3. Upgraded PostgreSQL to version 15.3. 4. Updated PostGIS again using the same previous function. I tried to update PostGIS consecutively and SELECT PostGIS_full_version(); always return: "POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="140" (procs need upgrade for use with PostgreSQL "150") GEOS="3.11.2-CAPI-1.17.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)"". Let me know if I can provide any other info. Thank you.

  • Brandon SUPPORT ENGINEER
    10 months ago

    Hey Andre, Follow the below: -- What upgrade paths are available from 3.3.2? d1=> SELECT * FROM pg_extension_update_paths('postgis') where source = '3.3.2' AND path is not null; source | target | path --------+-----------+------------------ 3.3.2 | 3.3.2next | 3.3.2--3.3.2next (1 row)

    -- Start a transaction d1=> begin; BEGIN

    -- Let's update the extension to 3.3.2next this will force the procs to be updated to v15 d1=> alter extension postgis update to '3.3.2next'; ALTER EXTENSION

    -- Confirm the procs were upgraded d1=> select postgis_full_version(); postgis_full_version

    POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" GEOS="3.11.2-CAPI-1.17.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.15" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)" (1 row)

    -- THIS IS CRITICAL, YOU MUST UPDATE THE EXTENSION back to 3.3.2 <-- if you don't do this you will break postgis at a later date! d1=> alter extension postgis update to '3.3.2'; ALTER EXTENSION

    -- Verify we successfully moved to the 3.3.2 version d1=*> select * from pg_available_extension_versions where name = 'postgis' AND installed = 't'; name | version | installed | superuser | trusted | relocatable | schema | requires | comment ---------+---------+---------

  • Andre
    10 months ago

    Thanks Brandon. It worked. Should I do something else different in the future because of this (procs not updating automatically) (when upgrading PostgreSQL or PostGIS)? Your comment says to open a transaction 'begin;' but it never ended it (end;). Wasn't it necessary?

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

Relevant content