So, you accidentally upgraded your local Postgresql installation. Now you run brew postgresql-upgrade-database. It fails, with this beautiful error message:

pg_dump: error: query failed: ERROR: could not access file "$libdir/postgis-3": No such file or directory

When we started researching (cough google cough) this error message, there weren't really many immediately useful results. You probably stumbled over homebrew-core#60686 or homebrew-core#47110 yourself as well. There kind of is a long(er) history of failing Postgres upgrades with Homebrew when Postgis is involved.

Following, you will find how to fix this. The process has been assembled by working through the above issues. Credit to all those involved. The community provides!

Installing Postgis for your old Postgresql

The upgrade fails because only your latest local Postgres has Postgis installed. When you want to run the upgrade command, brew pulls in your previously installed version but does not install Postgis as well. Hence, we need to do this manually.

First, let's download the sources for your current Postgis version. At the time of this writing, it was 3.1.1.

$ brew info postgis
postgis: stable 3.1.1 (bottled), HEAD
Adds support for geographic objects to PostgreSQL
https://postgis.net/
/usr/local/Cellar/postgis/3.1.1_1 (462 files, 30.0MB) *
  Poured from bottle on 2021-02-26 at 17:58:44
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/postgis.rb 
License: GPL-2.0-or-later

Open the link to the Formula (From: https://github.com/…) in your Browser. There you will see the link to the Postgis source.

class Postgis < Formula
  desc "Adds support for geographic objects to PostgreSQL"
  # This here ⬇
  url "https://download.osgeo.org/postgis/source/postgis-3.1.1.tar.gz"

Let's download the source, unpack and compile it.

First, a word about versions and some more context: we did an upgrade from Postgres v12 to Postgres v13. As our old (v12) installation is missing Postgis, we want to install it there. Hence, we configure against v12. The Postgres v13 already has Postgis installed via Homebrew.

Remember that if you are reading this at some point in the future, your versions will most certainly be different.

$ cd ~/Downloads
$ wget https://download.osgeo.org/postgis/source/postgis-3.1.1.tar.gz 
$ tar xzf postgis-3.1.1.tar.gz
# Note: at some point in the future it is likely you might download a .tar.xz 
# file. Use "unxz" to unpack then:
# $ unxz postgis-X.Y.Z.tar.xz
$ cd postgis-3.1.1/
# Now we configure against our old postgres.
# Please note that all these versions might be outdated when you read this. 
# So first check your `/usr/local/Cellar/` which versions of proj, gettext, 
# json-c, protobuf-c and prcre are latest on your system.
$ ./configure \
    --disable-nls \
    --with-pgconfig=/usr/local/opt/postgresql@12/bin/pg_config \
    --with-projdir=/usr/local/Cellar/proj/7.2.1 \
    --with-gettext=/usr/local/Cellar/gettext/0.21 \
    --with-jsondir=/usr/local/Cellar/json-c/0.15/ \
    --with-protobufdir=/usr/local/Cellar/protobuf-c/1.3.3_4/ \
    --with-pcredir=/usr/local/Cellar/pcre/8.44
# Without this line, the compilation process complains about missing header files.
$ ln -s /usr/local/opt/postgresql@12/include/postgresql/server /usr/local/opt/postgresql@12/include/server
$ make
$ make install

And concluding, the actual upgrade:

$ brew postgresql-upgrade-database

This time around it should run through flawlessly.

I hope this post saves you the 2 hours it took us.

Other Posts

  • Wagtail multi-language and internationalization

    Websites often have an international audience. The process of serving translated content is referred to as "internationalization" or "multi-language" support. Here's how we're approaching that in our web projects using Wagtail.

  • Is System.Text.Json ready for prime-time?

    With the advent of .NET Core 3, there is finally builtin support for JSON, without the need to use the excellent Json.NET. Let's take a quick dive in and see if we should jump ship yet.