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