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
Table of Contents
Let's break down upgrading PostgreSQL and PostGIS at once into the following steps:
Our demo server (Ubuntu 20.04.02) is already equipped with two different PostgreSQL cluster versions running side by side:
First, let's list installed packages on the OS level:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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] |
Next, we quickly grab cluster states to verify both are up and running:
|
1 2 3 4 |
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 |
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.
|
1 2 3 4 5 6 7 |
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) |
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.
First, we stop our database clusters:
|
1 2 3 4 5 |
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 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 |
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.
|
1 2 3 4 5 |
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.
pg_upgrade. This produces additional workload, but we don’t have to touch our legacy system.pg_upgrade to think the required 2.5 libraries are already in place for PostgreSQL 15.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:
|
1 2 3 |
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 |
Now pg_upgrade does not complain about conflicting extensions ????:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 ---------------- |
This looks pretty good so far – is it real? Let’s start our cluster pg_15 and connect to our database pgdemo.
|
1 2 3 4 5 |
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.
|
1 2 3 4 5 6 7 |
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) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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:
|
1 2 3 |
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) |
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 Facebook or LinkedIn.
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply