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@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
