[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] [External] Re: Upgrade 12->13 stuck due to postgis / raster issue
From: Allan Chase <allan.chase () bluestaq ! com>
Date: 2023-01-24 6:52:40
Message-ID: MWHPR1301MB20646E99BD175A27B4E59F6F80C99 () MWHPR1301MB2064 ! namprd13 ! prod ! outlook ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
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" ☹. 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.
Allan Chase
Data Engineer
From: postgis-users <postgis-users-bounces@lists.osgeo.org> On Behalf Of Jim \
VanPeursem
Sent: Monday, January 23, 2023 6:32 PM
To: PostGIS Users Discussion <postgis-users@lists.osgeo.org>
Subject: [External] Re: [postgis-users] Upgrade 12->13 stuck due to postgis / raster \
issue
**************************
ATTENTION - External Email
**************************
Please verify the sender before taking any actions or clicking any links.
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.
Hi Roxanne,
Thanks for the help. Here's what I see when I follow the normal postgis extension \
update path: 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
(1 row)
db=> select postgis_full_version();
\
postgis_full_version
-------------------------------------------------------------------------------------- \
-------------------------------------------------------------------------------------- \
-------------------------------------------------------------------------------------- \
----------------------------------------------------------------------------------- \
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) (1 row)
No luck.
->jvp
On Mon, Jan 23, 2023 at 5:30 PM Roxanne Reid-Bennett \
<rox@tara-lu.com<mailto:rox@tara-lu.com>> wrote:
Jim,
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
SELECT PostGIS_Extensions_Upgrade();
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.
There was one time where running
SELECT PostGIS_Extensions_Upgrade();
followed by
select postgis_full_version();
indicated we had to run the Extensions Upgrade AGAIN.
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.
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?
Roxanne
On 1/23/2023 4:03 PM, Raj Talati wrote:
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.
I guess whoever did PG 12 upgrade not did alter extension Postgis update and when now \
you trying that missed prior is giving error.
Most likely this the case.
Good luck
On Mon, Jan 23, 2023 at 6:15 PM Jim VanPeursem <jvp@jvp.llc><mailto:jvp@jvp.llc> \
wrote: 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<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org<mailto:postgis-users@lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
--
______________________________
Jim VanPeursem, PhD
http://jvp.llc<http://jvpcoaching.com/>/
us: +1 847 414 2759 (+ WhatsApp) -- skype: jimvanpeursem
Bringing clarity and helping you go from where you are to where you want to be
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.
[Attachment #3 (text/html)]
<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>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
--===============4426999343576039648==--
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic