CYBERTEC Logo

Let’s party and upgrade PostgreSQL and PostGIS together

05.2023 / Category: / Tags: |

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:

  1. Status quo assessment
  2. PostgreSQL upgrade via pg_upgrade
  3. 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:

  1. Cluster 1: PostgreSQL 13, PostGIS 2.5.5
  2. 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:

Step 2 status quo assessment

Next, we quickly grab cluster states to verify both are up and running:

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.

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:

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.

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.

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.

Table showing features of different PostgreSQL versions
  1. 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.
  2. We could install PostGIS 3.2 for PostgreSQL 13 on both the OS and database level, to circumvent version mismatch. This produces additional workload ☹️.
  3. 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:

That's better!

Now pg_upgrade does not complain about conflicting extensions 😊:

…and we can execute pg_upgrade without a parameter check. Here's an extract from the output:

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.

Which PostGIS version is registered in our demo database? The registration is still on 2.5.5.

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.

Calling postgis_full_version() confirms our expectations:

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 Facebook or LinkedIn.

0 0 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram