psql Commands I Keep Forgetting

Connect

psql -U postgres                          # connect as postgres user
psql -U postgres -d mydb                  # connect to specific database
psql -h localhost -U postgres -d mydb     # with host
psql "postgresql://user:pass@host/dbname" # connection string

Navigate

\l              -- list all databases
\c mydb         -- switch to database
\dt             -- list all tables
\dt schema.*    -- list tables in specific schema
\d tablename    -- describe table (columns, types, constraints)
\dn             -- list schemas
\du             -- list users/roles
\q              -- quit

Query Shortcuts

\x              -- toggle expanded display (useful for wide rows)
\timing         -- show query execution time
\e              -- open query in editor
\i file.sql     -- run sql from file

Common Queries

-- list all tables with row count
SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- show running queries
SELECT pid, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle';

-- kill a query
SELECT pg_terminate_backend(pid);

-- show table size
SELECT pg_size_pretty(pg_total_relation_size('tablename'));

-- show all indexes on a table
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'tablename';

Database & Table Management

CREATE DATABASE mydb;
DROP DATABASE mydb;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW()
);

DROP TABLE users;
TRUNCATE TABLE users;          -- delete all rows, keep structure

Dump & Restore

pg_dump -U postgres mydb > backup.sql           # dump database
pg_dump -U postgres -t tablename mydb > t.sql   # dump single table
psql -U postgres mydb < backup.sql              # restore