Issues Upgrading PostGIS and PostgreSQL

PostGIS is arguably the best GIS implementation in the world. There may be other database technologies with GIS but the value proposition of quality, performance, and sophistication, and, of course, cost, is nowhere near what PostgreSQL offers.

So let me set the stage for this blog of mine: you’ve been using PostGIS on your PostgreSQL server for a few years now but having reached its end-of-life, it’s time to upgrade the system.

How do you do it?

Most times upgrading PostgreSQL, although a significant activity, can be a pain-free experience. And for those people who’ve implemented their PostgreSQL database in the cloud, upgrading PostgreSQL is often a matter of just clicking a few buttons. The only real work is fleshing out the steps as you compose your run-book and rehearse drills before performing your scheduled maintenance window.

Upgrading a PostgreSQL server with extensions is a matter of ensuring the same versions are present on both source and target hosts. In the case of extensions bundled with PostgreSQL, such as pg_stat_statements, there’s nothing to worry about since compatibility is pretty much guaranteed. Things are just a touch more complex when using popular extensions such as pg_repack as you must install its package on the target host. Less popular extensions can be more challenging in that they may not have been as rigorously tested. But in the case of upgrading a PostGIS-enabled database engine, you can bid any hopes for a stress-free experience a sweet goodbye as it saunters out the door waving fondly at you.

Looking at the library dependencies for pg_stat_statements, pg_repack and postgis respectively should help you appreciate the issues a little better:

pg_stat_statements

ldd /usr/lib/postgresql/12/lib/pg_stat_statements.so
 
linux-vdso.so.1 (0x00007ffc3759c000)
 
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f5e4ff60000)
 
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f5e4fb6f000)
 
/lib64/ld-linux-x86-64.so.2 (0x00007f5e50508000)

pg_repack

ldd /usr/lib/postgresql/12/lib/pg_repack.so
 
linux-vdso.so.1 (0x00007ffeaaf8c000)
 
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f9601226000)
 
/lib64/ld-linux-x86-64.so.2 (0x00007f960181e000)

postgis

ldd /usr/lib/postgresql/12/lib/postgis-3.so
 
linux-vdso.so.1 (0x00007fff7f736000)
 
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f953bf27000)
 
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f953bb9e000)
 
libgeos_c.so.1 => /usr/lib/x86_64-linux-gnu/libgeos_c.so.1 (0x00007f953b96d000)
 
libproj.so.12 => /usr/lib/x86_64-linux-gnu/libproj.so.12 (0x00007f953b704000)
 
libjson-c.so.3 => /lib/x86_64-linux-gnu/libjson-c.so.3 (0x00007f953b4f9000)
 
libprotobuf-c.so.1 => /usr/lib/x86_64-linux-gnu/libprotobuf-c.so.1 (0x00007f953b2f0000)
 
libxml2.so.2 => /usr/lib/x86_64-linux-gnu/libxml2.so.2 (0x00007f953af2f000)
 
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f953ad17000)
 
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f953a926000)
 
/lib64/ld-linux-x86-64.so.2 (0x00007f953c612000)
 
libgeos-3.7.1.so => /usr/lib/x86_64-linux-gnu/libgeos-3.7.1.so (0x00007f953a583000)
 
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f953a364000)
 
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f953a160000)
 
libicuuc.so.60 => /usr/lib/x86_64-linux-gnu/libicuuc.so.60 (0x00007f9539da8000)
 
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f9539b8b000)
 
liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x00007f9539965000)
 
libicudata.so.60 => /usr/lib/x86_64-linux-gnu/libicudata.so.60 (0x00007f9537dbc000)

PostGIS is probably the most sophisticated extension you will ever encounter on PostgreSQL. Its dependencies encompass not just the database system but a number of disparate libraries and projects that the RDBMS under normal circumstances has no relation.

Let’s itemize some key issues:

The naming scheme protocol used in PostGIS is not the same as PostgreSQL. For example, PostgreSQL version 13.4 means the major version is “13” and the minor release or bug release is “4”. But PostGIS still uses the old numbering system that the PostgreSQL project followed before version 10. For example, PostGIS version 2.4.1 means the major version is “2.4” and the minor release is “1”.
All currently supported versions of PostGIS are NOT being supported for all currently maintained versions of PostgreSQL. For example, in the case of Ubuntu, the PostgreSQL community repository currently makes available PostGIS 2.4 for PostgreSQL versions 10 and older but it isn’t found for versions 11 and newer. Referring to the postgis support matrix, which lists all currently supported versions postgis and their end-of-life, one can see that technically speaking version 2.4 is supported on postgres version 11. On the other hand, there is support on the Red/Centos side of things. This isn’t laziness on the part of the maintainers, rather there’s so much in the way of advances that it’s simply not practical to maintain this technical debt for longer than a few years at a time.
Best practices require the same versioned extension to be present on both source and target data clusters. Keep in mind when it comes to major version differences some extensions are more flexible than others. Otherwise, one is forced to remove it from the source data cluster if it’s not present on the target.
For some extensions, such as the internal ones that come bundled with the PostgreSQL packages and certain 3rd party extensions, such as pg_repack, it is possible to upgrade between extension major versions without issue.
In regards to PostGIS: when faced with different extension versions between the source and target data clusters one must begin by upgrading postgis on the source data cluster such that it matches BOTH the major and minor versions of the target data cluster and only thereafter performing the PostgreSQL upgrade. Alternatively, one can install a downgraded major/minor version of PostGIS on the target data cluster, matching the source data cluster’s major and minor version of PostGIS, and then upgrade PostgreSQL (yes, this is convoluted).
Some cloud providers do NOT offer more than one version of PostGIS for any particular version of PostgreSQL thereby making the previous recommendation redundant. The reason is simple, they are not prepared to handle PostGIS upgrades. There are ways of dealing with them but that’s for another blog. Please note that this is a rapidly moving target and can/will probably change in the future.
Because the PostgreSQL community repository removes all versions of PostgreSQL and their extensions when they have reached end-of-life. PostGIS package support becomes problematic when upgrading database servers that have reached end-of-life.

Over the past twenty years, I’ve had occasion to upgrade some fairly large installations using PostGIS, the most complex being over 120TB data on a version of PostgreSQL that hadn’t been upgraded in several years and the PostGIS was so old that it wasn’t even installed using extensions. As a result of this and other experiences I have, where possible, adopted the practice of performing PostGIS and PostgreSQL upgrades as separate exercises.

I can’t emphasize enough that regression testing for each GIS function call be made thoroughly in a development environment before going into production. Because the PostGIS project’s development cycle advances at the same torrid pace as the demand for GIS, it can happen that the intrinsic behavior of a function can change seemingly without warning. Therefore, merely validating the API via a document review is insufficient, i.e. TEST IT!

Here’s a tip: Turn on run-time parameter track_functions and you’ll be able to identify all those wonderful PostGIS functions your application stack depends upon.

Finally, by all means, take a look at an earlier blog “Working with PostgreSQL and PostGIS: How To Become A GIS Expert“ for more information about PostGIS.

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

This information was first published on https://www.percona.com/blog/issues-upgrading-postgis-and-postgresql/