PostgreSQL Server Setup on Mac OS with MacPorts

This is in particular aimed at using multiple versions of PostgreSQL at different ports.

export VERSION=15

Install

port info postgresql${VERSION}-server
port install postgresql${VERSION}-server

Initial Setup

sudo mkdir -p /opt/local/var/db/postgresql${VERSION}/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql${VERSION}/defaultdb
sudo su postgres -c 'cd /opt/local/var/db/postgresql${VERSION} && /opt/local/lib/postgresql15/bin/initdb -D /opt/local/var/db/postgresql15/defaultdb'

change the port in /opt/local/var/db/postgresql${VERSION}/defaultdb/postgresql.conf

our|my convention is to use "54${VERSION}", currently 5415

Starting manually / Troubleshooting

as user postgres, e.g. sudo -i -u postgres

/opt/local/lib/postgresql${VERSION}/bin/pg_ctl -D /opt/local/var/db/postgresql${VERSION}/defaultdb start -l /opt/local/var/log/postgresql${VERSION}/postgres.log

Add User

initially also as user postgres

CREATE USER tom WITH SUPERUSER LOGIN PASSWORD 'tom';
CREATE DATABASE tom owner tom

My `bash` Config

# POSTGRESQL ##################################################################

function pg_env_set(){
  if [ -z $1 ]; then
    export PGVERSION=$1
  else
    export PGVERSION=${1:-"15"}
  fi

  if shopt -q login_shell; then
     save_echo "SETTING UP POSTGRES ENVIRONMENT FOR VERSION ${PGVERSION}"
  fi

  export POSTGRESQL="/opt/local/lib/postgresql${PGVERSION}"


  case $PGVERSION in
    *)
      export PGPORT="54${PGVERSION}"
      ;;
  esac
  export PATH="$POSTGRESQL/bin:$PATH"
  export PGUSER="tom"
  export PGPASSWORD="tom"
  export PGPIDNAME="pid"

}

function pg_start(){
  sudo -i -u postgres /opt/local/lib/postgresql${PGVERSION}/bin/pg_ctl \
    -D /opt/local/var/db/postgresql${PGVERSION}/defaultdb \
    -l /opt/local/var/log/postgresql${PGVERSION}/postgres.log \
    start
}

function pg_stop(){
  set -euo pipefail
  sudo -u postgres /opt/local/lib/postgresql${PGVERSION}/bin/pg_ctl \
    -D /opt/local/var/db/postgresql${PGVERSION}/defaultdb \
    stop
}

pg_env_set 15


# END POSTGRESQL ##############################################################

avary/MacPort PostgreSQL setup.md