<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<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 class="moz-cite-prefix">On 1/23/2023 4:03 PM, Raj Talati wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAP7G0B7kmJHS64x2WN=JgJjreaY0aPRaus7RHcyt_B7jOy1Y4Q@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<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 class="moz-txt-link-rfc2396E" href="mailto:jvp@jvp.llc"><jvp@jvp.llc></a> 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.
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" moz-do-not-send="true"
class="moz-txt-link-freetext">postgis-users@lists.osgeo.org</a><br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true"
class="moz-txt-link-freetext">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
</blockquote>
</div>
</div>
<br>
<fieldset class="moz-mime-attachment-header"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a>
</pre>
</blockquote>
</body>
</html>