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.
