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

❤️

@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.

Thanks a lot

thank you!

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

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

@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