Skip to content
Hands-on IT IT Drills

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_series and pgbench join 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 list

multipass 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 kps in the namespace monitoring); 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 cp1 to the workers (a user with sudo) and crictl there.
  • Day 18: a CNI that enforces NetworkPolicies; for Drill 8, Cilium including the cilium and hubble CLIs and Hubble Relay (as in the Cilium book). Without Cilium, Hubble and the CiliumNetworkPolicy are skipped.
  • pg1, pg2 and the cluster should be able to reach each other on the network: on Day 14, pg2 connects to the cluster from outside, and on Day 17 Prometheus scrapes metrics from pg1.

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.