Skip to content

Instantly share code, notes, and snippets.

@phortuin
Last active October 10, 2024 23:00
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:

PG_CONNECTION_STRING=postgres://myuser@localhost/mydatabase
@mgan59
Copy link

mgan59 commented Jan 19, 2022

👍 Looking good thanks for sharing/posting, these instructions work for folks running default homebrew/postgres (at this time 14), but if you need a previous version you will look for 13.x in /opt/homebrew/var/postgresql@13

@cadyherron
Copy link

This worked perfectly for me. Thanks so much for putting it together!

@swalahamani
Copy link

Thanks @phortuin 🙏

@maximumjpeg
Copy link

Great info here! Helped me get set up for a big school project. My compliments to the chef!!🤌

@mateuszbaldyga
Copy link

Thank You So Much! ❤️

@orestesnetobr
Copy link

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?

@Pander64
Copy link

Thanks

@g2px1
Copy link

g2px1 commented Sep 21, 2022

❤️

@Cna59
Copy link

Cna59 commented Oct 17, 2022

👍

@DEVSOG12
Copy link

❤️

@phortuin
Copy link
Author

phortuin commented Nov 9, 2022

@orestesnetobr

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.

@tawheedrony
Copy link

Thanks a lot

Copy link

ghost commented Nov 17, 2022

thank you!

@bowrna-unravel
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

@emadbaqeri
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 ;)

@phortuin
Copy link
Author

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

@hassanyahya400
Copy link

This works for me, thanks.

@benoitlahoz
Copy link

Thank you!

@bolarge
Copy link

bolarge commented Feb 2, 2023

Super helpful, thanks.

@maheshj01
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 https://gist.github.com/maheshmnj/f9149479a3eda49d34ae53aa4c6e80dc

@jessieharada6
Copy link

Very helpful, many thanks

@Liferenko
Copy link

Liferenko commented Apr 18, 2023

thanks. Round and clear

@placidee
Copy link

thanks for sharing, worked like a charm

@Sazhan99
Copy link

Sazhan99 commented May 1, 2023

Thanks a lot! This guide is very useful

@Lper258
Copy link

Lper258 commented May 6, 2023

Thank you!

@beyondswamps
Copy link

Clear and nice. Thanks from postgres newbie

@puneeth-chanda
Copy link

Thank you.

@0x-2a
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
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

@cetinozgur
Copy link

Straight to the point!

@ramkrishnagalla
Copy link

Thanks for detailed article

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