PostgreSQL Admin Cheat Sheet

\conninfo        who and where am I
\l               databases
\c shop          switch database
\dn              schemas
\dt              tables      \dt+  with sizes
\d orders        columns, indexes, constraints
\du              roles
\dp orders       privileges on a table
\x auto          wide rows vertically
\timing on       show query time
\i file.sql      run a file
\q               quit

CREATE ROLE app LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE shop TO app;
GRANT USAGE ON SCHEMA public TO app;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;
-- tables created later, by the role that runs this:
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;
ALTER ROLE app PASSWORD 'new-password';

SELECT pg_size_pretty(pg_database_size(current_database()));

SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

SELECT pid, usename, state, now() - query_start AS runtime,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC NULLS LAST;

-- who is waiting for whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT pg_cancel_backend(12345);     -- cancel the query
SELECT pg_terminate_backend(12345);  -- end the session

-- after an import: next id = max(id) + 1
SELECT setval(pg_get_serial_sequence('orders', 'id'),
              COALESCE(MAX(id), 0) + 1, false)
FROM orders;

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

VACUUM (ANALYZE, VERBOSE) orders;
ANALYZE;                             -- refresh planner stats

SHOW config_file;
SHOW hba_file;                       -- who may connect, and how
SHOW max_connections;
ALTER SYSTEM SET work_mem = '64MB';  -- written to postgresql.auto.conf
SELECT pg_reload_conf();             -- apply without restart
SELECT name, setting, pending_restart
FROM pg_settings WHERE pending_restart;