PostgreSQL Administration Cheat Sheet

initdb -D /var/lib/postgresql/data

# Create a new PostgreSQL data directory.

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

# Start a PostgreSQL server from a data directory.

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

# Stop the server with fast shutdown mode.

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

# Restart the server cleanly.

pg_isready -h localhost -p 5432 -d appdb

# Probe whether the server is accepting connections.

SHOW data_directory;

# Inspect the server's active data directory from SQL.

SHOW config_file;

# Find the active `postgresql.conf` path.

SELECT pg_reload_conf();

# Ask PostgreSQL to reload configuration files.

createdb -h localhost -U postgres appdb

# Create a database using the `createdb` utility.

dropdb -h localhost -U postgres appdb

# Drop a database using the `dropdb` utility.

createuser -h localhost -U postgres --interactive

# Interactively create a new role.

dropuser -h localhost -U postgres olduser

# Remove a role using the command-line utility.

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

# Rebuild indexes in a database.

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

# Run vacuum/analyze from the shell.

CLUSTER orders USING idx_orders_created_at;

# Rewrite a table using an index order.

ANALYZE public.users;

# Refresh planner statistics for a table.

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

# Inspect current sessions and queries.

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

# Cancel a running query without terminating the session.

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

# Force a backend to disconnect.

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

# Inspect lock acquisition and contention.

SELECT pg_size_pretty(pg_database_size('appdb'));

# Measure one database's size.

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

# Measure one relation including indexes and TOAST.

SELECT * FROM pg_stat_bgwriter;

# Review checkpoint and buffer write statistics.

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

# Review index scan counts when cleaning up indexes.

GRANT SELECT ON TABLE public.users TO readonly;

# Allow read access to a table.

GRANT USAGE ON SCHEMA public TO readonly;

# Allow object lookup inside a schema.

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

# Allow use of sequences for inserts or reads.

REVOKE UPDATE ON TABLE public.users FROM readonly;

# Remove write capability from a role.

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

# Set permissions that future objects should inherit.

ALTER TABLE public.users OWNER TO appuser;

# Transfer ownership of an object to another role.

SET ROLE readonly;

# Temporarily assume another role for the session.

RESET ROLE;

# Return to the original session authorization.