Unable to upgrade PostGIS from 2.x to 3.2.1 on PostgreSQL 9.6.x

[milosh]

Trying to upgrade PostGIS 2.3.7 or 2.5.2 enabled PostgreSQL 9.6 database (tried both PostGIS versions) fails when running:

SELECT postgis_extensions_upgrade();

with:

NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason
NOTICE:  Packaging extension postgis_raster

ERROR:  could not stat file "C:/Program Files/PostgreSQL/9.6/share/extension/postgis_raster--unpackaged.sql": No such file or directory
CONTEXT:  SQL statement "CREATE EXTENSION postgis_raster SCHEMA public VERSION unpackaged;ALTER EXTENSION postgis_raster UPDATE TO "3.2.1""
PL/pgSQL funkce postgis_extensions_upgrade() řádek 71 na EXECUTE
SQL state: 58P01

postgis full version gives (for PostGIS 2.5.2 database):

"POSTGIS="3.2.1 3.2.1" [EXTENSION] PGSQL="96" GEOS="3.10.2-CAPI-1.16.0" PROJ="7.2.1" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" TOPOLOGY (topology procs from "2.5.2 r17328" need upgrade) RASTER (raster lib from "2.5.2 r17328" need upgrade) [UNPACKAGED!] (raster procs from "2.5.2 r17328" need upgrade)"

and for PostGIS 2.3.7 database:

"POSTGIS="3.2.1 3.2.1" [EXTENSION] PGSQL="96" GEOS="3.10.2-CAPI-1.16.0" PROJ="7.2.1" GDAL="GDAL 2.2.4, released 2018/03/19" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER (raster lib from "2.3.7 r16523" need upgrade) [UNPACKAGED!] (raster procs from "2.3.7 r16523" need upgrade)" }}}

Raster functions are not used. Any idea? Hard reload somehow?

[robe]

I think I ran into this issue when upgrading an old database. I thought it was just because I was using a dev version, as I could find the file.

I think to work around the problem I did these steps:

CREATE EXTENSION postgis_raster FROM unpackaged;
ALTER EXTENSION postgis_raster UPDATE;
DROP EXTENSION postgis_raster; --since you don't need it

SELECT postgis_extensions_upgrade();

[robe]

I suspect that maybe for PostgreSQL 9.6 our

CREATE EXTENSION postgis_raster SCHEMA public VERSION unpackaged;

doesn't work or maybe the unpackaged part needs to be quoted. But anyway since you are using PostgreSQL < 13, the From unpackaged step should work instead of trying to use fake unpackaged version.

@strk, I'm surprised we haven't run into this issue in any of our bots.

[milosh]

When trying

CREATE EXTENSION postgis_raster from unpackaged

I get:

2022-04-23 08:19:32 CEST ERROR:  could not load library "C:/Program Files/PostgreSQL/9.6/lib/postgis_raster-3.dll": The specified procedure could not be found.
2022-04-23 08:19:32 CEST STATEMENT:  CREATE EXTENSION postgis_raster from unpackaged

Sysinternals Procmon does not show anything special.

As a workaround, should I backup the database, then drop it, create new one (calling CREATE EXTENSION POSTGIS) and than just do a restore?

[milosh]

I have solved the issue somehow.

I remember that when updating PostgreSQL 9.6 minor versions I needed in the past to manually copy libeay32.dll and ssleay32.dll from bin/postgisgui to PostgreSQL bin directory - otherwise I would get errors like "rtpostgis-2.3.dll: %1 is not a valid Win32 application".

I tried to install ​http://download.osgeo.org/postgis/windows/pg96/archive/postgis-bundle-pg96x64-setup-3.0.1-1.exe just to test if there is not some issue with DLLs and figured out that zlib1.dll was not found in PATH.

So I copied zlib1.dll again from bin/postgisgui into PostgreSQL bin directory and then

CREATE EXTENSION postgis_raster from unpackaged;

just worked.

So I repeated those steps with ​http://download.osgeo.org/postgis/windows/pg96/postgis-bundle-pg96x64-setup-3.2.1-1.exe, i.e. again after install manually copied libeay32.dll, ssleay32.dll and zlib1.dll from bin/postgisgui into bin/ and voila:

CREATE EXTENSION postgis_raster from unpackaged;

also worked.

So the update path from PostGIS 2.3.7 to 3.2.1 in my case is:

1/ Install ​http://download.osgeo.org/postgis/windows/pg96/postgis-bundle-pg96x64-setup-3.2.1-1.exe

2/ Manually copy libeay32.dll, ssleay32.dll and zlib1.dll from bin/postgisgui into bin/

3/ Run:

ALTER EXTENSION postgis UPDATE;
CREATE EXTENSION postgis_raster from unpackaged;
SELECT postgis_extensions_upgrade();
SELECT postgis_extensions_upgrade();

Than I get:

Upgrade completed, run SELECT postgis_full_version(); for details

And SELECT postgis_full_version() gives:

POSTGIS="3.2.1 3.2.1" [EXTENSION] PGSQL="96" GEOS="3.10.2-CAPI-1.16.0" PROJ="7.2.1" GDAL="GDAL 3.4.2, released 2022/03/08 GDAL_DATA not found" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER

I think we can close this ticket as WONTFIX.

[robe]

hmm well that's a bit concerning you had to copy those files? Are you sure you needed to do that, or you had originally tried to do that?

libeay32.dll and ssleay32.dll were replaced with libcrypto-1_1-x64.dll and libssl-1_1-x64.dll a while ago, so you should not have any PostGIS code that relies on those anymore. Your 2.3.7 did, but 3.2.1 should not. Also I think around that time of 9.6, EDB also replaced libeay32.dll and ssleay32.dll with libcrypto-1_1-x64.dll and libssl-1_1x64.dll, so if you are running the latest micro of 9.6, your PostgreSQL 9.6 shouldn't have a dependency on that.

zlib1 is included as part of PostgreSQL 9.6, so you shouldn't need to copy that into bin either.

What does your

SELECT version();

return.

[milosh]

SELECT version()

returns:

"PostgreSQL 9.6.20, compiled by Visual C++ build 1800, 64-bit"

It is possible that ssleay32 and libeay32 DLLs copy was not needed. I have not tried that. It had to do that every time when installing update to PostgreSQL minor version to prevent

"rtpostgis-2.3.dll: %1 is not a valid Win32 application". 

error.

On the other hand without copying zlib1.dll, I got the error:

"C:/Program Files/PostgreSQL/9.6/lib/postgis_raster-3.dll":
The specified procedure could not be found.

when running

CREATE EXTENSION postgis_raster from unpackaged

under PostGIS 3.2.1 and

"C:/Program Files/PostgreSQL/9.6/lib/postgis_raster-3.dll":
The specified module could not be found.

when running the same command under PostGIS 3.0.1-1. While looking into Sysinternals Procmon I observed that zlib1.dll was not loaded successfuly from PostgreSQL bin directory during execution of the above command, so I tried to overwrite it with zlib1.dll from postgisgui directory and that worked.

Not sure why, it's hard to debug such errors.