Last time, we installed PostGIS on top of PostgreSQL. Today, I will complement this article by describing how to upgrade PostGIS on Ubuntu. A detailed description can be found at postgis.net and should be referred to in parallel.
Let me first define our scenario and goal:
An artificial customer wants his PostGIS-enabled PostgreSQL 13 cluster running on Ubuntu 20.04.02 to be upgraded. The current PostGIS extension states version 2.5 and should be upgraded to its latest version. At the customer site, one spatial database serving both vector and raster data must be upgraded to its successor.
It should be highlighted here, that PostGIS upgrades can be accomplished in two ways, namely a soft or hard upgrade. A soft upgrade refers to only the binary upgrade of a PostGIS’ extension, a hard upgrade implies dumping and restoring the whole database in a fresh PostGIS enabled database. From this description, it would be natural to choose the soft upgrade path by default. Unfortunately, a soft upgrade is not possible all the time, especially when PostGIS objects’ internal storage changes. So how do we know which path to follow? Fortunately, PostGIS release notes explicitly state when a hard upgrade is required. Seems we are in luck and a soft upgrade is sufficient for our use case 😊. For completeness, the annex contains needed steps to carry out a hard upgrade too.
Now, let’s break down this task in the following steps:
- Status quo assessment
- PostGIS upgrade on OS level
- PostGIS upgrade on database level
Status quo
Let’s start and cross-check statements with reality by listing installed packages on the OS level.
sudo apt list --installed | grep postgresql
From the listing, we realize that PostGIS extension 2.5 has been installed 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]
Verify PostGIS extension
It makes sense to extend our test by verifying that our PostGIS extension has been registered correctly within 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, surprise – results of our query confirm the availability of PostGIS extension(s) in version 2.5 only. Finally, it is not only interesting to list available extensions on a cluster level but rather explicitly the listing installed extensions on the database level.
Use \dx
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)
How to get more detailed info on the PostGIS extension
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)
I, by the way, recommend using all available mechanisms to assess the status on the cluster and database level to gain a holistic picture of the system.
PostGIS Upgrade on the OS level
After assessing our cluster, we can move forward and install the latest PostGIS packages for PostgreSQL 13 on Ubuntu 20.
Let’s quickly ensure that the required 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 - Geographic objects support for PostgreSQL 13 postgresql-13-postgis-2.5-dbgsym - debug symbols for postgresql-13-postgis-2.5 postgresql-13-postgis-2.5-scripts - Geographic objects support for PostgreSQL 13 -- 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
Install the latest PostGIS 3 version
Seems we are fine and can install the latest PostGIS 3 version as follows:
sudo apt-get install postgresql-13-postgis-3 postgresql-13-postgis-3-scripts
Verify packages registration within PostgreSQL
It can’t hurt to replay what I mentioned in the beginning: query installed PostgreSQL packages on the OS level (1) and verify packages registration (2) within PostgreSQL.
sudo apt list --installed | grep 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)
PostGIS upgrade on 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";
Upgrade and install PostGIS_Raster
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’s 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)
Conclusion on PostGIS Upgrade
Congratulations! You made it here and followed my partially repetitive steps to upgrade your PostGIS installation. It should be stated that depending on the PostGIS version and OS, upgrades can turn out to be tricky and cause headaches and sleepless nights. Do not forget to backup and assess your system comprehensively before initiating an upgrade!
Annex
Manual soft upgrade from PostGIS versions <2.5 (PostgreSQL <=12*)
Alter extension postgis update to '3.1.1'; Create extension postgis_raster from unpackaged;
*-From unpackaged removed by PostgreSQL version 13.0, https://www.postgresql.org/docs/release/13.0/
Semi-automatic soft upgrade from PostGIS versions <2.5
Alter extension postgis update to '2.5.5'; Select PostGIS_Extensions_Upgrade();
Hard upgrade from PostGIS version 2.5
1. Create a new database and enable PostGIS
Create database pdemo_new; \c pdemo_new; Create extension postgis with version '3.1.1'; Create extension postgis_raster with version '3.1.1';
2. Dump and restore database
pg_dump -Fc -b -v -f "pdemo.backup" pdemo pg_restore "pdemo.backup" -d pdemo_new
3. Rename databases
alter database pdemo rename to pdemo_old; alter database pdemo_new rename to pdemo;
In case you’d like to read more about PostGIS, see my post about upgrading PostGIS-related libraries such as GEOS and GDAL.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.