Installing PostgreSQL and psql

psql is the tool every PostgreSQL user, DBA and tutorial relies on, and a local server is where you learn safely.

PostgreSQL is a client-server database: a server process (postgres) manages the data directory and accepts connections, and clients such as psql, GUI tools or your application connect to it over TCP (port 5432 by default) or a Unix socket.

Ways to get a server running:

Docker is the quickest: docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:18.
Packages: the PostgreSQL project's apt/yum repositories, Postgres.app or Homebrew on macOS, the EDB installer on Windows.
Managed services (Amazon RDS/Aurora, Google Cloud SQL, Azure, Neon, Supabase) for production.

A fresh installation has a superuser role called postgres and a database also called postgres. Connect with psql, the interactive terminal: psql -h localhost -U postgres. At the postgres=# prompt you can type SQL terminated by ;, or meta-commands that start with a backslash and are handled by psql itself: \l lists databases, \c dbname connects to another, \dt lists tables, \d name describes a table, \? shows help and \q quits.

Create a database for each project instead of working in postgres, and create a non-superuser role for your application — the superuser bypasses every permission check.

Example

# start a throwaway server (data is lost when the container is removed)
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:18

psql -h localhost -U postgres          # prompts for the password
# postgres=# SELECT version();
# postgres=# CREATE DATABASE shop;
# postgres=# \c shop
# You are now connected to database "shop" as user "postgres".
# shop=# CREATE TABLE product (id int PRIMARY KEY, name text);
# shop=# \dt
#          List of relations
#  Schema |  Name   | Type  |  Owner
#  public | product | table | postgres
# shop=# \q

Remember

The server owns the data; psql and apps are clients
Backslash commands (\l, \c, \dt, \d) are psql features, not SQL
Use a database per project and a non-superuser role for apps

Watch out for

Forgetting the semicolon: psql keeps waiting for more input and the prompt changes to postgres-#.

Check yourself

Which of these is handled by psql itself rather than sent to the server as SQL?

Official docs: www.postgresql.org/docs/current/app-psql.html