We all are aware of the pg_upgrade utility for upgrading the Postgresql database to the latest version. However, have you ever encountered an error message like could not load library "$libdir/PostGIS-x.x”: ERROR: could not access file "$libdir/PostGIS-x.x”: No such file or directory?
We generally come across these kind of error messages when there is a version mismatch between the PostGIS version installed on the database servers i.e the database server version which needs to be upgraded and the database server version to which upgrade has to be done. Meanwhile, mentioning that PostGIS is a spatial database extender for the Postgresql object-relational database. It adds support for geographic objects allowing location queries to be run in SQL.
Further, I am trying to explain the nature of the problem using the following example:
The user is looking to upgrade the Postgresql database server from PPAS-9.5(Postgis-2.1.8) to EPAS-10 (Postgis-2.4.1), however, the challenge is PostGIS version needs to be same on both database server version otherwise the following format of error message will be encountered.
could not load library "$libdir/postgis-2.1": ERROR: could not access file "$libdir/postgis-2.1": No such file or directory
could not load library "$libdir/rtpostgis-2.1": ERROR: could not access file "$libdir/rtpostgis-2.1": No such file or directory
So, the prerequisite for these scenarios would be to have the same PostGIS version installed on the database servers. We checked and found that Postgis-2.1.8 is not released for EPAS-10 and Postgis-2.4.1 is not available for EPAS-9.5.
We advised the user to adopt the following upgrade strategy:
First, upgrade the PPAS-9.5 database server to EPAS-9.6 as the postgis-2.1.8 version was available for both database servers.
Step 1:-- Install the latest version of PPAS-9.5
Step 2:-- Install PostGIS 2.1.8 on PPAS-9.5
Step 3:-- Install the latest version of EPAS-9.6
Step 4:-- Install the Postgis 2.1.8 on EPAS-9.6
Step 5:-- upgrade the database server to EPAS-9.6.
Now, we need to plan for the upgrade of EPAS-9.6 to EPAS-10 with the fact that PostGIS-2.3.1 is available for EPAS-9.6 and Postgis-2.3.4 is available for EPAS-10.
We cannot use pg_upgrade as Postgis version are not compatible and in such case, the only option left is a dump and restore method for postgis database servers.
Step 6:- Install the PostGIS version PostGIS-2.3.1 on EPAS-9.6.
alter extension PostGIS update to "2.3.1";------We need to execute this as PostGIS-2.1.8 is already present on this database.
Step 7:- Take pg_dump of postGIS enabled database.
Step 8:-- Drop postGIS templated and PostGIS enabled databases.
Note:-- Please refer to the below-provided link
Step 9:-- Perform pg_upgrade from EPAS-9.6 to V-10 using pg_upgrade utility.
Step10:- Start V-10 server and then install postGIS-2.3.4
Step 11:-- Create a new PostGIS database on EPAS-10
Step 12:-- Restore the PostGIS database from the backup file using postgis_restore.pl
Step 13:-- Verify the PostGIS database properly restored.
Step 14:-- Now upgrade PostGIS-2.3.4 to PostGIS-2.4.1
alter extension postgis update to "2.4.1";
... View more