PostgreSQL Cheat Sheet

psql operations and admin — connect, query, manage roles, dumps, and the everyday SQL you can't avoid.

Databases5 sections · 35 commands
⌘K

Connecting & Running

5
psql -U USER -d DB -h HOST

Connect to a database

psql 'postgresql://user:pass@host:5432/db'

Connection string

psql -c 'SELECT version();' -d DB

Run a command non-interactively

psql -f script.sql -d DB

Run a SQL file

SET search_path TO schema1, public;

Switch the default schema

Inspecting the Server

10
\l

List databases

\dt

List tables

\d TABLE

Describe a table

\di

List indexes

\du

List roles

\dn

List schemas

\x

Toggle expanded display

\timing on

Show query timings

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Query plan with details

SELECT pg_size_pretty(pg_database_size('db'));

Database size

Roles & Privileges

7
CREATE ROLE app LOGIN PASSWORD 'secret';

Create a login role

ALTER ROLE app CREATEDB;

Grant CREATEDB

GRANT ALL ON SCHEMA public TO app;

Schema access

GRANT SELECT, INSERT ON TABLE t TO app;

Table privileges

\du+

Role attributes with settings

DROP ROLE app;

Remove a role

SELECT rolname FROM pg_roles WHERE rolsuper;

Find superusers

Backup & Restore

6
pg_dump -Fc -d DB -f db.dump

Custom-format dump (compressed)

pg_dump -t TABLE -d DB -f table.sql

Dump one table

pg_dumpall | gzip > all.sql.gz

Dump all databases and roles

pg_restore -d DB -c db.dump

Restore, drop objects first

pg_restore --list db.dump

List objects in a dump

psql -d DB < db.sql

Restore a plain-text dump

Troubleshooting

7
SELECT * FROM pg_stat_activity WHERE state = 'active';

Running queries

SELECT pg_terminate_backend(PID);

Kill a stuck session

SELECT pg_cancel_backend(PID);

Cancel a query gracefully

SELECT * FROM pg_locks WHERE NOT granted;

Blocked locks

SHOW max_connections;

Connection limit

SELECT COUNT(*) FROM pg_stat_activity WHERE state = 'idle in transaction';

Idle-in-txn sessions holding locks

tail -n 50 /var/log/postgresql/postgresql*.log

Server log tail