psql command line cheat sheet

I switch between MySQL and Postgres often enough that the psql meta-commands never stick in my head. Every time it’s the same five minutes of googling \d vs \dt vs \l. This is the list I actually use, so I stop doing that.

Connect

psql -h localhost -U postgres -d mydb
psql "postgresql://user:password@localhost:5432/mydb"

Run one query and exit, useful in scripts:

psql -h localhost -U postgres -d mydb -Atc "SELECT id FROM users WHERE active;"

-A drops the alignment padding, -t drops headers and row count, -c runs the query.

Databases and schemas

\l          -- list databases
\c mydb     -- connect to a different database
\dn         -- list schemas

Database sizes, when \l+ isn’t enough:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Tables

\dt              -- list tables in current schema
\dt schema.*     -- list tables in a specific schema
\d table_name    -- describe columns, indexes, constraints
\d+ table_name   -- same, plus storage and stats target
\di              -- list indexes
\dv              -- list views
\df              -- list functions

Table sizes, biggest first:

SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Row count without a full COUNT(*) scan (from planner stats, not exact, but instant on a big table):

SELECT relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

Users and privileges

\du       -- list roles and their attributes
\dp       -- list table privileges

Output formatting

\x            -- toggle expanded display, one column per line
\x auto       -- expanded only when a row doesn't fit the terminal width
\timing       -- toggle showing query execution time
\pset pager off

\x auto is the one I leave on permanently in ~/.psqlrc.

Editing and scripts

\e             -- open the last query in $EDITOR
\i script.sql  -- run a script file
\q             -- quit

Import and export

\copy runs on the client side, so it works even when the server process has no filesystem access to your machine:

\copy (SELECT * FROM orders WHERE status = 'shipped') TO 'shipped.csv' WITH CSV HEADER
\copy users FROM 'users.csv' WITH CSV HEADER

Common admin queries

Currently running, non-idle queries, longest first:

SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

Cancel a query gracefully, or kill the connection if it won’t stop:

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

Who is blocking whom:

SELECT blocked.pid AS blocked_pid,
       blocking.pid AS blocking_pid,
       blocked_a.query AS blocked_query,
       blocking_a.query AS blocking_query
FROM pg_locks blocked
JOIN pg_locks blocking
  ON blocking.locktype = blocked.locktype
  AND blocking.pid != blocked.pid
JOIN pg_stat_activity blocked_a ON blocked_a.pid = blocked.pid
JOIN pg_stat_activity blocking_a ON blocking_a.pid = blocking.pid
WHERE NOT blocked.granted;

Dump and restore

pg_dump -h localhost -U postgres mydb > mydb.sql
psql -h localhost -U postgres -d mydb < mydb.sql

pg_dump -h localhost -U postgres -Fc mydb > mydb.dump
pg_restore -h localhost -U postgres -d mydb --clean mydb.dump

-Fc is the custom format, needed if you want pg_restore to run in parallel with -j.