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 to manage PostgreSQL replacing Homebrew which allowed me to continue using my old databases on PostgreSQL 13.


  1. The Process
  2. Taking My Data and Leaving Homebrew

The Process

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
[21669] FATAL:  database files are incompatible with server
[21669] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0.

I ran brew info postgresql, then copied and pasted and ran Homebrew’s magical script to upgrade my database, brew postgresql-upgrade-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

Consult the last few lines of "pg_upgrade_dump_36405.log" for
the probable cause of the failure.
Failure, exiting


Consult the last few lines of "pg_upgrade_dump_40096.log" for
the probable cause of the failure.
Failure, exiting


Consult the last few lines of "pg_upgrade_dump_42199.log" for
the probable cause of the failure.
Failure, exiting


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 and 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 postgresql@13, postgresql, and 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 postgis with postgresql, not postgresql@13.

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 \
ln -s /usr/local/opt/postgresql@13/include/postgresql/server /usr/local/opt/postgresql@13/include/server
$ make
$ make install

But 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[2]: *** [] Error 1
make[1]: *** [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 MacOSX<xx>.sdk.

Taking My Data and Leaving Homebrew

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, 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, I followed these steps:

  1. Download with all currently supported versions (Universal)
  2. Install by moving it to the Applications folder
  3. Copied my data directory used by the Homebrew installation to the data directory that I planned to use for with PostgreSQL 13
# make the directory that 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 data directory
cp /usr/local/var/postgresql@13 /Users/<username>/Library/Application Support/Postgres/var-13
  1. Clicked the “+” button at the bottom left of
  2. Created a new server with PostgreSQL version 13 and the data directory created and pre-populated above
  3. Followed the instructions on setting up your path:
sudo mkdir -p /etc/paths.d &&
echo /Applications/ | sudo tee /etc/paths.d/postgresapp

At that point, I was able to use 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