PostgreSQL Cheat-Sheet

PostgreSQL, also known as Postgres, is a free and open-source relational database management system. PostgreSQL features transactions with Atomicity, Consistency, Isolation, Durability (ACID) properties, automatically updatable views, materialized views, triggers, foreign keys, and stored procedures. It is designed to handle a range of workloads, from single machines to data warehouses or web services with many concurrent users.

Documentation: PostgreSQL Docs

See also: PostgreSQL Backup and Replication · Redis · MongoDB · MySQL · SQLite Cheat-Sheet · SQL

Installation #

Install PostgreSQL on Ubuntu / Debian #

sudo apt update
sudo apt install -y postgresql postgresql-contrib postgresql-client
sudo systemctl status postgresql.service

This installs the version shipped with your release (16 on Ubuntu 24.04, 12 on 20.04). For a newer version use the official PGDG apt repository. In paths below replace 16 with your major version (ls /etc/postgresql).

Run in Docker #

docker run -d --name postgres --restart unless-stopped \
  -e POSTGRES_PASSWORD=Supersecret \
  -e POSTGRES_USER=myuser -e POSTGRES_DB=dbname \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql/data \
  postgres:16
docker exec -it postgres psql -U myuser -d dbname

See Docker and Docker-Compose. For major versions 18 and later the image changed its data directory layout, so check the image documentation for the mount path before upgrading.

Install / deploy Postgres on Kubernetes with Zalando Postgres Operator #

Postgres is probably the most common database on cloud platforms and also runs on Kubernetes. Several “Kubernetes Operators” handle the deployment of Postgres clusters for you. One of them is the Postgres Operator by Zalando.

Tutorials for deploying the operator and working with it:

• Deploy Zalando Postgres Operator on your Kubernetes cluster
• Configure Zalando Postgres Operator Backup with WAL-G
• Configure Zalando Postgres Operator Restore with WAL-G

Initial database connection #

A local connection (from the database server) can be made with:

sudo -u postgres psql

psql (16.4 (Ubuntu 16.4-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

Set password for postgres database user #

Set the password for the postgres database user with the \password meta-command or with ALTER USER postgres PASSWORD 'Supersecret';. By default pg_hba.conf only lets the OS user postgres connect locally (peer), so a password login is not possible until you change it.

Update pg_hba.conf to allow password connections #

To allow the postgres database user to log in without OS user authentication, edit pg_hba.conf (/etc/postgresql/<VERSION>/main/pg_hba.conf). Ask Postgres where it is with sudo -u postgres psql -c 'SHOW hba_file;'.

local   all             postgres                                peer

Change the method at the end of that line to scram-sha-256 (the current default password method since PostgreSQL 14; md5 still works but is older).

local   all             postgres                                scram-sha-256

Passwords set while password_encryption was md5 keep working under md5 only. Re-set them (\password) after switching to scram-sha-256.

To accept remote connections two more things are needed:

1. In postgresql.conf set listen_addresses = '*' (or a specific IP).
2. Add a host line to pg_hba.conf for the allowed network:

host    all             all             192.168.1.0/24          scram-sha-256

Apply with a restart (a reload is enough for pg_hba.conf alone):

sudo systemctl restart postgresql

Connect from another host, for example:

psql -U postgres -d postgres -h databasehostname

Open TCP port 5432 in the firewall only for trusted networks (see UFW (uncomplicated firewall)).

Creation of additional database users #

CREATE USER myuser WITH ENCRYPTED PASSWORD 'Supersecret';

CREATE ROLE

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 myuser    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Creation of additional databases #

You can create new databases within an instance. Log in with psql (see above).

CREATE DATABASE dbname OWNER myuser;

CREATE DATABASE

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 dbname    | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres

If you leave out OWNER, the current user becomes the owner of the new database.

To change the owner of an existing database later:

ALTER DATABASE dbname OWNER TO myuser;

Grant a user rights on a database:

GRANT ALL PRIVILEGES ON DATABASE dbname TO myuser;

On PostgreSQL 15 and later, ordinary users can no longer create objects in the public schema by default. Make the user the database owner (as above) or run GRANT ALL ON SCHEMA public TO myuser; while connected to that database.

psql Meta-Commands #

Backup and Restore #

Maintenance and Inspection #

SELECT version();
SELECT pg_size_pretty(pg_database_size('dbname'));
SELECT pid, usename, state, query FROM pg_stat_activity;
SELECT pg_terminate_backend(<PID>);
VACUUM (ANALYZE, VERBOSE) tablename;

Drop Users and Databases #

DROP DATABASE dbname;
DROP USER myuser;

A user that owns objects cannot be dropped. Use REASSIGN OWNED BY myuser TO postgres; DROP OWNED BY myuser; first.

Roles and Privileges #

In Postgres users and groups are both roles. CREATE USER is CREATE ROLE ... LOGIN.

Give each application its own role and database. Do not use the postgres superuser in applications.

Indexes #

Find unused and missing indexes:

SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan ASC LIMIT 20;
SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 20;

Primary keys and unique constraints create their own indexes. Foreign key columns are not indexed automatically.

EXPLAIN #

EXPLAIN ANALYZE really executes the statement. Wrap INSERT, UPDATE and DELETE in BEGIN; ... ROLLBACK;.

What to look for: Seq Scan on a big table with a selective filter (missing index), estimated rows far from actual rows (run ANALYZE), Sort spilling to disk (work_mem), nested loops with large row counts, Rows Removed by Filter very high.

Find slow queries with the pg_stat_statements extension:

CREATE EXTENSION pg_stat_statements;      -- also needs shared_preload_libraries = 'pg_stat_statements' and a restart
SELECT calls, round(total_exec_time) AS ms, round(mean_exec_time) AS mean_ms, left(query, 80) AS query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Log slow statements: log_min_duration_statement = 500 (milliseconds) in postgresql.conf.

Vacuum and Autovacuum #

Updates and deletes leave dead rows. Autovacuum removes them and updates statistics. It is on by default and should stay on.

Command line: vacuumdb --all --analyze-in-stages (after a pg_upgrade), reindexdb. For bloat without downtime use the pg_repack extension.

Extensions #

In Docker, use an image that ships the extension (for example postgis/postgis, pgvector/pgvector, timescale/timescaledb) because extension binaries must be on the server host.

Tuning Basics #

Settings in postgresql.conf (or ALTER SYSTEM SET ...; then SELECT pg_reload_conf();). Defaults are conservative. Check with SHOW <SETTING>; and see what needs a restart: SELECT name, setting, pending_restart FROM pg_settings WHERE pending_restart;

Use PGTune for a first draft and measure afterwards. Docker: pass settings as postgres -c shared_buffers=1GB -c max_connections=200 after the image name. Also see Linux Performance Troubleshooting and PostgreSQL Backup and Replication.

Useful Queries #

-- Database and table sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database ORDER BY pg_database_size(datname) DESC;
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- Active queries and blockers
SELECT pid, usename, state, wait_event_type, now() - query_start AS runtime, left(query, 60)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY runtime DESC;
SELECT pg_blocking_pids(pid), pid, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

-- Cancel (polite) or terminate (hard)
SELECT pg_cancel_backend(<PID>);
SELECT pg_terminate_backend(<PID>);

-- Cache hit ratio (aim for >99% on OLTP)
SELECT sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0) AS hit_pct FROM pg_stat_database;