[postgis-users] Upgrade 12->13 stuck due to postgis / raster issue
Jim VanPeursem
jvp at jvp.llc
Mon Jan 23 17:31:40 PST 2023
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
On Mon, Jan 23, 2023 at 5:30 PM Roxanne Reid-Bennett <rox at tara-lu.com>
wrote:
> 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
> On 1/23/2023 4:03 PM, Raj Talati wrote:
>
> 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
>
> On Mon, Jan 23, 2023 at 6:15 PM Jim VanPeursem <jvp at jvp.llc> <jvp at jvp.llc>
> wrote:
>
>> 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
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>
> _______________________________________________
> postgis-users mailing listpostgis-users at lists.osgeo.orghttps://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
--
______________________________
Jim VanPeursem, PhD
http://jvp.llc <http://jvpcoaching.com/>/
us: +1 847 414 2759 (+ WhatsApp) -- skype: jimvanpeursem
*Bringing clarity and helping you go from where you are to where you want
to be*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20230123/cf29003d/attachment.htm>
More information about the postgis-users
mailing list