Upgrading Postgresql on macOS

Many Mac users are perfectly well served by install PostgreSQL through homebrew and letting it automatically upgrade and migrate your data. For work though, I have to be sure I’m running the same version on my local dev machine as our dev and prod servers.

I keep things from changing underneath me by using the installers provided by EDB, which are free (they make money on support contracts and an enterprise version of PG).

Upgrading is something I have to do just infrequently enough that I forget the details and have to figure it out again each time, so I’m writing this for with hopes that it also can benefit other people.

Starting with the assumption that we’re upgrading from 11.x to 12.x, but subsitute the older version and newer version on your system as necessary.

• Install new version of EDB Postgres

• Stop it with: sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-12.plist

• sudo su postgres and then cd /Library/PostgreSQL/11/data/ (you need to run the upgrade command in a directory postgres user has write perms in)

  • Edit /Library/PostgreSQL/11/data/pg_hba.conf and change local auth lines from md5 to trust (this is temporary because the upgrade script doesn’t pass in passwords)
  • Do same for 12
  • Check that the upgrade script is going to work:

  /Library/PostgreSQL/12/bin/pg_upgrade \
      --old-datadir=/Library/PostgreSQL/11/data \
      --new-datadir=/Library/PostgreSQL/12/data
      --old-bindir=/Library/PostgreSQL/11/bin \
      --new-bindir=/Library/PostgreSQL/12/bin \
      --old-options='-c config_file=/Library/PostgreSQL/11/data/postgresql.conf' \
      --new-options='-c config_file=/Library/PostgreSQL/12/data/postgresql.conf' \
      --check

• Stop old server with: sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-11.plist

• Run upgrade script without --check

  /Library/PostgreSQL/12/bin/pg_upgrade \
      --old-datadir=/Library/PostgreSQL/11/data \
      --new-datadir=/Library/PostgreSQL/12/data
      --old-bindir=/Library/PostgreSQL/11/bin \
      --new-bindir=/Library/PostgreSQL/12/bin \
      --old-options='-c config_file=/Library/PostgreSQL/11/data/postgresql.conf' \
      --new-options='-c config_file=/Library/PostgreSQL/12/data/postgresql.conf'
  

• Edit /Library/PostgreSQL/12/data/postgresql.conf and change port to 5432

• Edit /Library/PostgreSQL/11/data/postgresql.conf and change port to 5433 (optional; you can also just uninstall it by running the uninstall app at /Library/PostgreSQL/11/uninstall-postgresql.app)

• Edit /Library/PostgreSQL/12/data/pg_hba.conf and change local auth lines from trust to md5

• Do same for 11 (if you aren’t uninstalling it)

• Run ./analyze_new_cluster.sh to optimize the new databases

• Start the new server with: sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-12.plist