[postgis-users] extension "postgis_raster" has no installation script nor update path for version "unpackaged"

[Marcelo Marques]

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 |  

[Regina Obe]

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

[Marcelo Marques]

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

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users

[Regina Obe]

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.

[Regina Obe]

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:l...@pcorp.us]

Sent: Tuesday, August 30, 2022 8:00 PM
To: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>

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:l...@pcorp.us]
Sent: Tuesday, August 30, 2022 7:56 PM
To: 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>
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:postgis-us...@lists.osgeo.org] On Behalf Of Marcelo Marques
Sent: Tuesday, August 30, 2022 7:39 PM
To: PostGIS Users Discussion <postgi...@lists.osgeo.org

>

[Marcelo Marques]

-- 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

[Regina Obe]

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.

[Marcelo Marques]

oh my. sorry. forgot the '%'  thanks for catching that. Thanks a lot. :)

[Marcelo Marques]

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

[Regina Obe]

Is this a different database?

 

I thought your other one showed raster fully installed and on version 3.2.3.

 

Try doing:

 

CREATE EXTENSION postgis_raster VERSION 'unpackaged';

 

ALTER EXTENSION postgis_raster UPDATE;

[Marcelo Marques]

Hi Regina,

Yes, it is a different database. The older one the upgrade worked fine.

I am testing on this database what happens if the customer my mistake drops the raster type and then tries to upgrade.

-- DROP TYPE IF EXISTS public.raster CASCADE; 

I ran the commands you mentioned but still got the error message "type raster does not exist".

-- CREATE EXTENSION postgis_raster VERSION 'unpackaged';

ERROR:  type raster does not exist

SQL state: 42704

-- ALTER EXTENSION postgis_raster UPDATE;

ERROR:  extension "postgis_raster" does not exist
SQL state: 42704

Any idea on how to be able to recreate the raster type and be able to upgrade ???

Thanks,

Marcelo

[Regina Obe]

Well if you did that, you destroyed all your raster data anyway. 

If you had no raster data, then just run the uninstall_rtpostgis.sql

 

Which is located in – C:\Program Files\PostgreSQL\11\share\contrib\postgis-3.2\ uninstall_rtpostgis.sql

 

That should clean up the mess and allow you to do regular after if you still want the extension.

 

CREATE EXTENSION postgis_raster;

 

If you did have raster data, then you’d need to restore that from backup.  If many tables, probably the cleanest is to

 

1) Create a new database and do: CREATE EXTENSION postgis; CREATE EXTENSION postgis_raster;

2) Then restore your database backup into this new database.

 

Note for cases where you have raster data and need to restore from backup, you need to do CREATE EXTENSION postgis_raster;  since that won’t be in your backup and would fail restore if it can’t find the raster type.

 

For databases with only vector, then doing a restore without running any CREATE EXTENSION should work fine, since CREATE EXTENSION postgis; would be included in your backup.

[Marcelo Marques]

Hi Regina,

It resolved the problem.

--SOLUTION:

-- uninstall postgis_raster
psql
\c pm
\i 'C:\\Program Files\\PostgreSQL\\11\\share\\contrib\\postgis-3.2\\uninstall_rtpostgis.sql'

-- 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)"""

-- CREATE EXTENSION postgis_raster;

CREATE EXTENSION

Query returned successfully in 310 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 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%';

"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" "3.2.3" "PostGIS raster types and functions"

-- DROP EXTENSION postgis_raster;

DROP EXTENSION

Query returned successfully in 162 msec.

Thanks a lot.

Marcelo :)