On October 5th the PostgreSQL Global Development Group announced the release of PostgreSQL 10. It comes with tremendous amount of new features like

  • Table partitioning
  • Logical replication
  • Improved parallel queries
  • Stronger password hashing
  • Durable Hash Indexes
  • and more.

A nice list, including explanations can be found on Robert Haas’ blog.

This post explains how to upgrade to the latest version of PostgreSQL on macOS using Homebrew. At the time of this writing I was using macOS 10.30 High Sierra and Postgresql 9.6.5.

  1. Take a backup of your databases just in case anything goes wrong, you can recreate the data:

     $ pg_dumpall | gzip > all_db.sql.gz

  2. Stop the old server:

     $ brew services stop postgresql

  3. Install the new version (the old version remains installed):

     $ brew update
 ....
 $ brew upgrade postgresql
 ....

    You'll see this message at the end of the upgrade process:

     To migrate existing data from a previous major version of PostgreSQL, see:
 https://www.postgresql.org/docs/10/static/upgrading.html

 You will need your previous PostgreSQL installation from brew to perform
 `pg_upgrade` or `pg_dumpall` depending on your upgrade method.

 Do not run `brew cleanup postgresql` until you have performed the migration.

 To have launchd start postgresql now and restart at login:
 brew services start postgresql
 Or, if you don't want/need a background service you can just run:
 pg_ctl -D /usr/local/var/postgres start

    Take a look in /usr/local/Cellar/postgresql. You should see both your old installed version and version 10.0:

     $ ls -l
 total 0
 drwxr-xr-x  12 stade  admin  384 Oct 18 19:28 10.0
 drwxr-xr-x  12 stade  admin  384 Oct  1 12:48 9.6.5

    The default linked binaries, in /usr/local/bin now belong to the 10.0 installation, but the database directory has not yet been upgraded.

     $ which psql
 /usr/local/bin/psql
 $ ls -l /usr/local/bin/psql
 lrwxr-xr-x  1 stade  admin  34 Oct 18 19:28 /usr/local/bin/psql -> ../Cellar/postgresql/10.0/bin/psql

 $ psql -V
 psql (PostgreSQL) 10.0

    If you need to you can switch your binaries between the old and new versions with brew switch postgresql . For now keep the new version active as we'll be using commands from the new version to complete the upgrade.

  4. Move the old cluster data directory out of the way:

     $ mv /usr/local/var/postgres /usr/local/var/postgres.old

  5. Initialize a new cluster data dir:

     $ initdb /usr/local/var/postgres.new

  6. Run the upgrade process:

     $ pg_upgrade -b /usr/local/Cellar/postgresql/9.6.5/bin/ \
 -B /usr/local/Cellar/postgresql/10.0/bin/ \
 -d /usr/local/var/postgres.old/ \
 -D /usr/local/var/postgres.new/

    Use the appropriate old and new binary and cluster directory locations, if they're different from the ones I had.

  7. Move the new cluster into place:

     $ mv postgres.new postgres

  8. Start the new server:

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

 $ pg_ctl -D /usr/local/var/postgres/ status
 pg_ctl: server is running (PID: 59964)
 /usr/local/Cellar/postgresql/10.0/bin/postgres "-D" "/usr/local/var/postgres"

    If you've made any customizations to the database connection configuration files pg_hba.conf and pg_ident.conf you will probably want to copy those changes over to the new cluster.

  9. Run table analyzer to update statistics, check that your data looks right:

     $ ./analyze_new_cluster.sh
 ....
 $ psql
 psql (10.0)
 Type "help" for help.

 stade=# \d
 stade=# \q

  10. If the new data looks right, remove the old data cluster and the old software

    $ ./delete_old_cluster.sh
$ brew cleanup postgresql

  11. In case you had any extensions installed you will have to install them again, or your Postgresql database won't might not start.