Upgrading to Postgres 12 and PostGIS 3

My initial testing to upgrade our PostGIS servers to Postgres 12 and PostGIS 3 went well. After that testing I noticed an extra snippet in the output of SELECT PostGIS_Full_Version(); in all of our databases, post-upgrade:

procs need upgrade for use with PostgreSQL "120"

The only way I have found to resolve this message is to perform a hard upgrade of PostGIS databases after the main upgrade is complete. If you have managed PostGIS databases for any amount of time you have likely experienced this before.

"By HARD UPGRADE we mean full dump/reload of postgis-enabled databases. You need a HARD UPGRADE when PostGIS objects' internal storage changes or when SOFT UPGRADE is not possible."

At first I thought this was only affecting a lone, older database that maybe I didn't get cleanly upgraded last time. Then I noticed it affecting a PostGIS-enabled database I had created in the past couple of months on Postgres 11 and PostGIS 2.5.

This post outlines how to upgrade to Postgres 12 and PostGIS 3 from the most recent prior versions.

Warning: You should have good (tested) backups available before performaning database upgrades!

Setup

With Postgres 11.6 and PostGIS 2.5.3 installed, switch to postgres user and create a database to test.

sudo su - postgres
psql -c "CREATE DATABASE test_gis;"
psql -d test_gis -c "CREATE EXTENSION postgis;"

Check PostgreSQL version:

psql -d test_gis -c "SELECT version();"
┌──────────────────────────────────────────────────────────────────────────────┐
│                                   version                                    │
╞══════════════════════════════════════════════════════════════════════════════╡
│ PostgreSQL 11.6 (Ubuntu 11.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled…│
│… by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit                        │
└──────────────────────────────────────────────────────────────────────────────┘

Check PostGIS version:

psql -d test_gis -c "SELECT PostGIS_Full_Version();"

┌──────────────────────────────────────────────────────────────────────────────┐
│                             postgis_full_version                             │
╞══════════════════════════════════════════════════════════════════════════════╡
│ POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e…│
│…771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20…│
│…" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER                 │
└──────────────────────────────────────────────────────────────────────────────┘

Install New Versions

Install the new versions to upgrade to.

 apt install postgis postgresql-12 \
    postgresql-12-postgis-3 postgresql-12-postgis-3-scripts
    postgresql-11-postgis-3

Back in psql, use ALTER EXTENSION to update PostGIS.

([local]) postgres@test_gis=# ALTER EXTENSION postgis UPDATE;
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;
ALTER EXTENSION

Following instructions and running the postgis_extensions_upgrade() function provides some warnings and notices. Nothing too concerning though.

([local]) postgres@test_gis=# SELECT postgis_extensions_upgrade();
NOTICE:  Extension postgis_sfcgal is not available or not packagable for some reason
NOTICE:  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
NOTICE:  Extension postgis_topology is not available or not packagable for some reason
NOTICE:  Extension postgis_tiger_geocoder is not available or not packagable for some reason
┌───────────────────────────────────────────────────────────────────┐
│                    postgis_extensions_upgrade                     │
╞═══════════════════════════════════════════════════════════════════╡
│ Upgrade completed, run SELECT postgis_full_version(); for details │
└───────────────────────────────────────────────────────────────────┘

Again, following instructions we can see we are running PostGIS 3.0 in Postgres 11. Everything looks good at this step.

([local]) postgres@test_gis=# SELECT postgis_full_version();
┌───────────────────────────────────────────────────────────────────────┐
│                         postgis_full_version                          │
╞═══════════════════════════════════════════════════════════════════════╡
│ POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.…│
│…1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, relea…│
│…sed 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" W…│
│…AGYU="0.4.3 (Internal)" RASTER                                        │
└───────────────────────────────────────────────────────────────────────┘

Upgrade Postgres

As the postgres user, create a temp directory to work in to keep the generated files together.

sudo su - postgres
mkdir ~/tmp
cd ~/tmp

Ensure both servers are stopped.

/usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main/ stop
/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/main/ stop

Run the upgrade with the --check flag.

/usr/lib/postgresql/12/bin/pg_upgrade -b /usr/lib/postgresql/11/bin/ \
    -B /usr/lib/postgresql/12/bin/ \
    -d /var/lib/postgresql/11/main/ \
    -D /var/lib/postgresql/12/main/ \
    -o ' -c config_file=/etc/postgresql/11/main/postgresql.conf' \
    -O ' -c config_file=/etc/postgresql/12/main/postgresql.conf' \
    --link --check

If you got PostGIS 3 installed properly you should receive output like the following.

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 ok
Checking database user is the install user                  ok
Checking for prepared transactions                          ok

*Clusters are compatible*

Re-run the above and omit the --check flag to peform the upgrade.

Post Upgrade

Start the upgraded server.

/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/main/ \
    start -o  ' -c config_file=/etc/postgresql/12/main/postgresql.conf'

Now the message I started this with, procs need upgrade for use with 12. At this point it seems PostGIS is functional and simply misses out on some Postgres 12 extras. There have been other times that things seem one simple way but it turns out to be more complex.

([local]) postgres@test_gis=# SELECT postgis_full_version();
┌──────────────────────────────────────────────────────────────────────────────┐
│                             postgis_full_version                             │
╞══════════════════════════════════════════════════════════════════════════════╡
│ POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="110" (procs need upgrade for use w…│
│…ith PostgreSQL "120") GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15…│
│… August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON=…│
│…"0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal)" RASTER                 │
└──────────────────────────────────────────────────────────────────────────────┘

Trying out the function postgis_extensions_upgrade() again returns an error.

([local]) postgres@test_gis=# SELECT postgis_extensions_upgrade();
NOTICE:  Updating extension postgis 3.0.0
ERROR:  cannot change name of input parameter "line1"
HINT:  Use DROP FUNCTION st_linecrossingdirection(geometry,geometry) first.
CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO "3.0.0next";"
PL/pgSQL function postgis_extensions_upgrade() line 69 at EXECUTE

Solution

With the upgrade function not resolving the warning, the solution is to create a new database with a fresh PostGIS installation and reload the database. This is (unfortunately) a per-database solution.

psql -c "CREATE DATABASE test_gis2;"
psql -d test_gis2 -c "CREATE EXTENSION postgis;"

A simple example of using pg_dump and psql to dump and restore a database is below. For a more complete explanation on ways to use pg_dump see this post.

pg_dump -d test_gis -f test_gis_dump.sql \
    && psql -d test_gis2 -f test_gis_dump.sql

Now connecting to the database test_gis2 the extension reports being installed properly.

SELECT postgis_full_version();
┌──────────────────────────────────────────────────────────────────────────────┐
│                             postgis_full_version                             │
╞══════════════════════════════════════════════════════════════════════════════╡
│ POSTGIS="3.0.0 r17983" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 27a5e…│
│…771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20…│
│…" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" WAGYU="0.4.3 (Internal…│
│…)" RASTER                                                                    │
└──────────────────────────────────────────────────────────────────────────────┘

Summary

PostGIS is a complex extension and the upgrade to take advantage of Postgres 12 improvements is a little more involved this time. The extra step isn't too difficult on small databases, larger databases will have to be more careful about the process to keep downtime to a minimum. Postgres 12 includes improvements to parallel processing, GIST indexes, and more and is worth considering the upgrade.

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!