Easy setup PostgreSQL on MacOS

Lately, I've been working on a personal project of mine, and this one particular project uses postgresql, so I'm going to go over my setup of postgresql (particularly on MacOS) in this post. :D

TOC

• Installing postgresql
• Creating user and database
• Use our newly created user and database
• Final note and refs

Installing postgresql

I used homebrew to install postgresql on my machine.

brew install postgresql

Run postgres -V to check the version (and also if it's installed).
To get postgresql up and running, run

brew services start postgres
# or
pg_ctl -D /usr/local/var/postgres start

_{Note: pg_ctl is a utility for starting, stopping, or restarting the PostgreSQL backend server that comes with postgresql}

Creating user and database

Postgresql installed with homebrew automatically creates a user with the same name as your username with no password assigned. This user has a SUPERUSER role. However, it's not really good practice to use this user. Hence, we're creating a new one.

_{Note: there are other ways besides doing it via psql, but I'm not covering that here 🥺}

• First of all, we're going to run psql. We need to specify which database we're running from. Since there's a pre-created database called postgres, we're going to use that.

psql -d postgres

• Let's create a user (or aka role in postgresql term)

# replace <_> with your own thing
CREATE USER <user> WITH ENCRYPTED PASSWORD '<password>';
# check if your user was created
SELECT usename FROM pg_user;  # will show a table of user
# or run a psql meta command
\du

• Let's give our user a role (or they'll just be crippled). For more info on role (or if there are other role attributes besides CREATEDB), check this great article from prisma.

ALTER ROLE <user> CREATEDB;

• Next, let's create a database. Note that you can grant different types of access (besides ALL PRIVILEGES that was used here). For more info, check this awesome article from TablePlus or check the official document.

# again, replace <_> with yours
CREATE DATABASE <db_name>;
GRANT ALL PRIVILEGES ON DATABASE <db_name> TO <user>;
# list all databases to see if ours was created
\list

Use our newly created user and database

To do this, you can...

1. Switch user and database inside psql session

\c <db_name> <user>

1. Or you can stop current psql session and start it using new user and database

\q  # to quit
psql -d <db_name> -U <user> 

Final note and refs

And that's it, y'all. It wasn't hard, but it can be confusing for the first time. (Yes, I'm talking from my (in)experience here 🥲)

Cover Photo by Shubham Dhage on Unsplash
ref1 | ref2