Connect to database
psql -U username -d database_name
Connect to remote database
psql -h hostname -U username -d database_name
List all databases
\l
Connect to database
\c database_name
List all tables
\dt
Describe table structure
\d table_name
List all users/roles
\du
List all schemas
\dn
Execute SQL file
psql -U username -d database_name -f script.sql
Export query results to CSV
psql -U username -d database_name -c "SELECT * FROM users" --csv > users.csv
Backup database
pg_dump -U username database_name > backup.sql
Restore database
psql -U username database_name < backup.sql
Show running queries
SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state != 'idle';
Kill a query
SELECT pg_cancel_backend(pid);
Show database size
SELECT pg_size_pretty(pg_database_size('database_name'));
Show table sizes
SELECT
table_name,
pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY pg_total_relation_size(quote_ident(table_name)) DESC;
Enable timing
\timing
Quit psql
\q