Welcome to the Drills
You run servers or platforms, and somewhere underneath, PostgreSQL is running. As long as everything works, nobody notices. Then the disk is full of WAL, a query has been holding a lock for hours, yesterday’s backup cannot be restored, or the primary is gone and nobody knows whether the standby was up to date. This book prepares you for exactly these moments – not with theory, but in the terminal.
The path leads in two parts from a single server to the operator. In the first part you run PostgreSQL the classic way on two VMs: install, secure, configure, understand queries, clean up, monitor, back up, restore, replicate, pool and upgrade. In the second part CloudNativePG on Kubernetes takes over the same tasks – and because you did them by hand before, you know what the operator actually does and where to look when it does not.
The book is aimed at admins, DevOps and platform engineers with Linux
basics: you should be able to log in with ssh, work with
sudo, systemctl and journalctl
and edit configuration files. Basic SQL knowledge helps, but gets a
quick refresher. For the second part you need Kubernetes basics: Pods,
Services, Secrets and PVCs, reading YAML, using
kubectl.
How the book works
- One day, one topic. Each day has a goal, a time estimate, a short briefing and 5–8 drills. You type every command yourself and compare with “Expected”.
- Before, configure, after. Every feature starts with
the state without it and ends with proof: a query on
pg_stat_*, a line in the log, a successful restore, a survived outage. - No helper script. Every step is shown as a single command, including the Checkpoint at the end of each day.
- Speed Drills at the end of each day: small tasks with a target time.
- Checkpoint, Challenge, Interview Note, Enterprise Note close out every day. The Interview Note collects the questions on the topic that come up in a job interview or on call, along with the core answer. The Enterprise Note tells you what is different in production than in the lab.
- One database through many days:
notes, the database of the small Go API notes-api. For load and larger amounts of data,generate_seriesandpgbenchjoin in.
Part 1 – PostgreSQL on VMs:
| Days | Topic |
|---|---|
| 0–3 | Training ground, psql, roles and privileges,
configuration, pg_hba.conf, TLS |
| 4–7 | MVCC and locks, indexes and EXPLAIN, VACUUM, monitoring
and logs |
| 8–11 | Logical and physical backups, PITR, streaming and logical replication |
| 12–13 | Connection pooling with PgBouncer, major upgrade |
Part 2 – CloudNativePG on Kubernetes:
| Days | Topic |
|---|---|
| 14 | Install the operator, first cluster with three instances |
| 15 | Backups to object storage, point-in-time recovery into a new cluster |
| 16 | High availability: failover, switchover, rolling update, fencing, upgrade |
| 17 | Operations: Pooler, monitoring, declarative roles and databases, TLS, resources |
| 18 | Connecting the application: Secrets, connections from Pods, migrations, read replicas |
To finish:
- Competition (Day 19): broken databases that you repair under time pressure.
- Appendix A is the cheat sheet, Appendix B the final exam: two hours, about 16 tasks across the whole book.
What you need
For Part 1, two VMs with Ubuntu 24.04 on the same network, with SSH
access and sudo:
| Role | Purpose | IP in the book | In the lab |
|---|---|---|---|
| pg1 | primary, main working host | 172.16.0.182 | 4 vCPU, 8 GB RAM |
| pg2 | client, backup target, standby | 172.16.0.123 | 2 vCPU, 4 GB RAM |
In the lab the VMs are called argo and prod
– name yours however you like, and use your own IPs everywhere. There is
no name resolution between the VMs; that is why the book uses IPs
everywhere.
Proxmox, VMware, a cloud or Multipass – it does not matter. On your own machine, Multipass is the easiest way. So that Day 0 finds your SSH key on both VMs, you pass it in via cloud-init:
cat > pg-cloud-init.yaml <<EOF
#cloud-config
ssh_authorized_keys:
- $(cat ~/.ssh/id_ed25519.pub)
EOF
multipass launch 24.04 --name pg1 --cpus 4 --memory 8G --disk 40G \
--cloud-init pg-cloud-init.yaml
multipass launch 24.04 --name pg2 --cpus 2 --memory 4G --disk 30G \
--cloud-init pg-cloud-init.yaml
multipass listmultipass list shows the IPs. On a Mac with Apple
Silicon the VMs are arm64. The PGDG repository builds PostgreSQL and
PgBouncer for arm64 too, and all package sources and downloads in the
book detect the architecture themselves – file names and output then
show arm64 where the book shows amd64.
For Part 2 you also need a kubeadm cluster with three nodes on which
you are cluster admin. In the lab this is the cluster ost
(one control plane cp1 at 172.16.0.126, two workers with 2
vCPU and 4 GB each, Cilium as the CNI). If you still have the training
ground from “CKA hands-on” or “Cilium hands-on”, use it. To be honest
about what the days require:
- Day 14: a StorageClass
local-path(the default class in the lab; if yours has a different name, use its name) and room for three small PostgreSQL Pods with a 256 MiB request each. - You install cert-manager and kube-prometheus-stack yourself on Day
14 (release
kpsin the namespacemonitoring); if you already have them, use yours and substitute the release name, namespace and Grafana password accordingly. - Day 16: more than one worker, so that a node can fail while the
database keeps running on another one. For Drill 3, SSH from
cp1to the workers (a user withsudo) andcrictlthere. - Day 18: a CNI that enforces NetworkPolicies; for Drill 8, Cilium
including the
ciliumandhubbleCLIs and Hubble Relay (as in the Cilium book). Without Cilium, Hubble and theCiliumNetworkPolicyare skipped. pg1,pg2and the cluster should be able to reach each other on the network: on Day 14,pg2connects to the cluster from outside, and on Day 17 Prometheus scrapes metrics frompg1.
Time: Days 0 to 14 add up to just under 21 hours (1255 minutes). Days 15 to 18 add 75 to 90 minutes each (330 minutes together), the Competition on Day 19 adds 120 minutes – about 28 hours in total, plus the two-hour final exam. At one training day per evening and five evenings per week, you are through in a good four weeks.
On Day 0 you install PostgreSQL 18 from the project’s official repository (PGDG, apt.postgresql.org) instead of version 16 from Ubuntu. At the time of writing, PostgreSQL 19 was available as a release candidate. That is why Day 13 practices the major upgrade from 17 to 18 and shows you how to check whether 19 has been released in the meantime – the steps are the same.
The companion repo
The source code of notes-api (folder notes-api) and the
material for the book live at
https://github.com/thomaszachmann/postgresql-zum-anfassen.
You do not need to build anything: the image
ghcr.io/thomaszachmann/notes-api:0.5.1 is ready-made for
amd64 and arm64. You still type the configurations, SQL and manifests in
the book yourself – that is the training.
What you can do afterwards
- Install PostgreSQL from PGDG, grant roles and privileges following
the principle of least privilege, and secure access with
pg_hba.conf, SCRAM and your own CA. - Find parameters along with their source and know whether a reload is enough or a restart is needed.
- Find locks, slow queries and bloat, read execution plans and build the right index.
- See what the server is doing with
pg_stat_activity,pg_stat_statements, logs and postgres_exporter. - Back up logically and physically, prove a backup with a restore, and use point-in-time recovery to go back to the second before the mistake.
- Set up a standby, perform failover and switch back with
pg_rewind, replicate tables logically, pool connections with PgBouncer and upgrade a major version. - The same with CloudNativePG on Kubernetes: create clusters, back up, restore, survive outages, monitor and connect an application.
Note: This book is an independent practice book. It is not affiliated with the PostgreSQL Global Development Group, the PostgreSQL Community Association of Canada or the CloudNativePG project and is neither reviewed nor endorsed by them. PostgreSQL is a trademark of the PostgreSQL Community Association of Canada.
Versions
| Component | Version | from Day |
|---|---|---|
| Ubuntu (pg1, pg2) | 24.04.5 LTS | 0 |
| PostgreSQL (PGDG) | 18.7 | 0 |
| pg_repack | 1.5.3 | 6 |
| postgres_exporter | 0.21.1 | 7 |
| pgBackRest | 2.60.1 | 9 |
| PgBouncer (PGDG) | 1.26.1 | 12 |
| PostgreSQL 17 (starting version for the upgrade) | 17.12 | 13 |
Kubernetes (lab cluster ost) |
v1.37.2 | 14 |
| Helm | v4.3.2 | 14 |
| cert-manager | v1.21.3 | 14 |
cmctl |
2.6.2 | 14 |
| kube-prometheus-stack (Helm chart) | 93.4.1 | 14 |
CloudNativePG (operator and kubectl cnpg) |
1.30.2 | 14 |
| PostgreSQL image for CloudNativePG | 18.7-minimal-trixie | 14 |
RustFS (S3 on pg2) |
1.0.6 | 15 |
| Barman Cloud Plugin | 0.16.0 | 15 |
| PgBouncer image (CNPG Pooler) | 1.26.1 | 17 |
| notes-api | 0.5.1 | 18 |
curlimages/curl |
8.17.0 | 18 |
All output comes from real runs, shortened where it makes sense.
Passwords such as lab-notes-123 or
lab-repl-123 are lab values only – in production, passwords
belong in a password vault or a Secret, never in a book.
Enjoy your drills.