Upgrade Postgres with Ubuntu 20.04.02

Article

The previous time, PostgreSQL was installed on top of PostGIS. Today, I’ll add to this article by explaining how to upgrade PostGIS on Ubuntu. Postgis.net has a comprehensive description that should be consulted concurrently.

First, let me explain our scenario and objective:

A fake customer wants to upgrade his PostgreSQL 13 cluster with PostGIS support that is running on Ubuntu 20.04.02. Version 2.5 is the most recent version that should be used for the PostGIS extension. One spatial database that serves both vector and raster data at the customer site must be upgraded to its successor.

It should be emphasized that PostGIS upgrades can be completed in one of two ways: a soft upgrade or a hard upgrade. A soft upgrade is only a binary upgrade of a PostGIS extension; a hard upgrade entails dumping and restoring the entire database into a new PostGIS enabled database. It would make sense to select the soft upgrade path by default based on this description. A soft upgrade isn’t always possible, especially when the way PostGIS objects store their data on the inside changes. So how do we decide which route to take? Thankfully, the PostGIS release notes make it clear when a hard upgrade is necessary. Apparently, a soft upgrade is enough for our use case, so we’re in luck. For completeness’ sake, the annex includes the necessary steps to execute a hard upgrade as well.

The steps I’ll take to complete this task are as follows:

1. A review of the situation
2. OS-level upgrade for PostGIS
3. Upgrade of PostGIS at the database level

status quo

Let’s start by making a list of installed packages at the OS level to compare claims to reality.

 sudo apt list --installed | grep postgresql

From the list, we can see that PostGIS extension 2.5 has been put on top of PostgreSQL 13.

 postgresql-13-postgis-2.5-scripts/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 all [installed] postgresql-13-postgis-2.5/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 amd64 [installed] postgresql-13/focal-pgdg,now 13.2-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-13/focal-pgdg,now 13.2-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic] postgresql-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic] postgresql-contrib/focal-pgdg,now 13+225.pgdg20.04+1 all [installed] postgresql/focal-pgdg,now 13+225.pgdg20.04+1 all [installed]

It makes sense to add to our test by making sure that our PostGIS extension has been registered correctly in PostgreSQL’s ecosystem. This can be quickly checked on the cluster level by reviewing the related system catalogs or querying pg_available_extensions as a shortcut.

 pdemo=# select * from pg_available_extensions where name like 'postgis%'; name | default_version | installed_version | comment ----------------------------+-----------------+-------------------+--------------------------------------------------------------------- postgis_topology | 2.5.5 | | PostGIS topology spatial types and functions postgis-2.5 | 2.5.5 | | PostGIS geometry, geography, and raster spatial types and functions postgis_tiger_geocoder-2.5 | 2.5.5 | | PostGIS tiger geocoder and reverse geocoder postgis_tiger_geocoder | 2.5.5 | | PostGIS tiger geocoder and reverse geocoder postgis | 2.5.5 | | PostGIS geometry, geography, and raster spatial types and functions postgis_topology-2.5 | 2.5.5 | | PostGIS topology spatial types and functions postgis_sfcgal | 2.5.5 | | PostGIS SFCGAL functions postgis_sfcgal-2.5 | 2.5.5 | | PostGIS SFCGAL functions (8 rows)

Surprise! The results of our search confirm that PostGIS extensions are only available for version 2.5. Finally, it is useful not only to list available extensions at the cluster level, but also to explicitly list installed extensions at the database level.

To do so, let’s open up a psql session, connect to our database and finally utilize \dx to quickly grab the installed extensions.

 pdemo=# \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)

Additionally, querying postgis_full_version() returns even more detailed information about our PostGIS extension.

 pdemo=# 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)

By the way, to get a complete picture of the system, I advise using all methods available to evaluate the situation at the cluster and database levels.

OS-level PostGIS upgrade

After looking at our cluster, we can move on and install the latest PostGIS packages for PostgreSQL 13 on Ubuntu 20.

Let us quickly ensure that our needed PostGIS package is accessible by grabbing the available packages utilizing apt-cache search.

 sudo apt-cache search postgresql-13-postgis postgresql-13-postgis-2.5 -- PostgreSQL 13 geographic object support postgresql-13-postgis-2.5-dbgsym -- PostgreSQL 13 debug symbols postgresql-13-postgis-2.5-scripts -- PostgreSQL 13 geographic object support SQL scripts postgresql-13-postgis-3 - Geographic objects support for PostgreSQL 13 postgresql-13-postgis-3-dbgsym - debug symbols for postgresql-13-postgis-3 postgresql-13-postgis-3-scripts - Geographic objects support for PostgreSQL 13 -- SQL scripts

We appear to be in good shape and can perform the following installation of PostGIS 3:

 sudo apt-get install postgresql-13-postgis-3 postgresql-13-postgis-3-scripts

Reiterating what I said earlier, it won’t hurt to check the OS level installed PostgreSQL packages (1) and the PostgreSQL package registration (2).

grep sudo apt list --installed postgresql postgresql-13-postgis-2.5-scripts/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 all [installed] postgresql-13-postgis-2.5/focal-pgdg,now 2.5.5+dfsg-1.pgdg20.04+2 amd64 [installed] postgresql-13-postgis-3-scripts/focal-pgdg,now 3.1.1+dfsg-1.pgdg20.04+1 all [installed,automatic] postgresql-13-postgis-3/focal-pgdg,now 3.1.1+dfsg-1.pgdg20.04+1 amd64 [installed] postgresql-13/focal-pgdg,now 13.2-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-13/focal-pgdg,now 13.2-1.pgdg20.04+1 amd64 [installed,automatic] postgresql-client-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic] postgresql-common/focal-pgdg,now 225.pgdg20.04+1 all [installed,automatic] postgresql-contrib/focal-pgdg,now 13+225.pgdg20.04+1 all [installed] postgresql/focal-pgdg,now 13+225.pgdg20.04+1 all [installed]

 pdemo=# select * from pg_available_extensions where name like 'postgis%' and default_version= '3.1.1'; name | default_version | installed_version | comment --------------------------+-----------------+-------------------+------------------------------------------------------------ postgis_topology | 3.1.1 | | PostGIS topology spatial types and functions postgis_tiger_geocoder-3 | 3.1.1 | | PostGIS tiger geocoder and reverse geocoder postgis_raster | 3.1.1 | | PostGIS raster types and functions postgis_tiger_geocoder | 3.1.1 | | PostGIS tiger geocoder and reverse geocoder postgis_raster-3 | 3.1.1 | | PostGIS raster types and functions postgis | 3.1.1 | 2.5.5 | PostGIS geometry and geography spatial types and functions postgis-3 | 3.1.1 | | PostGIS geometry and geography spatial types and functions postgis_sfcgal | 3.1.1 | | PostGIS SFCGAL functions postgis_topology-3 | 3.1.1 | | PostGIS topology spatial types and functions postgis_sfcgal-3 | 3.1.1 | | PostGIS SFCGAL functions (10 rows)

Upgrade of PostGIS at the database level

As the requirements are fulfilled, we can proceed and upgrade PostGIS within our database. Let’s open up a psql session, connect to our database and call PostGIS_Extensions_Upgrade(). Please note that PostGIS_Extensions_Upgrade() is only available from 2.5 upwards. Upgrading from prior versions imply manual steps (see annex) or upgrading to 2.5 as intermediate version. From version 3 PostGIS separates functionality for vector and raster in different extensions – PostGIS_Extensions_Upgrade() takes care of this fact.

 lapdemo=# select postgis_extensions_upgrade(); postgis_extensions_upgrade ----------------------------------------------------------------------------------------------------------------------------------- POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130" 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 (raster lib from "2.5.5 r0" need upgrade) [UNPACKAGED!] (raster procs from "2.5.5 r0" need upgrade) (1 row) 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.1.1";

Reading the query result, it seems we are not done and the PostGIS raster extension must be upgraded separately. This should be necessary only when moving from PostGIS version <3 to PostGIS 3. The reason behind this is, that raster functionality has been moved to a separate extension named PostGIS_Raster. To upgrade and install PostGIS_Raster, a second call to PostGIS_Extensions_Upgrade() does the trick.

 pdemo=# select postgis_extensions_upgrade(); WARNING: unpackaging raster WARNING: PostGIS Raster functionality has been unpackaged TIP: type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster; HINT: ALTER EXTENSION postgis UPDATE TO "3.1.1"; post25=# select postgis_extensions_upgrade(); HINT: Packaging extension postgis_raster WARNING: 'postgis.gdal_datapath' is already set and cannot be changed until you reconnect WARNING: 'postgis.gdal_enabled_drivers' is already set and cannot be changed until you reconnect WARNING: 'postgis.enable_outdb_rasters' is already set and cannot be changed until you reconnect HINT: Extension postgis_sfcgal is not available or not packageable for some reason HINT: Extension postgis_topology is not available or not packageable for some reason HINT: Extension postgis_tiger_geocoder is not available or not packageable for some reason postgis_extensions_upgrade ------------------------------------------------------------------- Upgrade completed, run SELECT postgis_full_version(); for details (1 Zeile)

Finally, let us utilize \dx again to see what we achieved.

 pdemo=# \dx List of installed extensions Name | Version | Schema | Description ----------------+---------+------------+--------------------------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgis | 3.1.1 | public | PostGIS geometry, geography, and raster spatial types and functions postgis_raster | 3.1.1 | public | PostGIS raster types and functions (3 rows)

Final thoughts on PostGIS Upgrade

Congratulations if you’ve made it this far and followed my steps to upgrade your PostGIS installation, which were sometimes the same. It should be noted that upgrades can be challenging, giving rise to headaches and restless nights, depending on the PostGIS version and OS. Before beginning an upgrade, don’t forget to backup your data and thoroughly evaluate your system.

Annex

from PostGIS versions 2.5 (PostgreSQL =12*) manually upgraded soft

 Alter extension postgis update to '3.1.1'; Create extension postgis_raster from unpackaged;

Semi-automatic soft upgrade from PostGIS versions <2.5

 Alter extension postgis update to '2.5.5'; Select PostGIS_Extensions_Upgrade();

upgrading severely from PostGIS version 2.5

Activate PostGIS and make a new database.

 Create database pdemo_new; \c pdemo_new; Create extension postgis with version '3.1.1'; Create extension postgis_raster with version '3.1.1';

Database backup and recovery

 pg_dump -Fc -b -v -f "pdemo.backup" pdemo pg_restore "pdemo.backup" -d pdemo_new

Rename database names

 alter database pdemo rename to pdemo_old; alter database pdemo_new rename to pdemo;

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex,, business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​