Some time ago, I presented a typical approach to upgrade PostGIS under Ubuntu. Please take a look at my previous post to recall how we accomplished this task.
Today, we’ll extend this procedure by upgrading PostgreSQL and PostGIS in one row to replay a quite realistic scenario: A customer runs PostgreSQL 13 with an outdated PostGIS 2.5.5 and wants to upgrade these packages to the very latest ones: PostgreSQL 15, PostGIS 3.3.2
Let’s break down upgrading PostgreSQL and PostGIS at once into the following steps:
- Status quo assessment
- PostgreSQL upgrade via pg_upgrade
- PostGIS upgrade on the database level
Status quo
Our demo server (Ubuntu 20.04.02) is already equipped with two different PostgreSQL cluster versions running side by side:
- Cluster 1: PostgreSQL 13, PostGIS 2.5.5
- Cluster 2: PostgreSQL 15, PostGIS 3.3.2
Step 1 – Assess status quo before you upgrade PostGIS together with PostgreSQL
First, let’s list installed packages on the OS level:
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# apt list --installed | grep postgresql postgresql-13-postgis-2.5-scripts/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 all [installed,automatic] postgresql-13-postgis-2.5/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 amd64 [installed] postgresql-13/focal-pgdg,now 13.10-1.pgdg20.04+1 amd64 [installed] postgresql-15-postgis-3-scripts/focal-pgdg,now 3.3.2+dfsg-1.pgdg20.04+1 all [installed,automatic] postgresql-15-postgis-3/focal-pgdg,now 3.3.2+dfsg-1.pgdg20.04+1 amd64 [installed] postgresql-15/focal-pgdg,now 15.2-1.pgdg20.04+1 amd64 [installed] postgresql-client-13/focal-pgdg,now 13.10-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-15/focal-pgdg,now 15.2-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-common/focal-pgdg,now 248.pgdg20.04+1 all [installed,automatic] postgresql-common/focal-pgdg,now 248.pgdg20.04+1 all [installed,automatic]
Step 2 status quo assessment
Next, we quickly grab cluster states to verify both are up and running:
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 13 pg_13 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 15 pg_15 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log
Step 3 status quo assessment
Finally, let’s connect to our legacy cluster via port 5432 to quickly assess databases, we must upgrade. This legacy cluster serves only one spatially enabled database pgdemo, utilizing PostGIS 2.5.5.
postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:/root$ psql -p 5432
postgres=# \c pgdemo
pgdemo=# select postgis_full_version();
postgis_full_version
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
POSTGIS="2.5.5" [EXTENSION] PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="Rel. 6.3.1, February 10th, 2020" GDAL="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" RASTER
(1 row)
PostgreSQL upgrade via pg_upgrade
In contrast to the last blog post, we want to upgrade PostgreSQL and PostGIS in one step. To do so, we’ll utilize pg_upgrade, a great tool, which is the gold standard for this kind of task. Check out pg_upgrade's latest documentation to learn all the details about it.
Step 1 PostgreSQL upgrade via pg_upgrade
First, we stop our database clusters:
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# systemctl stop postgresql root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 13 pg_13 5432 down postgres /var/lib/postgresql/13/pg_13 /var/log/postgresql/postgresql-13-pg_13.log 15 pg_15 5433 down postgres /var/lib/postgresql/15/pg_15 /var/log/postgresql/postgresql-15-pg_15.log
Step 2 PostgreSQL upgrade via pg_upgrade
Second, as the user postgres, we construct our pg_upgrade command with the parameter check. This parameter enables us to verify upgrade compatibility before finally upgrading our clusters.
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# su postgres
postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:/root$ cd ~
postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:~$ /usr/lib/postgresql/15/bin/pg_upgrade \
> --old-datadir=/var/lib/postgresql/13/pg_13 \
> --new-datadir=/var/lib/postgresql/15/pg_15 \
> --old-bindir=/usr/lib/postgresql/13/bin \
> --new-bindir=/usr/lib/postgresql/15/bin \
> --old-options '-c config_file=/etc/postgresql/13/pg_13/postgresql.conf' \
> --new-options '-c config_file=/etc/postgresql/15/pg_15/postgresql.conf' --check
Performing Consistency Checks
-----------------------------
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 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:
/var/lib/postgresql/15/pg_15/pg_upgrade_output.d/20230424T070230.213/loadable_libraries.txt
How to finish the upgrade – ways to solve compatibility issues
One line jumps right off the page: Checking for presence of required libraries fatal
The log referenced claims it is missing PostGIS 2.5 libraries for PostgreSQL 15, which would be necessary to proceed with the upgrade.
postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:~$ cat /var/lib/postgresql/15/pg_15/pg_upgrade_output.d/20230424T070230.213/loadable_libraries.txt could not load library "$libdir/postgis-2.5": ERROR: could not access file "$libdir/postgis-2.5": No such file or directory In database: pgdemo could not load library "$libdir/rtpostgis-2.5": ERROR: could not access file "$libdir/rtpostgis-2.5": No such file or directory In database: pgdemo
pg_upgrade requires both PostgreSQL clusters not only serving the same extensions, but its versions too. Let’s think about our possibilities respecting PostGIS` compatibility matrix, we can check that at the PostGIS Wiki to resolve this conflict.
- We could install PostGIS 2.5 for PostgreSQL 15 on the OS level first, and upgrade to PostGIS 3.3.2 at the database level after
pg_upgrade. This produces additional workload, but we don’t have to touch our legacy system. - We could install PostGIS 3.2 for PostgreSQL 13 on both the OS and database level, to circumvent version mismatch. This produces additional workload ☹️.
- Alternatively, we can trick
pg_upgradeto think the required 2.5 libraries are already in place for PostgreSQL 15.
Use the workaround to fix compatibility issues – after you back up!
Let’s take the challenge and take option 3. Please note, this is not an official upgrade procedure. Take care and backup first!
So, we must pretend that PostgreSQL 15 is equipped with PostGIS 2.5. Luckily, pg_upgrade’s log states its expectations regarding missing libraries in detail. By creating symbolic links pointing from PostGIS 3 to PostGIS 2.5.5, we can accomplish this step:
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# cd /usr/lib/postgresql/15/lib/ root@ubuntu-s-2vcpu-2gb-intel-fra1-01:/usr/lib/postgresql/15/lib# ln -s postgis-3.so postgis-2.5.so root@ubuntu-s-2vcpu-2gb-intel-fra1-01:/usr/lib/postgresql/15/lib# ln -s postgis_raster-3.so rtpostgis-2.5.so
That’s better!
Now pg_upgrade does not complain about conflicting extensions 😊:
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:/usr/lib/postgresql/15/lib# su postgres postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:/usr/lib/postgresql/15/lib$ cd ~ postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:~$ /usr/lib/postgresql/15/bin/pg_upgrade --old-datadir=/var/lib/postgresql/13/pg_13 --new-datadir=/var/lib/postgresql/15/pg_15 --old-bindir=/usr/lib/postgresql/13/bin --new-bindir=/usr/lib/postgresql/15/bin --old-options '-c config_file=/etc/postgresql/13/pg_13/postgresql.conf' --new-options '-c config_file=/etc/postgresql/15/pg_15/postgresql.conf' --check Performing Consistency Checks Checking for presence of required libraries ok Checking database user is the install user ok Checking for prepared transactions ok Checking for new cluster tablespace directories ok *Clusters are compatible*
…and we can execute pg_upgrade without a parameter check. Here’s an extract from the output:
Setting next OID for new cluster ok
Sync data directory to disk ok
Creating script to delete old cluster ok
Checking for extension updates notice
Your installation contains extensions that should be updated
with the ALTER EXTENSION command. The file
update_extensions.sql
when executed by psql by the database superuser will update
these extensions.
Upgrade Complete
----------------
PostGIS upgrade on the database level
This looks pretty good so far – is it real? Let’s start our cluster pg_15 and connect to our database pgdemo.
root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# pg_ctlcluster 15 pg_15 start root@ubuntu-s-2vcpu-2gb-intel-fra1-01:~# su postgres postgres@ubuntu-s-2vcpu-2gb-intel-fra1-01:/root$ psql -p 5433 postgres=# \c pgdemo You are now connected to database "pgdemo" as user "postgres".
Which PostGIS version is registered in our demo database? The registration is still on 2.5.5.
pgdemo=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+---------------------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 2.5.5 | public | PostGIS geometry, geography, and raster spatial types and functions
(2 rows)
Use postgis_extensions_upgrade to finish the task
We fix this by executing postgis_extensions_upgrade(). Twice – as with PostGIS v3 and upwards – vector and raster functionality has been separated in two individual extensions.
pgdemo=# select postgis_extensions_upgrade();
WARNING: unpackaging raster
WARNING: PostGIS Raster functionality has been unpackaged
HINT: type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;
NOTICE: ALTER EXTENSION postgis UPDATE TO "3.3.2";
pgdemo=# select postgis_extensions_upgrade();
NOTICE: Updating extension postgis 3.3.2
NOTICE: Packaging and updating postgis_raster
postgis_extensions_upgrade
------------------------------------------------------------------------------------
Upgrade to version 3.3.2 completed, run SELECT postgis_full_version(); for details
(1 row)
Calling postgis_full_version() confirms our expectations:
pgdemo=# select postgis_full_version(); POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="150" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" GDAL="GDAL 3.0.4, released 2020/01/28" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" RASTER (1 row)
Conclusion – you can do it!
Today we introduced a shortcut to upgrade PostgreSQL clusters with “mismatching” PostGIS versions in one row. This is a nice approach to save time and circumvent PostGIS version conflicts during upgrades. Take care and backup your databases first– and then experiment with this appealing procedure.
You may also like: Upgrading and Updating PostgreSQL
In order to receive regular updates on important changes in PostgreSQL, you can subscribe to our newsletter, follow us on Twitter, Facebook, LinkedIn, or check out our tutorials and conference lectures on YouTube