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.

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:

PostgreSQL version	PostGIS 2.4.	PostGIS 2.5	PostGIS 3.0	PostGIS 3.1 (master)
PostGIS release date	2017/09/30	2018/09/23	2019/10/20	2020/XX/XX
13	No	No	Yes*	Yes*
12	No	Yes	Yes	Yes
11	Yes*	Yes	Yes	Yes
10	Yes	Yes	Yes	Yes
9.6	Yes	Yes	Yes	Yes
9.5	Yes	Yes	Yes	No

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.

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.

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:

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:

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

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.

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 ????.

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

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.

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.