<div dir="auto">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.</div><div dir="auto"><br></div><div dir="auto">I guess whoever did PG 12 upgrade not did alter extension Postgis update and when now you trying that missed prior is giving error.</div><div dir="auto"><br></div><div dir="auto">Most likely this the case. </div><div dir="auto"><br></div><div dir="auto">Good luck</div><div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jan 23, 2023 at 6:15 PM Jim VanPeursem <jvp@jvp.llc> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)"><div dir="ltr">Greetings,<br><br>[originally posted on pgsql-admin, but referred here]<br><br>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.<br><br><div>When I try to upgrade on aws, I get the following error:</div><div style="margin-left:40px">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.<br>Please upgrade all installations of PostGIS and drop its dependent extensions and try again.</div><div><br></div><div>SELECT postgis_full_version(); gives the following (reformatted for clarity):<br><div style="margin-left:40px"> POSTGIS="3.1.7 aafe1ff" [EXTENSION]<br> PGSQL="120"<br> GEOS="3.8.2-CAPI-1.13.4"<br> PROJ="Rel. 5.2.0, September 15th, 2018"<br> GDAL="GDAL 2.4.4, released 2020/01/08"<br> LIBXML="2.9.1"<br> LIBJSON="0.13.1"<br> LIBPROTOBUF="1.3.2"<br> WAGYU="0.5.0 (Internal)"<br> RASTER (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs from "2.5.2 r17328" need upgrade)<br></div></div><div><br></div><div>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?).<br><br>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.</div><div><div style="margin-left:40px"><span style="font-family:monospace">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%';<br> extname | extowner | extnamespace | extversion | nspname | nspowner<br> ---------+------------+--------------+------------+--------------------+----------<br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_1 | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_1 | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_catalog | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | information_schema | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | extensions | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | schema_1 | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | my_new_topo | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | tiger | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | tiger_data | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | topology | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | schema_2 | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | public | <local_admin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_4 | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_4 | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_temp_5 | <rdsadmin><br> postgis | <rdsadmin> | 16404 | 3.1.7 | pg_toast_temp_5 | <rdsadmin></span><br></div><div><br></div><div>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.<br><br>Some things that I've tried:</div><div style="margin-left:40px"><span style="font-family:monospace">db=> SELECT postgis_extensions_upgrade();<br>NOTICE: Extension postgis_raster is not available or not packagable for some reason<br>NOTICE: Extension postgis_topology is not available or not packagable for some reason<br>NOTICE: Extension postgis_tiger_geocoder is not available or not packagable for some reason<br> postgis_extensions_upgrade<br>-------------------------------------------------------------------<br>Upgrade completed, run SELECT postgis_full_version(); for details</span><br></div><div><br></div><div>Also:</div><div style="margin-left:40px"><span style="font-family:monospace">db=> select * from pg_available_extensions where name like 'postgis%';<br> name | default_version | installed_version | comment<br>------------------------+-----------------+-------------------+------------------------------------------------------------<br> postgis | 3.1.7 | 3.1.7 | PostGIS geometry and geography spatial types and functions<br> postgis_tiger_geocoder | 3.1.7 | | PostGIS tiger geocoder and reverse geocoder<br> postgis_topology | 3.1.7 | | PostGIS topology spatial types and functions<br> postgis_raster | 3.1.7 | | PostGIS raster types and functions<br>(4 rows)</span></div><div><br></div><div>And:</div><div style="margin-left:40px"><span style="font-family:monospace">db=> \dx<br> List of installed extensions<br> Name | Version | Schema | Description<br>---------------+---------+------------+---------------------------------------------------------------------<br> fuzzystrmatch | 1.1 | extensions | determine similarities and distance between strings<br> plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language<br> postgis | 3.1.7 | extensions | PostGIS geometry, geography, and raster spatial types and functions<br> sslinfo | 1.2 | public | information about SSL certificates<br>(4 rows)</span></div><div><br></div><div>And:</div><div style="margin-left:40px"><span style="font-family:monospace">db=> CREATE EXTENSION postgis_raster;<br>ERROR: PostGIS Raster is already installed in schema 'extensions'<br>CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE</span><br></div><div><br></div><div><span style="font-family:monospace">And:</span></div><div style="margin-left:40px"><span style="font-family:monospace">db=> DROP EXTENSION postgis_raster;<br>ERROR: extension "postgis_raster" does not exist</span><br><br></div><div>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.<br><br>Erik Weinhold from the pgsql-admin mailing list helpfully pointed out the following:</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)"><div>Could be that someone "uninstalled" them by deleting from pg_extension.<div><div><div id="m_-3669456867618042892gmail-q_170"><div></div></div></div></div>
Deleting from pg_extension reproduces the error:<br>
<br>
test=# create extension postgis_raster;<br>
CREATE EXTENSION<br>
test=# delete from pg_extension where extname = 'postgis_raster';<br>
DELETE 1<br>
test=# drop extension postgis_raster;<span><br>
ERROR: extension "postgis_raster" does not exist<br></span>
test=# create extension postgis_raster;<br>
ERROR: PostGIS Raster is already installed in schema 'public'<span><br>
CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE<br>
<br></span>
That last error message does not come from checking pg_extension but rather<br>
postgis_raster itself[2].<br>
<br>
I tried DROP SCHEMA extensions CASCADE at this point to get rid of the<br>
remaining objects but that fails:<br>
<br>
test=# drop schema extensions cascade;<br>
ERROR: cache lookup failed for extension 27232<span><br></span></div></blockquote><div><br></div><div>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?<br><br>Thanks,<br><br>->jvp</div><div><br></div></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote></div></div>