Set up postgres + database on MacOS (M1)

postgres.md

Based on this blogpost.

Install with Homebrew:

$ brew install postgresql@14

(The version number 14 needs to be explicitly stated. The @ mark designates a version number is specified. If you need an older version of postgres, use postgresql@13, for example.)

Run server:

$ pg_ctl -D /opt/homebrew/var/postgresql@14 start

Note: if you’re on Intel, the /opt/homebrew probably is /usr/local.

Start psql and open database postgres, which is the database postgres uses itself to store roles, permissions, and structure:

$ psql postgres

We need to create a role (user) with permissions to login (LOGIN) and create databases (CREATEDB). In PostgreSQL, there is no difference between users and roles. A user is simply a role with login permissions. The first line below could be rewritten as CREATE USER myuser;:

postgres-# CREATE ROLE myuser WITH LOGIN;
postgres-# ALTER ROLE myuser CREATEDB;

Note that the user has no password. Listing users \du should look like this:

postgres-# \du
                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 <root user> | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 myuser      | Create DB                                                  | {}

Quit psql, because we will login with the new user to create a database:

postgres-# \q

On shell, open psql with postgres database with user myuser:

$ psql postgres -U myuser

Note that the postgres prompt looks different, because we’re not logged in as a root user anymore. We’ll create a database and grant all privileges to our user:

postgres-> CREATE DATABASE mydatabase;
postgres-> GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

List databases to verify:

postgres-> \list

If you want to connect to a database and list all tables:

postgres-> \c mydatabase
mydatabase-> \dt

...should print Did not find any relations. for an empty database. To quit the postgres CLI:

mydatabase-> \q

Finally, in a .env file for Node.js software development, your database connection string should look like this:

PG_CONNECTION_STRING=postgres://myuser@localhost/mydatabase

@bowrna-unravel @emadbaqeri thanks for the heads up, I updated the instructions to reflect these changes 👍

This works for me, thanks.

Thank you!

Super helpful, thanks.

Once you have your cli ready you may want to query the database, I will be maintaining this gist to document the same https://gist.github.com/maheshmnj/f9149479a3eda49d34ae53aa4c6e80dc

Very helpful, many thanks

thanks. Round and clear

thanks for sharing, worked like a charm

Thanks a lot! This guide is very useful

Thank you!

Clear and nice. Thanks from postgres newbie

Thank you.

Here's an updated version, which has the idea of "this is a local dev db, just grant everything to my user and set their password".

brew install postgresql@14

# Copy launch agents for background start/stop.
ln -sfv /opt/homebrew/opt/postgresql@14/*.plist ~/Library/LaunchAgents

# Add aliases to shell profile to always have available
open ~/.zprofile # or vi ~/.zprofile

# In ~/.zprofile, add to the bottom
# 
alias pg_start="launchctl load ~/Library/LaunchAgents/[email protected]"
alias pg_stop="launchctl unload ~/Library/LaunchAgents/[email protected]"
#
# Save and quit

# Load what you just added in zprofile
source ~/.zprofile

# No turn on postgres, run pg_stop to turn off
pg_start 

createdb localdb

# open psql console
psql postgres 

# Inside the psql console, Use semicolons!
# 
#
create role dev_local with password 'dev_local' login;
alter role dev_local superuser createdb createrole replication bypassrls;
grant all privileges on database localdb to dev_local;
grant usage on schema public to dev_local;
alter database localdb owner to dev_local;
alter schema public owner to dev_local;
\list # Check dbs
\du # Check roles
\dn+ # Check owners
\q # Quit
#
#
# End psql console

# CONN URL is postgresql://127.0.0.1:5432/localdb
# user: dev_local
# pass: dev_local

Straight to the point!

Thanks for detailed article