psql Quick Reference

The PostgreSQL CLI’s backslash meta-commands — inspect schemas, time queries, and stay productive without a GUI.

psql is the PostgreSQL client. It reads two kinds of input: SQL, which goes to the server and runs when you type ;, and meta-commands starting with a backslash (\d, \x, \copy), which psql handles itself and which end at the line break — no semicolon.

Most meta-commands are shorthand for catalog queries, so \d+ and friends work on any server you can reach. Mind the client/server split: \copy and \o write files on your machine, while SQL COPY reads and writes on the database server.

Connect

Connect: psql -h host -U user -d dbname
URL: psql postgres://user:pass@host:5432/db
Switch database: \c otherdb
Run a file: psql -d db -f script.sql · inside: \i script.sql
Quit: \q
psql -d db -c "select 1": run one statement and exit — exit status 1 if it fails
\conninfo: which database, user, host and port this session is using
~/.pgpass: host:port:db:user:password per line, chmod 600 — no prompts, no password in env
psql -X -v ON_ERROR_STOP=1 -f x.sql: for scripts: skip ~/.psqlrc and stop at the first error (exit 3)
\password ana: change a password without it landing in shell history or server logs

Inspect (meta-commands)

List databases: \l
List tables: \dt · all relations: \d
Describe a table: \d tablename — columns, types, indexes
List indexes / views: \di · \dv
List schemas / roles: \dn · \du
List functions: \df
Search objects: \dt *user* — pattern match
\d+ users: adds storage, compression, stats target and comments to \d
\dt+: tables with their on-disk size; \l+ does the same for databases
\sf add_one: print a function’s source; \sv prints a view’s definition
\dx: installed extensions and their versions
\dp users: access privileges (GRANTs) on a table, view or sequence
\h CREATE INDEX: SQL syntax help for any statement; \? lists every meta-command
\set ECHO_HIDDEN on: print the catalog query behind each \d command — how to learn the catalogs

Work faster

Expanded rows: \x — vertical output for wide tables (toggle)
Time every query: \timing
Output to file: \o out.txt — then queries write there; \o to stop
Edit query in $EDITOR: \e
Re-run last query: \g
Save/use a snippet: \set q 'select 1' then :q;
\x auto: expanded display only when a row is too wide for the screen
select … \gx: run this one query with expanded output
\watch 2: re-run the last query every 2 seconds until Ctrl-C
select count(*) as n … \gset: store result columns in psql variables — use :n afterwards

Output & export

\pset null '∅': make NULLs visible — by default they print exactly like empty strings
psql --csv -c "…": CSV output (psql 12+); inside a session, \pset format csv
psql -At -c "select …": unaligned, tuples only: bare values for shell scripts; -F, sets the separator
\copy (select …) to 'out.csv' csv header: export to a file on your machine — SQL COPY writes on the server
\copy users from 'in.csv' csv header: bulk-load a local CSV; columns go by position unless listed: users(id, name)
select … \g out.txt: send only this result to a file (\o redirects everything after it)

Transactions & safety

BEGIN; … ROLLBACK;: try a risky UPDATE, check the row count it prints, then ROLLBACK or COMMIT
\set AUTOCOMMIT off: every statement opens a transaction; quitting without COMMIT throws the work away
\set ON_ERROR_ROLLBACK interactive: a typo inside BEGIN no longer aborts the whole transaction
SET statement_timeout = '5s';: cancel any statement that runs longer — cheap insurance on production
SET lock_timeout = '2s';: fail instead of queueing behind a lock — set it before running DDL

Diagnose

Query plan: EXPLAIN ANALYZE SELECT …; — estimated vs actual rows
Active queries: SELECT * FROM pg_stat_activity;
Table / db size: SELECT pg_size_pretty(pg_total_relation_size('t'));
Kill a query: SELECT pg_cancel_backend(pid); (or pg_terminate_backend)
EXPLAIN (ANALYZE, BUFFERS) …: adds buffer hits vs reads; ANALYZE really executes — wrap writes in BEGIN … ROLLBACK
pg_blocking_pids(pid): the sessions holding locks this one waits on — select it from pg_stat_activity
pg_stat_user_tables: seq_scan vs idx_scan and dead rows per table — spot missing indexes and bloat
pg_stat_statements: top queries by total time; an extension needing shared_preload_libraries and a restart
VACUUM (ANALYZE) users;: reclaim dead rows and refresh planner statistics after a bulk change
SHOW work_mem;: read a setting; SHOW ALL lists them, SET changes one for the session

Backup & restore

pg_dump -Fc -d db -f db.dump: compressed custom-format dump — restore all of it or parts with pg_restore
pg_restore -d newdb -j 4 db.dump: restore in parallel; --clean --if-exists drops existing objects first
pg_dump -s -t users -d db: schema only (-s) or data only (-a), here for one table
pg_dumpall --globals-only: roles and tablespaces — pg_dump never includes them
pg_isready -h host: exits 0 once the server accepts connections — for health checks and CI waits

Recipes

Find and stop a runaway query

SELECT pid, now() - query_start AS runtime, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY runtime DESC;

SELECT pg_cancel_backend(12345);     -- polite: cancel the running query
SELECT pg_terminate_backend(12345);  -- if that fails: drop the whole connection

Cancel first: pg_terminate_backend ends the session and rolls back its open transaction. Check pg_blocking_pids(pid) too — a “slow” query is often just waiting on a lock.

Export a query to CSV

# from the shell
psql -X -d shop --csv -c "SELECT id, name, email FROM users ORDER BY id" > users.csv

# from inside psql, written on your machine
\copy (SELECT id, name, email FROM users ORDER BY id) TO 'users.csv' CSV HEADER

\copy streams through the client, so it needs no superuser rights or server file access. SQL COPY … TO 'file' writes the file on the database server instead.

Try a risky UPDATE safely

\set ON_ERROR_ROLLBACK interactive
BEGIN;
UPDATE orders SET amount = amount * 1.1 WHERE day < '2024-01-02';
-- psql prints "UPDATE 1": is that the row count you expected?
SELECT * FROM orders WHERE day < '2024-01-02';
ROLLBACK;   -- or COMMIT; once it looks right

Nobody else sees the change before COMMIT, but the updated rows stay locked while the transaction is open — decide quickly rather than leaving it idle.

Back up a database and restore it on another server

pg_dump -Fc -d shop -f shop.dump
pg_dumpall --globals-only > globals.sql      # roles the objects belong to
psql -h newhost -d postgres -f globals.sql
createdb -h newhost shop
pg_restore -h newhost -d shop -j 4 shop.dump

The custom format (-Fc) is compressed and lets pg_restore run in parallel or restore single tables with -t. Use a pg_restore at least as new as the pg_dump that wrote the file.

Make a script fail loudly

psql -X -v ON_ERROR_STOP=1 -d shop -f migrate.sql || echo "migration failed: $?"

n=$(psql -X -At -d shop -c "SELECT count(*) FROM users WHERE email IS NULL")
[ "$n" -eq 0 ] || echo "found $n users without an email"

Without ON_ERROR_STOP, psql carries on past a failed statement and still exits 0. -X keeps a personal ~/.psqlrc from changing the output a script parses.

Questions about psql Cheat Sheet

How do I list all tables in psql?

Type \dt to list the tables in the schemas on your search_path, which usually means public. Use \dt *.* to include every schema, \dt sales.* for one schema, and \dt+ to add each table’s size and description. Plain \d lists every relation, including views, sequences and materialized views, while \d tablename describes one table’s columns, indexes and constraints. Outside psql, the same information is available to any SQL client from information_schema.tables or the pg_catalog.pg_tables view.

What is the difference between COPY and \copy?

COPY is a SQL command executed by the database server, so the file path in COPY ... TO or FROM refers to the server’s filesystem, and it requires superuser rights or the pg_read_server_files and pg_write_server_files roles. \copy is a psql meta-command with the same options that runs COPY to or from STDOUT and STDIN behind the scenes, so psql reads or writes the file on your own machine with your own permissions. Use \copy for exports and imports from a laptop or a managed database such as RDS; use COPY for files that already live on the server.

How do I see running queries and kill one in PostgreSQL?

Query pg_stat_activity: every row is one connection, with its pid, state, query text and query_start. Filter out idle sessions and order by now() minus query_start to put the longest runners first. To stop one, call pg_cancel_backend with its pid, which cancels the current query but keeps the connection. If that does not work, pg_terminate_backend ends the whole session and rolls back its open transaction. You need to be the same role or have pg_signal_backend rights. If a query is merely waiting, pg_blocking_pids shows which session holds the lock.

How do I exit psql?

Type \q and press Enter, or press Ctrl-D on an empty line. Since PostgreSQL 11 you can also type exit or quit on a line by themselves at the interactive prompt. If none of these seem to work, check the prompt: one such as db-# or db(# means psql is still collecting an unfinished statement, so press Ctrl-C to clear the buffer first. If you are looking at long output with a colon at the bottom, you are in the pager, and q returns you to the prompt.

Go deeper

SQL cheatsheet →Lab: Query Planner →