Hello all
i am trying to upgrade from postgres 9.6 to the latest minor version of 14.
And we have a couple of extensions inclusing postgis .
We have postgis 2.3 as seen below:
xxxxxx=# SELECT * FROM pg_available_extensions WHERE name = 'postgis';
name | default_version | installed_version |
comment
---------+-----------------+-------------------+---------------------------------------------------------------------
postgis | 2.3.7 | 2.3.7 | PostGIS geometry,
geography, and raster spatial types and functions
(1 row)
First when i try to update postgis to a newer version, it will fail
because there is no upgrade path( which is expected)
as we are running on an outdated version.
When we run pg_upgrade with the check option for consistency check,
postgis will complain about missing libraries.
[postgres@xxxxxx tmp]$ /usr/pgsql-14/bin/pg_upgrade -b
/usr/pgsql-9.6/bin -B /usr/pgsql-14/bin -d
/var/lib/pgsql/9.6/pgcluster -D /var/lib/pgsql/14/data -o '-c
config_file=/var/lib/pgsql/9.6/pgcluster/postgresql.conf' -O '-c
config_file=/var/lib/pgsql/14/data/postgresql.conf' --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for invalid "unknown" user columns ok
Checking for hash indexes ok
Checking for presence of required libraries fatal
Your installation references loadable libraries that are missing from the
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
[postgres@xxxxxx tmp]$cat loadable_libraries.txt
could not load library "$libdir/postgis-2.3": ERROR: could not access
file "$libdir/postgis-2.3": No such file or directory
In database: xxxxxx
In database: yyyyyy
In database: aaaaaaa
In database: wwwwwww
In database: gvvvvvvv
In database: qqqqqq
In database: rrrrrrr
In database: hhhhhhh
In database: postgres
In database: mmmmmmm
In database: ssssssss
In database: jjjjjjjj
WHAT I HAVE DONE SO FAR
1) I copied every postgis library from 9.6 to the newly installed version 14.
cp /usr/pgsql-9.6/lib/postgis* usr/pgsql-14/lib/
and below got copied
postgis-2.3.so
postgis_topology-2.3.so
postgis-2.2.so -> /usr/pgsql-9.6/lib/postgis-2.3.so
2) It was still complaining because i could not create postgis on
version 14 cluster. So i had to uninstall postgis on the
server and also drop the extension from the database but still
having thesame error .
yum remove postgis23_96
DROP EXTENSION postgis CASCADE;
Please Can advise on how i should go about this?
