PostgreSQL

Administration Commands

Manage PostgreSQL databases, users, permissions, and performance. Essential commands for database administrators.

9 commands

Pro Tips

Run VACUUM ANALYZE regularly to maintain query performance.

Use pg_stat_activity to monitor active queries and connections.

Common Mistakes

pg_terminate_backend forcefully kills connections. Use with caution.

Commands

Create database

$ CREATE DATABASE mydb;

Create a new database.

Create user

$ CREATE USER appuser WITH PASSWORD 'secret';

Create a new user with password.

Grant privileges

$ GRANT ALL PRIVILEGES ON DATABASE mydb TO appuser;

Grant database privileges to user.

Active connections

$ SELECT * FROM pg_stat_activity;

View all active connections.

Kill connection

$ SELECT pg_terminate_backend(pid);

Terminate a backend process by PID.

Database size

$ SELECT pg_size_pretty(pg_database_size('mydb'));

Get database size in human-readable format.

Table sizes

$ SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

List tables by size.

Vacuum analyze

$ VACUUM ANALYZE tablename;

Clean up and update statistics.

Reindex table

$ REINDEX TABLE tablename;

Rebuild table indexes.