PostgreSQL runs as a server process that listens for connections — your Python application, the psql command-line client, and GUI tools like pgAdmin all connect to the same PostgreSQL server. Understanding this client-server model helps you understand connection strings, ports, authentication, and why database access requires credentials. The psql CLI is the universal tool for interacting with PostgreSQL — learning its meta-commands and workflow is the foundation for everything that follows: creating databases, inspecting schemas, and running SQL queries.
Installing PostgreSQL
# ── macOS (Homebrew) ──────────────────────────────────────────────────────────
brew install postgresql@16
brew services start postgresql@16
# or for older version:
# brew install postgresql
# ── Ubuntu / Debian Linux ─────────────────────────────────────────────────────
sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql # start on boot
# ── Verify installation ───────────────────────────────────────────────────────
psql --version # psql (PostgreSQL) 16.x
# ── Docker (recommended for development — isolated, easy to reset) ────────────
docker run -d \
--name postgres-dev \
-e POSTGRES_PASSWORD=devpassword \
-e POSTGRES_USER=devuser \
-e POSTGRES_DB=blog_dev \
-p 5432:5432 \
postgres:16
# Connect to Docker container's psql
docker exec -it postgres-dev psql -U devuser -d blog_devNote: Docker is highly recommended for PostgreSQL in development — it gives you a clean, isolated database that is easy to reset (
docker rm -f postgres-dev and recreate), easy to reproduce across team members, and does not interfere with any existing PostgreSQL installation. Use a docker-compose.yml file to define the database alongside your FastAPI application so the whole stack starts with one command.Tip: PostgreSQL uses a role-based access system where the initial superuser role is called
postgres. On Linux installations, a system user named postgres is also created — you can switch to it with sudo -u postgres psql to get a superuser psql session without a password. Create a dedicated database role for each application (covered in Lesson 5) rather than using the postgres superuser role in your application’s connection string.Warning: Never use the
postgres superuser role in your FastAPI application’s database connection string. A superuser can drop tables, modify roles, and access any database on the server. If your application is compromised, the attacker gains superuser access. Create a dedicated application role with only the permissions it needs: SELECT, INSERT, UPDATE, DELETE on the specific tables it uses.Connecting with psql
# ── Basic connection ──────────────────────────────────────────────────────────
psql -h localhost -p 5432 -U devuser -d blog_dev
# -h: host -p: port (5432 is the default)
# -U: username -d: database name
# ── Connection string format ──────────────────────────────────────────────────
psql "postgresql://devuser:devpassword@localhost:5432/blog_dev"
# ── Environment variables (avoids typing credentials each time) ───────────────
export PGHOST=localhost
export PGPORT=5432
export PGUSER=devuser
export PGPASSWORD=devpassword
export PGDATABASE=blog_dev
psql # connects using environment variablesEssential psql Meta-Commands
# Meta-commands start with \ and are psql-specific (not SQL)
\l -- list all databases
\c dbname -- connect to (change to) a database
\dt -- list all tables in current database
\d tablename -- describe a table (columns, types, constraints)
\d+ tablename -- detailed description (including indexes)
\du -- list all roles/users
\dp -- list table permissions
\i file.sql -- execute SQL from a file
\timing on -- show query execution time
\x -- toggle expanded display (easier to read wide rows)
\q -- quit psql
-- Help
\? -- psql command help
\h SELECT -- SQL syntax help for SELECT
\h CREATE TABLE-- Once connected, you can run regular SQL:
SELECT version(); -- PostgreSQL version
SELECT current_database(); -- which database you're in
SELECT current_user; -- which role you're using
-- List tables (SQL equivalent of \dt)
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public';Creating a Database and Role
-- Connect as superuser first (sudo -u postgres psql on Linux)
-- Or use your initial postgres superuser connection
-- Create a dedicated application role
CREATE ROLE blog_app
WITH LOGIN
PASSWORD 'your_secure_password_here'
NOSUPERUSER
NOCREATEDB
NOCREATEROLE;
-- Create the application database, owned by the app role
CREATE DATABASE blog_dev OWNER blog_app;
-- Grant privileges on the new database
GRANT ALL PRIVILEGES ON DATABASE blog_dev TO blog_app;
-- Connect as the new role to verify
\c blog_dev blog_app -- change database AND user in psqlCommon Mistakes
Mistake 1 — Running psql as postgres superuser in the app connection string
❌ Wrong — production .env with superuser:
DATABASE_URL=postgresql://postgres:password@localhost/blog_prod✅ Correct — dedicated application role:
DATABASE_URL=postgresql://blog_app:password@localhost/blog_prodMistake 2 — Forgetting the database name when connecting
❌ Wrong — connects to a database named after the current OS user:
psql -U devuser # tries to connect to database "devuser" — may not exist✅ Correct — always specify the database:
psql -U devuser -d blog_dev # ✓ explicit databaseMistake 3 — Not enabling pg_hba.conf for local connections
If psql says “peer authentication failed”, your pg_hba.conf uses peer authentication. Fix:
# In /etc/postgresql/16/main/pg_hba.conf
# Change: local all all peer
# To: local all all md5 (password authentication)
# Then: sudo systemctl restart postgresqlQuick Reference — psql Connection
| Task | Command |
|---|---|
| Connect | psql -h host -U user -d dbname |
| List databases | \l |
| Switch database | \c dbname |
| List tables | \dt |
| Describe table | \d tablename |
| List roles | \du |
| Run SQL file | \i path/to/file.sql |
| Quit | \q |