Upgrading PostGIS: A Proof Of Concept

My last blog introduced the issues one can face when upgrading PostGIS and PostgreSQL at the same time. The purpose of this blog is to walk through the steps with an example.

For our purposes, we will confine ourselves to working with the community versions of 9.6 and 11 respectively, and use LXD in order to create a working environment prototyping the steps, and profiling the issues.

Creating the Demo Environment Using LXD

The first step is creating a template container with the requisite packages and configurations. This template is a basic distribution of Ubuntu 18.04, which has already been installed in the development environment.

These packages install the necessary supporting packages, installing PostgreSQL from the community repository:

These steps are copied from the community download page for Ubuntu distributions:

The aforementioned repository is now updated thus making it possible to install our two versions of PostgreSQL (i.e. 9.6 and 11, respectively). Installing pg_repack pulls in the requisite packages while installing this very useful package at the same time too:

These next packages are useful. Midnight Commander, mc, is a terminal-based shell navigator and file manager while the other package installs utilities, such as netstat, to monitor the status of all network-based services on the localhost:

This last step merely updates the man pages database and the mlocate database. It makes it easier to locate files on the host. Beware this can be a security risk if used on a production host.

This little snippet of code creates our simulated production host. Creating the instance from a template container makes it much easier to try different variations in quick order:

As per our scenario, upgrading PostGIS requires two different versions to be installed on the host. Notice that PostgreSQL version 9.6 has the older version of PostGIS, while version 11 has the newer one.

For our purposes, this presentation assumes upgrading both PostgreSQL and PostGIS is the method to be used.

ATTENTION: Executing this upgrade operation into two distinct phases is preferred. Either upgrade PostgreSQL and then upgrade PostGIS or upgrade PostGIS on the old version to match the new version on PostgreSQL and then upgrade the PostgreSQL data cluster. 

The underlying assumption is that application code can break between PostGIS version upgrades therefore pursuing an incremental process can mitigate potential issues.

https://PostGIS.net/docs/PostGIS_Extensions_Upgrade.html

https://PostGIS.net/workshops/PostGIS-intro/upgrades.html

About PostGIS

Available versions of PostGIS, as per the community repository at the time of this blog’s publication:

• 9.6:
  • postgresql-9.6-postgis-2.4
  • postgresql-9.6-postgis-2.5
  • postgresql-9.6-PostGIS-3
• 11:
  • postgresql-11-postgis-2.5
  • postgresql-11-postgis-3
• PostGIS supported versions matrix

ATTENTION: Azure supports only PostgreSQL 9.6 with PostGIS 2.3.2.

Before You Upgrade

About

This query lists all user-defined functions that have been installed in your database. Use it to summarize not only what you’ve created but the entire suite of PostGIS function calls:

In order to validate your functions, you need to know which ones are being used, therefore tracking the functions prior to the upgrade process will identify them. Please note there are two settings i.e. pl, all. Out of an abundance of caution, it is suggested initially using all for an extended period of time:

This view collects all the statistics related to function calls:

Example

This is a simple example demonstrating tracking function call usage. Note there are two function calls and one of them is invoked in the other:

This SQL statement resets all statistics being tracked in the PostgreSQL database. Please note there are other functions that can be used to reset specific statistics while preserving others:

And here’s our functions’ invocations:

An Upgrade Example Using pg_upgrade

SYNOPSIS

There are two discrete upgrades:

1. pg_upgrade: pg 9.6 -> pg 11
2. PostGIS upgrade: postgis-2.4 -> postgis2.5 -> postgis-3

HOUSE CLEANING

An Ubuntu-based upgrade requires removing the target data cluster because installing PostgreSQL packages onto a Debian-based distro always includes creating a data cluster:

For our purposes we are simply adding the extension, no user-defined functions have been included:

Shutting down the source data cluster is the last step before the upgrade process can begin:

Debian based distros provide a convenient CLI, making upgrades easy:

It’s important to check the upgrade logs before starting PostgreSQL version 11. This is a one-way process and once it’s active the old PostgreSQL 9.6 cluster is no longer available and must be destroyed:

Here’s confirmation of the PostgreSQL and PostGIS upgrade respectively:

PostGIS Function API, Inspection, and Review

This is critical; the process validates that the application logic works or that it must be updated.

METHOD: inspect each function call used between all versions:

• from 2.4 -> 2.5
• from 2.5 -> 3.0
• from 3.0 -> 3.1

TIP: 3.1 documentation encapsulates all previous versions i.e. section 9.12

REFERENCES:

Regression Testing

• In the current setup, pg 9.6
  • Identify all functions used in PostGIS
  • Execute a simple function call with every type of parameter typically used in your environment
  • Collect, record all variables returned
• In the target setup, pg 11 or pg 13
  • Execute a simple function call with every type of parameter typically used in your environment
  • Collect, record all variables returned
• Analysis
  • Compare values: similar values mean you don’t have a problem

Working With Cloud Provider Technology

Be advised, cloud environments are not ideal upgrade candidates. The aforementioned process is quite detailed and will facilitate a successful upgrade process.

• AZURE
  • pg 9.6: PostGIS 2.3.2
  • pg 11: PostGIS 2.5.1
• AMAZON
  • pg 11, 13: PostGIS 3.1.4
  • pg 9.6.*: PostGIS 2.3.[0247], 2.5.[25]

References:

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions#postgres-96-extensions

https://docs.microsoft.com/en-us/azure/postgresql/concepts-extensions#postgres-11-extensions

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.PostGIS.html

---

As more companies look at migrating away from Oracle or implementing new databases alongside their applications, PostgreSQL is often the best option for those who want to run on open source databases.

Read Our New White Paper:

Why Customers Choose Percona for PostgreSQL