PostgreSQL Administration Cheat Sheet

Server lifecycle, utility commands, grants, monitoring, and cluster maintenance tasks.

View
StandardDetailedCompact
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
## Server Control and Cluster Management
Initialize cluster
initdb -D /var/lib/postgresql/data

# Create a new PostgreSQL data directory.

Start server with pg_ctl
pg_ctl -D /var/lib/postgresql/data -l logfile start

# Start a PostgreSQL server from a data directory.

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

# Stop the server with fast shutdown mode.

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

# Restart the server cleanly.

Check readiness
pg_isready -h localhost -p 5432 -d appdb

# Probe whether the server is accepting connections.

Show data directory
SHOW data_directory;

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

Show config file path
SHOW config_file;

# Find the active `postgresql.conf` path.

Reload config
SELECT pg_reload_conf();

# Ask PostgreSQL to reload configuration files.

## Database Administration Utilities
Create database from shell
createdb -h localhost -U postgres appdb

# Create a database using the `createdb` utility.

Drop database from shell
dropdb -h localhost -U postgres appdb

# Drop a database using the `dropdb` utility.

Create role from shell
createuser -h localhost -U postgres --interactive

# Interactively create a new role.

Drop role from shell
dropuser -h localhost -U postgres olduser

# Remove a role using the command-line utility.

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

# Rebuild indexes in a database.

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

# Run vacuum/analyze from the shell.

Physically reorder table
CLUSTER orders USING idx_orders_created_at;

# Rewrite a table using an index order.

Collect planner stats
ANALYZE public.users;

# Refresh planner statistics for a table.

## Monitoring and Statistics
Show active sessions
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
ORDER BY backend_start;

# Inspect current sessions and queries.

Cancel a query
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE pid = 12345;

# Cancel a running query without terminating the session.

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

# Force a backend to disconnect.

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

# Inspect lock acquisition and contention.

Show database size
SELECT pg_size_pretty(pg_database_size('appdb'));

# Measure one database's size.

Show table and indexes size
SELECT pg_size_pretty(pg_total_relation_size('public.users'));

# Measure one relation including indexes and TOAST.

Inspect bgwriter stats
SELECT * FROM pg_stat_bgwriter;

# Review checkpoint and buffer write statistics.

Find unused indexes
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.

## Security, Grants, and Default Privileges
Grant table select
GRANT SELECT ON TABLE public.users TO readonly;

# Allow read access to a table.

Grant schema usage
GRANT USAGE ON SCHEMA public TO readonly;

# Allow object lookup inside a schema.

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

# Allow use of sequences for inserts or reads.

Revoke update privilege
REVOKE UPDATE ON TABLE public.users FROM readonly;

# Remove write capability from a role.

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

# Set permissions that future objects should inherit.

Change object owner
ALTER TABLE public.users OWNER TO appuser;

# Transfer ownership of an object to another role.

Set active role
SET ROLE readonly;

# Temporarily assume another role for the session.

Reset active role
RESET ROLE;

# Return to the original session authorization.

Recommended next

No recommendations yet.