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,\?,--helpundkubectl explain. Nicht erlaubt: dieses Buch während der Zeit. - Teil 1 läuft auf
pg1(Datenbankpruef, Dateien in~/pruef) undpg2(Dateien in~/pruef), Teil 2 aufcp1im Namespacepruef(Dateien in~/pg/pruef). - Rollen, die du anlegst, beginnen mit
pruef_, Kubernetes-Objekte mitpruef-. - 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;
EOFFü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 ~/pruefAuf 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.txtErwartet:
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 PGPASSWORDErwartet (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 PGPASSWORDErwartet:
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 PGPASSWORDErwartet:
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.txtErwartet:
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.txtErwartet (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 pruefErwartet (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.yamlEtwa 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.ioNach 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.backupIdErwartet:
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.yamlErwartet:
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.yamlNach 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.podIPErwartet (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 -cErwartet (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}'; echoErwartet: 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 pruefAuf 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"
doneAuf 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 IDENTISCHErwartet: 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 pruefErwartet: viermal 0.
sudo rm -r /var/lib/postgresql/backup-pruef
rm -rf ~/pruefAuf 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 tag14Erwartet (nach etwa einer Minute): 0,
pg-notes mit 3 Instanzen und
Cluster in healthy state.
Damit ist das Trainingslager geschafft.