PostgreSQL Administration Cheat Sheet

initdb -D /var/lib/postgresql/data

pg_ctl -D /var/lib/postgresql/data -l logfile start

pg_ctl -D /var/lib/postgresql/data stop -m fast

pg_ctl -D /var/lib/postgresql/data restart

pg_isready -h localhost -p 5432 -d appdb

SHOW data_directory;

SHOW config_file;

SELECT pg_reload_conf();

createdb -h localhost -U postgres appdb

dropdb -h localhost -U postgres appdb

createuser -h localhost -U postgres --interactive

dropuser -h localhost -U postgres olduser

reindexdb -h localhost -U postgres -d appdb --concurrently

vacuumdb -h localhost -U postgres -d appdb --analyze-in-stages

CLUSTER orders USING idx_orders_created_at;

ANALYZE public.users;

SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
ORDER BY backend_start;

SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

SELECT pid, relation::regclass, mode, granted
FROM pg_locks
ORDER BY relation, mode;

SELECT pg_size_pretty(pg_database_size('appdb'));

SELECT pg_size_pretty(pg_total_relation_size('public.users'));

SELECT * FROM pg_stat_bgwriter;

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, relname;

GRANT SELECT ON TABLE public.users TO readonly;

GRANT USAGE ON SCHEMA public TO readonly;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser;

REVOKE UPDATE ON TABLE public.users FROM readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;

ALTER TABLE public.users OWNER TO appuser;

SET ROLE readonly;

RESET ROLE;