<div dir="ltr"><div>Hi Roxanne,</div><div><br></div><div>Thanks for the help. Here's what I see when I follow the normal postgis extension update path:</div><div style="margin-left:40px">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<br>(1 row)</div><div style="margin-left:40px"><br></div><div style="margin-left:40px">db=> select postgis_full_version();</div><div style="margin-left:40px"> postgis_full_version<br>-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br> 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)<br>(1 row)</div><div><br></div><div>No luck.</div><div><br></div><div>->jvp</div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jan 23, 2023 at 5:30 PM Roxanne Reid-Bennett <<a href="mailto:rox@tara-lu.com">rox@tara-lu.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div>
<p>Jim,</p>
<p>We did a series of updates from 10.17 through to 13+ with Postgis
some time back on AWS. I just pulled up the notes from the dry
run</p>
<p>SELECT PostGIS_Extensions_Upgrade();</p>
<p>does apply to "all the pieces and parts". It wasn't uncommon for
it to say Raster (Topology, etc) wasn't available, especially when
it wasn't installed.</p>
<p>There was one time where running <br>
</p>
<p>SELECT PostGIS_Extensions_Upgrade();</p>
<p>followed by <br>
</p>
<p>select postgis_full_version();</p>
<p>indicated we had to run the Extensions Upgrade AGAIN.</p>
<p>Based on your research, it does sound like something may have
been deleted "not using" the AWS/Postgis packaged tools. I don't
know the internals of PostGis enough to even guess.<br>
</p>
<p>Can you maybe create a new v 12 database with Postgis and Rastor
installed.. and look in the extension there to see what might be
missing in your main DB?<br>
</p>
<p>Roxanne<br>
</p>
<div>On 1/23/2023 4:03 PM, Raj Talati wrote:<br>
</div>
<blockquote type="cite">
<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 <a href="mailto:jvp@jvp.llc" target="_blank"><jvp@jvp.llc></a> wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<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:1px solid rgb(204,204,204);padding-left:1ex">
<div>Could be that someone "uninstalled" them by
deleting from pg_extension.
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>
<br>
<fieldset></fieldset>
<pre>_______________________________________________
postgis-users mailing list
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</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><br clear="all"><br>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div>______________________________</div><div>Jim VanPeursem, PhD</div><div><a href="http://jvpcoaching.com/" target="_blank">http://jvp.llc</a>/</div><div><span style="font-size:12.8px">us: </span><span title="Call with Google Voice" style="font-size:12.8px"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice"><span title="Call with Google Voice">+1 847 414 2759</span></span></span></span></span></span></span></span></span></span><span style="font-size:12.8px"> (+ WhatsApp) -- </span><span style="font-size:12.8px">skype: jimvanpeursem</span><br></div><div><b><i>Bringing clarity and helping you go from where you are to where you want to be</i></b></div></div></div>