Let’s party and upgrade PostgreSQL and PostGIS together

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:

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]

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

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)

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

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

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

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_upgrade to 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

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*

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
----------------

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'.

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)

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)

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)

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

0 0 votes

Article Rating

0 Comments

Inline Feedbacks

View all comments

Stay tuned with our

Let’s party and upgrade PostgreSQL and PostGIS together

Let's break down upgrading PostgreSQL and PostGIS at once into the following steps:

Status quo

Step 1 - Assess status quo before you upgrade PostGIS together with PostgreSQL

Step 2 status quo assessment

Step 3 status quo assessment

PostgreSQL upgrade via pg_upgrade

Step 1 PostgreSQL upgrade via pg_upgrade

Step 2 PostgreSQL upgrade via pg_upgrade

How to finish the upgrade - ways to solve compatibility issues

Use the workaround to fix compatibility issues - after you back up!

That's better!

PostGIS upgrade on the database level

Use postgis_extensions_upgrade to finish the task

Conclusion - you can do it!

Florian Nadler

Blog Tags

NEWSLETTER

Articles by our PostgreSQL Experts