<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:"MS Gothic";
panose-1:2 11 6 9 7 2 5 8 2 4;}
@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:"\@MS Gothic";
panose-1:2 11 6 9 7 2 5 8 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
code
{mso-style-priority:99;
font-family:"Courier New";}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0in;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.gmail-m-2698229643303687711msolistparagraph, li.gmail-m-2698229643303687711msolistparagraph, div.gmail-m-2698229643303687711msolistparagraph
{mso-style-name:gmail-m_-2698229643303687711msolistparagraph;
mso-margin-top-alt:auto;
margin-right:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@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><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>One more note I forgot<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For the case where you are using raster. Try to do <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SELECT postgis_extensions_upgrade();<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>After your restore. This is needed especially if you are using the backup after you had tried to upgrade (which removed the raster functions from postgis extension), your backup would container these functions since they are loose and might overwrite the newer functions in postgis_raster extension. I don’t think Amazon let’s you create C functions loosely, so probably would fail on those calls anyway. So just a precaution.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <b>On Behalf Of </b>Jin Igarashi<br><b>Sent:</b> Friday, May 29, 2020 9:55 AM<br><b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br><b>Cc:</b> postgis-devel@lists.osgeo.org<br><b>Subject:</b> Re: [postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to 3.0 on RDS<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><div><p class=MsoNormal>Hi, Regina,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thank you very much for the information!<o:p></o:p></p></div><div><p class=MsoNormal>Now, I can understand clearly where this problem is coming from.<o:p></o:p></p></div><div><p class=MsoNormal>It's really helpful for us!<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>Requires<br>1) Create a new database with both postgis and postgis_raster installed.<br>2) Backup your old database that has postgis_raster installed and restore on top of this new database<o:p></o:p></p></blockquote></div><div><p class=MsoNormal>This is just what I am thinking. Fortunately, we only use one database with postgis_raster, we can work in this way.<o:p></o:p></p></div><div><p class=MsoNormal>But it's actually very painful, the volume of that database is so huge, it may take a lot of time to restore...<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Thank you for the script which can uninstall raster functions from the database. I was also wondering how I can delete old postgis_raster functions from those databases...I could use your attached script!<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Have a nice weekend!<o:p></o:p></p></div><div><p class=MsoNormal>Jin<o:p></o:p></p></div></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>2020<span style='font-family:"Calibri",sans-serif'>年</span>5<span style='font-family:"Calibri",sans-serif'>月</span>29<span style='font-family:"Calibri",sans-serif'>日</span>(<span style='font-family:"Calibri",sans-serif'>金</span>) 21:54 Regina Obe <<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>>:<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><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Added dev to the thread, since we might be able to change something in next release to fix.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>You could try running the below – that is essentially where the script is failing, but I suspect this will fail as well.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE EXTENSION postgis_raster FROM unpackaged;</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think the part of the script that is triggering their error is this part of the raster unpackaged script.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>CREATE TEMPORARY TABLE _postgis_upgrade_info AS WITH versions AS (</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> SELECT '3.1'::text as upgraded,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> postgis_raster_scripts_installed() as installed</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>) SELECT</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> upgraded as scripts_upgraded,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> installed as scripts_installed,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> substring(upgraded from '([0-9]*)\.')::int * 100 +</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> substring(upgraded from '[0-9]*\.([0-9]*)\.')::int</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> as version_to_num,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> substring(installed from '([0-9]*)\.')::int * 100 +</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> substring(installed from '[0-9]*\.([0-9]*)\.')::int</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> as version_from_num,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> installed ~ 'dev|alpha|beta'</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> as version_from_isdev</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> FROM versions;</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>That’s used to pull the VERSION number of current running version.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>We (PostGIS Development Group) could change that to a declare variable at the top since everthing is wrapped in a big DO anyway, without too much fuss. </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Given it’s only Amazon having this issue, it may not be worth doing it from our end.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Even if we do it on our end, who knows how long it would take Amazon to apply the fix and unpackaged won’t be available in PG13 so won’t work moving forward for anyone.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The other way to work around this issue is more painful.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Requires </span><o:p></o:p></p><p class=gmail-m-2698229643303687711msolistparagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>1)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Create a new database with both postgis and postgis_raster installed.</span><o:p></o:p></p><p class=gmail-m-2698229643303687711msolistparagraph><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>2)</span><span style='font-size:7.0pt;color:#1F497D'> </span><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Backup your old database that has postgis_raster installed and restore on top of this new database</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>For your databases that don’t use postgis raster, you could just run the uninstall_rtpostgis.sql script</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Which I have attached.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Hope that helps,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>] <br><b>Sent:</b> Friday, May 29, 2020 8:16 AM<br><b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br><b>Subject:</b> RE: [postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to 3.0 on RDS</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>This looks like an issue specific to PostGIS on Amazon. </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The only work arounds I can think of such as first upgrading the PostGIS raster with the script approach would not work on Amazon because of the security restrictions they have put in place.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I think this is something they need to fix on their end.</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'> </span><o:p></o:p></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">mailto:postgis-users-bounces@lists.osgeo.org</a>] <b>On Behalf Of </b>Jin Igarashi<br><b>Sent:</b> Friday, May 29, 2020 12:01 AM<br><b>To:</b> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br><b>Subject:</b> [postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to 3.0 on RDS</span><o:p></o:p></p></div></div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi,<o:p></o:p></p><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>We are currently using PostgreSQL 9.6.11 / PostGIS 2.3.7 on RDS.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Because AWS started to support pg12/PostGIS3.0 recently, we are trying to upgrade our PostGIS database to 3.0.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Then, I am facing a problem that could not upgrade postgis_raster to 3.0.0. I need some help if possible.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>My upgrading procedures are as follows.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>1. I upgrade postgis from 2.2.5 to 2.3.7 on the current RDS database by following SQL.<o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-top-left-radius:3px;border-top-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;background-position:initial initial;background-repeat:initial initial;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION postgis UPDATE;</span></code><o:p></o:p></pre></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>2. I upgrade PostgreSQL from 9.6.11 to 9.6.17 by AWS management console<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>3. Then, I upgrade PostGIS from 2.3.7 to 2.5.2 by using following SQL.<o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-top-left-radius:3px;border-top-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;background-position:initial initial;background-repeat:initial initial;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION postgis UPDATE TO '2.5.2';</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>There are some warnings appeared, but I think it's not problems...I continued.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>4. I upgraded PostgreSQL from 9.6.17 to 12.2 by AWS management console<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>5. I upgraded PostGIS from 2.5.2 to 3.0.0 by following SQL.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-top-left-radius:3px;border-top-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;background-position:initial initial;background-repeat:initial initial;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION postgis UPDATE TO '3.0.0';</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: unpackaging raster</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: PostGIS Raster functionality has been unpackaged</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>HINT: type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION</span></code><o:p></o:p></pre></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Then, I tried as follow;<o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-top-left-radius:3px;border-top-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;background-position:initial initial;background-repeat:initial initial;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>SELECT postgis_extensions_upgrade();</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'> </span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>NOTICE: Packaging extension postgis_raster</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ERROR: cannot create temporary table within security-restricted operation</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE</span></code><o:p></o:p></pre></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>After running "SELECT postgis_extensions_upgrade();", its error appeared and I could not upgrade postgis_raster from 2.5.2 to 3.0.0. <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Current PostGIS version is like below. <o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-top-left-radius:3px;border-top-right-radius:3px;border-bottom-right-radius:3px;border-bottom-left-radius:3px;background-position:initial initial;background-repeat:initial initial;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>SELECT postgis_full_version();</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>postgis_full_version </span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'> POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.8.0-CAPI-1.13.1 " 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.4.3 (Internal)" RASTER (raster lib from "2.5.2 r17328" need upgrade) [UNPACKAGED!] (raster procs from "2.5.2 r17328" need upgrade)</span></code><o:p></o:p></pre><pre style='background:#F6F8FA;background-position:initial initial;background-repeat:initial initial'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>(1 row)</span></code><o:p></o:p></pre></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Although most of our databases do not use raster functions, a database is using raster functions. I found upgraded databases on PostGIS3.0 is working very well apart from a database which is using postgis_raster.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I tried to search the solution on how to upgrade postgis_raster, but I could not find any information about it. It is maybe some particular problem of RDS, I don't know...<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I found a information on AWS Developer Forum, someone faced the same error..but there is no solution on the site.<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><a href="https://forums.aws.amazon.com/thread.jspa?messageID=938665" target="_blank">https://forums.aws.amazon.com/thread.jspa?messageID=938665</a><o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Does anyone have a similar experience of this error? If so, I want to get some advice on how to solve it..<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Best Regards,<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Jin Igarashi<o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div></div></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></div></div></body></html>