psql.

psql -h host -U user -d db

Classic flags (prompts for password)

psql "postgres://user@host:5432/db"

Connection URL

\c otherdb

Switch database in place

\conninfo

Where am I connected?

~/.pgpass

host:port:db:user:pass — no prompts

\q

Quit

\l

Databases

\dt

Tables (current schema)

\d orders

Columns, types, indexes

\d+ orders

Plus storage and comments

\di \df

Indexes / functions

\dn \du

Schemas / roles

\x auto

Expanded output when rows are wide

\timing

Print each query's duration

\e

Edit the last query in $EDITOR

\watch 2

Re-run the last query every 2s

\i file.sql

Run a script

\o out.txt

Send results to a file (\o to stop)

\! ls

Run a shell command

\copy orders TO 'orders.csv' CSV HEADER

Table → local CSV

\copy orders FROM 'orders.csv' CSV HEADER

Local CSV → table

\copy (SELECT …) TO 'out.csv' CSV HEADER

Any query → CSV

COPY

Same, but server-side: reads/writes the server's filesystem

-- live queries, oldest first
SELECT pid, state,
       now() - query_start AS age,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY age DESC;

-- who is blocking pid 123?
SELECT pg_blocking_pids(123);

-- kill it (politely)
SELECT pg_terminate_backend(123);

-- biggest tables (incl. indexes)
SELECT relname, pg_size_pretty(
  pg_total_relation_size(relid))
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid)
  DESC LIMIT 10;

EXPLAIN q

Plan only — estimates

EXPLAIN ANALYZE q

Runs it — real rows and times

EXPLAIN (ANALYZE, BUFFERS) q

Plus cache hits vs disk reads