I accidentally upgraded PostgreSQL by a major version via Homebrew, from 13 to 14. Since some of my databases used PostGIS, the standard
brew postgresql-upgrade-database command failed. I ended up downloading Postgres.app to manage PostgreSQL — replacing Homebrew — which allowed me to continue using my old databases on PostgreSQL 13.
I recently updated Homebrew, upgraded some packages, and removed some packages. I started to try to work on a React project but couldn’t authenticate. I checked the logs of the local API I was running and suspected I had a problem with my database. I attempted to look at my data with my graphical client, but couldn’t connect. I tried to inspect it via the command line, and realized I must have accidentally upgraded PostgreSQL 13 to 14 with Homebrew:
$ postgres -D /usr/local/var/postgres  FATAL: database files are incompatible with server  DETAIL: The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0.
brew info postgresql, then copied and pasted and ran Homebrew’s magical script to upgrade my database,
==> Caveats To migrate existing data from a previous major version of PostgreSQL run: brew postgresql-upgrade-database
But it failed. At the end of my output, I saw:
==> Migrating and upgrading data... 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 system-defined composite types in user tables ok Checking for reg* data types in user tables ok Checking for contrib/isn with bigint-passing mismatch ok Checking for user-defined encoding conversions ok Checking for user-defined postfix operators ok Creating dump of global objects ok Creating dump of database schemas some-database-name *failure* Consult the last few lines of "pg_upgrade_dump_36405.log" for the probable cause of the failure. Failure, exiting *failure* Consult the last few lines of "pg_upgrade_dump_40096.log" for the probable cause of the failure. Failure, exiting *failure* Consult the last few lines of "pg_upgrade_dump_42199.log" for the probable cause of the failure. Failure, exiting *failure* Consult the last few lines of "pg_upgrade_dump_31568.log" for the probable cause of the failure. Failure, exiting child process exited abnormally: status 256 Failure, exiting Error: Upgrading postgresql data from 13 to 14 failed! ==> Removing empty postgresql initdb database... ==> Moving postgresql data back from /usr/local/var/postgres.old to /usr/local/var/postgres... Error: Failure while executing; `/usr/local/opt/postgresql/bin/pg_upgrade -r -b /usr/local/Cellar/postgresql@13/13.4/bin -B /usr/local/opt/postgresql/bin -d /usr/local/var/postgres.old -D /usr/local/var/postgres -j 4` exited with 1.
I repeatedly reinstalled
postgresql@13 in different orders and tried running
brew postgresql-upgrade-database again. I finally checked one of the mentioned log files with
vim /usr/local/var/log/pg_upgrade_dump_36405.log. Towards the top of the file, I saw
pg_dump: error: query failed: ERROR: could not access file "$libdir/postgis-3": No such file or directory.
I started to search the internet with Brave. It seems that when you attempt to run
brew postgresql-upgrade-database, Homebrew uses an older version of PostgreSQL to help in the migration without considering or installing PostGIS for the older version. I returned to repeatedly reinstalling
postgis in various orders, and trying to add the PostGIS extensions while running
postgresql@13. I felt convinced that installing
postgis via Homebrew will only associate
I only found one article where someone solved the problem, When Homebrew’s Postgres Upgrade Fails by Luis Nell on the Codista blog. I found multiple posts and comments elsewhere where people said they just abandoned their old data and started from scratch with the upgraded version of PostgreSQL on Homebrew.
At the bottom of the post by Luis Nell, he described how to find and download the PostGIS zip file that Homebrew uses, then how to manually compile and install PostGIS on the old PostgreSQL version. I tried slightly modifying the commands for myself and running:
$ cd ~/Downloads/postgis-3.1.4/ $ ./configure \ --disable-nls \ --with-pgconfig=/usr/local/opt/postgresql@13/bin/pg_config \ --with-projdir=/usr/local/Cellar/proj@7/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.4.0/ \ --with-pcredir=/usr/local/Cellar/pcre/8.45 \ --with-gdalconfig=/usr/local/Cellar/gdal/3.3.2_3/bin/gdal-config \ --with-geosconfig=/usr/local/Cellar/geos/3.9.1/bin/geos-config ln -s /usr/local/opt/postgresql@13/include/postgresql/server /usr/local/opt/postgresql@13/include/server $ make $ make install
make failed. The bottom of the output contained:
clang: warning: no such sysroot directory: '/Library/Developer/CommandLineTools/SDKs/MacOSX11.sdk' [-Wmissing-sysroot] ld: -platform_version sdk version malformed: '11' clang: error: linker command failed with exit code 1 (use -v to see invocation) make: *** [postgis_raster-3.so] Error 1 make: *** [pglib] Error 2 make: *** [all] Error 1
I navigated to
/Library/Developer/CommandLineTools/SDKs/, and I saw
MacOSX11.1.sdk, but not
MacOSX11.sdk. When searching the internet for my output, I found suggestions to create a symlink, with caveats that this may fail or do unexpected things. I also think I found a GitHub repository containing historical versions of
I decided to just abandon using Homebrew for PostgreSQL altogether. I felt I didn’t understand the potential consequences of experimenting with these
sdk files, and was just generally tired of Homebrew problems. I also thought I vaguely recalled spending a night in panic while upgrading from PostgreSQL 12 to 13 with Homebrew less than a year ago.
With Postgres.app, I was able to use my previous databases on PostgreSQL 13 pretty easily, and it seems like it should be easier to upgrade to PostgreSQL 14 when I choose and am prepared to do so.
To switch from Homebrew to Postgres.app, I followed these steps:
# make the directory that Postgres.app would use by default for a PostgreSQL 13 server mkdir /Users/<username>/Library/Application Support/Postgres/var-13 # Copy the contents of the Homebrew PostgreSQL database to the expected Postgres.app data directory cp /usr/local/var/postgresql@13 /Users/<username>/Library/Application Support/Postgres/var-13
sudo mkdir -p /etc/paths.d && echo /Applications/Postgres.app/Contents/Versions/latest/bin | sudo tee /etc/paths.d/postgresapp
At that point, I was able to use Postgres.app to start a PostgreSQL 13 server and access my old data.
On a Ruby on Rails project, when attempting to start the Rails server (
rails s), I started to see an error about
pg_ext.bundle not being found:
Library not loaded: /usr/local/opt/postgresql/lib/libpq.5.dylib (LoadError) Referenced from: /Users/<username>/.rvm/gems/ruby-2.6.6@<project>/gems/pg-1.2.3/lib/pg_ext.bundle Reason: image not found - /Users/<username>/.rvm/gems/ruby-2.6.6@<project>/gems/pg-1.2.3/lib/pg_ext.bundle
To resolve this, I just ran
gem uninstall pg and
gem install pg.
rails s started working.
At this point, I uninstalled all PostgreSQL installations with Homebrew.
brew uninstall postgis brew uninstall postgresql brew uninstall postgresql@13