Skip to content

Instantly share code, notes, and snippets.

Last active July 16, 2024 04:19
Show Gist options
  • Save phortuin/2fe698b6c741fd84357cec84219c6667 to your computer and use it in GitHub Desktop.
Save phortuin/2fe698b6c741fd84357cec84219c6667 to your computer and use it in GitHub Desktop.
Set up postgres + database on MacOS (M1)

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:

Copy link

bolarge commented Feb 2, 2023

Super helpful, thanks.

Copy link

Once you have your cli ready you may want to query the database, I will be maintaining this gist to document the same

Copy link

Very helpful, many thanks

Copy link

Liferenko commented Apr 18, 2023

thanks. Round and clear

Copy link

thanks for sharing, worked like a charm

Copy link

Sazhan99 commented May 1, 2023

Thanks a lot! This guide is very useful

Copy link

Lper258 commented May 6, 2023

Thank you!

Copy link

Clear and nice. Thanks from postgres newbie

Copy link

Thank you.

Copy link

0x-2a commented Sep 26, 2023

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/homebrew.mxcl.postgresql@14.plist"
alias pg_stop="launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist"
# Save and quit

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

# No turn on postgres, run pg_stop to turn off

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://
# user: dev_local
# pass: dev_local

Copy link

Straight to the point!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment