> On 23/01/2023 20:57 CET Jim VanPeursem <jvp@xxxxxxx> wrote: > > 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. > > 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 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?). 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=>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. Extension postgis_tiger_geocoder and postgis_topology create schemas tiger and topology, respectively. That's expected *if* those extensions are installed. Could be that someone "uninstalled" them by deleting from pg_extension. > 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 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 > 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. > > So I'm both stuck and confused. It seems that I'm in the middle of a partial > upgrade/install that broke along the way. Does anyone have other suggestions > 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 my > 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=postgresql.git;a=blob;f=src/bin/psql/describe.c;h=c7ff55fbd778bab3efb0d9db36e6d9a3c5d89a63;hb=7cd0d523d2581895e65cd0ebebc7e50caa8bbfda#l5501 [2] https://git.osgeo.org/gitea/postgis/postgis/src/tag/3.1.7/raster/rt_pg/rtpostgis.sql.in#L54 -- Erik