Hi Regina, I have one more question.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Platform: Windows Server 2019 Standard PostgreSQL 11.17 x64 PostGIS 3.2.3 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Problem: -- I installed Postgis 3.2.3 for PostgreSQL 11 and I was able to upgrade the postgis extension to 3.2.3 -- the raster extension is still 2.5.2 and just for testing I dropped the raster type -- now I cannot upgrade postgis_raster -- I can restore a backup but I wonder if there is a way to fix this and be able to do the raster extension upgrade -- database has no raster data, hence the goal is to upgrade the postgis_raster extension and then drop the postgis_raster extension -- see details below. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -- The postgis extension was updated fine to PostGIS 3.2.3 -- SELECT postgis_full_version(); "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" GDAL=""GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" (core procs from ""3.2.2 3.2.2"" need upgrade) <== !!! 3.2.2 NEED UPGRADE !!! RASTER (raster lib from ""2.5.2 r17328"" need upgrade) [UNPACKAGED!] (raster procs from ""2.5.2 r17328"" need upgrade)" -- ALTER EXTENSION postgis UPDATE; Query returned successfully in 599 msec. -- SELECT postgis_full_version(); "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" GDAL=""GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" RASTER (raster lib from ""2.5.2 r17328"" need upgrade) [UNPACKAGED!] (raster procs from ""2.5.2 r17328"" need upgrade)" -- ALTER EXTENSION postgis UPDATE; NOTICE: version "3.2.3" of extension "postgis" is already installed ALTER EXTENSION Query returned successfully in 160 msec. -- SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%'; "name" "default_version" "installed_version" "comment" "postgis" "3.2.3" "3.2.3" "PostGIS geometry and geography spatial types and functions" "postgis_raster" "3.2.3" <null> "PostGIS raster types and functions" -- DROP TYPE IF EXISTS public.raster CASCADE; <== DROPPED THE RASTER TYPE WITH CASCADE OPTION !!! I KNOW SHOULD NOT HAVE DONE THAT BUT THIS IS JUST A TEST !!! -- SELECT postgis_extensions_upgrade(); "postgis_extensions_upgrade" "Upgrade completed, run SELECT postgis_full_version(); for details" NOTICE: Extension postgis_raster is not available or not packagable for some reason NOTICE: Extension postgis_sfcgal is not available or not packagable for some reason NOTICE: Extension postgis_topology is not available or not packagable for some reason NOTICE: Extension postgis_tiger_geocoder is not available or not packagable for some reason Successfully run. Total query runtime: 129 msec. 1 rows affected. -- CREATE EXTENSION postgis_raster; ERROR: PostGIS Raster is already installed in schema 'public' CONTEXT: PL/pgSQL function inline_code_block line 10 at RAISE SQL state: P0001 -- ALTER EXTENSION postgis_raster UPDATE; ERROR: extension "postgis_raster" does not exist SQL state: 42704 -- DROP EXTENSION postgis_raster; ERROR: extension "postgis_raster" does not exist SQL state: 42704 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Thanks for the assistance. Marcelo Marques Principal Product Engineer | Esri | www.esri.com On Wed, Aug 31, 2022 at 11:58 AM Marcelo Marques <[email protected]> wrote: > oh my. sorry. forgot the '%' thanks for catching that. Thanks a lot. :) > > > On Wed, Aug 31, 2022 at 11:25 AM Regina Obe <[email protected]> wrote: > >> Marcelo, >> >> >> >> I think it’s your query and below behavior you describe as I understand >> it seems correct. >> >> And your output is correct: >> >> >> >> "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> GDAL=""GDAL 3.4.3, released 2022/04/22 GDAL_DATA not found"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 >> (Internal)"" RASTER" >> >> >> >> Instead of: >> >> >> >> SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis'; >> >> >> >> Should be: >> >> >> >> SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%'; >> >> >> >> And then the postgis_raster should show along with postgis_topology and >> postgis_sfcgap and postgis_tiger_geocoder. >> >> >> >> >> >> *From:* postgis-users [mailto:[email protected]] *On >> Behalf Of *Marcelo Marques >> *Sent:* Wednesday, August 31, 2022 2:03 PM >> *To:* PostGIS Users Discussion <[email protected]> >> *Subject:* Re: [postgis-users] extension "postgis_raster" has no >> installation script nor update path for version "unpackaged" >> >> >> >> >> -- Yes, I have Postgis 3.2.2 for PostgreSQL 11 installed and it already >> contain a file named "postgis--unpackaged--3.2.2.sql" under "C:\Program >> Files\PostgreSQL\11\share\extension" >> >> C:\Program Files\PostgreSQL\11\share\extension>dir postgis--unpackaged*.* >> >> Directory of C:\Program Files\PostgreSQL\11\share\extension >> >> 03/11/2019 12:12 PM 1,475,393 postgis--unpackaged--2.5.2.sql >> 07/30/2022 12:49 AM 1,068,869 postgis--unpackaged--3.2.2.sql >> 08/30/2022 04:14 PM 21 postgis--unpackaged.sql >> >> -- I compared the file C:\Program >> Files\PostgreSQL\11\share\extension\postgis--unpackaged--3.2.2.sql on my >> server >> -- with the one from >> https://winnie.postgis.net/download/windows/pg11/buildbot/extras/postgis--unpackaged--3.2.2.sql >> -- and they are identical. >> -- and still fails to upgrade. >> >> -- SELECT postgis_extensions_upgrade(); >> >> NOTICE: Extension postgis_sfcgal is not available or not packagable for >> some reason >> NOTICE: Packaging extension postgis_raster >> >> ERROR: extension "postgis_raster" has no installation script nor update >> path for version "unpackaged" >> CONTEXT: SQL statement "CREATE EXTENSION postgis_raster SCHEMA public >> VERSION unpackaged;ALTER EXTENSION postgis_raster UPDATE TO "3.2.2"" >> PL/pgSQL function postgis_extensions_upgrade() line 71 at EXECUTE >> SQL state: 22023 >> >> -- I followed you suggestion and installed Postgis 3.2.3 for PostgreSQL 11 >> -- I checked and PostGIS 3.2.3 has the empty file "C:\Program >> Files\PostgreSQL\11\share\extension\postgis--unpackaged.sql" >> -- This file just has text "-- Nothing to do here" >> >> C:\Program Files\PostgreSQL\11\share\extension>dir postgis--unpa*.* >> >> Directory of C:\Program Files\PostgreSQL\11\share\extension >> >> 03/11/2019 12:12 PM 1,475,393 postgis--unpackaged--2.5.2.sql >> 08/19/2022 04:24 PM 1,465,258 postgis--unpackaged--3.2.3.sql >> 08/30/2022 04:14 PM 21 postgis--unpackaged.sql >> >> -- I was able to upgrade the postgis extension >> -- and upgrade the raster extension and then >> -- remove the raster extension and >> -- if I want I can reinstall postgis_raster extension >> >> -- SELECT postgis_full_version(); >> >> "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> GDAL=""GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" >> LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" (core procs from ""3.2.2 >> 3.2.2"" need upgrade) <== !!! 3.2.2 NEED UPGRADE !!! >> RASTER (raster lib from ""2.5.2 r17328"" need upgrade) [UNPACKAGED!] >> (raster procs from ""2.5.2 r17328"" need upgrade)" >> >> -- ALTER EXTENSION postgis UPDATE; >> >> Query returned successfully in 599 msec. >> >> -- SELECT postgis_full_version(); >> >> "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> GDAL=""GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" >> LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" >> RASTER (raster lib from ""2.5.2 r17328"" need upgrade) [UNPACKAGED!] >> (raster procs from ""2.5.2 r17328"" need upgrade)" >> >> -- ALTER EXTENSION postgis UPDATE; >> >> NOTICE: version "3.2.3" of extension "postgis" is already installed >> ALTER EXTENSION >> >> Query returned successfully in 160 msec. >> >> -- SELECT postgis_extensions_upgrade(); >> >> "Upgrade completed, run SELECT postgis_full_version(); for details" >> >> NOTICE: Updating extension postgis from 3.2.2 to 3.2.3 >> NOTICE: Extension postgis_sfcgal is not available or not packagable for >> some reason >> 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 >> NOTICE: Extension postgis_topology is not available or not packagable >> for some reason >> NOTICE: Extension postgis_tiger_geocoder is not available or not >> packagable for some reason >> >> Successfully run. Total query runtime: 1 secs 884 msec. >> 1 rows affected. >> >> -- SELECT postgis_full_version(); >> >> "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> GDAL=""GDAL 3.4.3, released 2022/04/22 GDAL_DATA not found"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 >> (Internal)"" RASTER" >> >> -- SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis'; >> >> "postgis" "3.2.3" "3.2.3" "PostGIS geometry and geography spatial types >> and functions" >> >> -- CREATE EXTENSION postgis_raster; >> >> ERROR: extension "postgis_raster" already exists >> SQL state: 42710 >> >> -- NOTE: if postgis_raster already exists then pg_available_extensions >> should have showed that, seems another bug to fix !!! >> >> -- DROP EXTENSION postgis_raster; >> >> DROP EXTENSION >> >> Query returned successfully in 125 msec. >> >> -- SELECT postgis_full_version(); >> >> "POSTGIS=""3.2.3 3.2.3"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 >> (Internal)""" >> >> --NOTE: RASTER EXTENSION REMOVED !!! >> >> -- Try to install postgis_raster extension >> >> -- CREATE EXTENSION postgis_raster; >> >> CREATE EXTENSION >> >> Query returned successfully in 252 msec. >> >> - SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis'; >> >> "postgis" "3.2.3" "3.2.3" "PostGIS geometry and geography spatial types >> and functions" >> >> --NOTE: pg_available_extensions still does not show postgis_raster but in >> pgAdmin when expand the "Extensions" I can see "postgis" and >> "postgis_raster". >> >> -- DROP EXTENSION postgis_raster; >> >> DROP EXTENSION >> >> Query returned successfully in 125 msec. >> >> >> >> I appreciated your assistance. >> >> >> >> Thanks, >> >> Marcelo >> >> >> >> On Tue, Aug 30, 2022 at 8:42 PM Regina Obe <[email protected]> wrote: >> >> I guess my attachment was too big. >> >> >> >> Here is a link to it if you need it, >> https://winnie.postgis.net/download/windows/pg11/buildbot/extras/postgis--unpackaged--3.2.2.sql >> >> >> >> >> >> but if you are running a patched PostgreSQL 11, you’ll really need to be >> using the PostGIS 3.2.3 install which you can get from application >> stackbuilder or https://download.osgeo.org/postgis/windows/pg11/ >> >> >> >> Hope that helps, >> >> Regina >> >> >> >> >> >> >> >> *From:* Regina Obe [mailto:[email protected]] >> *Sent:* Tuesday, August 30, 2022 8:00 PM >> *To:* 'PostGIS Users Discussion' <[email protected]> >> *Subject:* RE: [postgis-users] extension "postgis_raster" has no >> installation script nor update path for version "unpackaged" >> >> >> >> Attached is the one for 3.2.2 >> >> >> >> But that is in my artifacts so should have already been installed by your >> setup. Do you have the attached file in your share/extension folder? >> >> >> >> *From:* Regina Obe [mailto:[email protected] <[email protected]>] >> *Sent:* Tuesday, August 30, 2022 7:56 PM >> *To:* 'PostGIS Users Discussion' <[email protected]> >> *Subject:* RE: [postgis-users] extension "postgis_raster" has no >> installation script nor update path for version "unpackaged" >> >> >> >> Oh you are running 3.2.2 and not 3.2.3. I know 3.2.3 has this file >> >> >> >> postgis_raster--unpackaged--3.2.3.sql >> >> >> >> which is what you are missing. >> >> >> >> Can you install 3.2.3? Then once you install the binaries, run again and >> it should work. >> >> >> >> >> >> >> >> >> >> >> >> *From:* postgis-users [mailto:[email protected] >> <[email protected]>] *On Behalf Of *Marcelo Marques >> *Sent:* Tuesday, August 30, 2022 7:39 PM >> *To:* PostGIS Users Discussion <[email protected]> >> *Subject:* Re: [postgis-users] extension "postgis_raster" has no >> installation script nor update path for version "unpackaged" >> >> >> >> Hello Regina, >> >> >> >> I tried but the upgrade still failed, see below. >> >> >> >> --create an empty file named "postgis--unpackaged.sql" in your >> share/extension folder of your PostgreSQL install. >> --This file just has text >> -- Nothing to do here >> --C:\Program Files\PostgreSQL\11\share\extension folder >> --And then try your upgrade again. >> >> >> >> --SELECT postgis_extensions_upgrade(); >> >> NOTICE: Extension postgis_sfcgal is not available or not packagable for >> some reason >> NOTICE: Packaging extension postgis_raster >> >> ERROR: extension "postgis_raster" has no installation script nor update >> path for version "unpackaged" >> CONTEXT: SQL statement "CREATE EXTENSION postgis_raster SCHEMA public >> VERSION unpackaged;ALTER EXTENSION postgis_raster UPDATE TO "3.2.2"" >> PL/pgSQL function postgis_extensions_upgrade() line 71 at EXECUTE >> SQL state: 22023 >> >> >> >> --SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis'; >> >> "postgis" "3.2.2" "3.2.2" "PostGIS geometry and geography spatial types >> and functions" >> >> --only the postgis extension is installed!!! >> >> >> >> --CREATE EXTENSION postgis_raster; >> >> >> >> ERROR: PostGIS Raster is already installed in schema 'public' CONTEXT: >> PL/pgSQL function inline_code_block line 10 at RAISE SQL state: P0001 >> >> >> >> --SELECT postgis_full_version(); >> >> >> >> "POSTGIS=""3.2.2 3.2.2"" [EXTENSION] PGSQL=""110"" >> GEOS=""3.10.3-CAPI-1.16.1"" PROJ=""7.2.1"" >> >> GDAL=""GDAL 2.2.4, released 2018/03/19 GDAL_DATA not found"" >> LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 >> (Internal)"" >> >> RASTER (raster lib from ""2.5.2 r17328"" need upgrade) [UNPACKAGED!] >> (raster procs from ""2.5.2 r17328"" need upgrade)" >> >> >> >> >> Thanks for the assistance. >> >> >> >> Marcelo >> >> >> >> On Tue, Aug 30, 2022 at 3:56 PM Regina Obe <[email protected]> wrote: >> >> Marcelo, >> >> >> >> Apologies, this file was left out in my packaging. I’ll create an >> updated package later this week to fix this issue and will also have a >> 3.3.0 which doesn’t have this issue. >> >> >> >> I’ve updated ticket to reflect. >> >> >> >> Copy the attached file (which is essentially an empty file) into your >> >> >> >> C:\Program Files\PostgreSQL\11\share\extension folder >> >> And then try your upgrade again. >> >> >> >> Sorry for the omission, >> >> >> >> Regina >> >> >> >> *From:* postgis-users [mailto:[email protected]] *On >> Behalf Of *Marcelo Marques >> *Sent:* Tuesday, August 30, 2022 6:08 PM >> *To:* [email protected] >> *Subject:* [postgis-users] extension "postgis_raster" has no >> installation script nor update path for version "unpackaged" >> >> >> >> Hello, >> >> >> >> I am trying to get a workaround for the problem below. >> >> >> >> extension "postgis_raster" has no installation script nor update path for >> version "unpackaged" >> >> >> >> see more details in this link >> >> https://trac.osgeo.org/postgis/ticket/5222#ticket >> >> >> >> Have you encountered this issue before? If yes, do you know how to fix it >> ? >> >> >> >> Thanks, >> >> >> >> | *Marcelo Marques *| Esri PS Products | Principal Product Engineer | >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> https://lists.osgeo.org/mailman/listinfo/postgis-users >> >
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
