Hi, Regina, Thank you! I noted to upgrade postgis_extensions after restoring the database which uses postgis_raster.
By the way, I just run your script trying to remove raster functions from our databases which do not use postgis_raster. However, the script failed...maybe our PostGIS version 2.3.11 is too old? Please find the attached log file after running uninstall_rtpostgis.sql script. Best regards, Jin 2020年5月29日(金) 23:36 Regina Obe <l...@pcorp.us>: > 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>: > > 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] > *Sent:* Friday, May 29, 2020 8:16 AM > *To:* 'PostGIS Users Discussion' <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 > <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 > *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 > 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
uninstall_rtpostgis.log
Description: Binary data
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users
