PostgreSQL on macOS

Installing PostgreSQL on macOS

Install MacPorts: See InstallingMacPorts, then:

$ sudo port install postgresql15-server

Optionally, install the documentation:

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

Upgrading

These instructions assume you are upgrading from PostgreSQL 13 to PostgreSQL 15.

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

  2. Install the new version of PostgreSQL using MacPorts as described in the section above.

    $ sudo mkdir -p /opt/local/var/db/postgresql15/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql15/defaultdb

  3. Create the database instance as described in the MacPort notes which are displayed after installing the new PostgreSQL server:

    $ sudo su postgres -c 'cd /opt/local/var/db/postgresql15 &&
/opt/local/lib/postgresql15/bin/initdb -D /opt/local/var/db/postgresql15/defaultdb'
$ sudo port load postgresql15-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. Restore the earlier backup:

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

  6. Stop the existing database server:

    $ sudo port unload postgresql13-server

  7. Update the new configuration file:

    $ sudo diff -u /opt/local/var/db/postgresql15/defaultdb/pg_hba.conf \
/opt/local/var/db/postgresql13/defaultdb/pg_hba.conf


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

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

    $ sudo port select --set postgresql postgresql15

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

    $ port variants postgis3
$ sudo port install postgis3 +postgresql15

  10. Start the new version of the server:

    $ sudo port load postgresql15-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

-- Frank Dean - 31 Dec 2023

-- Frank Dean - 26 Jun 2018

Related Topics: PostgreSQL, InstallingMacPorts, MacOSXTips