<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;}
/* 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";}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;}
span.EmailStyle20
{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'>This looks like an issue specific to PostGIS on Amazon. <o:p></o:p></span></p><p class=MsoNormal><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.<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'>I think this is something they need to fix on their end.<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'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina <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 12:01 AM<br><b>To:</b> postgis-users@lists.osgeo.org<br><b>Subject:</b> [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><p class=MsoNormal>Hi,<o:p></o:p></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>We are currently using PostgreSQL 9.6.11 / PostGIS 2.3.7 on RDS.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>My upgrading procedures are as follows.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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-radius:3px;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION postgis UPDATE;</span></code><span style='font-family:Consolas;color:#24292E'><o:p></o:p></span></pre></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>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-radius:3px;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';<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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><span style='font-family:Consolas;color:#24292E'><o:p></o:p></span></pre></div><div><p class=MsoNormal>There are some warnings appeared, but I think it's not problems...I continued.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-radius:3px;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';<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: unpackaging raster<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>WARNING: PostGIS Raster functionality has been unpackaged<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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;<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ALTER EXTENSION</span></code><span style='font-family:Consolas;color:#24292E'><o:p></o:p></span></pre></div><div><p class=MsoNormal>Then, I tried as follow;<o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-radius:3px;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>SELECT postgis_extensions_upgrade();<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'><o:p> </o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>NOTICE: Packaging extension postgis_raster<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>ERROR: cannot create temporary table within security-restricted operation<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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><span style='font-family:Consolas;color:#24292E'><o:p></o:p></span></pre></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>Current PostGIS version is like below. <o:p></o:p></p></div><div><pre style='background:#F6F8FA;box-sizing:border-box;border-radius:3px;overflow:auto'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>SELECT postgis_full_version();<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>postgis_full_version <o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><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)<o:p></o:p></span></code></pre><pre style='background:#F6F8FA'><code><span style='font-family:Consolas;color:#24292E;border:none windowtext 1.0pt;padding:0in'>(1 row)</span></code><span style='font-family:Consolas;color:#24292E'><o:p></o:p></span></pre></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>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><a href="https://forums.aws.amazon.com/thread.jspa?messageID=938665">https://forums.aws.amazon.com/thread.jspa?messageID=938665</a><o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>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><o:p> </o:p></p></div><div><p class=MsoNormal>Best Regards,<o:p></o:p></p></div><div><p class=MsoNormal>Jin Igarashi<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></div></div></body></html>