<div dir="ltr">Hi,<div><br></div><div>We are currently using PostgreSQL 9.6.11 / PostGIS 2.3.7 on RDS.</div><div><br></div><div>Because AWS started to support pg12/PostGIS3.0 recently, we are trying to upgrade our PostGIS database to 3.0.</div><div><br></div><div>Then, I am facing a problem that could not upgrade postgis_raster to 3.0.0. I need some help if possible.</div><div><br></div><div>My upgrading procedures are as follows.</div><div><br></div><div>1. I upgrade postgis from 2.2.5 to 2.3.7 on the current RDS database by following SQL.</div><div><pre style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;background-color:rgb(246,248,250);border-radius:3px;color:rgb(36,41,46)"><code style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;padding:0px;margin:0px;background:initial;border-radius:3px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">ALTER EXTENSION postgis UPDATE;</code></pre></div><div><br></div><div>2. I upgrade PostgreSQL from 9.6.11 to 9.6.17 by AWS management console<br></div><div><br></div><div>3. Then, I upgrade PostGIS from 2.3.7 to 2.5.2 by using following SQL.</div><div><pre style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;background-color:rgb(246,248,250);border-radius:3px;color:rgb(36,41,46)"><code style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;padding:0px;margin:0px;background:initial;border-radius:3px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">ALTER EXTENSION postgis UPDATE TO '2.5.2';
WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect</code></pre></div><div>There are some warnings appeared, but I think it's not problems...I continued.</div><div><br></div><div>4. I upgraded PostgreSQL from 9.6.17 to 12.2 by AWS management console</div><div><br></div><div>5. I upgraded PostGIS from 2.5.2 to 3.0.0 by following SQL.</div><div><br></div><div><pre style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;background-color:rgb(246,248,250);border-radius:3px;color:rgb(36,41,46)"><code style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;padding:0px;margin:0px;background:initial;border-radius:3px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">ALTER EXTENSION postgis UPDATE TO '3.0.0';
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
HINT: type `SELECT postgis_extensions_upgrade(); to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
ALTER EXTENSION</code></pre></div><div>Then, I tried as follow;</div><div><pre style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;background-color:rgb(246,248,250);border-radius:3px;color:rgb(36,41,46)"><code style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;padding:0px;margin:0px;background:initial;border-radius:3px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">SELECT postgis_extensions_upgrade();
NOTICE: Packaging extension postgis_raster
WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect
ERROR: cannot create temporary table within security-restricted operation
CONTEXT: SQL statement "CREATE EXTENSION postgis_raster FROM unpackaged"
PL/pgSQL function postgis_extensions_upgrade() line 48 at EXECUTE</code></pre></div><div>After running "SELECT postgis_extensions_upgrade();", its error appeared and I could not upgrade postgis_raster from 2.5.2 to 3.0.0. </div><div><br></div><div>Current PostGIS version is like below. </div><div><pre style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;margin-top:0px;margin-bottom:16px;padding:16px;overflow:auto;line-height:1.45;background-color:rgb(246,248,250);border-radius:3px;color:rgb(36,41,46)"><code style="box-sizing:border-box;font-family:SFMono-Regular,Consolas,"Liberation Mono",Menlo,monospace;font-size:13.6px;padding:0px;margin:0px;background:initial;border-radius:3px;word-break:normal;border:0px;display:inline;overflow:visible;line-height:inherit">SELECT postgis_full_version();
postgis_full_version
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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)
(1 row)</code></pre></div><div><br></div><div>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.</div><div><br></div><div>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...</div><div><br></div><div>I found a information on AWS Developer Forum, someone faced the same error..but there is no solution on the site.</div><div><a href="https://forums.aws.amazon.com/thread.jspa?messageID=938665">https://forums.aws.amazon.com/thread.jspa?messageID=938665</a><br></div><div><br></div><div>Does anyone have a similar experience of this error? If so, I want to get some advice on how to solve it..</div><div><br></div><div>Best Regards,</div><div>Jin Igarashi</div><div><br></div><div><br></div></div>