Zum Inhalt springen
Trainingslager IT-Trainingslager

Anhang B: Abschlussprüfung

Ziel: Du löst 16 Aufgaben aus Teil 1 und Teil 2 in 120 Minuten und bewertest dich selbst. · Dauer: 120 min · Voraussetzung: Tag 0–19

Spielregeln

  • Zeit: 120 Minuten, Uhr stellen. Jede Aufgabe hat ein Gewicht in Prozent, zusammen 100 %: 61 % für den klassischen Betrieb (Aufgabe 1–10), 39 % für CloudNativePG (Aufgabe 11–16).
  • Ein Zertifikat gibt es nicht. Die eigene Messlatte: ab 70 % sitzt das Handwerk.
  • Erlaubt: postgresql.org/docs/18, cloudnative-pg.io/docs, \h, \?, --help und kubectl explain. Nicht erlaubt: dieses Buch während der Zeit.
  • Teil 1 läuft auf pg1 (Datenbank pruef, Dateien in ~/pruef) und pg2 (Dateien in ~/pruef), Teil 2 auf cp1 im Namespace pruef (Dateien in ~/pg/pruef).
  • Rollen, die du anlegst, beginnen mit pruef_, Kubernetes-Objekte mit pruef-.
  • Die Aufgaben sind voneinander abhängig, wo es dasteht. Sonst ist die Reihenfolge frei.
  • Die Lösungen stehen am Ende. Erst nach Ablauf der Zeit lesen.

Vorbereitung

Auf pg1: Sicherung der Zugangskonfiguration, Datenbank, Rollen und Testdaten (etwa 20 s):

mkdir -p ~/pruef && cd ~/pruef
sudo -u postgres mkdir -p /var/lib/postgresql/backup-pruef
sudo -u postgres cp -a /etc/postgresql/18/main/pg_hba.conf \
  /var/lib/postgresql/18/main/postgresql.auto.conf /var/lib/postgresql/backup-pruef/
createdb pruef
psql -d pruef <<'EOF'
CREATE ROLE pruef_owner NOLOGIN;
CREATE ROLE pruef_anna LOGIN PASSWORD 'lab-anna-123';
CREATE ROLE pruef_report LOGIN PASSWORD 'lab-report-123';
CREATE SCHEMA lager AUTHORIZATION pruef_owner;
SET ROLE pruef_owner;
CREATE TABLE lager.artikel (id int PRIMARY KEY, name text NOT NULL, preis numeric);
INSERT INTO lager.artikel SELECT i, 'Artikel ' || i, i * 0.5
  FROM generate_series(1, 5000) i;
RESET ROLE;
CREATE TABLE konto (id int PRIMARY KEY, saldo numeric NOT NULL);
INSERT INTO konto SELECT i, 100 FROM generate_series(1, 10) i;
CREATE TABLE bestellung (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL, status text NOT NULL, angelegt timestamptz NOT NULL);
INSERT INTO bestellung (email, status, angelegt)
SELECT 'Kunde' || (i % 20000) || '@Example.org',
  CASE WHEN i % 50 = 0 THEN 'offen' ELSE 'erledigt' END,
  now() - i * interval '1 minute'
FROM generate_series(1, 1000000) i;
CREATE TABLE protokoll (id int PRIMARY KEY, eintrag text)
  WITH (autovacuum_enabled = off);
INSERT INTO protokoll SELECT i, repeat('p', 200) FROM generate_series(1, 400000) i;
DELETE FROM protokoll WHERE id % 4 <> 0;
VACUUM ANALYZE bestellung;
EOF

Für Aufgabe 5 eine Inventur, die eine Tabelle festhält:

cat > inventur.sql <<'EOF'
BEGIN;
LOCK TABLE konto IN SHARE MODE;
\! sleep 3000
EOF
PGAPPNAME=inventur nohup psql -X -d pruef -f inventur.sql >/dev/null 2>&1 &

Auf pg2:

mkdir -p ~/pruef

Auf cp1: eigene kubeconfig, Namespace und für Aufgabe 16 ein Pod, der sich an pg-notes versucht:

mkdir -p ~/pg/pruef && cp ~/.kube/config ~/pg/pruef/.kc
export KUBECONFIG=~/pg/pruef/.kc
cd ~/pg/pruef
kubectl create namespace pruef
kubectl run -n pruef angreifer \
  --image=ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie --restart=Never \
  --env=PGPASSWORD=geraten --env=PGCONNECT_TIMEOUT=3 --command -- sh -c \
  'for i in 1 2 3 4 5 6 7; do psql -h pg-notes-rw.tag14 -U notes_app -d notes \
  -c "SELECT 1"; sleep 2; done; sleep infinity'

Uhr starten.

Aufgabe 1: Bestandsaufnahme (5 %)

Auf pg1. Schreibe nach ~/pruef/01.txt drei Zeilen: die Server-Version als Zahl (etwa 180007), den Pfad der aktiven pg_hba.conf und die Zahl der fehlerfreien Regeln darin.

Aufgabe 2: Leser für heute und morgen (6 %)

Datenbank pruef. Lege die Gruppenrolle pruef_leser an. Sie darf alle Tabellen im Schema lager lesen – auch solche, die pruef_owner künftig anlegt –, aber nichts ändern. pruef_anna wird Mitglied. Weise es mit einer neuen Tabelle lager.lieferant (angelegt von pruef_owner) nach.

Aufgabe 3: Nur verschlüsselt (6 %)

pruef_anna soll sich von pg2 an pruef auf pg1 anmelden dürfen – per SCRAM und ausschließlich über TLS. Eine Verbindung ohne TLS wird abgewiesen. Alle anderen Regeln bleiben, wie sie sind.

Aufgabe 4: Berichtsrolle zähmen (5 %)

pruef_report bekommt in der Datenbank pruef – und nur dort – ein statement_timeout von 2 s und work_mem = 64MB. In anderen Datenbanken gelten die Standardwerte. Ohne Reload und ohne Neustart.

Aufgabe 5: Wer hält konto? (6 %)

Datenbank pruef. Ein UPDATE konto SET saldo = saldo + 1 WHERE id = 1 hängt. Ermittle über die Sperrtabelle, welche Anwendung (application_name) die Tabelle hält, schreibe den Namen nach ~/pruef/05.txt und beende nur diese Sitzung. Das UPDATE muss danach durchlaufen.

Aufgabe 6: Offene Bestellungen eines Kunden (7 %)

Datenbank pruef. Diese Abfrage soll ohne Seq Scan und ohne Sort auskommen; Schreib- zugriffe auf bestellung dürfen dabei nicht gesperrt werden. Der Index soll nur offene Bestellungen enthalten.

SELECT id, angelegt FROM bestellung
WHERE status = 'offen' AND lower(email) = '[email protected]'
ORDER BY angelegt DESC LIMIT 5;

Aufgabe 7: Protokoll schrumpfen (6 %)

Datenbank pruef. In protokoll sind drei Viertel der Zeilen gelöscht, die Datei ist noch voll groß. Verkleinere sie im laufenden Betrieb, ohne die Tabelle für die ganze Dauer exklusiv zu sperren. Danach gilt für die Tabelle: fillfactor 90, Autovacuum an und schon ab 2 % toten Zeilen.

Aufgabe 8: Schema umziehen (6 %)

Auf pg2. Sichere als pruef_anna (Aufgabe 2 und 3) nur das Schema lager der Datenbank pruef von pg1 übers Netz mit geprüftem TLS ins Archiv ~/pruef/08-lager.dump. Stelle es auf pg2 in einer neuen Datenbank pruef_kopie wieder her – ohne Fehler, Owner wird postgres.

Aufgabe 9: Physisches Backup prüfen (7 %)

Auf pg2. Ziehe als Rolle pruef_backup (anlegen, Lab-Passwort lab-pruef-backup, kein Superuser) ein Basis-Backup von pg1 im Tar-Format mit gzip nach /var/lib/postgresql/pruef-09, mit dem WAL der Sicherung. Prüfe es mit pg_verifybackup und schreibe den Namen des WAL-Segments, bei dem es beginnt, nach ~/pruef/09.txt.

Aufgabe 10: Der Standby mit Gedächtnislücke (7 %)

Auf pg2. Baue einen zweiten Cluster 18/pruef (Port 5433) als Standby von pg1 mit dem Slot pruef_pg2. Er empfängt das WAL sofort, spielt Änderungen aber erst 5 Minuten später ein – als Schutz gegen ein versehentliches DROP. Weise nach: Eine neue Tabelle auf pg1 ist auf dem Standby zuerst nicht da, obwohl das WAL schon angekommen ist.

Aufgabe 11: Cluster auf zwei Nodes (6 %)

Auf cp1, Namespace pruef. Lege den Cluster pruef-db an: zwei Instanzen PostgreSQL 18.7 (minimal-trixie), Datenbank lager mit Owner lager, max_connections = 50, Speicher 512Mi, Requests 50m/128Mi, Limit 256Mi. Die beiden Instanzen dürfen nie auf demselben Node laufen.

Aufgabe 12: Lesende Rolle per CRD (6 %)

Lege in lager als Rolle lager die Tabelle bestand mit 300 Zeilen an. Lege dann deklarativ die Rolle pruef_leser an (Login, höchstens 5 Verbindungen, Lab-Passwort lab-leser-123 aus einem Secret). Sie darf bestand lesen. Weise nach, dass sie über den Service pruef-db-ro auf einer Replica liest.

Aufgabe 13: Backup in den Bucket (7 %)

pruef-db archiviert WAL in den Bucket cnpg-backups auf pg2 (RustFS, Zugang pglab/lab-s3-secret-123). Ziehe danach ein Backup, das garantiert auf dem Primary läuft, und schreibe dessen Backup-ID nach ~/pg/pruef/13.txt. Die Daten von pg-notes im Bucket bleiben unberührt.

Aufgabe 14: Zurück auf das Backup (7 %)

Lösche in pruef-db die Tabelle bestand (der Unfall). Stelle in einem neuen Cluster pruef-klon (eine Instanz) genau den Stand vom Ende des Backups aus Aufgabe 13 her – nicht später – und weise nach, dass bestand dort vollständig ist.

Aufgabe 15: Pooler für Berichte (7 %)

Lege den Pooler pruef-db-lesen an: eine Instanz, nur Replicas, Session-Modus, höchstens 50 Clients. Weise mit der Owner-Rolle lager nach, dass Verbindungen über den Pooler auf der Replica landen.

Aufgabe 16: Wer klopft an? (6 %)

Irgendwer versucht seit Beginn der Prüfung, sich als notes_app an pg-notes (Namespace tag14) anzumelden. Ermittle aus den Logs des Primary die Client-IP und die Zahl der gescheiterten Anmeldungen, und schreibe beides (IP Anzahl) nach ~/pg/pruef/16.txt.

Uhr stoppen.

Lösungen

Teil 1 auf pg1 im Ordner ~/pruef, außer wo pg2 steht. Teil 2 auf cp1 im Ordner ~/pg/pruef mit KUBECONFIG=~/pg/pruef/.kc.

Lösung 1

psql -Atc 'SHOW server_version_num' > 01.txt
psql -Atc 'SHOW hba_file' >> 01.txt
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NULL' >> 01.txt
cat 01.txt

Erwartet:

180007
/etc/postgresql/18/main/pg_hba.conf
8

server_version_num ist für Skripte gemacht: als Zahl vergleichbar (>= 180000). Die Regelzahl hängt von deinem Stand ab; pg_hba_file_rules liest die Datei frisch, wie sie beim nächsten Reload gelten würde (Tag 3).

Lösung 2

psql -d pruef <<'EOF'
CREATE ROLE pruef_leser NOLOGIN;
GRANT USAGE ON SCHEMA lager TO pruef_leser;
GRANT SELECT ON ALL TABLES IN SCHEMA lager TO pruef_leser;
ALTER DEFAULT PRIVILEGES FOR ROLE pruef_owner IN SCHEMA lager
  GRANT SELECT ON TABLES TO pruef_leser;
GRANT pruef_leser TO pruef_anna;
SET ROLE pruef_owner;
CREATE TABLE lager.lieferant (id int PRIMARY KEY, name text);
INSERT INTO lager.lieferant VALUES (1, 'Nordholz');
EOF
export PGPASSWORD=lab-anna-123
A="host=127.0.0.1 dbname=pruef user=pruef_anna"
psql "$A" -Atc 'SELECT count(*) FROM lager.artikel' \
  -c 'SELECT name FROM lager.lieferant'
psql "$A" -c "INSERT INTO lager.lieferant VALUES (2, 'x')"
unset PGPASSWORD

Erwartet (gekürzt):

CREATE ROLE
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
GRANT ROLE
SET
CREATE TABLE
INSERT 0 1
5000
Nordholz
ERROR:  permission denied for table lieferant

Drei Ebenen: USAGE aufs Schema, SELECT auf das Bestehende, Default Privileges für die anlegende Rolle auf das Künftige (Tag 2). CONNECT hat pruef_anna über PUBLIC.

Lösung 3

H=/etc/postgresql/18/main/pg_hba.conf
echo 'hostssl  pruef  pruef_anna  172.16.0.123/32  scram-sha-256' | sudo tee -a $H
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL' \
  -c 'SELECT pg_reload_conf()'

Erwartet:

hostssl  pruef  pruef_anna  172.16.0.123/32  scram-sha-256
0
t

Auf pg2:

export PGPASSWORD=lab-anna-123
C="host=172.16.0.182 dbname=pruef user=pruef_anna"
psql "$C sslmode=disable" -Atc 'SELECT 1'
psql "$C sslmode=verify-full" \
  -Atc 'SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'
unset PGPASSWORD

Erwartet:

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  no
pg_hba.conf entry for host "172.16.0.123", user "pruef_anna", database "pruef", no
encryption
t|TLSv1.3

hostssl passt nur auf TLS-Verbindungen. Die Zeile steht am Ende – davor gibt es keine Regel, die pruef_anna aus dem Labornetz trifft. verify-full klappt, weil ~/.postgresql/root.crt auf pg2 die Lab-CA aus Tag 3 enthält.

Lösung 4

psql -c "ALTER ROLE pruef_report IN DATABASE pruef SET statement_timeout = '2s'" \
  -c "ALTER ROLE pruef_report IN DATABASE pruef SET work_mem = '64MB'" \
  -c '\drds pruef_report'
export PGPASSWORD=lab-report-123
psql "host=127.0.0.1 dbname=pruef user=pruef_report" -Atc 'SHOW work_mem' \
  -c 'SELECT pg_sleep(3)'
psql "host=127.0.0.1 dbname=postgres user=pruef_report" -Atc 'SHOW work_mem' \
  -c 'SHOW statement_timeout'
unset PGPASSWORD

Erwartet:

ALTER ROLE
ALTER ROLE
                List of settings
     Role     | Database |       Settings
--------------+----------+----------------------
 pruef_report | pruef    | statement_timeout=2s+
              |          | work_mem=64MB
(1 row)

64MB
ERROR:  canceling statement due to statement timeout
4MB
0

Die Einstellung greift bei jeder neuen Anmeldung – ein Reload ist nicht nötig. Ein nacktes ALTER ROLE … SET gälte in allen Datenbanken: verfehlt.

Lösung 5

Das UPDATE im Hintergrund, dann die Sperrtabelle:

psql -d pruef -c 'UPDATE konto SET saldo = saldo + 1 WHERE id = 1' &
sleep 2
psql -d pruef -c "SELECT a.pid, a.application_name, a.state, l.mode, l.granted
  FROM pg_locks l JOIN pg_stat_activity a USING (pid)
  WHERE l.relation = 'konto'::regclass ORDER BY l.granted DESC"

Erwartet:

  pid  | application_name |        state        |       mode       | granted
-------+------------------+---------------------+------------------+---------
 36925 | inventur         | idle in transaction | ShareLock        | t
 37157 | psql             | active              | RowExclusiveLock | f
(2 rows)

SHARE lässt Leser zu, aber keine Schreiber (RowExclusiveLock). Namen sichern und gezielt beenden:

psql -d pruef -Atc "SELECT a.application_name FROM pg_locks l
  JOIN pg_stat_activity a USING (pid) WHERE l.relation = 'konto'::regclass
  AND l.granted AND l.mode = 'ShareLock'" > 05.txt
psql -d pruef -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE application_name = 'inventur'"
wait
cat 05.txt

Erwartet:

t
UPDATE 1
inventur

Lösung 6

psql -d pruef -c "CREATE INDEX CONCURRENTLY ix_offen_email
  ON bestellung (lower(email), angelegt) WHERE status = 'offen'"
psql -d pruef -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF)
  SELECT id, angelegt FROM bestellung
  WHERE status = 'offen' AND lower(email) = '[email protected]'
  ORDER BY angelegt DESC LIMIT 5"
psql -d pruef -c "SELECT pg_size_pretty(pg_relation_size('ix_offen_email')),
  indisvalid FROM pg_index WHERE indexrelid = 'ix_offen_email'::regclass"

Erwartet (gekürzt):

CREATE INDEX
 Limit (actual rows=5.00 loops=1)
   ->  Index Scan Backward using ix_offen_email on bestellung (actual rows=5.00 ...
         Index Cond: (lower(email) = '[email protected]'::text)
         Index Searches: 1
 Execution Time: 0.322 ms
 pg_size_pretty | indisvalid
----------------+------------
 984 kB         | t

Drei Techniken aus Tag 5 in einem Index: Ausdruck (lower(email)), Sortierspalte dahinter (kein Sort, rückwärts gelesen) und partiell (WHERE status = 'offen', nur 2 % der Zeilen, 984 kB). Vorher: Parallel Seq Scan mit top-N-Sort, rund 95 ms. CONCURRENTLY und danach indisvalid = t prüfen gehören dazu.

Lösung 7

pg_repack ist seit Tag 6 auf pg1 installiert. Erst die Tabellenparameter, dann neu schreiben – dabei gilt der neue fillfactor schon:

psql -d pruef -Atc "SELECT pg_size_pretty(pg_table_size('protokoll'))"
psql -d pruef -c "ALTER TABLE protokoll SET (fillfactor = 90,
  autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0.02)" \
  -c 'CREATE EXTENSION pg_repack'
pg_repack -d pruef -t protokoll
psql -d pruef -Atc "SELECT pg_size_pretty(pg_table_size('protokoll'))" \
  -c "SELECT unnest(reloptions) FROM pg_class WHERE relname = 'protokoll'"

Erwartet:

92 MB
ALTER TABLE
CREATE EXTENSION
INFO: repacking table "public.protokoll"
25 MB
fillfactor=90
autovacuum_enabled=on
autovacuum_vacuum_scale_factor=0.02

VACUUM FULL schrumpft genauso, hält aber die ganze Zeit ein AccessExclusiveLock: verfehlt. pg_repack sperrt nur kurz am Anfang und Ende.

Lösung 8

Auf pg2:

cd ~/pruef
export PGPASSWORD=lab-anna-123
pg_dump "host=172.16.0.182 dbname=pruef user=pruef_anna sslmode=verify-full" \
  -n lager -Fc -f 08-lager.dump; echo rc=$?
unset PGPASSWORD
pg_restore -l 08-lager.dump | grep -E 'SCHEMA|TABLE DATA'
sudo -u postgres createdb pruef_kopie
sudo -u postgres pg_restore -d pruef_kopie --no-owner --no-acl < 08-lager.dump
echo rc=$?
sudo -u postgres psql -d pruef_kopie -Atc "SELECT (SELECT count(*) FROM lager.artikel),
  (SELECT count(*) FROM lager.lieferant)" -c '\dn lager'

Erwartet (gekürzt):

rc=0
7; 2615 25565 SCHEMA - lager pruef_owner
3482; 0 0 ACL - SCHEMA lager pruef_owner
3473; 0 25566 TABLE DATA lager artikel pruef_owner
3474; 0 25607 TABLE DATA lager lieferant pruef_owner
rc=0
5000|1
lager|postgres

pg_dump -n nimmt den SCHEMA-Eintrag mit, also legt der Restore ohne Filter das Schema an (anders als pg_restore -n, Tag 8). --no-owner --no-acl, weil es die Rollen auf pg2 nicht gibt. pruef_anna reicht für den Dump, weil sie dank Aufgabe 2 alles in lager lesen darf.

Lösung 9

Auf pg1 Rolle und Zugang:

psql -c "CREATE ROLE pruef_backup LOGIN REPLICATION PASSWORD 'lab-pruef-backup'"
echo 'host  replication  pruef_backup  172.16.0.123/32  scram-sha-256' \
  | sudo tee -a /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'

Auf pg2:

echo '172.16.0.182:5432:replication:pruef_backup:lab-pruef-backup' \
  | sudo -u postgres tee /var/lib/postgresql/.pgpass >/dev/null
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres pg_basebackup -h 172.16.0.182 -U pruef_backup \
  -D /var/lib/postgresql/pruef-09 -Ft -z -X stream -c fast
sudo -u postgres ls -lh /var/lib/postgresql/pruef-09
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup -n \
  /var/lib/postgresql/pruef-09
sudo -u postgres tar -xzOf /var/lib/postgresql/pruef-09/base.tar.gz backup_label \
  | grep 'START WAL' | grep -o 'file [0-9A-F]*' | cut -d' ' -f2 > ~/pruef/09.txt
cat ~/pruef/09.txt

Erwartet (gekürzt):

-rw------- 1 postgres postgres 268K Nov 19 07:36 backup_manifest
-rw------- 1 postgres postgres  23M Nov 19 07:36 base.tar.gz
-rw------- 1 postgres postgres  19K Nov 19 07:36 pg_wal.tar.gz
backup successfully verified
000000030000000200000048

Tar-Backups prüft pg_verifybackup nur mit -n (ohne WAL-Analyse, Tag 9). Das Startsegment steht in backup_label innerhalb von base.tar.gz – tar -O liest es, ohne auszupacken.

Lösung 10

Die Rolle aus Aufgabe 9 darf auch streamen; die .pgpass-Zeile (Datenbank replication) passt ebenfalls. Auf pg2:

sudo pg_createcluster 18 pruef >/dev/null
sudo -u postgres rm -rf /var/lib/postgresql/18/pruef
sudo -u postgres pg_basebackup -h 172.16.0.182 -U pruef_backup \
  -D /var/lib/postgresql/18/pruef -X stream -c fast -C -S pruef_pg2 -R
sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/pruef/postgresql.auto.conf
echo "recovery_min_apply_delay = '5min'" \
  | sudo -u postgres tee /etc/postgresql/18/pruef/conf.d/verzoegert.conf
sudo pg_ctlcluster 18 pruef start
pg_lsclusters | grep pruef

Erwartet (gekürzt):

recovery_min_apply_delay = '5min'
18  pruef   5433 online postgres /var/lib/postgresql/18/pruef ...

Auf pg1:

psql -d pruef -c 'CREATE TABLE verzoegert (t timestamptz DEFAULT now())' \
  -c 'INSERT INTO verzoegert DEFAULT VALUES'
psql -c 'SELECT application_name, state, sent_lsn, replay_lsn FROM pg_stat_replication'

Erwartet:

CREATE TABLE
INSERT 0 1
 application_name |   state   |  sent_lsn  | replay_lsn
------------------+-----------+------------+------------
 18/pruef         | streaming | 2/4B400000 | 2/4B01B0D8
(1 row)

Auf pg2:

sudo -u postgres psql -p 5433 -Atc 'SHOW recovery_min_apply_delay' \
  -c 'SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()'
sudo -u postgres psql -p 5433 -d pruef -c 'SELECT count(*) FROM verzoegert'

Erwartet:

5min
2/4C6FE000|2/4B01B0D8
ERROR:  relation "verzoegert" does not exist
LINE 1: SELECT count(*) FROM verzoegert
                             ^

Empfangen bis 2/4C6FE000, eingespielt nur bis 2/4B01B0D8: Das WAL ist da, die Commits warten. Nach gut fünf Minuten liefert dieselbe Abfrage 1. Mit pg_wal_replay_pause() hält man den Standby im Ernstfall an, bevor das DROP eingespielt ist.

Lösung 11

cat > 11-cluster.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pruef-db
  namespace: pruef
spec:
  instances: 2
  imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
  storage:
    size: 512Mi
  resources:
    requests: {cpu: 50m, memory: 128Mi}
    limits: {memory: 256Mi}
  affinity:
    enablePodAntiAffinity: true
    podAntiAffinityType: required
    topologyKey: kubernetes.io/hostname
  postgresql:
    parameters:
      max_connections: "50"
  bootstrap:
    initdb:
      database: lager
      owner: lager
EOF
kubectl apply -f 11-cluster.yaml
kubectl wait --for=condition=Ready cluster/pruef-db -n pruef --timeout=300s
kubectl get pods -n pruef -l cnpg.io/cluster=pruef-db -o wide \
  -L cnpg.io/instanceRole | awk '{print $1, $3, $7, $NF}'
kubectl cnpg psql pruef-db -n pruef -- -Atc 'SHOW max_connections'

Erwartet:

cluster.postgresql.cnpg.io/pruef-db created
cluster.postgresql.cnpg.io/pruef-db condition met
NAME STATUS NODE INSTANCEROLE
pruef-db-1 Running worker1 primary
pruef-db-2 Running worker2 replica
50

Standard ist Anti-Affinity preferred – dann dürften beide Instanzen auf einem Node landen, wenn der andere voll ist. required verbietet das (Tag 16, Enterprise-Notiz). Mit nur zwei Workern passen so höchstens zwei Instanzen.

Lösung 12

kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'SET ROLE lager' \
  -c 'CREATE TABLE bestand AS SELECT i AS id, i * 3 AS menge
  FROM generate_series(1, 300) i'
kubectl create secret generic pruef-leser-pw -n pruef --type=kubernetes.io/basic-auth \
  --from-literal=username=pruef_leser --from-literal=password=lab-leser-123
cat > 12-rolle.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: DatabaseRole
metadata:
  name: pruef-leser
  namespace: pruef
spec:
  cluster:
    name: pruef-db
  name: pruef_leser
  login: true
  connectionLimit: 5
  databaseRoleReclaimPolicy: delete
  passwordSecret:
    name: pruef-leser-pw
EOF
kubectl apply -f 12-rolle.yaml

Etwa 40 s warten, bis der Operator das Secret in die Role der Instanzen eingetragen hat (Tag 17, Drill 5), dann:

kubectl get databaserole -n pruef
kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'SET ROLE lager' \
  -c 'GRANT SELECT ON bestand TO pruef_leser'
kubectl exec -n pruef pruef-db-1 -c postgres -- env PGPASSWORD=lab-leser-123 \
  psql "host=pruef-db-ro user=pruef_leser dbname=lager sslmode=require" \
  -Atc 'SELECT count(*), pg_is_in_recovery() FROM bestand'
kubectl cnpg psql pruef-db -n pruef -- -c '\du pruef_leser'

Erwartet (gekürzt):

NAME          AGE   CLUSTER    PG NAME       APPLIED   MESSAGE
pruef-leser   33s   pruef-db   pruef_leser   true
SET
GRANT
300|t
        List of roles
  Role name  |  Attributes
-------------+---------------
 pruef_leser | 5 connections

SET ROLE lager sorgt dafür, dass die Tabelle der Owner-Rolle gehört; den GRANT gibt der Owner. pg_is_in_recovery() = t ist der Nachweis für die Replica.

Lösung 13

kubectl create secret generic s3-creds -n pruef \
  --from-literal=ACCESS_KEY_ID=pglab --from-literal=ACCESS_SECRET_KEY=lab-s3-secret-123
cat > 13-objectstore.yaml <<'EOF'
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
  name: pruef-s3
  namespace: pruef
spec:
  retentionPolicy: "1d"
  configuration:
    destinationPath: s3://cnpg-backups/
    endpointURL: http://172.16.0.123:9000
    s3Credentials:
      accessKeyId: {name: s3-creds, key: ACCESS_KEY_ID}
      secretAccessKey: {name: s3-creds, key: ACCESS_SECRET_KEY}
    wal:
      compression: gzip
    data:
      compression: gzip
  instanceSidecarConfiguration:
    resources:
      requests: {cpu: 10m, memory: 64Mi}
      limits: {memory: 256Mi}
EOF
kubectl apply -f 13-objectstore.yaml
kubectl patch cluster pruef-db -n pruef --type merge -p '{"spec":{"plugins":[
  {"name":"barman-cloud.cloudnative-pg.io","isWALArchiver":true,
   "parameters":{"barmanObjectName":"pruef-s3"}}]}}'

Der Operator rollt beide Instanzen neu aus (im Labor rund 40 s). Dann:

kubectl cnpg status pruef-db -n pruef | grep -E 'Working WAL|Primary instance'
kubectl cnpg backup pruef-db -n pruef --backup-name pruef-13 --backup-target primary \
  --method plugin --plugin-name barman-cloud.cloudnative-pg.io

Nach etwa 20 s:

kubectl get backup pruef-13 -n pruef -o jsonpath='{.status.backupId}' > 13.txt
kubectl get backup pruef-13 -n pruef -o custom-columns=PHASE:.status.phase,\
POD:.status.instanceID.podName,ID:.status.backupId

Erwartet:

Primary instance:        pruef-db-1
Working WAL archiving:          OK
backup/pruef-13 created
PHASE       POD          ID
completed   pruef-db-1   20261119T073958

Ohne serverName legt das Plugin alles unter dem Clusternamen ab (pruef-db/) – pg-notes/ bleibt unberührt. --backup-target primary überstimmt den Standard prefer-standby (Tag 15).

Lösung 14

Bei targetImmediate (Ende des Backups, sobald konsistent) braucht CNPG die backupID (Tag 15, Challenge):

kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'DROP TABLE bestand'
cat > 14-klon.yaml <<EOF
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: pruef-klon
  namespace: pruef
spec:
  instances: 1
  imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
  storage:
    size: 512Mi
  resources:
    requests: {cpu: 50m, memory: 128Mi}
    limits: {memory: 256Mi}
  bootstrap:
    recovery:
      source: pruef-backup
      recoveryTarget:
        targetImmediate: true
        backupID: $(cat 13.txt)
  externalClusters:
  - name: pruef-backup
    plugin:
      name: barman-cloud.cloudnative-pg.io
      parameters:
        barmanObjectName: pruef-s3
        serverName: pruef-db
EOF
grep -A2 recoveryTarget 14-klon.yaml
kubectl apply -f 14-klon.yaml

Erwartet:

DROP TABLE
      recoveryTarget:
        targetImmediate: true
        backupID: 20261119T073958
cluster.postgresql.cnpg.io/pruef-klon created

Der Heredoc ohne Anführungszeichen (<<EOF) setzt die ID aus 13.txt ein. Nach etwa 80 s:

kubectl get cluster -n pruef
kubectl cnpg psql pruef-klon -n pruef -- -d lager \
  -Atc 'SELECT count(*), sum(menge) FROM bestand'

Erwartet (gekürzt):

NAME         AGE     INSTANCES   READY   STATUS                     PRIMARY
pruef-db     4m48s   2           2       Cluster in healthy state   pruef-db-1
pruef-klon   78s     1           1       Cluster in healthy state   pruef-klon-1
300|135450

Ein targetTime hätte es auch getan – aber nur, wenn die Zeit sicher zwischen Backup-Ende und DROP liegt. targetImmediate ist genau „Stand des Backups“.

Lösung 15

cat > 15-pooler.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: pruef-db-lesen
  namespace: pruef
spec:
  cluster:
    name: pruef-db
  instances: 1
  type: ro
  pgbouncer:
    poolMode: session
    parameters:
      max_client_conn: "50"
  template:
    spec:
      containers:
      - name: pgbouncer
        resources:
          requests: {cpu: 10m, memory: 32Mi}
          limits: {memory: 64Mi}
EOF
kubectl apply -f 15-pooler.yaml

Nach einigen Sekunden (das Deployment entsteht erst durch den Operator):

kubectl rollout status deploy/pruef-db-lesen -n pruef
kubectl exec -n pruef deploy/pruef-db-lesen -c pgbouncer -- grep -E \
  '^\* =|^pool_mode|^max_client_conn' /controller/configs/pgbouncer.ini
PW=$(kubectl get secret pruef-db-app -n pruef -o jsonpath='{.data.password}' \
  | base64 -d)
kubectl exec -n pruef pruef-db-1 -c postgres -- env PGPASSWORD=$PW psql \
  "host=pruef-db-lesen user=lager dbname=lager sslmode=require" \
  -Atc 'SELECT pg_is_in_recovery(), inet_server_addr()'
kubectl get pods -n pruef -l cnpg.io/cluster=pruef-db,cnpg.io/podRole=instance \
  -o custom-columns=NAME:.metadata.name,IP:.status.podIP

Erwartet (gekürzt):

deployment "pruef-db-lesen" successfully rolled out
* = host=pruef-db-ro
pool_mode = session
max_client_conn = 50
t|10.0.2.186
NAME         IP
pruef-db-1   10.0.1.60
pruef-db-2   10.0.2.186

Direkt nach apply meldet kubectl rollout status noch NotFound – der Pooler ist kurz inactive, bis der Operator das Deployment anlegt.

Lösung 16

P=$(kubectl get cluster pg-notes -n tag14 -o jsonpath='{.status.currentPrimary}')
kubectl logs $P -n tag14 -c postgres --since=60m | jq -r 'select(
  .record.error_severity == "FATAL" and .record.user_name == "notes_app")
  | [.record.connection_from, .record.message] | @tsv' | head -2
kubectl logs $P -n tag14 -c postgres --since=60m | jq -r 'select(
  .record.error_severity == "FATAL" and .record.user_name == "notes_app")
  | .record.connection_from' | cut -d: -f1 | sort | uniq -c

Erwartet (gekürzt):

10.0.1.115:40826    password authentication failed for user "notes_app"
10.0.1.115:40838    password authentication failed for user "notes_app"
     14 10.0.1.115
echo '10.0.1.115 14' > 16.txt
kubectl get pod angreifer -n pruef -o jsonpath='{.status.podIP}'; echo

Erwartet: 10.0.1.115 – der Pod aus der Vorbereitung.

Sieben Versuche, aber 14 Logzeilen: psql probiert mit sslmode=prefer erst TLS, dann ohne (Tag 0, Drill 7). Gefragt war nach gescheiterten Anmeldungen im Log – 14 ist richtig, 7 beschreibt die Aufrufe des Clients. Wer nur --tail liest, verpasst die frühen Versuche.

Auswertung

Zähle die Gewichte der Aufgaben, die vollständig und nachweisbar gelöst sind – Teilpunkte gibt es nicht. Wer eine Vorgabe umgeht (Rechte an PUBLIC, VACUUM FULL statt online, CREATE INDEX ohne CONCURRENTLY, ALTER ROLE für alle Datenbanken), bekommt für die Aufgabe null.

Ergebnis Einschätzung
85–100 % sicher im Betrieb – klassisch wie auf Kubernetes
70–84 % das Handwerk sitzt; Schwachstellen gezielt wiederholen
unter 70 % die Tage zu den verfehlten Aufgaben wiederholen und neu starten
Aufgaben Bereich Tage
1–4 Zugang, Rollen, Konfiguration (22 %) 1–3
5–7 Sperren, Indizes, VACUUM (19 %) 4–6
8–10 Backup, Recovery, Replikation (20 %) 8–10
11–12 CNPG-Cluster und Rollen (12 %) 14, 16, 17
13–14 CNPG-Backup und Restore (14 %) 15
15–16 Pooler und Logs (13 %) 17, 18

Nicht abgefragt: logische Replikation (Tag 11), Major-Upgrade (Tag 13), Failover und Fencing (Tag 16), NetworkPolicies (Tag 18) – diese Tage zum Abschluss noch einmal durchgehen.

Aufräumen

Auf cp1 zuerst den Namespace, damit nichts mehr in den Bucket archiviert:

kubectl delete namespace pruef

Auf pg2: Standby, Backup, Kopie, Passwortdatei und die Objekte von pruef-db:

sudo pg_dropcluster --stop 18 pruef
sudo -u postgres rm -rf /var/lib/postgresql/.pgpass /var/lib/postgresql/pruef-09
sudo -u postgres dropdb --if-exists pruef_kopie
rm -rf ~/pruef
S3="--aws-sigv4 aws:amz:us-east-1:s3 --user pglab:lab-s3-secret-123"
U=http://172.16.0.123:9000/cnpg-backups
for k in $(curl -s $S3 "$U?list-type=2&prefix=pruef-db/" \
  | grep -oE '<Key>[^<]*</Key>' | sed -E 's/<[^>]+>//g'); do
  curl -s -o /dev/null $S3 -X DELETE "$U/$k"
done

Auf pg1: Inventur-Prozess, Slot des Standby, pg_hba.conf aus der Sicherung, Datenbank und Rollen. Die Rolleneinstellungen aus Aufgabe 4 verschwinden mit der Datenbank:

pkill -f 'sleep 3000'
B=/var/lib/postgresql/backup-pruef
psql -Atc "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots
  WHERE slot_name = 'pruef_pg2'"
sudo cp $B/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
dropdb pruef
psql -c 'DROP ROLE IF EXISTS pruef_anna, pruef_report, pruef_leser, pruef_backup,
  pruef_owner'

Erwartet: eine leere Zeile, t, DROP ROLE.

Kontrollpunkt

Auf pg1:

B=/var/lib/postgresql/backup-pruef
sudo diff $B/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf && echo IDENTISCH
sudo diff $B/postgresql.auto.conf /var/lib/postgresql/18/main/postgresql.auto.conf \
  && echo IDENTISCH

Erwartet: zweimal IDENTISCH.

psql -Atc 'SELECT count(*) FROM pg_replication_slots' \
  -c "SELECT count(*) FROM pg_roles WHERE rolname LIKE 'pruef%'" \
  -c 'SELECT count(*) FROM pg_db_role_setting'
psql -l | grep -c pruef

Erwartet: viermal 0.

sudo rm -r /var/lib/postgresql/backup-pruef
rm -rf ~/pruef

Auf pg2:

pg_lsclusters
S3="--aws-sigv4 aws:amz:us-east-1:s3 --user pglab:lab-s3-secret-123"
U=http://172.16.0.123:9000/cnpg-backups
curl -s $S3 "$U?list-type=2&prefix=pruef-db/" | grep -c '<Key>'

Erwartet: nur 18 main auf Port 5432, dann 0.

Auf cp1:

kubectl get ns | grep -c pruef
kubectl get cluster pg-notes -n tag14

Erwartet (nach etwa einer Minute): 0, pg-notes mit 3 Instanzen und Cluster in healthy state.

Damit ist das Trainingslager geschafft.