[postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to 3.0 on RDS

Fri May 29 06:55:16 PDT 2020

Hi, Regina,

Thank you very much for the information!
Now, I can understand clearly where this problem is coming from.
It's really helpful for us!

Requires
> 1)      Create a new database with both postgis and postgis_raster
> installed.
> 2)      Backup your old database that has postgis_raster installed and
> restore on top of this new database

This is just what I am thinking. Fortunately, we only use one database with
postgis_raster, we can work in this way.
But it's actually very painful, the volume of that database is so huge, it
may take a lot of time to restore...

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!

Have a nice weekend!
Jin

2020年5月29日(金) 21:54 Regina Obe <lr at pcorp.us>:

> Added dev to the thread, since we might be able to change something in
> next release to fix.
>
>
>
> You could try running the below – that is essentially where the script is
> failing, but I suspect this will fail as well.
>
>
>
> CREATE EXTENSION postgis_raster FROM unpackaged;
>
>
>
>
>
>
>
> I think the part of the script that is triggering their error is this part
> of the raster unpackaged script.
>
>
>
> CREATE TEMPORARY TABLE _postgis_upgrade_info AS WITH versions AS (
>
>   SELECT '3.1'::text as upgraded,
>
>   postgis_raster_scripts_installed() as installed
>
> ) SELECT
>
>   upgraded as scripts_upgraded,
>
>   installed as scripts_installed,
>
>   substring(upgraded from '([0-9]*)\.')::int * 100 +
>
>   substring(upgraded from '[0-9]*\.([0-9]*)\.')::int
>
>     as version_to_num,
>
>   substring(installed from '([0-9]*)\.')::int * 100 +
>
>   substring(installed from '[0-9]*\.([0-9]*)\.')::int
>
>     as version_from_num,
>
>   installed ~ 'dev|alpha|beta'
>
>     as version_from_isdev
>
>   FROM versions;
>
>
>
> That’s used to pull the VERSION number of current running version.
>
>
>
> 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.
>
> Given it’s only Amazon having this issue, it may not be worth doing it
> from our end.
>
> 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.
>
>
>
> The other way to work around this issue is more painful.
>
>
>
> Requires
>
> 1)      Create a new database with both postgis and postgis_raster
> installed.
>
> 2)      Backup your old database that has postgis_raster installed and
> restore on top of this new database
>
>
>
> For your databases that don’t use postgis raster, you could just run the
> uninstall_rtpostgis.sql script
>
> Which I have attached.
>
>
>
> Hope that helps,
>
> Regina
>
>
>
>
>
>
>
> *From:* Regina Obe [mailto:lr at pcorp.us]
> *Sent:* Friday, May 29, 2020 8:16 AM
> *To:* 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
> *Subject:* RE: [postgis-users] Failed to upgrade PostGIS Raster from
> 2.2.5 to 3.0 on RDS
>
>
>
> This looks like  an issue specific to PostGIS on Amazon.
>
> 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.
>
>
>
> I think this is something they need to fix on their end.
>
>
>
> Thanks,
>
> Regina
>
>
>
> *From:* postgis-users [mailto:postgis-users-bounces at lists.osgeo.org
> <postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Jin Igarashi
> *Sent:* Friday, May 29, 2020 12:01 AM
> *To:* postgis-users at lists.osgeo.org
> *Subject:* [postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to
> 3.0 on RDS
>
>
>
> Hi,
>
>
>
> We are currently using PostgreSQL 9.6.11 / PostGIS 2.3.7 on RDS.
>
>
>
> Because AWS started to support pg12/PostGIS3.0 recently, we are trying to
> upgrade our PostGIS database to 3.0.
>
>
>
> Then, I am facing a problem that could not upgrade postgis_raster to
> 3.0.0. I need some help if possible.
>
>
>
> My upgrading procedures are as follows.
>
>
>
> 1. I upgrade postgis from 2.2.5 to 2.3.7 on the current RDS database by
> following SQL.
>
> ALTER EXTENSION postgis UPDATE;
>
>
>
> 2. I upgrade PostgreSQL from 9.6.11 to 9.6.17 by AWS management console
>
>
>
> 3. Then, I upgrade PostGIS from 2.3.7 to 2.5.2 by using following SQL.
>
> 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
>
> There are some warnings appeared, but I think it's not problems...I
> continued.
>
>
>
> 4. I upgraded PostgreSQL from 9.6.17 to 12.2 by AWS management console
>
>
>
> 5. I upgraded PostGIS from 2.5.2 to 3.0.0 by following SQL.
>
>
>
> 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
>
> Then, I tried as follow;
>
> 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
>
> After running "SELECT postgis_extensions_upgrade();", its error appeared
> and I could not upgrade postgis_raster from 2.5.2 to 3.0.0.
>
>
>
> Current PostGIS version is like below.
>
> 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)
>
>
>
> 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.
>
>
>
> 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...
>
>
>
> I found a information on AWS Developer Forum, someone faced the same
> error..but there is no solution on the site.
>
> https://forums.aws.amazon.com/thread.jspa?messageID=938665
>
>
>
> Does anyone have a similar experience of this error? If so, I want to get
> some advice on how to solve it..
>
>
>
> Best Regards,
>
> Jin Igarashi
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200529/6339b536/attachment.html>