Upgrading PostgreSQL Using Homebrew
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.
Take a backup of your databases just in case anything goes wrong, you can recreate the data:
$ pg_dumpall | gzip > all_db.sql.gz
Stop the old server:
$ brew services stop postgresql
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.
Move the old cluster data directory out of the way:
$ mv /usr/local/var/postgres /usr/local/var/postgres.old
Initialize a new cluster data dir:
$ initdb /usr/local/var/postgres.new
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.
Move the new cluster into place:
$ mv postgres.new postgres
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_ident.confyou will probably want to copy those changes over to the new cluster.
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
If the new data looks right, remove the old data cluster and the old software
$ ./delete_old_cluster.sh $ brew cleanup postgresql
In case you had any extensions installed you will have to install them again, or your Postgresql database won’t might not start.