PostgreSQL on macOS

Installing PostgreSQL on macOS

Install MacPorts: See InstallingMacPorts, then:

$ sudo port install postgresql96-server

Optionally, install the documentation:

$ sudo port install postgresql96-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 postgresql96-server
$ sudo port unload postgresql96-server


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

Install the new version of PostgreSQL using MacPorts as described above and follow the instructions as far as creating the database instance. Do not start the server yet.

These instructions assume you are upgrading from PostgreSQL 9.5 to PostgreSQL 9.6.

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

As a user with PostgreSQL superuser privileges:

$ /opt/local/lib/postgresql96/bin/pg_dumpall >dump.sql

Stop the existing database server:

$ sudo port unload postgresql95-server

Update the new configuration file:

$ diff -u /opt/local/var/db/postgresql95/defaultdb/pg_hba.conf \

$ sudo cp -p /opt/local/var/db/postgresql95/defaultdb/pg_hba.conf \

Use the port select command to switch to the newly installed PostgreSQL binaries and start the new version of the server:

$ sudo port select --set postgresql postgresql96
$ sudo port load postgresql96-server

As the Unix user who is the PostgreSQL user you used to perform the pg_dumpall command, create the superuser role in the new database:

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

Restore the database from the dump created earlier:

$ psql -d postgres -f dump.sql

-- Frank Dean - 26 Jun 2018

Related Topics: PostgreSQL, InstallingMacPorts, MacOSXTips