[postgis-users] Upgrade 12->13 stuck due to postgis / raster issue

[Jim VanPeursem]

Greetings,

[originally posted on pgsql-admin, but referred here]

I recently took over the management of a postgresql + postgis db on aws rds. Given the age of this project, the db itself is probably ~7-8 years old. It is currently on v12.12 and I'm unable to upgrade it to 13+. The db does use postgis, but as far as I can tell, no raster or topology or other postgis-related fields/features.

When I try to upgrade on aws, I get the following error:

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.

SELECT postgis_full_version(); gives the following (reformatted for clarity):

    POSTGIS="3.1.7 aafe1ff" [EXTENSION]
    PGSQL="120"
    GEOS="3.8.2-CAPI-1.13.4"
    PROJ="Rel. 5.2.0, September 15th, 2018"
    GDAL="GDAL 2.4.4, released 2020/01/08"
    LIBXML="2.9.1"
    LIBJSON="0.13.1"
    LIBPROTOBUF="1.3.2"
    WAGYU="0.5.0 (Internal)"
    RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs from "2.5.2 r17328" need upgrade)

Note that it lists RASTER both as being unpackaged and needing an upgrade, even though postgis_raster is apparently not installed. My thinking is that somewhere along the way, postgis_raster and possibly topology were installed and later uninstalled (perhaps after being unbundled?).

For more clues, I issued the following command. For clarity I replace the account numbers with pseudo-usernames for clarity. Also note that schema_1 and schema_2 are two schemas that the project uses.

db=> select a.extname, a.extowner, a.extnamespace, a.extversion, b.nspname, b.nspowner from pg_catalog.pg_extension a, pg_namespace b where a.extname LIKE '%postgis%';
     extname |  extowner  | extnamespace | extversion |      nspname       | nspowner
    ---------+------------+--------------+------------+--------------------+----------
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_toast           | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_temp_1          | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_toast_temp_1    | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_catalog         | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | information_schema | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | extensions         | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | schema_1           | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | my_new_topo        | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | tiger              | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | tiger_data         | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | topology           | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | schema_2           | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | public             | <local_admin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_temp_4          | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_toast_temp_4    | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_temp_5          | <rdsadmin>
     postgis | <rdsadmin> |        16404 | 3.1.7      | pg_toast_temp_5    | <rdsadmin>

I'm not familiar enough with postgresql nor postgis to understand whether the nspname entries for tiger, topology, etc. are expected, or offer clues as to the problem that I am encountering.

Some things that I've tried:

db=> SELECT postgis_extensions_upgrade();
NOTICE:  Extension postgis_raster is not available or not packagable for some reason
NOTICE:  Extension postgis_topology is not available or not packagable for some reason
NOTICE:  Extension postgis_tiger_geocoder is not available or not packagable for some reason
                   postgis_extensions_upgrade
-------------------------------------------------------------------
Upgrade completed, run SELECT postgis_full_version(); for details

Also:

db=> select * from pg_available_extensions where name like 'postgis%';
          name          | default_version | installed_version |                          comment
------------------------+-----------------+-------------------+------------------------------------------------------------
 postgis                | 3.1.7           | 3.1.7             | PostGIS geometry and geography spatial types and functions
 postgis_tiger_geocoder | 3.1.7           |                   | PostGIS tiger geocoder and reverse geocoder
 postgis_topology       | 3.1.7           |                   | PostGIS topology spatial types and functions
 postgis_raster         | 3.1.7           |                   | PostGIS raster types and functions
(4 rows)

And:

db=> \dx
                                      List of installed extensions
     Name      | Version |   Schema   |                             Description
---------------+---------+------------+---------------------------------------------------------------------
 fuzzystrmatch | 1.1     | extensions | determine similarities and distance between strings
 plpgsql       | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis       | 3.1.7   | extensions | PostGIS geometry, geography, and raster spatial types and functions
 sslinfo       | 1.2     | public     | information about SSL certificates
(4 rows)

And:

db=> CREATE EXTENSION postgis_raster;
ERROR:  PostGIS Raster is already installed in schema 'extensions'
CONTEXT:  PL/pgSQL function inline_code_block line 10 at RAISE

And:

db=> DROP EXTENSION postgis_raster;
ERROR:  extension "postgis_raster" does not exist

I also did a snapshot backup and restored to a new instance on aws, and this resulted in exactly the same problem on the new instance.

Erik Weinhold from the pgsql-admin mailing list helpfully pointed out the following:

Could be that someone "uninstalled" them by deleting from pg_extension.

Deleting from pg_extension reproduces the error:

        test=# create extension postgis_raster;
        CREATE EXTENSION
        test=# delete from pg_extension where extname = 'postgis_raster';
        DELETE 1
        test=# drop extension postgis_raster;
        ERROR:  extension "postgis_raster" does not exist
        test=# create extension postgis_raster;
        ERROR:  PostGIS Raster is already installed in schema 'public'
        CONTEXT:  PL/pgSQL function inline_code_block line 10 at RAISE

That last error message does not come from checking pg_extension but rather
postgis_raster itself[2].

I tried  DROP SCHEMA extensions CASCADE  at this point to get rid of the
remaining objects but that fails:

        test=# drop schema extensions cascade;
        ERROR:  cache lookup failed for extension 27232

Is there a way to either restore or remove the unbundled / partial raster support so that it can be upgraded? Does anyone have other suggestions on what I could try? I'd like to get to postgresql v13+ with only postgis (no raster, topology, etc.) installed without losing any data along the journey. Is my only recourse to do a full data backup to sql followed by creating a new instance and restoring data?

Thanks,

->jvp

[Raj Talati]

You tried to do upgrade the extension it might be case that the current old extension was not upgraded .  Alter extension PostGis update and then you can retry upgrade.

I guess whoever did PG 12 upgrade not did alter extension Postgis update and when now you trying that missed prior is giving error.

Most likely this the case. 

Good luck

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Roxanne Reid-Bennett]

Jim,

We did a series of updates from 10.17 through to 13+ with Postgis some time back on AWS.  I just pulled up the notes from the dry run

SELECT PostGIS_Extensions_Upgrade();

does apply to "all the pieces and parts".  It wasn't uncommon for it to say Raster (Topology, etc) wasn't available, especially when it wasn't installed.

There was one time where running

SELECT PostGIS_Extensions_Upgrade();

followed by

select postgis_full_version();

indicated we had to run the Extensions Upgrade AGAIN.

Based on your research, it does sound like something may have been deleted "not using" the AWS/Postgis packaged tools.  I don't know the internals of PostGis enough to even guess.

Can you maybe create a new v 12 database with Postgis and Rastor installed.. and look in the extension there to see what might be missing in your main DB?

Roxanne

[Jim VanPeursem]

Hi Raj,

Thanks for the reply, but it's more complicated than that. I guess I failed to mention that I already tried the traditional extension upgrade paths prior to attempting the aws postgresql upgrade. From my original email you can see that postgis is version 3.1.7, which appears to be the current version on aws rds. So I don't think the existing extension(s) are out of date, other than the partially (un)installed raster.

Here's what the extension upgrade command looks like:

db=> alter extension postgis update;
NOTICE:  version "3.1.7" of extension "postgis" is already installed
ALTER EXTENSION

It seems that the extensions were previously updated, but then partially deleted in an incorrect way.

Do you have any other ideas?

Thanks,

->jvp

--

______________________________

Jim VanPeursem, PhD

http://jvp.llc/

us: +1 847 414 2759 (+ WhatsApp) --  skype: jimvanpeursem

Bringing clarity and helping you go from where you are to where you want to be

[Jim VanPeursem]

Hi Roxanne,

Thanks for the help. Here's what I see when I follow the normal postgis extension update path:

db=> SELECT PostGIS_Extensions_Upgrade();

NOTICE:  Extension postgis_raster is not available or not packagable for some reason
NOTICE:  Extension postgis_topology is not available or not packagable for some reason
NOTICE:  Extension postgis_tiger_geocoder is not available or not packagable for some reason
                    postgis_extensions_upgrade
-------------------------------------------------------------------
 Upgrade completed, run SELECT postgis_full_version(); for details

(1 row)

db=> select postgis_full_version();

                                                                                                                                                                postgis_full_version
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="120" GEOS="3.8.2-CAPI-1.13.4" PROJ="Rel. 5.2.0, September 15th, 2018" GDAL="GDAL 2.4.4, released 2020/01/08" LIBXML="2.9.1" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)" RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs from "2.5.2 r17328" need upgrade)

(1 row)

No luck.

->jvp

[Roxanne Reid-Bennett]

Jim,

I did a search on this phrase:

raster procs from "2.5.2 r17328" need upgrade

I found a post from Regina from 2020 moving from 2.5.2 to 3. It might illumine a bit.

https://lists.osgeo.org/pipermail/postgis-users/2020-May/044225.html

And this one for someone who had 100% control of their PG hardware from last fall.

https://groups.google.com/g/postgis-users/c/26iNuhXFHgg

And this one from the AWS forums

https://repost.aws/questions/QUEk9HG5x3Qq6sEB3lVmynUw/upgrade-from-postgres-12-7-r-1-to-13-3-r-1-stuck

which references this:

https://stackoverflow.com/questions/42634626/postgresql-upgrade-on-amazon-rds-blocked-by-postgis-version

which uses

ALTER EXTENSION postgis UPDATE;

The observation in the last post about waiting overnight and things "magically being fixed", I saw during our dry run, not the production run, but definitely during the dry run.  Their observation about rebooting the PG server between upgrades isn't totally "off" either.

Posting in the AWS Forums might get some help from inside Amazon.

Or, if you have any level of support from AWS that would allow you get someone to "poke/prod" on disk, you might get a fix. 

Migrating the data is "of course" a last resort, but at least possible.

Given my lack of knowledge of the upgrade steps, I've done what I can.  Good luck.

Roxanne

[Allan Chase]

If this is on AWS RDS, create a ticket with AWS as they have to perform what they call an “under the hood fix”.  Hopefully AWS had logged the steps they took when they fixed my instance.  Unfortunately I can’t give you the ticket number because of “reasons” ☹.   I created a snapshot of our instance, created a DB out of that snapshot and allowed AWS to use that as their testing ground for the fix; They fixed it and I was able to continue my upgrade path.  Afterwards, I had to use some hair color to cover up those greys for sure. 

 

Allan Chase

Data Engineer

 

 

From: postgis-users <postgis-us...@lists.osgeo.org> On Behalf Of Jim VanPeursem
Sent: Monday, January 23, 2023 6:32 PM
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: [External] Re: [postgis-users] Upgrade 12->13 stuck due to postgis / raster issue

 

************************** ATTENTION - External Email **************************

Please verify the sender before taking any actions or clicking any links. Please treat this email with caution, especially if you are requested to click on a link, decrypt/open an attachment, or enable macros. If you determine this email to be malicious, please report it to phishing.

CONFIDENTIALITY NOTICE: This electronic message contains information from Bluestaq LLC, which may be company sensitive, proprietary, privileged, or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution, or use of this transmission or its contents is prohibited. Please notify the sender immediately if you have received this transmission in error.

[Regina Obe]

Jim,

 

At a glance what you have their looks fine.  You just need to run

 

SELECT postgis_extensions_upgrade();

 

One more time, so it repackages raster functions into postgis_raster and upgrades them.

 

Then if you don’t need raster support, you can run:

 

DROP EXTENSION postgis_raster;

 

You can ignore all those notices, just telling you those extensions you don’t have installed.

Strk, remind me why we have these notices, I think it just confuses people more than being helpful.

 

Thanks,

Regina

 

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Jim VanPeursem

Sent: Monday, January 23, 2023 8:32 PM
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>

[Regina Obe]

Jim,

 

Sorry I had missed the thread Roxanne had written.

 

So you already ran twice

 

SELECT postgis_extensions_upgrade(); 

SELECT postgis_extensions_upgrade(); 

 

And still shows raster is UNPACKAGED.

If you did delete some raster functions in panic, then I think that would prevent it from being upgraded.

 

You want to first make sure you don’t have raster tables running the below query should return no records.

 

SELECT *

FROM raster_columns;

 

 

Easy fix especially if you don’t need raster support, is to run the uninstall raster script I had attached in that mailing list thread Roxanne pointed to

 

https://lists.osgeo.org/pipermail/postgis-users/attachments/20200529/e32c5d8c/attachment.obj

 

 

That should cleanly get rid of all remnants of raster. 

 

Hope that helps,

Regina

 

 

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Roxanne Reid-Bennett
Sent: Monday, January 23, 2023 9:29 PM
To: postgi...@lists.osgeo.org
Subject: Re: [postgis-users] Upgrade 12->13 stuck due to postgis / raster issue

 

Jim,

[Jim VanPeursem]

Roxanne, Allan and Regina,

Thanks so much for your help.

I created a snapshot backup, restored to a new test rds instance, and ran the sql attachment "Spatial Types for PostgreSQL" that you highlighted. That's a great find. My google sleuthing didn't uncover that one. Running that script cleared up the postgis_full_version raster issues:

db=> SELECT postgis_full_version();

POSTGIS="3.1.7 aafe1ff" [EXTENSION] PGSQL="120" (procs need upgrade for use with PostgreSQL "130") GEOS="3.9.1-CAPI-1.14.2" PROJ="8.0.1" LIBXML="2.9.1" LIBJSON="0.13.1" LIBPROTOBUF="1.3.2" WAGYU="0.5.0 (Internal)"
(1 row)

No more warning about raster being unbundled and requiring an upgrade, but the PGSQL warning is new. I then did the following:

db=> SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.1.7

NOTICE:  Extension postgis_raster is not available or not packagable for some reason
NOTICE:  Extension postgis_topology is not available or not packagable for some reason
NOTICE:  Extension postgis_tiger_geocoder is not available or not packagable for some reason
                    postgis_extensions_upgrade
-------------------------------------------------------------------
 Upgrade completed, run SELECT postgis_full_version(); for details
(1 row)

db=> SELECT postgis_full_version();

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

Success!

Then I requested an aws upgrade to the test instance via the console and it succeeded! I anticipate the same should work on the production db. I'll give it a try during our next maintenance period.

Thanks again for everyone's help. Allan, I might need to borrow your hair color after all of this. ;-)

->jvp

_______________________________________________

postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Sandro Santilli]

On Tue, Jan 24, 2023 at 01:55:44AM -0500, Regina Obe wrote:

> Strk, remind me why we have these notices, I think it just confuses people more than being helpful.

Debugging left over, I've changed that in commit 0e97ae29e to:

- RAISE NOTICE 'Extension % is not available or not packagable for some reason', rec.name;
+ RAISE DEBUG 'Skipping % (not in use)', rec.name;

--strk;