Skip to content

Instantly share code, notes, and snippets.

Last active November 3, 2023 11:07
Star You must be signed in to star a gist
What would you like to do?
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


Copy link

g2px1 commented Sep 21, 2022


Copy link

Cna59 commented Oct 17, 2022


Copy link


Copy link

phortuin commented Nov 9, 2022


Thank You So Much! Would like to know how I can view the server and tables in pgAdmin4. I installed pgAdmin4, but I can't see what I did in the tutorial. Would you help me?

In pgAdmin4, select 'Add New Server' from the Welcome screen. You can choose any name (it’s just a label for that server that you use in pgAdmin4). Under the 'Connection' tab, set localhost as the Host name and myuser as the username. Save, and you will find your server in the Browser list on the left of the screen. If you expand your’s server’s structure, under 'Databases' you should see mydatabase. Hope this helps.

Copy link

Thanks a lot

Copy link

ghost commented Nov 17, 2022

thank you!

Copy link

thank you so much.
When i ran

brew install postgresql

It installed postgresql@14
So i had to start the PostgreSQL with following command

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

Copy link

thank you so much. When i ran

brew install postgresql

It installed postgresql@14 So i had to start the PostgreSQL with following command

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

Yeah this also worked form me ;)

Copy link

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

Copy link

This works for me, thanks.

Copy link

Thank you!

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