After upgrading to 18.04 Bionic, you will receive an error if you try to upgrade PostgreSQL 9.6 database which use PostGIS 2.3 which is the default versions in Ubuntu 17.10 Artful. You will receive an error message in
loadable_libraries.txt error file when running
pg_upgradecluster -m upgrade 9.6 main
could not load library "$libdir/postgis-2.3": ERROR: could not access file "$libdir/postgis-2.3": No such file or directory
could not load library "$libdir/rtpostgis-2.3": ERROR: could not access file "$libdir/rtpostgis-2.3": No such file or directory
The problem is due to PostgreSQL 9.6 does not have PostGIS 2.4 library installed and PostgreSQL 10 not having support library for PostGIS 2.3. So the upgrade is impossible with their infinite wisdom, Ubuntu 18.04 does not provide PostGIS 2.3 for PostgreSQL 9.6.
To be able to overcome this problem you will need to install necessary libraries which produce an upgrade path from PostGIS 2.3 to PostGIS 2.4. Eg. both 2.3 and 2.4 libraries should be installed for the PostgreSQL 9.6 or 10.
However there is no
postgresql-9.6-postgis-2.4 package for Ubuntu 17.10 and installing the 18.04 package will yield an error as seen below.
# dpkg -i postgresql-9.6-postgis-2.4_2.4.4+dfsg-4.pgdg18.04+1_amd64.deb
Selecting previously unselected package postgresql-9.6-postgis-2.4.
(Reading database ... 177473 files and directories currently installed.)
Preparing to unpack postgresql-9.6-postgis-2.4_2.4.4+dfsg-4.pgdg18.04+1_amd64.deb ...
Unpacking postgresql-9.6-postgis-2.4 (2.4.4+dfsg-4.pgdg18.04+1) ...
dpkg: dependency problems prevent configuration of postgresql-9.6-postgis-2.4:
postgresql-9.6-postgis-2.4 depends on libgeos-c1v5 (>= 3.6.0); however:
Version of libgeos-c1v5 on system is 3.5.1-3.
postgresql-9.6-postgis-2.4 depends on liblwgeom-2.4-0 (>= 2.4.0~rc1); however:
Package liblwgeom-2.4-0 is not installed.
postgresql-9.6-postgis-2.4 depends on libprotobuf-c1 (>= 1.0.1); however:
Package libprotobuf-c1 is not installed.
dpkg: error processing package postgresql-9.6-postgis-2.4 (--install):
dependency problems - leaving unconfigured
Errors were encountered while processing:
postgresql-9.6-postgis-2.4
The package gets installed never the less. If you have tried to install
postgresql-9.6-postgis-2.4 on Ubuntu 17.10 then you will need to remove it before upgrading to Ubuntu 18.04 Bionic or the upgrade will fail. So remove it before continuing.
# dpkg -r postgresql-9.6-postgis-2.4
(Reading database ... 177501 files and directories currently installed.)
Removing postgresql-9.6-postgis-2.4 (2.4.4+dfsg-4.pgdg18.04+1) ...
Also there is no
postgresql-10-postgis-2.3 package available in Ubuntu 18.04 Bionic.
Upgrade Solution 1
Do not use
-m upgrade , you can simply use
pg_upgradecluster 9.6 main However this will dump the database and import it and it may take very long time with large databases.
Upgrade Solution 2
Luckily there is a
postgresql-9.6-postgis-2.4 package available for Ubuntu 18.04. The solution is to first upgrade to Ubuntu 18.04 then install
postgresql-9.6-postgis-2.4 package from PostgreSQL PPA and upgrade the 9.6 database first.
There are two ways to install the required packages. First way is to add the PostgreSQL PPA to your system using the instructions at
PostgreSQL Ubuntu download site. However if you do this, you will end up upgrading to PostgreSQL packages provided from the PostgreSQL PPA in your next upgrade. If you want to stick with Ubuntu provided packages, you should either not do this or remove the PPA after installation of required packages.
The second way is downloading the .deb packages directly from PostgreSQL site. It is possible to get a listing of all the packages at the
PostgreSQL PostGIS APT download site. I simply downloaded the .deb file for this process
You can install the .deb file and then you will need to copy the upgrade path sql file to PostgreSQL 9.6 folder. (as root)
# dpkg -i postgresql-9.6-postgis-2.4_2.4.4+dfsg-4.pgdg18.04+1_amd64.deb
# cp /usr/share/postgresql/10/extension/postgis--2.3.3--2.4.3.sql /usr/share/postgresql/9.6/extension/postgis--2.3.3--2.4.3.sql
After the installation become
postgres user and connect to databases which use the postgis extension using
psql and update it to 2.4.3.
# ALTER EXTENSION postgis UPDATE;
NOTICE: version "2.3.3" of extension "postgis" is already installed
ALTER EXTENSION
# ALTER EXTENSION postgis UPDATE TO '2.4.3';
WARNING: 'postgis.backend' is already set and cannot be changed until you reconnect
WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect
WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect
WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect
ALTER EXTENSION
After that you should be able to run pg_upgradecluster -m upgrade 9.6 main without any troubles.
After Upgrade
After the upgrade, if everything is working. You can remove the PostgreSQL 9.6 cluster and you can also remove all other packages related to PostgreSQL 9.6.