Обсуждение: postgis Error during upgrade

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?

Hi Roland,

On Fri, Jul 21, 2023 at 9:39 AM Roland Che <rolandngwa31@gmail.com> wrote:
>
> Hello all
> i am trying to upgrade from postgres 9.6 to the latest minor version of 14.

Upgrading Postgres from such an old version with postgis is a bit
tricky. In order to upgrade to Postgres version 13 you need to upgrade
postgis extension to version 3.0, otherwise your postgis would be
broken, and you would need to transfer data logically which is not a
nice experience.

for Postgis 3.0 PostgreSQL 9.5 or higher is required, so you can
upgrade Postgis according to documentation and then do a Postgres
upgrade.

Best regards,
Ilya

> And we have a couple of extensions inclusing postgis .
> We have postgis 2.3 as seen below:

>
>

Thanks everyone for @Erik yes dumping the data and restore on the
target will work but we have like 900 servers , like 5 thousand
databases and we use ansible for all deployments so to do dump and
restore will be cumbersome that is the reason we are looking into
pg_upgrade.

On Tue, Jul 25, 2023 at 1:58 PM Erik Serrano <eserranos@gmail.com> wrote:
>
> Dear,
> Very old versions will never have the new features that current applications have, which will always fail when you
wantto migrate, so I advise you... look, there is an official postgresql page where you can see the compatibility
betweenversions and after that, mount the PG14 version on a destination server and only export the schemas, users and
data.
> Then you check your applications and if everything goes well, that's it... or you can do a POC with what I'm telling
youbefore doing it in a productive environment 
> I hope it works for you, regards
>
>
>
> Erik R. Serrano Saavedra
> Ingeniero de Sistemas Informáticos
>       Data Base Administrator
>         eserranos@gmail.com
>                  998596691
>
>
> El vie, 21 jul 2023 a las 4:23, Ilya Kosmodemiansky (<ik@dataegret.com>) escribió:
>>
>> Hi Roland,
>>
>> On Fri, Jul 21, 2023 at 9:39 AM Roland Che <rolandngwa31@gmail.com> wrote:
>> >
>> > Hello all
>> > i am trying to upgrade from postgres 9.6 to the latest minor version of 14.
>>
>> Upgrading Postgres from such an old version with postgis is a bit
>> tricky. In order to upgrade to Postgres version 13 you need to upgrade
>> postgis extension to version 3.0, otherwise your postgis would be
>> broken, and you would need to transfer data logically which is not a
>> nice experience.
>>
>> for Postgis 3.0 PostgreSQL 9.5 or higher is required, so you can
>> upgrade Postgis according to documentation and then do a Postgres
>> upgrade.
>>
>> Best regards,
>> Ilya
>>
>> > And we have a couple of extensions inclusing postgis .
>> > We have postgis 2.3 as seen below:
>>
>> >
>> >
>>
>>