PostgreSQL on macOS

Installing PostgreSQL on macOS

Install MacPorts: See InstallingMacPorts, then:

$ sudo port install postgresql17-server

For versions of PostgreSQL before 16, optionally, install the documentation (newer versions install the documentation by default):

$ sudo port install postgresql15-doc

See the notes that are listed after the installation and follow the instructions therein.

Thereafter the server can be started and stopped with:

$ sudo port load postgresql17-server
$ sudo port unload postgresql17-server

Upgrading

These instructions assume you are upgrading from PostgreSQL 16 to PostgreSQL 17.

  1. Install the documentation as described above and see /opt/local/share/doc/postgresql17/html/upgrading.html

    $ open /opt/local/share/doc/postgresql17/html/upgrading.html

  2. Install the new version of PostgreSQL using MacPorts as shown in the notes after installing the server port.

    $ sudo port select postgresql postgresql17
$ sudo mkdir -p /opt/local/var/db/postgresql17/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql17/defaultdb

  3. Create the database instance:

    $ sudo -u postgres /bin/sh -c 'cd /opt/local/var/db/postgresql17 && \
/opt/local/lib/postgresql17/bin/initdb -D \
/opt/local/var/db/postgresql17/defaultdb'


$ sudo port load postgresql17-server

  4. Leave the old database version running and create a dump of the existing database, using the new version of pg_dumpall

    $ port select --list postgresql
$ port select --show postgresql

  5. Create a database backup using the newer pg_dumpall:

    $ /opt/local/lib/postgresql17/bin/pg_dumpall >backup.dmp

  6. Stop the existing database server:

    $ sudo port unload postgresql16-server

  7. Update the new configuration file:

    $ sudo diff -u /opt/local/var/db/postgresql16/defaultdb/pg_hba.conf \
/opt/local/var/db/postgresql17/defaultdb/pg_hba.conf


$ sudoedit /opt/local/var/db/postgresql17/defaultdb/pg_hba.conf

  8. Use the port select command to switch to the newly installed PostgreSQL binaries

    $ sudo port select --set postgresql postgresql17

  9. Optionally, if using PostGIS, upgrade that postgis too:

    $ port search -n 'pg*-postgis3'
$ sudo port install pg17-postgis3

  10. Start the new version of the server:

    $ sudo port load postgresql17-server

  11. As the Unix PostgreSQL user, create the superuser role in the new database:

    $ cd /
$ sudo -u postgres createuser -drs $USER_NAME
$ cd -
$ psql -l

  12. Restore the database from the dump created earlier:

    $ psql -d postgres -f dump.sql

Trouble-shooting

Check the PostgreSQL log:

$ tail -n 20 /opt/local/var/log/postgresql17/postgres.log

-- Frank Dean - 14 May 2025

-- Frank Dean - 26 Jun 2018

Related Topics: PostgreSQL, InstallingMacPorts, MacOSXTips