[postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0

[Calle Hedberg]

Hi,

I just updated postgresql 13 and pg 14 (running on the D-drive under Windows 10 64 bits) from 3.14 to 3.2.0. (in order to install 3.2, I had to first remove 3.1.4 - then I installed 3.2.0 using Stackbuilder run as administrator).

When running "create extension postgis;" in pgAdmin, I get as expected a message that extension postgis already exists. But it actually does not exist/start up - if I run e.g. "select postgis_full_version();", it returns

ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: SQL statement "SELECT public.postgis_lib_version()" PL/pgSQL function postgis_full_version() line 26 at SQL statement SQL state: 58P01

If I run "ALTER EXTENSION postgis  UPDATE TO "3.2.0"; "  I get a similar message:

ERROR: could not access file "$libdir/postgis-3": No such file or directory CONTEXT: PL/pgSQL function _postgis_drop_function_if_needed(text,text) line 6 at FOR over SELECT rows SQL state: 58P01

I can force the issue by dropping the postgis extension and recreate it, but then I have to use drop extension postgis cascade and that command will wipe out the geometry fields in the database (dropping ext postgis on the template postgres db work fine, but that db does not have any geometry fields).

I have tried to re-start pg, reboot the machine, and googling the issue, to no avail.

I can see that postgis 3.2.0 has been installed:

D:\Program Files\PostgreSQL\13\share\contrib\postgis-3.2

I see the error message states it cannot find postgis-3  - but there IS no such file or directory, as you can see the directory is actually called postgis-3.2 . But I don't know if that's a bug or what...

Any suggestions - or will I have to dump all my databases and then re-install pg 13 and pg14 afresh?

Best regards

Calle

[Regina Obe]

Hmm okay it looks like I forgot to take off the minor version in my release so all the libs

 

Have -3.2 on them instead of -3.  I guess I didn’t notice cause I usually just install the new version over the old.

And then run

 

SELECT postgis_extensions_upgrade();

 

Though I’m still surprised it’s giving an error as I thought we fixed that issue a long time ago to handle a case where the lib file has been removed.

So that seems like a reemerging old bug.

 

That said , while I’m making a new package.  Can you do the following:

 

First try if:

-- works without doing anything else

SELECT postgis_extensions_upgrade();

 

If the above still gives you an error, do the following

 

Reinstall PostGIS 3.1.4

Reinstall PostGIS 3.2.0

Then run

 

SELECT postgis_extensions_upgrade();

 

In each of your databases.

[Regina Obe]

Side note I’ve ticketed the issue here:

 

https://trac.osgeo.org/postgis/ticket/5045

 

and will update once I release a new package

 

 

From: Regina Obe [mailto:l...@pcorp.us]
Sent: Monday, January 10, 2022 10:21 PM
To: 'calle....@gmail.com' <calle....@gmail.com>; 'PostGIS Users Discussion' <postgi...@lists.osgeo.org>
Subject: RE: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0

 

Hmm okay it looks like I forgot to take off the minor version in my release so all the libs

 

Have -3.2 on them instead of -3.  I guess I didn’t notice cause I usually just install the new version over the old.

And then run

 

SELECT postgis_extensions_upgrade();

 

Though I’m still surprised it’s giving an error as I thought we fixed that issue a long time ago to handle a case where the lib file has been removed.

So that seems like a reemerging old bug.

 

That said , while I’m making a new package.  Can you do the following:

 

First try if:

-- works without doing anything else

SELECT postgis_extensions_upgrade();

 

If the above still gives you an error, do the following

 

Reinstall PostGIS 3.1.4

Reinstall PostGIS 3.2.0

Then run

 

SELECT postgis_extensions_upgrade();

 

In each of your databases.

 

 

 

From: postgis-users [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Calle Hedberg
Sent: Monday, January 10, 2022 9:53 PM
To: PostGIS Users Discussion <postgi...@lists.osgeo.org>
Subject: [postgis-users] PostGIS problem after updating from 3.1.4 to 3.2.0

 

Hi,

[Calle Hedberg]

Dear Regina,

Just running SELECT postgis_extensions_upgrade(); did not work.

I reinstalled PostGIS 3.1.4, and verified that it was functional.

I then reinstalled 3.2.0 on top of that, this time successfully (not sure why I had to remove 3.1.4 the first time, but...), and then ran

SELECT postgis_extensions_upgrade();
SELECT postgis_full_version();

on all databases, and that worked OK.

So I will do the same for PG14 and ditto on my other two systems. It's a bit time consuming since I have 150-200 databases in total, so if you can fix that bug so that there is no need to run the extension upgrade command on every db it would be great.. I've got one pg10 and one pg12 installation too to cater for some backward compatibility and to provide an upgrade path for old databases, but I'm leaving those on 3.0 / 3.1

Thanks again for the rapid response and the clear instructions.

Best regards

Calle

--

Carl-Anders (Calle) Hedberg

HISP

Researcher & Technical Specialist

Health Information Systems Programme – South Africa

Cell:        +47 41461011 (Norway)

Iridium SatPhone: +8816-315-19119 (usually OFF)

E-mail1: ca...@hisp.org

E-mail2: calle....@gmail.com

Skype:  calle_hedberg

[Regina Obe]

I have the updated versions for (13, 14) below.  It should be on stack builder as well shortly (but there is a lag there since it needs to propagate to the mirrors).  I’ll have the others up later today.

13

https://download.osgeo.org/postgis/windows/pg13/

 

14

https://download.osgeo.org/postgis/windows/pg14/

 

 

You should be running the extension upgrade command on every db for every upgrade including micros. 

It’s okay to run it after, but some things may not work right for minors until you do. For micro, you may miss SQL patches (not as big of a deal since not too many changes are in the plpgsql except possibly for upgrade fixes).

 

Every once in a well we remove internal functions and replace with new ones in minors. The old ones are stubbed with an  error message.

I don’t think that happened in 3.2 but might have.

You also won’t be getting any new functions until you do an extension upgrade.

 

Do you have all the databases on one cluster or you have a database per cluster?

 

If you have that many databases on a single cluster, you can save yourself a bit of work by generating an SQL script to do it as follows:

 

1) First launch psql (should be in start menu or you can launch the one from pgAdmin4- PSQL menu option)

2) This generates an sql connect and tries to upgrade all databases except postgres, template0, template.

It will error in cases if there is no postgis installed or version lower than 2.5 is installed. You might need to change the C:/temp path if you don’t have a temp folder there.

 

\t\a

\o C:/temp/postgis_upgrade.sql

SELECT '\connect ' || datname || E'\n' || 'SELECT postgis_extensions_upgrade(); '

FROM pg_database

WHERE datname NOT IN('postgres', 'template0', 'template1')

ORDER BY datname;

\o

\i C:/temp/postgis_upgrade.sql

 

 

For the ones where you did just run an extension upgrade, it won’t upgrade again, but if it bothers you it is pointing at a 3.2.dll instead of a 3.dll, you can force an upgrade with

 

ALTER EXTENSION postgis UPDATE TO “3.2.0next”;

ALTER EXTENSION postgis UPDATE TO “3.2.0”;

 

Hope that helps,

Regina