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.
Contents
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
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 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 \
--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
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]: *** [postgis_raster-3.so] 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.
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