PostgreSQL & psql CLI Meta-Commands Cheat Sheet

The complete reference for PostgreSQL developers and DBAs. Essential psql slash commands, table inspection, roles, databases, and backup tools.

Connecting & Navigation Meta-Commands

Connect to PostgreSQL server from terminalpsql -U username -d dbname -h localhost

List all databases with owner, encoding, and size\l or \l+

Connect / switch to a different database\c <database>

List all tables in current schema with disk sizes\dt or \dt+

Describe table columns, types, indexes, and constraints\d <table_name>

List all schemas in database\dn

List all database users, roles, and assigned permissions\du

Toggle expanded display mode (ideal for wide tables)\x

Quit psql terminal interface\q

Performance & Diagnostic Queries

Execute query and show actual execution plan and node timingsEXPLAIN ANALYZE SELECT ...;

Get human-readable total database size on diskSELECT pg_size_pretty(pg_database_size('db'));

View all currently active executing queries and connectionsSELECT * FROM pg_stat_activity WHERE state != 'idle';

Safely cancel a slow running query by process IDSELECT pg_cancel_backend(pid);

Reclaim dead tuple storage and update query planner statisticsVACUUM (VERBOSE, ANALYZE);

Backup & Restore Utilities

Export compressed custom-format binary database backuppg_dump -U user -d dbname -F c -f backup.dump

Restore custom-format backup file into databasepg_restore -U user -d dbname -v backup.dump

Dump entire PostgreSQL cluster including global rolespg_dumpall -U postgres > full_cluster.sql

Frequently Asked Questions

• What is the benefit of EXPLAIN ANALYZE over plain EXPLAIN in PostgreSQL?

Plain `EXPLAIN` only shows the optimizer's theoretical cost estimate without running the query, whereas `EXPLAIN ANALYZE` actually executes the SQL query, measuring real wall-clock runtime, index scans, and row counts.