🐘
PostgreSQL cheat sheet
Commands for the psql client and PostgreSQL tools. Inside psql, backslash commands are meta-commands handled by the client; SQL statements end with a semicolon.
Click any command to copy it.
Connect
| Command | What it does |
|---|---|
psql -U user -d database -h host | Connect to a database. |
sudo -u postgres psql | Connect as the postgres superuser locally. |
psql "postgresql://user:pass@host:5432/db" | Connect using a connection URI. |
\conninfo | Show the current connection details. |
\q | Quit psql. |
Explore
| Command | What it does |
|---|---|
\l | List databases. |
\c dbname | Connect to another database. |
\dt | List tables (add + for sizes). |
\d tablename | Describe a table's columns, indexes and constraints. |
\du | List roles. |
\dn | List schemas. |
\df | List functions. |
\x | Toggle expanded (vertical) output for wide rows. |
\timing | Toggle query timing. |
Run files and export
| Command | What it does |
|---|---|
psql -d db -f script.sql | Run a SQL file. |
\i script.sql | Run a SQL file from inside psql. |
\copy tablename TO 'out.csv' CSV HEADER | Export a table to CSV on the client. |
\copy tablename FROM 'in.csv' CSV HEADER | Import a CSV file into a table. |
Backup and restore
| Command | What it does |
|---|---|
pg_dump -U user db > db.sql | Dump a database as plain SQL. |
pg_dump -Fc db -f db.dump | Dump in the compressed custom format. |
psql -U user -d db < db.sql | Restore a plain SQL dump. |
pg_restore -d db --clean db.dump | Destructive: Restore a custom-format dump, dropping existing objects first. |
pg_dumpall > all.sql | Dump every database and the roles. |
Roles and databases
| Command | What it does |
|---|---|
CREATE DATABASE appdb; | Create a database. |
CREATE USER appuser WITH PASSWORD 'secret'; | Create a login role. |
GRANT ALL PRIVILEGES ON DATABASE appdb TO appuser; | Allow a role full access to a database. |
\password appuser | Change a role's password without exposing it in history. |
DROP DATABASE appdb; | Destructive: Permanently delete a database. |
Diagnostics
| Command | What it does |
|---|---|
SELECT pg_size_pretty(pg_database_size('db')); | Database size in a readable form. |
SELECT * FROM pg_stat_activity; | Current connections and their queries. |
SELECT pg_terminate_backend(PID); | Disconnect a session by process ID. |
EXPLAIN ANALYZE SELECT ...; | Run a query and show its real execution plan. |
VACUUM ANALYZE tablename; | Reclaim space and refresh planner statistics. |
Frequently asked questions
How do I list all tables?
Inside psql run \dt, or \dt *.* to include every schema.
How do I avoid putting a password on the command line?
Use a ~/.pgpass file (chmod 600) or the PGPASSWORD environment variable for scripts, or \password inside psql.