Database cheatsheet

PostgreSQL 18 Cheatsheet

psql, backup and maintenance commands for PostgreSQL 18.

Connecting

Check the client version — Print the version of the psql client.
Connect to a database — Open psql against a specific host, port, user and database.
Connect with a connection URI — Open psql using a single connection string.
Check the server version — Run inside psql to see which server version you are connected to.
Quit psql — Leave the psql prompt and return to the shell.
Run a SQL file — Execute the statements in a file and stop at the first error.

List databases — Show every database on the server. Run \l inside psql or use the flag from the shell.
Switch database — Connect to another database from inside psql.
List tables — Show the tables in the current schema search path.
Describe a table — Show a table's columns, types, indexes and constraints.
List schemas — Show the schemas in the current database.
List roles — Show database roles and their attributes.
Toggle expanded output — Show each row as a vertical list, which is easier to read for wide tables.
Show how long queries take — Toggle timing output after each statement.
Check a database's size — Show the on-disk size of a database in a readable unit.

Create a database — Create a new database from the shell.
Drop a database — Delete a database and everything in it.
Create a login role — Create a role that can sign in with a password. Use a real password.
Grant table privileges — Let a role read and write all tables in the public schema.

Back up a database — Write a compressed custom-format dump that pg_restore can restore selectively.
Back up as plain SQL — Write a SQL script you can read and replay with psql.
Restore a custom-format dump — Restore a dump into an existing, empty database.
Restore a plain SQL dump — Replay a SQL script and stop at the first error.
Export a table to CSV — Write a table to a CSV file on your machine, from inside psql.

See how a query runs — Run the query and show the real plan, timings and buffer use.
Create an index — Add an index to speed up lookups on a column.
Create an index without blocking writes — Build the index while the table stays writable. Slower, and it cannot run inside a transaction.
Vacuum and update statistics — Reclaim dead rows and refresh the planner's statistics for a table.
See running queries — List sessions and what they are executing.
Cancel a running query — Ask a backend to stop its current query, using a pid from pg_stat_activity.

DuckDB 2 cheatsheet — Query CSV, Parquet and DataFrames with SQL from Python or the duckdb command line, without running a server.
Elasticsearch 9 cheatsheet — curl requests for indexes, documents, searches, bulk loads and cluster health. Security is on by default, so the examples authenticate.
MongoDB 8 cheatsheet — mongosh shell commands for documents, queries, aggregation, indexes, backups and administration.
MySQL 8.4 cheatsheet — mysql client, backup and maintenance commands for MySQL 8.4.
Redis 8 cheatsheet — redis-cli and core commands for keys, data structures and server administration.
SQLite 3 cheatsheet — sqlite3 shell dot-commands, pragmas, import and export, and the SQL you use most.