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 calledpostgres
, 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...
- Switch user and database inside
psql
session
\c <db_name> <user>
- 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
Top comments (0)