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-boun...@lists.osgeo.org] On Behalf Of Jin Igarashi Sent: Friday, May 29, 2020 9:55 AM To: PostGIS Users Discussion <postgis-users@lists.osgeo.org> Cc: postgis-de...@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 <l...@pcorp.us <mailto:l...@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:l...@pcorp.us <mailto:l...@pcorp.us> ] Sent: Friday, May 29, 2020 8:16 AM To: 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org <mailto:postgis-users@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-boun...@lists.osgeo.org] On Behalf Of Jin Igarashi Sent: Friday, May 29, 2020 12:01 AM To: postgis-users@lists.osgeo.org <mailto:postgis-users@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@lists.osgeo.org <mailto:postgis-users@lists.osgeo.org> https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
