Databases
PostgreSQL — the psql + SQL commands that come up daily
The psql and SQL commands that come up daily: connect, inspect tables, add indexes, read EXPLAIN, spot slow queries, back up.
psql session
psql postgresql://user:pass@host:port/db — connect via URL
psql -h host -U user -d db — connect with flags
\q — quit
\? — psql help
\h — SQL help (\h SELECT)
Inspect
\l / \list — list databases
\c dbname — switch database
\dt / \dt schema.* — list tables
\d table — describe table
\dn — list schemas
\du — list users / roles
Indexes + EXPLAIN
\di — list indexes
EXPLAIN ANALYZE SELECT ... — show actual execution plan + timing
EXPLAIN (FORMAT JSON, ANALYZE, BUFFERS) ... — full detail
Performance peeks
SELECT * FROM pg_stat_activity; — current connections + queries
SELECT * FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20; — slowest queries (needs extension)
SELECT pg_size_pretty(pg_database_size('db')); — db size
SELECT pg_size_pretty(pg_total_relation_size('table')); — table size incl. indexes
Backup + restore
pg_dump -Fc dbname > db.dump — custom-format dump
pg_restore -d newdb db.dump — restore
pg_dump -Fc dbname | gzip > db.dump.gz — compressed