Date: Mon, 23 Jan 2023 22:41:20 +0100 (CET)
From: Erik Wienhold <ewie@ewie.name>
To: Jim VanPeursem <jvp@jvp.llc>, pgsql-admin@lists.postgresql.org
Message-ID: <2049903701.70875.1674510080586@office.mailbox.org>
In-Reply-To: 
 <CADJG2i5JrFAaFki+ZZV_ho1p1gSsp+FCZAa5pyVMGCR4rapJxQ@mail.gmail.com>
References: 
 <CADJG2i5JrFAaFki+ZZV_ho1p1gSsp+FCZAa5pyVMGCR4rapJxQ@mail.gmail.com>
Subject: Re: Upgrade 12->13 stuck due to postgis / raster issue
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/2049903701.70875.1674510080586%40office.mailbox.org>
Precedence: bulk

> On 23/01/2023 20:57 CET Jim VanPeursem <jvp@jvp.llc> wrote:
>
> I recently took over the management of a postgresql=C2=A0+ 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 databa=
ses
> with an older version of PostGIS extension or its dependent extensions
> (address_standardizer, address_standardizer_data_us, postgis_tiger_geocod=
er,
> postgis_topology, postgis_raster) installed.
> > Please upgrade all installations of PostGIS and drop its dependent exte=
nsions and try again.
> >

The PostGIS mailing list may be better suited for this:
https://lists.osgeo.org/mailman/listinfo/postgis-users.

> SELECT postgis_full_version(); gives the following (reformatted for clari=
ty):
> > POSTGIS=3D"3.1.7 aafe1ff" [EXTENSION]
> > PGSQL=3D"120"
> > GEOS=3D"3.8.2-CAPI-1.13.4"
> > PROJ=3D"Rel. 5.2.0, September 15th, 2018"
> > GDAL=3D"GDAL 2.4.4, released 2020/01/08"
> > LIBXML=3D"2.9.1"
> > LIBJSON=3D"0.13.1"
> > LIBPROTOBUF=3D"1.3.2"
> > WAGYU=3D"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 th=
at
> somewhere along the way, postgis_raster and possibly topology were instal=
led
> and later uninstalled (perhaps after being unbundled?).

RASTER only appears in the version string if postgis_raster is installed.

> 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=3D>select a.extname, a.extowner, a.extnamespace, a.extversion, b.nsp=
name, b.nspowner from pg_catalog.pg_extension a, pg_namespace b where a.ext=
name LIKE '%postgis%';
> > extname | extowner | extnamespace | extversion | nspname | nspowner
> > ---------+------------+--------------+------------+--------------------=
+----------
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast | <rdsadmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_1 | <rdsadmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_1 | <rdsadmi=
n>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_catalog | <rdsadmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | information_schema | <rdsa=
dmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | extensions | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | schema_1 | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | my_new_topo | <local_admin=
>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | tiger | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | tiger_data | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | topology | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | schema_2 | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | public | <local_admin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_4 | <rdsadmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_4 | <rdsadmi=
n>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_5 | <rdsadmin>
> > =C2=A0postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_5 | <rdsadmi=
n>
>
> 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.

Extension postgis_tiger_geocoder and postgis_topology create schemas tiger =
and
topology, respectively.  That's expected *if* those extensions are installe=
d.

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

> Some things that I've tried:
> > db=3D> 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 f=
or some reason
> > NOTICE: Extension postgis_tiger_geocoder is not available or not packag=
able for some reason
> >  postgis_extensions_upgrade
> > -------------------------------------------------------------------
> > =C2=A0Upgrade completed, run SELECT postgis_full_version(); for details
>
> Also:
> > db=3D> select * from pg_available_extensions where name like 'postgis%'=
;
> >  name | default_version | installed_version | comment
> > ------------------------+-----------------+-------------------+--------=
----------------------------------------------------
> > =C2=A0postgis | 3.1.7 | 3.1.7 | PostGIS geometry and geography spatial =
types and functions
> > =C2=A0postgis_tiger_geocoder | 3.1.7 | | PostGIS tiger geocoder and rev=
erse geocoder
> > =C2=A0postgis_topology | 3.1.7 | | PostGIS topology spatial types and f=
unctions
> > =C2=A0postgis_raster | 3.1.7 | | PostGIS raster types and functions
> > (4 rows)
>
> And:
> > db=3D> \dx
> >  List of installed extensions
> >  Name | Version | Schema | Description
> > ---------------+---------+------------+--------------------------------=
-------------------------------------
> > =C2=A0fuzzystrmatch | 1.1 | extensions | determine similarities and dis=
tance between strings
> > =C2=A0plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
> > =C2=A0postgis | 3.1.7 | extensions | PostGIS geometry, geography, and r=
aster spatial types and functions
> > =C2=A0sslinfo | 1.2 | public | information about SSL certificates
> > (4 rows)
>
> And:
> > db=3D> 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=3D> DROP EXTENSION postgis_raster;
> > ERROR: extension "postgis_raster" does not exist

Deleting from pg_extension reproduces the error:

=09test=3D# create extension postgis_raster;
=09CREATE EXTENSION
=09test=3D# delete from pg_extension where extname =3D 'postgis_raster';
=09DELETE 1
=09test=3D# drop extension postgis_raster;
=09ERROR:  extension "postgis_raster" does not exist
=09test=3D# create extension postgis_raster;
=09ERROR:  PostGIS Raster is already installed in schema 'public'
=09CONTEXT:  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:

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

> I also did a=C2=A0snapshot backup and restored to a new instance on aws, =
and this
> resulted in exactly the same problem on the new instance.
>
> So I'm both stuck and confused. It seems that I'm in the middle of a part=
ial
> upgrade/install that broke along the way. Does anyone have other suggesti=
ons
> on what I might try? I'd like to get to v13+ with only postgis (no raster=
,
> topology, etc.) installed without losing any data along the journey. Is m=
y
> only recourse to do a full data backup to sql followed by creating a new
> instance and restoring data?

[1] https://git.postgresql.org/gitweb/?p=3Dpostgresql.git;a=3Dblob;f=3Dsrc/=
bin/psql/describe.c;h=3Dc7ff55fbd778bab3efb0d9db36e6d9a3c5d89a63;hb=3D7cd0d=
523d2581895e65cd0ebebc7e50caa8bbfda#l5501
[2] https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.1.7/raster/rt_pg/=
rtpostgis.sql.in#L54

--
Erik