<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
@font-face
{font-family:"Comic Sans MS";
panose-1:3 15 7 2 3 3 2 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
font-size:10.0pt;
font-family:"Courier New";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="EN-US" link="blue" vlink="purple" style="word-wrap:break-word">
<div class="WordSection1">
<p class="MsoNormal">If this is on AWS RDS, create a ticket with AWS as they have to perform what they call an “under the hood fix”. Hopefully AWS had logged the steps they took when they fixed my instance. Unfortunately I can’t give you the ticket number
because of “reasons” <span style="font-family:"Segoe UI Emoji",sans-serif">☹</span>. I created a snapshot of our instance, created a DB out of that snapshot and allowed AWS to use that as their testing ground for the fix; They fixed it and I was able to
continue my upgrade path. Afterwards, I had to use some hair color to cover up those greys for sure.
<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal"><span style="font-family:"Comic Sans MS"">Allan Chase<o:p></o:p></span></p>
<p class="MsoNormal">Data Engineer<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in">
<p class="MsoNormal"><b>From:</b> postgis-users <postgis-users-bounces@lists.osgeo.org>
<b>On Behalf Of </b>Jim VanPeursem<br>
<b>Sent:</b> Monday, January 23, 2023 6:32 PM<br>
<b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> [External] Re: [postgis-users] Upgrade 12->13 stuck due to postgis / raster issue<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="80%" style="width:80.0%;border-collapse:collapse;border:none">
<tbody>
<tr>
<td width="97%" valign="top" style="width:97.0%;border:solid #161616 2.25pt;background:#E10000;padding:0in 0in 0in 0in">
<p align="center" style="margin:0in;text-align:center;line-height:.75pt;mso-line-height-rule:exactly">
<span style="font-size:1.0pt;color:#E10000">************************** </span><span style="font-size:1.0pt"><o:p></o:p></span></p>
<p class="MsoNormal" align="center" style="margin:.75pt;text-align:center"><strong><span style="font-size:14.0pt;font-family:"Calibri",sans-serif;color:white">ATTENTION - External Email</span></strong><span style="color:black">
</span><o:p></o:p></p>
<p align="center" style="margin:0in;text-align:center;line-height:.75pt;mso-line-height-rule:exactly">
<span style="font-size:1.0pt;color:#E10000">**************************</span><span style="font-size:1.0pt;color:black">
</span><span style="font-size:1.0pt"><o:p></o:p></span></p>
</td>
</tr>
<tr>
<td valign="top" style="border:solid #E10000 1.0pt;border-top:none;background:white;padding:0in 0in 0in 0in">
<p align="center" style="margin:1.5pt;text-align:center;background:white"><strong><span style="font-size:12.0pt;font-family:"Calibri",sans-serif;color:black">Please verify the sender before taking any actions or clicking any links.
</span></strong><o:p></o:p></p>
<p align="center" style="mso-margin-top-alt:.75pt;margin-right:0in;margin-bottom:1.5pt;margin-left:0in;text-align:center;background:white">
<span style="font-size:9.5pt;color:black">Please treat this email with caution, especially if you are requested to click on a link, decrypt/open an attachment, or enable macros. If you determine this email to be malicious, please report it to phishing.
</span><o:p></o:p></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<div>
<p class="MsoNormal">Hi Roxanne,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks for the help. Here's what I see when I follow the normal postgis extension update path:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal">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)<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal">db=> select postgis_full_version();<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"> 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)<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">No luck.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">->jvp<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p>Jim,<o:p></o:p></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<o:p></o:p></p>
<p>SELECT PostGIS_Extensions_Upgrade();<o:p></o:p></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.<o:p></o:p></p>
<p>There was one time where running <o:p></o:p></p>
<p>SELECT PostGIS_Extensions_Upgrade();<o:p></o:p></p>
<p>followed by <o:p></o:p></p>
<p>select postgis_full_version();<o:p></o:p></p>
<p>indicated we had to run the Extensions Upgrade AGAIN.<o:p></o:p></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.<o:p></o:p></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?<o:p></o:p></p>
<p>Roxanne<o:p></o:p></p>
<div>
<p class="MsoNormal">On 1/23/2023 4:03 PM, Raj Talati wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">I guess whoever did PG 12 upgrade not did alter extension Postgis update and when now you trying that missed prior is giving error.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Most likely this the case. <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Good luck<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Mon, Jan 23, 2023 at 6:15 PM Jim VanPeursem <a href="mailto:jvp@jvp.llc" target="_blank">
<jvp@jvp.llc></a> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">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.<o:p></o:p></p>
<div>
<p class="MsoNormal">When I try to upgrade on aws, I get the following error:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">SELECT postgis_full_version(); gives the following (reformatted for clarity):<o:p></o:p></p>
<div style="margin-left:30.0pt">
<p class="MsoNormal"> 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)<o:p></o:p></p>
</div>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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.<o:p></o:p></p>
</div>
<div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><span style="font-family:"Courier New"">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><span style="font-family:"Courier New"">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Also:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><span style="font-family:"Courier New"">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">And:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><span style="font-family:"Courier New"">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">And:<o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal"><span style="font-family:"Courier New"">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><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-family:"Courier New"">And:</span><o:p></o:p></p>
</div>
<div style="margin-left:30.0pt">
<p class="MsoNormal" style="margin-bottom:12.0pt"><span style="font-family:"Courier New"">db=> DROP EXTENSION postgis_raster;<br>
ERROR: extension "postgis_raster" does not exist</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">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:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in">
<div>
<p class="MsoNormal">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;<br>
ERROR: extension "postgis_raster" does not exist<br>
test=# create extension postgis_raster;<br>
ERROR: PostGIS Raster is already installed in schema 'public'<br>
CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE<br>
<br>
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<o:p></o:p></p>
</div>
</blockquote>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</div>
</div>
<p class="MsoNormal">_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
</div>
<p class="MsoNormal"><br>
<br>
<o:p></o:p></p>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>postgis-users mailing list<o:p></o:p></pre>
<pre><a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><o:p></o:p></pre>
<pre><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></pre>
</blockquote>
</div>
<p class="MsoNormal">_______________________________________________<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" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><o:p></o:p></p>
</blockquote>
</div>
<p class="MsoNormal"><br clear="all">
<br>
-- <o:p></o:p></p>
<div>
<div>
<div>
<p class="MsoNormal">______________________________<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Jim VanPeursem, PhD<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><a href="http://jvpcoaching.com/" target="_blank">http://jvp.llc</a>/<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><span style="font-size:9.5pt">us: +1 847 414 2759 (+ WhatsApp) -- skype: jimvanpeursem</span><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><b><i>Bringing clarity and helping you go from where you are to where you want to be</i></b><o:p></o:p></p>
</div>
</div>
</div>
</div>
</div>
<br>
<br>
<p style="font-size:12.0pt;font-family:"Arial",sans-serif;
color:#888888;background:white">
CONFIDENTIALITY NOTICE: This electronic message contains information from Bluestaq LLC, which may be company sensitive, proprietary, privileged, or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named
above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution, or use of this transmission or its contents is prohibited. Please notify the sender immediately if you have received this transmission in error.</p>
</body>
</html>