Downloading postgres with brew

How to install postgres

Bash
# check what versions you have installed

brew search postgresql
# ==> Formulae
# postgresql-hll    postgresql@12     postgresql@14     postgresql@16 ✔   qt-postgresql
# postgresql@11     postgresql@13     postgresql@15     postgresql@17     postgrest

# install your choice
brew install postgresql@17

# check to see if its running
brew services list
# Name          Status  User        File
# postgresql@17 started patricktran ~/Library/LaunchAgents/homebrew.mxcl.postgresql@17.plist
# redis         none

# start the server
brew services restart postgresql@17
# optionally stop an old version first
# brew services stop postgresql@16

# link your choice
brew unlink postgresql@16
brew link postgresql@17 --force

# check psql
which psql
psql --version

# check the symlink
ls -l /opt/homebrew/bin/psql
# lrwxr-xr-x  1 patricktran  admin  37 Jun  1 23:31 /opt/homebrew/bin/psql -> ../Cellar/postgresql@17/17.5/bin/psql

# Location of the data directory (where your databases are stored)
ls /opt/homebrew/var/postgresql@17

What is happening?

Bash
psql is equivalent to

psql -U $(whoami) -d $(whoami)

-U specifies the postgres role (user)
-d specifies the database
$(whoami) returns your current system username


So by default, psql automatically tries to connect to
- a postgres role also named after your username
- a database also named after your username

When you install and initialize Postgres via Homebrew:
It sets up:
- A data directory where all your database files live
- A default superuser role, named after your local system user (patricktran)
- No databases (other than postgres, the default system DB) — you must create your own

^ is this more readable outside the code block?

So by default, psql automatically tries to connect to
– a postgres role also named after your username
– a database also named after your username

When you install and initialize Postgres via Homebrew:
It sets up:
– A data directory where all your database files live
– A default superuser role, named after your local system user (patricktran)
– No databases (other than postgres, the default system DB) — you must create your own

How to create a database

Bash
createdb patricktran

# Notice a superuser with $USER has been created
psql -d postgres -c '\du'

                              List of roles
  Role name  |                         Attributes
-------------+------------------------------------------------------------
 patricktran | Superuser, Create role, Create DB, Replication, Bypass RLS


The role patricktran is a superuser so it can:
-  Access to any database (even ones not yet created)
-  Ability to create/drop roles
-  Ability to DROP DATABASE or ALTER SYSTEM
-  Bypass any permission checks
-  Own system-wide settings (like replication)


psql -d postgres

# check your current user
SELECT current_user, session_user;
# list databases
\l
# list roles
\du
# list tables in current DB
\dt