[postgis-users] Upgrade 12->13 stuck due to postgis / raster issue
Jim VanPeursem
jvp at jvp.llc
Mon Jan 23 17:28:09 PST 2023
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
On Mon, Jan 23, 2023 at 5:03 PM Raj Talati <rajtalati at gmail.com> 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> 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 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/2626c067/attachment.htm>
More information about the postgis-users
mailing list