PostGIS setup with Ubuntu 20.04.2

PostGIS setup with Ubuntu 20.04.2

In one of his last blogposts, our CEO Hans-Jürgen Schönig explained how to set up PostgreSQL on Ubuntu. He consciously avoided any discussion of how to deal with our beloved PostGIS extension. Let’s fix that up right now by following these steps to implement PostGIS setup:

1. Status quo assessment
2. Setup on the OS level
3. Setup on the database level

Status quo assessment

Let’s assume you followed Hans’ blog post (see link above) and installed PostgreSQL 13 on Ubuntu.
By utilizing apt list –installed, we can quickly list installed packages related to PostgreSQL.

$ sudo apt list --installed | grep postgresql

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]

From the console’s output, we can confirm that both server and client have been installed in version 13.2.1. Together with this version string and the PostGIS support matrix we can ensure that our PostGIS version of interest is finally supported.
The table below displays a relevant excerpt of this matrix:

PostGIS setup on the OS level

The support matrix suggests installing PostGIS packages 3.0 or 3.1 on top of PostgreSQL 13. Now let’s verify that our main apt-repository contains the necessary packages, by utilizing apt-cache search.

$ 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

The listing does not include packages for all minor PostGIS versions. So how is it possible to install not the latest PostGIS 3.1 version, but PostGIS 3.0, instead? As an interim step, let’s output the packages’ version table first.

$ apt-cache policy postgresql-13-postgis-3 postgresql-13-postgis-3-scripts

postgresql-13-postgis-3:
Installed: (none)
Candidate: 3.1.1+dfsg-1.pgdg20.04+1
Version table:
3.1.1+dfsg-1.pgdg20.04+1 500
500 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages
postgresql-13-postgis-3-scripts:
Installed: (none)
Candidate: 3.1.1+dfsg-1.pgdg20.04+1
Version table:
3.1.1+dfsg-1.pgdg20.04+1 500
500 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages

From the output, we realize the repository used does not provide PostGIS 3.0 packages for PostgreSQL 13. So, let’s execute apt-cache policy for postgresql-12 to see and understand the difference:

$ apt-cache policy postgresql-12-postgis-3 postgresql-12-postgis-3-scripts</span>

postgresql-12-postgis-3:
Installed: (none)
Candidate: 3.1.1+dfsg-1.pgdg20.04+1
Version table:
<b>3.1.1</b>+dfsg-1.pgdg20.04+1 500
500 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages
<b>3.0.0</b>+dfsg-6ubuntu4 500
500 http://at.archive.ubuntu.com/ubuntu focal/universe amd64 Packages
postgresql-12-postgis-3-scripts:
Installed: (none)
Candidate: 3.1.1+dfsg-1.pgdg20.04+1
Version table:
<b>3.1.1</b>+dfsg-1.pgdg20.04+1 500
500 http://apt.postgresql.org/pub/repos/apt focal-pgdg/main amd64 Packages
<b>3.0.0</b>+dfsg-6ubuntu4 500
500 http://at.archive.ubuntu.com/ubuntu focal/universe amd64 Packages

The version table now includes minor PostGIS version strings too, which can be handed over to apt-get install, in order to choose one particular PostGIS version. If this version string is not provided, the latest PostGIS version will be installed by default.

For demonstration purposes, let’s install PostGIS 3.1.1 by parameterizing the version string as follows:

sudo apt-get install postgresql-13-postgis-3=3.1.1+dfsg-1.pgdg20.04+1 postgresql-13-postgis-3-scripts=3.1.1+dfsg-1.pgdg20.04+1

By executing apt list –-installed again, we can confirm our successful PostGIS 3 installation on Ubuntu.

$ sudo apt list --installed | grep postgresql

postgresql-13-postgis-3-scripts</b>/focal-pgdg,now 3.1.1+dfsg-1.pgdg20.04+1 all [installed]
postgresql-13-postgis-3</b>/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]

PostGIS setup on the database level

PostGIS must be enabled within each database separately. This implies that PostGIS must be registered as an extension within PostgreSQL’s ecosystem. PostgreSQL kindly provides a view containing all available extensions to verify this.

postgres=# select * from pg_available_extensions where name like 'postgis%';
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 | | 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

From these results, we realize there is more than one extension related to PostGIS setup. Why is that the case, and how should we handle it? PostGIS is a great extension, containing various data types and functions. To group types and functions by topics, the extension is organized in various sub-extensions. The most recent refactoring in this area was carried out by swapping out raster functionality, for instance. That means that someone who deals solely with vector data is not forced to install postgis_raster too.

Finally, let’s create a new database and enable PostGIS 😊.

postgres=# create database pdemo;
CREATE DATABASE
postgres=# \c pdemo;
You are now connected to database "pdemo" as user "postgres".
pdemo=# create extension postgis;
CREATE EXTENSION

Querying pg_extension lists the extensions installed in the context of the current database:

pdemo=# select extname, extversion from pg_extension;
extname | extversion
--------+------------
plpgsql | 1.0
postgis | 3.1.1
(2 rows)

To gather even more information about the PostGIS version installed, you can query postgis_full_version() – in order to see details about which libraries were referenced, too.

pdemo=# select * from postgis_full_version();
POSTGIS="3.1.1 aaf4c79" [EXTENSION] PGSQL="130" GEOS="3.8.0-CAPI-1.13.1 " PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"
(1 row)

Conclusion

We successfully completed our task: PostGIS setup on a fresh and clean Ubuntu. Good preparation should prevent almost anything from going wrong. When it comes to upgrades, things are a bit more complicated. Do not forget to consult the PostGIS support matrix and the folks at Cybertec 😊 for further support.