Skip to content
Hands-on IT IT Drills

Day 7: Postgres as a StatefulSet

Goal: Postgres runs as a StatefulSet with its own volume, a migration creates the table, all api replicas see the same notes, and a backup sits on your Mac. · Duration: approx. 90 min · Prerequisite: Day 6

Briefing

  • A StatefulSet gives every Pod a fixed name (db-0), a fixed DNS name and its own volume, which goes back to the same Pod on restart.
  • The DNS name is provided by a headless Service (clusterIP: None): it points directly to the Pod IPs, without a virtual IP in front.
  • The volume is requested by a PersistentVolumeClaim (PVC). The StorageClass standard (in kind: local-path) creates a matching PersistentVolume (PV) on the node.
  • The table is created by a Job: a Pod that is supposed to end successfully once.
 api ×3 ──db:5432──► Service db (headless) ──► db-0 ──► PVC data-db-0 ──► PV
                                               ▲          (StorageClass standard)
 Job migrate ──────────────────────────────────┘

Drill 1: The Storage Cliffhanger

Start of the day:

source ~/k8s-lab/setup/env.sh
mkdir -p ~/k8s-lab/tag07 && cd ~/k8s-lab/tag07
cp -R "$LAB/code/notes/." . && cp -R "$LAB/code/tag06/notes/." .

A reminder of Day 3 – write three notes, read six times:

for t in eins zwei drei; do
  curl -s -X POST localhost:30080/api/notes -H 'Content-Type: application/json' \
    -d "{\"text\":\"Notiz $t\"}" >/dev/null
done
for i in 1 2 3 4 5 6; do curl -s localhost:30080/api/notes | jq -c '[.[].text]'; done
kubectl get storageclass

Expected (abridged, names, IPs and times will differ on your machine):

["Notiz eins"]
["Notiz eins"]
["Notiz zwei","Notiz drei"]
["Notiz eins"]
["Notiz zwei","Notiz drei"]
["Notiz zwei","Notiz drei"]
NAME                 PROVISIONER             RECLAIMPOLICY   VOLUMEBINDINGMODE      …
standard (default)   rancher.io/local-path   Delete          WaitForFirstConsumer   …

Every api replica has its own list in memory. The solution is a shared database. kind ships with the StorageClass standard; WaitForFirstConsumer means: the volume is only created when a Pod needs it – on that Pod’s node.

Save your progress? Sign up for free

Drill 2: Secret and StatefulSet

secrets.sh additionally creates the Secret notes-db – the password for Postgres and the ready-made URL for api:

cat > k8s/secrets.sh <<'EOF'
#!/usr/bin/env bash
# k8s/secrets.sh – state at end of Day 7: creates the Secrets (idempotent, lab values only)
set -euo pipefail
kubectl -n notes create secret generic notes-api \
  --from-literal=api-key="${NOTES_API_KEY:-lab-api-key-123}" \
  --dry-run=client -o yaml | kubectl apply -f -
pw="${POSTGRES_PASSWORD:-lab-db-pw-123}"
kubectl -n notes create secret generic notes-db \
  --from-literal=password="$pw" \
  --from-literal=db-url="postgres://notes:$pw@db:5432/notes" \
  --dry-run=client -o yaml | kubectl apply -f -
EOF
bash -n k8s/secrets.sh && ./k8s/secrets.sh
kubectl get secret notes-db -o json | jq -r '.data | keys[]'

Expected:

secret/notes-api unchanged
secret/notes-db created
db-url
password

The complete k8s/db.yaml (headless Service plus StatefulSet) is in the companion code. The essentials:

# k8s/db.yaml (excerpt) – state at end of Day 7
spec:
  clusterIP: None              # Service db: headless
---
kind: StatefulSet
spec:
  serviceName: db              # provides the DNS names db-0.db, db-1.db, …
  replicas: 1
  template:
    spec:
      containers:
        - name: postgres
          image: postgres:18.6-alpine
          env:
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: notes-db
                  key: password
          # … POSTGRES_USER/DB notes, resources 100m/128Mi/256Mi …
          readinessProbe:
            exec:
              command: [pg_isready, -h, 127.0.0.1, -U, notes, -d, notes]
            periodSeconds: 5
          volumeMounts:
            - name: data
              mountPath: /var/lib/postgresql
  volumeClaimTemplates:        # one PVC per Pod: data-db-0
    - metadata:
        name: data
      spec:
        accessModes: [ReadWriteOnce]
        resources:
          requests:
            storage: 1Gi

pg_isready with -h 127.0.0.1 checks TCP, not the Unix socket – Postgres’s temporary init server only listens on the socket and would otherwise report “ready” too early.

cp "$LAB/code/tag07/notes/k8s/"{db,migrate,api}.yaml k8s/
kubectl apply --dry-run=server -f k8s/db.yaml
kubectl apply -f k8s/db.yaml
sleep 3; kubectl get pvc,pod -l app.kubernetes.io/name=db
kubectl rollout status statefulset/db --timeout 180s
c='PVC:.metadata.name,STATUS:.status.phase,PV:.spec.volumeName'
kubectl get pvc -o custom-columns="$c,SIZE:.status.capacity.storage"

Expected (abridged):

service/db created (server dry run)
statefulset.apps/db created (server dry run)
service/db created
statefulset.apps/db created
NAME                              STATUS    VOLUME   CAPACITY   ACCESS MODES   …
persistentvolumeclaim/data-db-0   Pending                                      …
NAME       READY   STATUS    RESTARTS   AGE
pod/db-0   0/1     Pending   0          3s
Waiting for 1 pods to be ready...
partitioned roll out complete: 1 new pods have been updated...
PVC         STATUS   PV                                         SIZE
data-db-0   Bound    pvc-1a10d53e-4c96-4c72-a1ea-e83ed0335344   1Gi

From the template data, the StatefulSet made the PVC data-db-0. Only once the scheduler had assigned db-0 to a node did local-path create the PV there. Where exactly, and what is in it?

pv=$(kubectl get pvc data-db-0 -o jsonpath='{.spec.volumeName}')
c='RECLAIM:.spec.persistentVolumeReclaimPolicy,PATH:.spec.hostPath.path'
kubectl get pv "$pv" -o custom-columns="$c"
kubectl get pod db-0 -o jsonpath='{.spec.nodeName}'; echo
kubectl exec db-0 -- ls /var/lib/postgresql /var/lib/postgresql/18

Expected (abridged):

RECLAIM   PATH
Delete    /var/local-path-provisioner/pvc-1a10d53e-…_notes_data-db-0
lab-worker
/var/lib/postgresql:
18

/var/lib/postgresql/18:
docker

Postgres 18 puts its data into a subdirectory named after the major version (/var/lib/postgresql/18/docker). That is why the volume is mounted one level higher, at /var/lib/postgresql – so that during a later major upgrade, the old and new versions sit on the same volume. The data lives in a directory on the node lab-worker; if the Pod moves, it only finds it there (ReadWriteOnce, local-path binds the PV via node affinity).

Save your progress? Sign up for free

Drill 3: Headless Service and Stable Names

kubectl get svc db
kubectl get endpointslice -l kubernetes.io/service-name=db \
  -o custom-columns='ADDR:.endpoints[*].addresses[0],HOSTNAME:.endpoints[*].hostname'
for n in api db db-0.db; do kubectl exec deploy/web -- getent hosts $n; done

Expected (abridged):

NAME   TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)    AGE
db     ClusterIP   None         <none>        5432/TCP   54s
ADDR          HOSTNAME
10.244.2.61   db-0
10.96.246.140     api.notes.svc.cluster.local  api.notes.svc.cluster.local api
10.244.2.61       db.notes.svc.cluster.local  db.notes.svc.cluster.local db
10.244.2.61       db-0.db.notes.svc.cluster.local  db-0.db.notes.svc.cluster.local …

api resolves to a virtual Service IP (10.96.…), and kube-proxy distributes behind it. db and db-0.db resolve directly to the Pod IP. With several replicas, db would return all Pod IPs, and db-0.db always exactly the one of db-0 – this is how database clusters address their primary. The IP changes with every new Pod; the name stays.

Save your progress? Sign up for free

Drill 4: Migration as a Job

migrate.yaml starts notes-api with the argument migrate. The most important part:

# k8s/migrate.yaml (excerpt) – state at end of Day 7
kind: Job
spec:
  backoffLimit: 4                # at most 4 retries
  ttlSecondsAfterFinished: 600   # Kubernetes cleans up 10 min after the end
  template:
    spec:
      restartPolicy: OnFailure
      containers:
        - name: migrate
          image: notes-api:0.2.0
          args: [migrate]
          env:
            - name: NOTES_DB_URL_FILE
              value: /etc/notes/db-url
          # … resources, volumeMounts: Secret notes-db, only the key db-url …
kubectl apply --dry-run=server -f k8s/migrate.yaml
kubectl apply -f k8s/migrate.yaml
kubectl wait --for=condition=Complete job/migrate --timeout 120s
kubectl get job migrate
kubectl logs job/migrate
kubectl exec db-0 -- psql -U notes -d notes -c '\d notes'

Expected (abridged):

job.batch/migrate created (server dry run)
job.batch/migrate created
job.batch/migrate condition met
NAME      STATUS     COMPLETIONS   DURATION   AGE
migrate   Complete   1/1           6s         7s
time=… level=INFO msg="migration complete" table=notes
                          Table "public.notes"
   Column   |           Type           | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
 id         | uuid                     |           | not null |
 text       | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()

restartPolicy: OnFailure restarts the container on a non-zero exit, and backoffLimit limits that. A Deployment wouldn’t work here: it would keep restarting the finished container over and over.

Save your progress? Sign up for free

Drill 5: Switch api to Postgres

In addition to the key, api gets the DB URL – both as files from a projected volume, which puts several Secrets into one directory:

# k8s/api.yaml (excerpt) – state at end of Day 7
          env:
            - name: NOTES_API_KEY_FILE
              value: /etc/notes/api-key
            - name: NOTES_DB_URL_FILE
              value: /etc/notes/db-url
      # …
      volumes:
        - name: secrets
          projected:
            sources:
              - secret:
                  name: notes-api
                  items:
                    - key: api-key
                      path: api-key
              - secret:
                  name: notes-db
                  items:
                    - key: db-url
                      path: db-url
kubectl apply -f k8s/ | grep -v unchanged
kubectl rollout status deploy/api >/dev/null
for t in eins zwei drei; do
  curl -s -X POST localhost:30080/api/notes -H 'Content-Type: application/json' \
    -d "{\"text\":\"Postgres $t\"}" >/dev/null
done
for i in 1 2 3 4 5 6; do curl -s localhost:30080/api/notes | jq -c '[.[].text]'; done
kubectl logs deploy/api | grep 'store ready'

Expected (abridged):

deployment.apps/api configured
statefulset.apps/db configured
["Postgres eins","Postgres zwei","Postgres drei"]
["Postgres eins","Postgres zwei","Postgres drei"]
["Postgres eins","Postgres zwei","Postgres drei"]
["Postgres eins","Postgres zwei","Postgres drei"]
["Postgres eins","Postgres zwei","Postgres drei"]
["Postgres eins","Postgres zwei","Postgres drei"]
Found 3 pods, using pod/api-b8f9cb55-5pr7w
time=… level=INFO msg="store ready" store=postgres

The same list six times – the cliffhanger from Day 3 is resolved. The notes from Drill 1 are gone; they only lived in the memory of the old Pods. kubectl apply reports statefulset.apps/db configured on every run, without anything changing (the generation stays 1, db-0 doesn’t restart) – harmless.

Warning: Only write once rollout status has finished. During the rollout, the old in-memory Pods (preStop plus grace period) keep serving existing keep-alive connections from web. A note that lands there is gone together with the Pod.

Save your progress? Sign up for free

Drill 6: Database Gone

What happens when Postgres is missing? Shut db down:

kubectl scale statefulset db --replicas 0
kubectl wait --for=delete pod/db-0 --timeout 60s; sleep 20
kubectl get pods -l app.kubernetes.io/name=api
curl -s -o /dev/null -w '%{http_code}\n' localhost:30080/api/notes

Expected (abridged):

statefulset.apps/db scaled
pod/db-0 condition met
NAME                 READY   STATUS    RESTARTS   AGE
api-b8f9cb55-5pr7w   0/1     Running   0          84s
api-b8f9cb55-gkmrs   0/1     Running   0          80s
api-b8f9cb55-rd7w2   0/1     Running   0          77s
500

The readiness on /healthz takes all api Pods out of traffic, and web answers with 500. But RESTARTS 0: the liveness on /version leaves the healthy processes alone – the proof from Day 5. And a new api Pod?

kubectl rollout restart deploy/api; sleep 40
kubectl get pods -l app.kubernetes.io/name=api
p=$(kubectl get pod -l app.kubernetes.io/name=api \
  --sort-by=.metadata.creationTimestamp -o name | tail -1)
kubectl logs "$p" --previous | tail -1

Expected (abridged):

deployment.apps/api restarted
NAME                   READY   STATUS             RESTARTS      AGE
api-79bc846579-282pv   0/1     CrashLoopBackOff   2 (25s ago)   41s
api-b8f9cb55-5pr7w     0/1     Running            0             2m6s
…
time=… level=ERROR msg="database unreachable" source=file err="failed to connect to
`user=notes database=notes`:\n\thostname resolving error: lookup db on 10.96.0.10:53:
server misbehaving…"

Without a reachable database, notes-api exits with exit code 1 at startup, Kubernetes restarts it, and RESTARTS counts up – until db is back. That is the counterpart of depends_on from Compose: no start order, but retrying until it works. By the way, without a Pod the headless Service returns no address – hence the DNS error.

kubectl scale statefulset db --replicas 1
kubectl rollout status statefulset/db >/dev/null
kubectl rollout status deploy/api --timeout 180s >/dev/null
kubectl get pods -l app.kubernetes.io/name=api
curl -s localhost:30080/api/notes | jq -c '[.[].text]'

Expected (abridged):

statefulset.apps/db scaled
NAME                   READY   STATUS        RESTARTS      AGE
api-79bc846579-282pv   1/1     Running       4 (56s ago)   97s
api-79bc846579-8lqsb   1/1     Running       0             5s
api-79bc846579-m6659   1/1     Running       0             2s
…
["Postgres eins","Postgres zwei","Postgres drei"]

The first new Pod needed four restarts. The others only came once it was ready – maxUnavailable: 0 kept the old ones in play until then.

Save your progress? Sign up for free

Drill 7: Data Survives

c='NAME:.metadata.name,UID:.metadata.uid,IP:.status.podIP'
kubectl get pod db-0 -o custom-columns="$c"
kubectl delete pod db-0
kubectl wait --for=condition=Ready pod/db-0 --timeout 120s
kubectl get pod db-0 -o custom-columns="$c"
sleep 3; curl -s localhost:30080/api/notes | jq -c '[.[].text]'

Expected (abridged):

NAME   UID                                    IP
db-0   2bf2ab00-74a1-407f-984d-87ac73817ff1   10.244.2.61
pod "db-0" deleted from notes namespace
pod/db-0 condition met
NAME   UID                                    IP
db-0   b4262687-7331-49b0-a0b9-ebbba3459cfb   10.244.2.65
["Postgres eins","Postgres zwei","Postgres drei"]

New Pod, new UID, new IP – but the same name and the same PVC. You may even delete the StatefulSet:

kubectl get sts db -o jsonpath='{.spec.persistentVolumeClaimRetentionPolicy}'; echo
kubectl delete statefulset db
kubectl wait --for=delete pod/db-0 --timeout 60s
kubectl get pvc -o custom-columns='PVC:.metadata.name,STATUS:.status.phase'
kubectl apply -f k8s/db.yaml && kubectl rollout status statefulset/db >/dev/null
kubectl exec db-0 -- psql -U notes -d notes -tAc 'SELECT count(*) FROM notes'

Expected (abridged):

{"whenDeleted":"Retain","whenScaled":"Retain"}
statefulset.apps "db" deleted from notes namespace
pod/db-0 condition met
PVC         STATUS
data-db-0   Bound
service/db unchanged
statefulset.apps/db created
3

PVCs from volumeClaimTemplates stay in place by default. The switch to Helm on Day 10 builds on exactly this behavior.

Warning: kubectl delete pvc data-db-0 really deletes the data: the reclaim policy of the StorageClass is Delete, and local-path then removes the directory on the node. kubectl delete namespace notes takes the PVCs with it.

Save your progress? Sign up for free

Drill 8: Backup and Restore Test

pg_dump runs in the Pod; the output lands on your Mac via kubectl exec:

kubectl exec db-0 -- pg_dump -U notes -d notes --clean --if-exists > backup.sql
ls -l backup.sql | awk '{print $5, $9}'
grep -E '^(DROP|CREATE|COPY)' backup.sql

Expected (abridged):

1644 backup.sql
DROP TABLE IF EXISTS public.notes;
CREATE TABLE public.notes (
COPY public.notes (id, text, created_at) FROM stdin;

--clean --if-exists puts a DROP … IF EXISTS in front of every table – that way the backup can also be restored over an existing state. A backup that has never been restored is a hope. So test it in a throwaway database:

kubectl exec db-0 -- createdb -U notes probe
kubectl exec -i db-0 -- psql -q -v ON_ERROR_STOP=1 -U notes -d probe \
  < backup.sql >/dev/null
kubectl exec db-0 -- psql -U notes -d probe -tAc \
  'SELECT text FROM notes ORDER BY created_at'
kubectl exec db-0 -- dropdb -U notes probe

Expected:

Postgres eins
Postgres zwei
Postgres drei

-i passes your standard input through to the container – without -i, psql would see nothing of backup.sql. It works without a password because the Postgres image trusts connections over the local Unix socket inside the container.

Save your progress? Sign up for free

Checkpoint

kubectl get pvc,svc

Expected: PVC data-db-0 is Bound, and the Service db has no cluster IP (None, headless).

NAME                              STATUS   VOLUME                     CAPACITY   …
persistentvolumeclaim/data-db-0   Bound    pvc-2d13ae07-f529-4fb3-…   1Gi        …

NAME          TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
service/api   ClusterIP   10.96.142.49    <none>        8080/TCP         3m33s
service/db    ClusterIP   None            <none>        5432/TCP         54s
service/web   NodePort    10.96.180.246   <none>        3000:30080/TCP   3m33s
kubectl get pod db-0

Expected: db-0 is running and ready.

NAME   READY   STATUS    RESTARTS   AGE
db-0   1/1     Running   0          53s
kubectl logs deploy/api | grep store

Expected: store=postgres.

Found 3 pods, using pod/api-f9c7bb64f-s4rdx
time=… level=INFO msg="store ready" store=postgres
curl -s localhost:30080/api/notes
curl -s localhost:30080/api/notes

Expected: the same list twice, no matter which replica answers (content differs).

[{"id":"c74b85b6-…","text":"bleibt in Postgres"}]
[{"id":"c74b85b6-…","text":"bleibt in Postgres"}]
grep COPY ~/k8s-lab/tag07/backup.sql

Expected: the backup from Drill 7 contains the table notes.

COPY public.notes (id, text, created_at) FROM stdin;

Challenge

Someone accidentally deleted all notes. Restore them from backup.sql without restarting db or api.

Hint: The backup already contains a DROP TABLE IF EXISTS – you can restore it directly into the database notes.

Solution

Re-create the accident and repair it:

kubectl exec db-0 -- psql -U notes -d notes -c 'DELETE FROM notes'
curl -s localhost:30080/api/notes; echo
kubectl exec -i db-0 -- psql -q -v ON_ERROR_STOP=1 -U notes -d notes \
  < backup.sql >/dev/null; echo "Exit: $?"
curl -s localhost:30080/api/notes | jq -c '[.[].text]'

Expected:

DELETE 3
[]

Exit: 0
["Postgres eins","Postgres zwei","Postgres drei"]

api notices nothing of the table being replaced: it queries anew on every request, and database/sql re-establishes broken connections by itself.

Save your progress? Sign up for free

Enterprise Note

  • Databases in Kubernetes are run with an operator (CloudNativePG, Zalando Postgres Operator): replication, failover, point-in-time recovery, upgrades (“Kubernetes operators hands-on”). Or you use a managed database outside the cluster.
  • local-path is only for the lab: the data is tied to one node. In production, a CSI driver provides network or distributed storage (Ceph/Rook, Longhorn, cloud disks) with snapshots via VolumeSnapshot.
  • pg_dump is a logical backup and good for small databases. For large ones: WAL archiving with pgBackRest or Barman, and regularly tested restores.
  • For production data, use StorageClasses with reclaimPolicy: Retain – then the PV survives even an accidental delete pvc.
  • With several instances, migrations need a lock and run in the pipeline or as a Helm hook (Day 10).