PostgreSQL cheat sheet

psql’s backslash commands, plus the queries you need when the database is slow: what’s running, what’s blocked, what’s big, and which indexes aren’t pulling their weight.

Download all 32 for Snippet Keeper Get Snippet Keeper

In Snippet Keeper, click Import at the bottom of the sidebar and choose the file. They land in your psql app, and ones you already have are skipped. The highlighted parts become fill-in fields: type a value (or pick one), and the finished command is on your clipboard.

Connecting

Connect to a database

psql -h host -p port -U user -d database

Connect with a URL

psql "postgresql://user@host:port/database"

Run one query and exit

psql -d database -c "query"

Run a SQL file

psql -d database -f file

psql commands

List databases

\l

Connect to another database

\c database

List tables

\dt

List tables in a schema

\dt schema.*

Describe a table

\d+ table

List roles

\du

List schemas

\dn

Readable output for wide rows

\x auto

Show how long each query takes

\timing

Edit the query in your editor

\e

Export a query to CSV

Writes the file on your computer, not the server.

\copy (query) TO 'file' WITH CSV HEADER

Sizes

Biggest tables

Includes indexes and TOAST data.

SELECT relname AS table, pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT limit;

Size of a database

SELECT pg_size_pretty(pg_database_size('database'));

Fast row count estimate

Instant on huge tables, unlike count(*). Accurate as of the last ANALYZE.

SELECT reltuples::bigint AS estimate FROM pg_class WHERE relname = 'table';

Performance

Queries running right now

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

Queries waiting on a lock

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Cancel a query

SELECT pg_cancel_backend(pid);

End a connection

SELECT pg_terminate_backend(pid);

Explain a query with real timings

Actually runs the query. Wrap writes in BEGIN … ROLLBACK.

EXPLAIN (ANALYZE, BUFFERS) query;

Indexes that are never used

SELECT relname AS table, indexrelname AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Add an index without locking writes

CREATE INDEX CONCURRENTLY index_name ON table (column);

Exploring data

Columns of a table

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'table'
ORDER BY ordinal_position;

Find duplicate values

SELECT column, count(*)
FROM table
GROUP BY column
HAVING count(*) > 1
ORDER BY count(*) DESC;

Roles

Create a user

CREATE ROLE user WITH LOGIN PASSWORD 'password';

Give a user read-only access

The last line covers tables created later.

GRANT USAGE ON SCHEMA schema TO user;
GRANT SELECT ON ALL TABLES IN SCHEMA schema TO user;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema GRANT SELECT ON TABLES TO user;

Backup and restore

Back up a database

Custom format: compressed, and lets pg_restore pick what to restore.

pg_dump -Fc -d database -f file

Restore a backup

pg_restore --no-owner -d database file

Back up one table as SQL

pg_dump -d database -t table -f table.sql

Keep these one keystroke away.

Snippet Keeper is a Mac app for the commands you keep looking up. Press ⌥⇧Space in any app, type a few letters, fill in the blanks, and it’s on your clipboard.

Snippet Keeper asking for the values in a kubectl command before copying it

Download the PostgreSQL snippets About Snippet Keeper