Patrick

writing, drawing, surfing, painting

Install, Upgrade Postgres on Mac with homebrew

Posted on Author theptrkPosted in postgresql

There are many options to install Postgres. Here we use “homebrew” (brew.sh).

  • Pros: super easy to install
    • Note: This installs the command line console (psql) as well as a PostgreSQL server locally, so you can create your own databases locally if desired.
  • Cons: may be confusing to move across versions: upgrade, downgrade

How to: Install Postgres on Mac with brew

Bash
$ brew update
$ brew doctor

# This installs the command line console (psql) as well as a 
# PostgreSQL server locally, so you can create your own databases 
# locally if desired.
$ brew install postgresql 

$ brew services start postgresql 
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)

GOTCHA: “Wait, I just installed an older Postgres version(postgresql@14)?

If you don’t specify a Postgres version, it downloads a default version that might not be the newest version or an older target version.

How to: Upgrade Postgres on Mac with brew

Plan:

  • install the new version of Postgres
  • use “pg_upgrade” command (postgresql.org):
    • this allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for major version upgrades
    • homebrew may have installed this command in different places, so we will need to search for this command
    • we need to find four directories:
      • 1. old bin dir (-b)
      • 2. new bin dir (B)
      • 3. old config dir (-d)
      • 4. new config dir (-D)
  • delete old postgres version
  • extra credit: delete the old cluster
  • extra credit: setup psql by adding PATH to zshrc
Bash
# step: find the version you want
$ brew search postgres

==> Formulae
check_postgres         postgresql@12          postgresql@15          qt-postgresql
postgresql@10          postgresql@13          postgresql@16          postgis
postgresql@11          postgresql@14 ✔        postgrest

# step: install so you have two versions in parallel
$ brew install postgresql@16

# step: find pg_upgrade
$ find /usr/local/opt /usr/local/Cellar /opt/homebrew/Cellar -name pg_upgrade
find: /usr/local/opt: No such file or directory
find: /usr/local/Cellar: No such file or directory
/opt/homebrew/Cellar/postgresql@14/14.12/bin/pg_upgrade
/opt/homebrew/Cellar/postgresql@16/16.3/bin/pg_upgrade

# step: find the oldbindir (-b) tab autocomplete or use the path above
ls /opt/homebrew/Cellar/postgresql@14/14.12/bin

# step: find the newbindir (-B) tab autocomplete or use the path above
ls /opt/homebrew/Cellar/postgresql@16/16.3/bin

# step: find newconfigdir (-D) and oldconfigdir (-d)
# The data directory (datadir) for PostgreSQL installations using Homebrew 
# is typically located in: 
#   - /usr/local/var/postgresql for Intel Macs or 
#   - /opt/homebrew/var/postgresql for Apple Silicon Macs (ARM architecture).
# Note: 2>/dev/null suppresses error messages
$ find /usr/local/var /opt/homebrew/var -name "postgresql*" -type d 2>/dev/null
/opt/homebrew/var/postgresql@14
/opt/homebrew/var/postgresql@16

# step: run pg_upgrade with four locations
/opt/homebrew/Cellar/postgresql@16/16.3/bin/pg_upgrade \
  --old-bindir=/opt/homebrew/Cellar/postgresql@14/14.12/bin \
  --old-datadir=/opt/homebrew/var/postgresql@14 \
  --new-bindir=/opt/homebrew/Cellar/postgresql@16/16.3/bin \
  --new-datadir=/opt/homebrew/var/postgresql@16

# step: delete old version
$ brew uninstall postgresql@12
Uninstalling /opt/homebrew/Cellar/postgresql@14/14.12... (3,322 files, 45.6MB)

You should see this success output! 🎉

pg_upgrade output “Upgrade Complete”

Extra credit: Note the pg_upgrade command created a script for us “delete_old_clusters.sh”

Bash
# step: look at the script, dont just run scripts!
$ cat ./delete_old_cluster.sh 
#!/bin/sh

rm -rf '/opt/homebrew/var/postgresql@14'

# step: run script
./delete_old_cluster.sh

# step: delete script, you'll never need to run this twice
rm ./delete_old_cluster.sh 

$ brew services start postgresql@16
==> Successfully started `postgresql@16` (label: homebrew.mxcl.postgresql@16)

Extra credit: Setup psql

Bash
$ which psql
psql not found

# remember you can find the bin folder with
$ find /usr/local/opt /usr/local/Cellar /opt/homebrew/Cellar -name pg_upgrade
find: /usr/local/opt: No such file or directory
find: /usr/local/Cellar: No such file or directory
/opt/homebrew/Cellar/postgresql@16/16.3/bin/pg_upgrade

# option 1: willy nilly append to zshrc
$ echo 'export PATH="/usr/local/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc

# option 2: be neat. add to zshrc neatly.
$ vim ~/.zshrc

""" Add this to your zshrc
export PATH="/usr/local/opt/postgresql@16/bin:$PATH"
"""

$ exec zsh

Sources: