Database & BackendUpdated: 2026-11-07
Essential PostgreSQL CLI, Meta-Commands & Database Administration
Quick reference for psql terminal commands, database dumps with pg_dump, performance profiling with EXPLAIN ANALYZE, and user role management.
PostgreSQL is the world's most advanced open-source relational database. Whether you are debugging production slow queries, exporting compressed database dumps, or managing user permissions via the interactive `psql` console, this cheat sheet provides essential commands.
Table of Contents
Essential psql Meta-Commands
Navigation and inspection commands inside the interactive psql terminal.
| Meta-Command | Description | Equivalent SQL |
|---|---|---|
| `\l` or `\l+` | Lists all databases on the PostgreSQL cluster with sizes and encoding. | `SELECT datname FROM pg_database;` |
| `\c dbname [user]` | Connects to a different database as current or specified user. | N/A (session switch) |
| `\dt` or `\dt+` | Lists all tables in current schema with disk consumption. | `SELECT tablename FROM pg_tables WHERE schemaname = 'public';` |
| `\d+ tablename` | Describes table schema, columns, constraints, and indexes in detail. | N/A (information_schema audit) |
| `\timing` | Toggles query execution time measurement in milliseconds. | N/A (psql client setting) |
Backup, Restore & Performance Profiling
Managing database dumps and diagnosing query execution bottlenecks.
| Task | Command Syntax | Purpose |
|---|---|---|
| Export Compressed Dump | `pg_dump -U postgres -Fc -b -v -f backup.dump mydb` | Generates high-speed custom binary dump file for pg_restore. |
| Restore Database | `pg_restore -U postgres -d mydb -v backup.dump` | Restores database schema and tables from binary dump. |
| Profile Query Execution | `EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;` | Executes query and displays actual millisecond CPU time and disk buffer hits. |
Interactive Client-Side Tools for this Guide
Test and run these patterns directly inside your browser with zero server uploads:
Frequently Asked Questions
Why is pg_dump with -Fc format preferred over plain SQL (.sql)?
The custom format (-Fc) compresses data losslessly using zlib, supports multi-threaded parallel restoration via `pg_restore -j 4`, and allows restoring specific tables.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the query planner's estimated cost without running the query. EXPLAIN ANALYZE actually executes the query to return true millisecond execution times and buffer hits.