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
- Installing postgresql
- Creating user and database
- Use our newly created user and database
- Final note and refs
I used homebrew to install postgresql on my machine.
brew install postgresql
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
pg_ctl is a utility for starting, stopping, or restarting the PostgreSQL backend server that comes with postgresql
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 PRIVILEGESthat 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
To do this, you can...
- Switch user and database inside
\c <db_name> <user>
- Or you can stop current
psqlsession and start it using new user and database
\q # to quit psql -d <db_name> -U <user>
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 🥲)