Update postgis in a replicated PostgreSQL 12 database now with postgis-3

[Andrew Hardy]

Hi,

I am looking for some guidance as to the correct steps in addition to ALTER
EXTENSION postgis UPDATE; to correctly and fully update postgis in a
replicated PostgreSQL 12 database on red Hat 9 which came with postgis-3.

We created a new host in order to upgrade to Red Hat 9.  We installed
PostgreSQL 12 same as the original host and set up replication from the
original host to create the DB schema and data on the new host database.

We noticed some odd failures executing a SELECT on a table with a point
type column.  The error was: 'could not access file "$libdir/postgis-2.5":
No such file or directory' .
We use DBeaver. postgis properties in the DBeaver extension folder showed
version 2.5 and attempting to open the function folder resulted in the same
error above.

Red Hat 9 came with these postgis libraries / extensions
postgis-3.so
postgis_raster-3.so
postgis_sfcgal-3.so
postgis_topology-3.so
postgres_fdw.so

under /usr/pgsql-12/lib

Therefore after running ALTER EXTENSION postgis UPDATE; the table with a
point type column became accessible, the postgis extension version updated
and the functions were available in the DBeaver functions folder.  I
thought this was the whole solution.

However at a later point another activity appeared to reveal that we had
not fully fixed the issue.

The background is this:  I have been researching locally (WSL Ubuntu)
upgrading to PostgreSQL 16 from 12.
I ran pg_dump on the Redhat 9 host's PostgreSQL 12 database in order to
create schema and data in a local database.
The created dump script included lots of references to
'$libdir/rtpostgis-2.5'
When this is run on the local PostgreSQL 16 server's database we get lots
of not found errors for '$libdir/rtpostgis-2.5'

Locally PostgreSQL 16 server includes these postgis libraries / extensions:
plpgsql.so
postgis-3.so
postgis_raster-3.so
postgis_sfcgal-3.so
postgis_topology-3.so
postgres_fdw.so

under /usr/lib/postgresql/16/lib/

This led me to think that on the Red Hat 9 host we had still not fully
fixed postgis since the replication and that some other postgis libraries /
extensions may need updating.

ALTER EXTENSION postgis_raster UPDATE;
results in
SQL Error [42704]: ERROR: extension "postgis_raster" does not exist

So I tried
CREATE EXTENSION postgis_raster;
which results in
SQL Error [P0001]: ERROR: PostGIS Raster is already installed in schema
'public'
  Where: PL/pgSQL function inline_code_block line 10 at RAISE

SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%';
results in
"name","default_version","installed_version","comment"
postgis,"3.4.2","3.4.2",PostGIS geometry and geography spatial types and
functions
postgis_raster,"3.4.2",NULL,PostGIS raster types and functions
postgis_sfcgal,"3.4.2",NULL,PostGIS SFCGAL functions
postgis_tiger_geocoder,"3.4.2",NULL,PostGIS tiger geocoder and reverse
geocoder
postgis_topology,"3.4.2",NULL,PostGIS topology spatial types and functions

executing
SELECT proname, probin FROM pg_proc WHERE proname LIKE '%raster%' LIMIT 10;
one can see that where probin is not NULL it is '$libdir/rtpostgis-2.5'

I am confused why ALTER EXTENSION postgis UPDATE; was successful
but ALTER EXTENSION postgis_raster UPDATE; was not.

I am looking for some guidance as to the correct steps in addition to ALTER
EXTENSION postgis UPDATE; to correctly and fully update postgis in the
replicated PostgreSQL 12 database on red Hat 9 which came with postgis-3.

Many thanks,

Andrew
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20240624/07bec0e8/attachment.htm>