As there are two ways to solve this question, I asked for another approach here.
So far so good, but the problem is that PostGIS also keeps updating and eventually will be incompatible with pinned or
Homebrew/core versions of PostgreSQL. Again, one can pin PostGIS, yet
Homebrew/core does not offer previous versions. From time to time I do
brew cleanup and somehow I am now left with a broken PostGIS database. Ok, so "time to jump to PostgreSQL 10.1", I though, but
pg_upgrade now gets stuck with the error
ERROR: could not access file "$libdir/postgis-2.3": No such file or directory
That means I can't back nor forth: I cannot figure out how to get PostGIS working with
firstname.lastname@example.org and I cannot pg_upgrade to the new version of PostgreSQL (10.1).
Therefore my question can be answered in two ways. Here I am asking:
How to upgrade PostgeSQL with a broken PostGIS installation ("$libdir/postgis-2.3" error)?
The second approach is described at Installing PostGIS on older homebrew versions of PostgreSQL (e.g. email@example.com)?
What I tried so far:
DROP EXTENSION postgis;does not work because the geo-spatial tables depend on it. I could
DROP CASCADEbut this would probably delete my tables and I would like to preserve them.