Thread: 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: > >
Dear,
Very old versions will never have the new features that current applications have, which will always fail when you want to migrate, so I advise you... look, there is an official postgresql page where you can see the compatibility between versions 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 you before doing it in a productive environment
I hope it works for you, regards
Very old versions will never have the new features that current applications have, which will always fail when you want to migrate, so I advise you... look, there is an official postgresql page where you can see the compatibility between versions 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 you before 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
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:
>
>
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: >> >> > >> > >> >>
Dear, I think that in this case the only feasible way is to jump from pg9.6 to pg10 and from there jump from pg10 to pg14
greetings
greetings
Erik R. Serrano Saavedra
Ingeniero de Sistemas Informáticos
Data Base Administrator
998596691
El mar, 25 jul 2023 a las 15:12, Roland Che (<rolandngwa31@gmail.com>) escribió:
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 want to migrate, so I advise you... look, there is an official postgresql page where you can see the compatibility between versions 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 you before 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:
>>
>> >
>> >
>>
>>