Cheatsheet: PostgreSQL

Created: Aug 25, 2025 | Updated: Aug 25, 2025

This cheatsheet contains the steps I take to set up and interact with PostgreSQL, mainly when I'm developing something locally. It documents commands and flags that I use but forget when I haven't worked with PostgreSQL in a while. The commands below work on Arch Linux so they may need to be modified if you want to run them on a different system. They also assume that you're using the default host, localhost, and port, 5432.

You don't have to go through steps 1-6 every time. You likely only need to go through a subset of them depending on your use case:

When setting up PostgreSQL for the first time, you probably want to do: 1, 2, 3, 4, 6.
When you have a new project to set up, you may want to do: 2, 3, 4, 6.
When you want to connect with a database via the command line, you can do: 2, 5, 6.

Steps

Initialize cluster:
```
sudo --user postgres initdb --pgdata <path>
```
Or shorthand:
```
sudo -u postgres initdb -D <path>
```
Notes:
- This is the bare minimum command to initialize a cluster, which is fine locally, but you probably want to set the authentication method, the bootstrap superuser's password, etc. for remote systems.
- --pgdata or -D can be omitted if the PGDATA environment variable is set.
Start server:
```
sudo --user postgres pg_ctl start --pgdata <path> --log <path>
```
Or shorthand:
```
sudo -u postgres pg_ctl start -D <path> -l <path>
```
Notes:
- You may need to run mkdir /run/postgresql then chown postgres:postgres /run/postgresql once with root privileges before you can start the server.
- --pgdata or -D can be omitted if the PGDATA environment variable is set.
- --log or -l is optional but it's nice to have a logfile in case you need to check what happened.
Create user:
```
sudo --user postgres createuser --superuser <username>
```
Or shorthand:
```
sudo -u postgres createuser -s <username>
```
Notes:
- This step is optional if you plan to use the default postgres superuser.
- Passing --superuser or -s when creating local users is for convenience, you probably need to be more careful with what privileges a user is granted on remote systems.
- Pass --pwprompt or -P to createuser if you want to set a password for the new user.
- For ease of use in running subsequent commands, you can set <username> to be the same as the current system user.
Create database:
If you created a superuser with the same username as the current system user and you want both the owner and database name to be the same:
```
createdb
```
Else, if you want a different database name:
```
createdb <dbname>
```
Else, you need to specify both the owner and database name:
```
sudo --user postgres createdb --owner <username> <dbname>
```
Or shorthand:
```
sudo -u postgres createdb -O <username> <dbname>
```
Notes:
- <dbname> can also be omitted if the PGDATABASE environment variable is set, in which case, it will be the value used instead of the current system user's username.
Connect to database:
If the current system user's username is the same as the database name and owner's username:
```
psql
```
Else, you may need to pass one or both of the following:
- --dbname or -d for the database name
- --username or -U for the owner's username
Once connected, you can do the rest with SQL.
Some useful meta-commands:
- \du to list users
- \l to list databases
- \connect <dbname> or \c <dbname> to connect to a different database
- \dt to list tables in current database
- \quit or \q to exit the shell
Stop server:
```
sudo --user postgres pg_ctl stop --pgdata <path>
```
Or shorthand:
```
sudo -u postgres pg_ctl stop -D <path>
```
Notes:
- --pgdata or -D can be omitted if the PGDATA environment variable is set.

Cheatsheet: PostgreSQL

Steps

References