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

Fri May 29 07:36:49 PDT 2020

One more note I forgot

For the case where you are using raster.  Try to do 

SELECT postgis_extensions_upgrade();

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.

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jin Igarashi
Sent: Friday, May 29, 2020 9:55 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Cc: postgis-devel at lists.osgeo.org
Subject: Re: [postgis-users] Failed to upgrade PostGIS Raster from 2.2.5 to 3.0 on RDS

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 <mailto: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 <mailto:lr at pcorp.us> ] 
Sent: Friday, May 29, 2020 8:16 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org <mailto: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] On Behalf Of Jin Igarashi
Sent: Friday, May 29, 2020 12:01 AM
To: postgis-users at lists.osgeo.org <mailto: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 <mailto: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-devel/attachments/20200529/403eb056/attachment-0001.html>