So, you accidentally upgraded your local Postgresql installation. Now you run brew postgresql-upgrade-database. It fails, with one of these beautiful error message:
pg_dump: error: query failed: ERROR: could not access file "$libdir/postgis-3": No such file or directory
# or:
Consult the last few lines of "pg_upgrade_dump_$SOMENUMBER.log" for
the probable cause of the failure.
Failure, exiting
pg_upgrade_dump log line, you can find the mentioned log files in /usr/local/var/log/. When you open these files, you will encounter various issues. However, one of the files should contain our original postgis error line from the snippet above
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. When you read this, it will most likely be a later version. Just use that one. Our instructions have been working for a couple of years now.
$ 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 though, a word about versions and some more context: in our example here, Homebrew already made the upgrade from Postgresql 12 to Postgresql 13. Our old (v12) installation is now missing it's Postgis though! That's why everything fails. So, we want to install Postgis for Postgresql v12. Hence, we configure against v12. Our installed Postgres v13 already had it's Postgis installed via brew.
Again, remember that if you are reading this at some point in the future, your versions will most certainly be different. That does not matter. Adjust and proceed!
$ 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. Then adjust the
# below line accordingly!
$ ./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.
# Again, adjust the version number. Use the old version.
$ 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.
