psql Cheatsheet

Top commands.

Connecting

psql connections are where security and ergonomics meet. Get the URL form and .pgpass right; the rest follows.

• psql -h host -U user -d dbname: Per-host the standard connection. Password prompted unless PGPASSWORD set.
• psql 'postgresql://user:pass@host:5432/db?sslmode=require': Per-connection the URL string. Required for SSL-enforced connections.
• .pgpass file: Per-host the stored credential. ~/.pgpass with mode 600. Avoids password prompts and CLI history exposure.
• PGSERVICE config: Per-environment the named service. The discipline supports many environments cleanly.

Inspecting structure

The backslash commands are the daily inspection surface. \dt, \d, \df cover most reads.

• \dt: Per-schema the table list. \dt schema.* for a specific schema.
• \d table_name: Per-table the structure. \d+ adds size and constraint detail.
• \df / \dn / \du: Per-database the functions, schemas, roles. The discipline supports navigation.
• \sf function_name: Per-function the source. The discipline supports inspection without re-querying pg_proc.

Query patterns

Query patterns are the ergonomics layer. Timing, expanded output, EXPLAIN ANALYZE.

• \timing on: Per-session the query-timing display. Useful for performance investigation.
• EXPLAIN ANALYZE select_query: Per-query the plan and actual stats. First step in slow query debugging.
• \x: Per-session the expanded output toggle. One column per line for wide rows.
• \watch 5: Per-query the 5-second re-run. The discipline supports live monitoring during incidents.

Admin queries

Admin queries are the operational surface. pg_stat_activity, pg_locks, pg_stat_user_tables.

• pg_stat_activity: Per-database the live connections. Find long-running or stuck queries.
• pg_locks: Per-database the lock state. Identify blocking queries during contention.
• pg_stat_user_tables: Per-table the read, write, vacuum stats. Identifies hot tables and bloat.
• pg_stat_statements: Per-database the query stats. The discipline surfaces top consumers.

Scripting

Scripting psql is its own discipline. Single-shot, file, CSV all map to different shell needs.

• psql -c 'SELECT ...' database: Per-script the single-shot query. Useful in shell scripts.
• psql -f script.sql: Per-script the file run. Pipe stdin for inline scripts.
• psql --csv -c 'SELECT ...': Per-script the CSV output. Pipe to file or other tools.
• psql -v ON_ERROR_STOP=1 -f script.sql: Per-script the fail-fast mode. The discipline catches partial-script bugs.