Zum Inhalt springen
Trainingslager IT-Trainingslager

Tag 19: Wettkampf – kaputte Datenbanken reparieren

Ziel: Du findest und behebst in acht Runden zehn eingebaute Fehler quer durch das Buch – gegen die Uhr, mit Nachweis vorher und nachher. · Dauer: ca. 120 min · Voraussetzung: Tag 0–18

Briefing

  • Acht Runden, alle Bereiche: Zugang und Rechte, Sperren, Indizes, WAL und Slots, Archivierung, Replikation, CloudNativePG mit Pooler und Backup.
  • Runde 1 bis 6 laufen auf pg1 und pg2 (Datenbank tag19), Runde 7 und 8 auf cp1 im Namespace tag19. Die Runden bauen aufeinander auf: der Reihe nach spielen.
  • Werkzeuge: pg_stat_activity, pg_locks, pg_hba_file_rules, pg_stat_archiver, pg_replication_slots, pg_stat_replication, EXPLAIN, Serverlogs, kubectl cnpg status, Pod-Logs.
  • Jede Runde endet mit einem Beweis-Befehl: kaputt vorher, heil nachher.
Runde  Fehler                                   Werkzeug
1      App kommt nicht in ihre Datenbank        Serverlog, pg_hba_file_rules, \dn+
2      Alles hängt                              pg_stat_activity, pg_blocking_pids
3      Abfrage plötzlich langsam                pg_stat_statements, EXPLAIN, pg_index
4      pg_wal wächst und wächst                 pg_replication_slots, du
5      Kein WAL im Archiv                       pg_stat_archiver, archive_status
6      Standby fällt zurück                     pg_stat_wal_receiver, Log, iptables
7      Shop nach Passwort-Rotation kaputt       Pod-Logs, Secret, Pooler
8      Backups schlagen fehl                    cnpg status, Sidecar-Log

Spielregeln

  • Pro Runde: Einbau-Befehle der Spielleitung ausführen, dann Uhr starten. Die Befehle nicht studieren – im Betrieb siehst du den Fehler auch erst, wenn du suchst.
  • Ursache finden, beheben, mit einem Befehl beweisen. Erst dann Uhr stoppen.
  • Repariere die Ursache, nicht das Symptom: Wer eine Regel löscht, statt sie zu korrigieren, einen Dienst neu startet, der nicht kaputt ist, oder Rechte an PUBLIC verteilt, bekommt null Punkte.
  • Den Hinweis erst lesen, wenn das Zeitziel abgelaufen ist.

Auf pg1 liegen alle Dateien des Tages in ~/tag19. Bevor die Spielleitung etwas an der Konfiguration ändert, sichert sie die drei Dateien – damit vergleichst du am Ende:

mkdir -p ~/tag19 && cd ~/tag19
sudo -u postgres mkdir -p /var/lib/postgresql/backup-tag19
sudo -u postgres cp -a /etc/postgresql/18/main/pg_hba.conf \
  /etc/postgresql/18/main/postgresql.conf \
  /var/lib/postgresql/18/main/postgresql.auto.conf /var/lib/postgresql/backup-tag19/
sudo ls /var/lib/postgresql/backup-tag19

Erwartet:

pg_hba.conf
postgresql.auto.conf
postgresql.conf

Runde 1: Ausgesperrt

Lage: Die Bestell-App tag19_app verbindet sich von pg2 mit der Datenbank tag19 auf pg1 (TLS verify-full). Seit einer „kleinen Härtung“ durch einen Kollegen kommt sie nicht mehr an ihre Tabelle auftrag. Bring sie zurück, ohne anderen Rollen mehr zu erlauben als vorher.

Spielleitung auf pg1:

createdb tag19
psql -d tag19 <<'EOF'
CREATE ROLE tag19_app LOGIN PASSWORD 'lab-tag19-app';
CREATE TABLE auftrag (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kunde text NOT NULL, betrag numeric(10,2) NOT NULL);
INSERT INTO auftrag (kunde, betrag) VALUES ('Anna', 19.90), ('Ben', 42.00);
GRANT SELECT, INSERT ON auftrag TO tag19_app;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
EOF
H=/etc/postgresql/18/main/pg_hba.conf
echo 'host  tag19  tag19_app  172.16.0.123/32  scram-sha-256' | sudo tee -a $H
sudo sed -i '/^host  tag19  tag19_app/i host  tag19  all  172.16.0.0/24  ident' $H
psql -Atc 'SELECT pg_reload_conf()'

Auf pg2 steht ab jetzt die Verbindung der App bereit (Lab-Passwort):

export PGPASSWORD=lab-tag19-app
A="host=172.16.0.182 dbname=tag19 user=tag19_app sslmode=verify-full"

Zeitziel: 6 min

Hinweis: Es sind zwei Fehler auf zwei Ebenen. Der erste steht vollständig nur im Serverlog, der zweite versteckt sich hinter einer irreführenden Meldung.

Lösung

Auf pg2 das Symptom:

psql "$A" -c 'SELECT count(*) FROM auftrag'

Erwartet:

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:  Ident
authentication failed for user "tag19_app"

ident – die App nutzt doch ein Passwort. Auf pg1 nennt das Log die Zeile, die gegriffen hat:

sudo tail -3 /var/log/postgresql/postgresql-18-main.log
psql -c "SELECT line_number AS nr, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE database = '{tag19}'"

Erwartet (gekürzt):

... LOG:  could not connect to Ident server at address "172.16.0.123", port 113:
Connection refused
... FATAL:  Ident authentication failed for user "tag19_app"
... DETAIL:  Connection matched file "/etc/postgresql/18/main/pg_hba.conf" line 134:
"host  tag19  all  172.16.0.0/24  ident"
 nr  | database |  user_name  |   address    |  auth_method
-----+----------+-------------+--------------+---------------
 134 | {tag19}  | {all}       | 172.16.0.0   | ident
 135 | {tag19}  | {tag19_app} | 172.16.0.123 | scram-sha-256
(2 rows)

Die erste passende Zeile entscheidet (Tag 3). Zeile 134 trifft jede Rolle aus dem Labornetz, die richtige Zeile 135 kommt nie dran. ident fragt einen Ident-Dienst auf dem Client – den gibt es auf pg2 nicht. Die Zeile muss weg (oder auf eine Rolle beschränkt hinter die App-Zeile):

sudo sed -i '/^host  tag19  all  172.16.0.0\/24  ident$/d' $H
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL' \
  -c 'SELECT pg_reload_conf()'

Erwartet: 0 und t. Auf pg2 erneut:

psql "$A" -c 'SELECT count(*) FROM auftrag'
psql "$A" -c 'SELECT count(*) FROM public.auftrag'

Erwartet:

ERROR:  relation "auftrag" does not exist
LINE 1: SELECT count(*) FROM auftrag
                             ^
ERROR:  permission denied for schema public
LINE 1: SELECT count(*) FROM public.auftrag
                             ^

Die Tabelle existiert. Ein Schema ohne USAGE überspringt die Namenssuche, deshalb erst „does not exist“ (Tag 2). Auf pg1 der Blick aufs Schema:

psql -d tag19 -c '\dn+ public'
psql -d tag19 -c "SELECT has_schema_privilege('tag19_app', 'public', 'USAGE'),
  has_table_privilege('tag19_app', 'auftrag', 'SELECT')"

Erwartet (gekürzt):

  Name  |       Owner       |           Access privileges            | ...
--------+-------------------+----------------------------------------+ ...
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner | ...
 has_schema_privilege | has_table_privilege
----------------------+---------------------
 f                    | t

Der Eintrag =U/pg_database_owner für PUBLIC fehlt – das REVOKE ALL ON SCHEMA public FROM PUBLIC der Härtung. Das Tabellenrecht ist da, die Ebene darüber nicht. Gezielt nur der App zurückgeben, nicht wieder allen:

psql -d tag19 -c 'GRANT USAGE ON SCHEMA public TO tag19_app'

Beweis auf pg2:

psql "$A" -c "INSERT INTO auftrag (kunde, betrag) VALUES ('Cem', 7.50)" \
  -Atc 'SELECT count(*), current_user FROM auftrag'
psql "$A" -c 'CREATE TABLE x (i int)'

Erwartet:

INSERT 0 1
3|tag19_app
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE x (i int)
                     ^

Lesen und schreiben ja, Tabellen anlegen weiter nein – die Härtung bleibt für alle anderen bestehen.

Fortschritt speichern? Kostenlos registrieren

Runde 2: Stillstand

Lage: Seit ein paar Minuten hängt jede Anfrage der Bestell-App an auftrag, auch reine Lesezugriffe. Niemand hat etwas gelöscht, pg1 hat keine Last. Bring den Betrieb wieder in Gang und sorge dafür, dass derselbe Auslöser nicht wieder stundenlang alles blockiert.

Spielleitung auf pg1 in ~/tag19:

psql -d tag19 <<'EOF'
CREATE ROLE tag19_bericht LOGIN PASSWORD 'lab-tag19-bericht';
GRANT USAGE ON SCHEMA public TO tag19_bericht;
GRANT SELECT ON auftrag TO tag19_bericht;
EOF
cat > r2-bericht.sql <<'EOF'
BEGIN;
SELECT kunde, sum(betrag) FROM auftrag GROUP BY kunde;
\! sleep 1900
EOF
PGPASSWORD=lab-tag19-bericht PGAPPNAME=monatsbericht nohup psql -X -h 127.0.0.1 \
  -U tag19_bericht -d tag19 -f r2-bericht.sql >/dev/null 2>&1 &
sleep 1
PGAPPNAME=migration nohup psql -X -d tag19 \
  -c 'ALTER TABLE auftrag ADD COLUMN status text' >/dev/null 2>&1 &
sleep 1
for i in 1 2 3; do
  PGPASSWORD=lab-tag19-app PGAPPNAME=bestell-api nohup psql -X -h 127.0.0.1 \
    -U tag19_app -d tag19 \
    -c "INSERT INTO auftrag (kunde, betrag) VALUES ('Web $i', 9)" >/dev/null 2>&1 &
done

Zeitziel: 4 min

Hinweis: Wer ganz vorne in der Warteschlange steht, tut selbst gar nichts.

Lösung

Auf pg2 das Symptom, mit Zeitlimit statt endlosem Warten:

PGOPTIONS='-c statement_timeout=3s' psql "$A" -c 'SELECT count(*) FROM auftrag'

Erwartet: ERROR: canceling statement due to statement timeout

Auf pg1 die Kette:

psql -d tag19 -c "SELECT pid, usename, application_name AS app, state,
  wait_event_type AS wtype, pg_blocking_pids(pid) AS blockiert_von,
  left(query, 28) AS query FROM pg_stat_activity
  WHERE datname = 'tag19' AND pid <> pg_backend_pid() ORDER BY backend_start"

Erwartet (gekürzt):

  pid  |    usename    |      app      |        state        | wtype  | blockiert_von
-------+---------------+---------------+---------------------+--------+--------------
 35042 | tag19_bericht | monatsbericht | idle in transaction | Client | {}
 35047 | ubuntu        | migration     | active              | Lock   | {35042}
 35052 | tag19_app     | bestell-api   | active              | Lock   | {35047}
 35053 | tag19_app     | bestell-api   | active              | Lock   | {35047}
 35054 | tag19_app     | bestell-api   | active              | Lock   | {35047}

Das Muster aus Tag 4: Ein Bericht hält in einer offenen, untätigen Transaktion ein AccessShareLock, die Migration wartet auf ihr AccessExclusiveLock, und alle anderen – auch Leser – stehen hinter der Migration. Schuld ist nicht die Migration, sondern der Anfang der Kette. Den findet eine Abfrage, ohne PIDs abzutippen:

psql -d tag19 <<'EOF'
SELECT pid, usename, application_name AS app, now() - xact_start AS dauer
FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
  AND cardinality(pg_blocking_pids(pid)) = 0;
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
  AND cardinality(pg_blocking_pids(pid)) = 0;
ALTER ROLE tag19_bericht SET idle_in_transaction_session_timeout = '1min';
EOF

Erwartet (gekürzt):

  pid  |    usename    |      app      |      dauer
-------+---------------+---------------+-----------------
 35042 | tag19_bericht | monatsbericht | 00:00:22.319799
(1 row)

 pg_terminate_backend
----------------------
 t
(1 row)

ALTER ROLE

Beweis: keine wartende Sitzung mehr, die Migration ist durch, die drei Bestellungen sind da, der Timeout hängt an der Rolle:

psql -d tag19 -Atc "SELECT count(*) FROM pg_stat_activity
  WHERE datname = 'tag19' AND wait_event_type = 'Lock'"
psql -d tag19 -Atc 'SELECT count(*) FROM auftrag'
psql -d tag19 -Atc "SELECT column_name FROM information_schema.columns
  WHERE table_name = 'auftrag' AND column_name = 'status'"
psql -c '\drds tag19_bericht'

Erwartet (gekürzt):

0
6
status
     Role      | Database |                 Settings
---------------+----------+------------------------------------------
 tag19_bericht |          | idle_in_transaction_session_timeout=1min

Wer stattdessen die Migration abbricht, löst den Stau auch – aber die Migration fehlt, und der Bericht blockiert beim nächsten Versuch wieder. Null Punkte.

Fortschritt speichern? Kostenlos registrieren

Runde 3: Plötzlich langsam

Lage: Gestern lief die Kundenübersicht in wenigen Millisekunden, heute braucht jede Abfrage über 100 ms. Laut Ticket hat das Team „gestern Abend den Index angelegt“. Mach die Abfrage wieder schnell, ohne Schreibzugriffe zu sperren.

Spielleitung auf pg1 (etwa 20 s; die Fehlermeldung am Ende gehört dazu):

psql -d tag19 <<'EOF'
CREATE TABLE bestellung (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kunde_id int NOT NULL,
  angelegt timestamptz NOT NULL,
  betrag numeric(10,2) NOT NULL);
INSERT INTO bestellung (kunde_id, angelegt, betrag)
SELECT (random() * 49999)::int + 1, now() - i * interval '10 seconds',
  round((random() * 500)::numeric, 2)
FROM generate_series(1, 1500000) AS i;
VACUUM ANALYZE bestellung;
GRANT SELECT ON bestellung TO tag19_app;
SET statement_timeout = '200ms';
CREATE INDEX CONCURRENTLY ix_bestellung_kunde ON bestellung (kunde_id);
EOF
psql -d postgres -Atc 'SELECT pg_stat_statements_reset()'

Auf pg2 ein paar Aufrufe der App:

for k in 4711 815 1234 42 999; do
  psql "$A" -Atc "SELECT count(*), sum(betrag) FROM bestellung WHERE kunde_id = $k" \
    >/dev/null
done

Zeitziel: 4 min

Hinweis: „Angelegt“ heißt nicht „benutzbar“. \d verrät mehr als \di.

Lösung

Auf pg1: Welche Abfrage kostet, und was macht der Planer?

psql -d postgres -c "SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms,
  left(query, 50) AS query FROM pg_stat_statements
  WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'tag19')
  ORDER BY mean_exec_time DESC LIMIT 3"
psql -d tag19 -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*), sum(betrag) FROM bestellung WHERE kunde_id = 4711"

Erwartet (gekürzt):

 calls | mean_ms |                       query
-------+---------+----------------------------------------------------
     5 |   155.2 | SELECT count(*), sum(betrag) FROM bestellung WHERE
               ->  Parallel Seq Scan on bestellung (actual rows=10.33 loops=3)
                     Filter: (kunde_id = 4711)
                     Rows Removed by Filter: 499990
                     Buffers: shared hit=11030
 Execution Time: 158.993 ms

Seq Scan über 1,5 Millionen Zeilen, obwohl es angeblich einen Index gibt:

psql -d tag19 -c "SELECT indexrelid::regclass AS index, indisvalid,
  pg_size_pretty(pg_relation_size(indexrelid)) AS groesse
  FROM pg_index WHERE indrelid = 'bestellung'::regclass"

Erwartet:

        index        | indisvalid | groesse
---------------------+------------+---------
 bestellung_pkey     | t          | 32 MB
 ix_bestellung_kunde | f          | 0 bytes
(2 rows)

Ein INVALID-Index, 0 Bytes groß: Das CREATE INDEX CONCURRENTLY ist abgebrochen (hier am statement_timeout) und hat ein Wrack hinterlassen (Tag 5). Der Planer ignoriert es. Weil auch indisready noch f ist, pflegen es nicht einmal die Schreiber – bricht der Bau später ab, kostet ein solches Wrack bei jedem Schreiben. Neu bauen, ohne Schreibsperre:

psql -d tag19 -c '\timing on' -c 'REINDEX INDEX CONCURRENTLY ix_bestellung_kunde'
psql -d tag19 -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
  SELECT count(*), sum(betrag) FROM bestellung WHERE kunde_id = 4711"

Erwartet (gekürzt):

REINDEX
Time: 2424.904 ms (00:02.425)
   ->  Bitmap Heap Scan on bestellung (actual rows=31.00 loops=1)
         ->  Bitmap Index Scan on ix_bestellung_kunde (actual rows=31.00 loops=1)
               Index Cond: (kunde_id = 4711)
 Execution Time: 1.816 ms

Und aus Sicht der App auf pg2:

psql "$A" -c '\timing on' \
  -c 'SELECT count(*), sum(betrag) FROM bestellung WHERE kunde_id = 4711'

Erwartet (gekürzt):

 count |   sum
-------+---------
    31 | 8161.46
Time: 7.014 ms

Von 155 auf 7 ms (inklusive Netz und TLS). DROP INDEX CONCURRENTLY plus CREATE INDEX CONCURRENTLY ist gleichwertig – ein CREATE INDEX ohne CONCURRENTLY sperrt dagegen alle Schreiber: null Punkte.

Fortschritt speichern? Kostenlos registrieren

Runde 4: Die Platte füllt sich

Lage: Das Monitoring meldet: pg_wal auf pg1 ist größer als max_wal_size und wächst mit jeder Schreiblast weiter. Der alte Standby pg3 wurde letzte Woche abgebaut. Hol den Platz zurück und sorge dafür, dass so etwas künftig nicht die Platte füllen kann.

Spielleitung auf pg1 (etwa 50 s):

psql -Atc "SELECT pg_create_physical_replication_slot('standby_alt', true)"
for i in 1 2 3 4; do pgbench -i -s 30 -q tag19 >/dev/null 2>&1; done
psql -c 'CHECKPOINT'

Zeitziel: 5 min

Hinweis: Wer WAL festhält, steht in einer Sicht, die mit pg_replication_ beginnt.

Lösung

sudo du -sh /var/lib/postgresql/18/main/pg_wal
psql -Atc 'SHOW max_wal_size'
psql -c "SELECT slot_name, slot_type, active, wal_status,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS behalten
  FROM pg_replication_slots"

Erwartet:

1.7G    /var/lib/postgresql/18/main/pg_wal
1GB
  slot_name  | slot_type | active | wal_status | behalten
-------------+-----------+--------+------------+----------
 standby_alt | physical  | f      | extended   | 1710 MB
(1 row)

Ein inaktiver Slot hält 1,7 GB WAL fest; extended heißt: mehr als max_wal_size. Der Standby dazu existiert nicht mehr, also wartet der Slot ewig (Tag 10). Slot löschen, Checkpoint, messen:

psql -Atc "SELECT pg_drop_replication_slot('standby_alt')"
psql -c 'CHECKPOINT'
sudo du -sh /var/lib/postgresql/18/main/pg_wal

Erwartet:


CHECKPOINT
961M    /var/lib/postgresql/18/main/pg_wal

Die leere Zeile ist der Rückgabewert void von pg_drop_replication_slot. Die restlichen knapp 1 GB sind normal: PostgreSQL hält recycelte Segmente bis max_wal_size zur Wiederverwendung vor. Jetzt die Notbremse – max_slot_wal_keep_size hat Kontext sighup, ein Reload genügt:

echo "max_slot_wal_keep_size = '2GB'" \
  | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag19-slots.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -Atc 'SHOW max_slot_wal_keep_size'

Erwartet:

max_slot_wal_keep_size = '2GB'
t
2GB

Ab jetzt wird ein Slot, der mehr als 2 GB zurückhält, ungültig (wal_status = lost), statt den Primary stillzulegen. Wer nur CHECKPOINT oder VACUUM probiert, gewinnt nichts: Der Slot ist die Ursache.

Fortschritt speichern? Kostenlos registrieren

Runde 5: Leeres Archiv

Lage: Das Backup-Team meldet: Im WAL-Archiv auf pg2 (/var/lib/postgresql/wal-archive) ist seit der Umstellung gestern nichts angekommen. PITR ist damit unmöglich. Repariere die Archivierung, ohne pg1 neu zu starten, und weise nach, dass nichts verloren ist.

Spielleitung auf pg2:

sudo -u postgres mkdir -m 700 /var/lib/postgresql/wal-archive

Spielleitung auf pg1:

cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag19-archiv.conf
archive_mode = on
archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archiv/%f'
CONF
sudo systemctl restart postgresql@18-main
psql -Atc "SELECT pg_stat_reset_shared('archiver')"
psql -Atc 'SELECT pg_switch_wal()'
psql -d tag19 -c "INSERT INTO auftrag (kunde, betrag) VALUES ('Dora', 5)"
psql -Atc 'SELECT pg_switch_wal()'

Zeitziel: 5 min

Hinweis: Lies die Zeile vor „archive command failed“ im Log.

Lösung

Etwa eine Minute nach dem Einbau:

psql -x -c 'SELECT archived_count, last_archived_wal, failed_count, last_failed_wal
  FROM pg_stat_archiver'
sudo ls /var/lib/postgresql/18/main/pg_wal/archive_status
sudo grep -B2 'archive command failed' /var/log/postgresql/postgresql-18-main.log \
  | tail -3

Erwartet (gekürzt):

-[ RECORD 1 ]-----+-------------------------
archived_count    | 0
last_archived_wal |
failed_count      | 3
last_failed_wal   | 000000030000000200000024
000000030000000200000024.ready
000000030000000200000025.ready
rsync: [Receiver] change_dir#3 "/var/lib/postgresql/wal-archiv" failed: No such file
or directory (2)
rsync error: errors selecting input/output files, dirs (code 3) at main.c(847) ...
... LOG:  archive command failed with exit code 3

Kein Segment archiviert, zwei warten (.ready). rsync findet das Zielverzeichnis nicht: wal-archiv statt wal-archive. Die Ursache sitzt im Befehl, nicht auf pg2 – also den Befehl korrigieren. archive_command hat Kontext sighup:

F=/etc/postgresql/18/main/conf.d/tag19-archiv.conf
sudo sed -i 's/wal-archiv\//wal-archive\//' $F
grep archive_command $F
psql -Atc 'SELECT pg_reload_conf()'

Erwartet:

archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archive/%f'
t

Der Archiver versucht es nach dem Reload von selbst erneut, im Labor war er nach fünf Sekunden durch:

psql -x -c 'SELECT archived_count, last_archived_wal, failed_count
  FROM pg_stat_archiver'
sudo ls /var/lib/postgresql/18/main/pg_wal/archive_status | tail -2

Erwartet:

-[ RECORD 1 ]-----+-------------------------
archived_count    | 2
last_archived_wal | 000000030000000200000025
failed_count      | 3
000000030000000200000024.done
000000030000000200000025.done

Auf pg2:

sudo ls /var/lib/postgresql/wal-archive

Erwartet:

000000030000000200000024
000000030000000200000025

Lückenlos: Solange ein Segment .ready ist, recycelt PostgreSQL es nicht (Tag 9). Ein Neustart wäre unnötig gewesen; wer auf pg2 ein Verzeichnis wal-archiv anlegt, repariert das Symptom und hat zwei Archive – null Punkte.

Fortschritt speichern? Kostenlos registrieren

Runde 6: Der Standby fällt zurück

Lage: pg2 betreibt einen Lese-Standby 18/tag19 (Port 5433). Seit einem Netzwerk-Change am Vormittag sieht er keine neuen Daten mehr, und auf pg1 wächst der Slot. PostgreSQL läuft auf beiden Seiten. Bring den Standby wieder auf null Rückstand.

Spielleitung auf pg1 (Rolle und Zugang für den Standby):

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

Spielleitung auf pg2 (der Standby wie an Tag 10):

echo '172.16.0.182:5432:*:tag19_repl:lab-tag19-repl' \
  | sudo -u postgres tee /var/lib/postgresql/.pgpass
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo pg_createcluster 18 tag19 >/dev/null
sudo -u postgres rm -rf /var/lib/postgresql/18/tag19
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag19_repl \
  -D /var/lib/postgresql/18/tag19 -X stream -c fast -C -S tag19_pg2 -R
sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/tag19/postgresql.auto.conf
sudo pg_ctlcluster 18 tag19 start

Spielleitung auf pg1 (der „Change“ und etwas Schreiblast; die pgbench-Tabellen stammen aus Runde 4):

sudo iptables -I INPUT -p tcp -s 172.16.0.0/25 --dport 5432 \
  -j REJECT --reject-with tcp-reset
pgbench -c 4 -T 20 tag19 2>&1 | grep '^tps'

Zeitziel: 6 min

Hinweis: „Connection refused“ heißt nicht immer, dass niemand lauscht.

Lösung

Auf pg2 die Sicht des Standby:

sudo -u postgres psql -p 5433 -x -c 'SELECT pg_is_in_recovery(),
  pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()' \
  -c 'SELECT status, sender_host FROM pg_stat_wal_receiver'
sudo tail -3 /var/log/postgresql/postgresql-18-tag19.log
pg_isready -h 172.16.0.182; echo rc=$?

Erwartet (gekürzt):

-[ RECORD 1 ]-----------+-----------
pg_is_in_recovery       | t
pg_last_wal_receive_lsn | 2/28000060
pg_last_wal_replay_lsn  | 2/28000060
(0 rows)
... FATAL:  streaming replication receiver "18/tag19" could not connect to the primary
server: connection to server at "172.16.0.182", port 5432 failed: Connection refused
... LOG:  waiting for WAL to become available at 2/28000078
172.16.0.182:5432 - no response
rc=2

Kein WAL-Receiver, der Standby steht. Auf pg1 sieht es zunächst gesund aus:

psql -c 'SELECT application_name, state FROM pg_stat_replication'

Erwartet (direkt nach dem Einbau):

 application_name |   state
------------------+-----------
 18/tag19         | streaming
(1 row)

Der Primary merkt den Abbruch erst nach wal_sender_timeout (60 s) – so lange zeigt er einen Standby, der längst weg ist. Danach:

psql -Atc "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(
  pg_current_wal_lsn(), restart_lsn)) FROM pg_replication_slots"
sudo grep 'replication timeout' /var/log/postgresql/postgresql-18-main.log | tail -1
sudo ss -tlnp | grep 5432
sudo iptables -S INPUT

Erwartet (gekürzt):

tag19_pg2|f|reserved|154 MB
... tag19_repl@[unknown] LOG:  terminating walsender process due to replication
timeout
LISTEN 0      200          0.0.0.0:5432      0.0.0.0:*    users:(("postgres",...
LISTEN 0      200             [::]:5432         [::]:*    users:(("postgres",...
-P INPUT ACCEPT
-A INPUT -s 172.16.0.0/25 -p tcp -m tcp --dport 5432 -j REJECT --reject-with tcp-reset

PostgreSQL lauscht, aber eine Firewall-Regel beantwortet alles aus 172.16.0.0/25 – also auch pg2 (.123) – mit einem TCP-Reset. Für den Client sieht das aus wie ein toter Server. Der Slot hat dafür gesorgt, dass kein WAL fehlt. Regel entfernen:

sudo iptables -D INPUT -p tcp -s 172.16.0.0/25 --dport 5432 \
  -j REJECT --reject-with tcp-reset
sudo iptables -S INPUT

Erwartet: -P INPUT ACCEPT

Nach wenigen Sekunden (der Standby probiert es alle 5 s, wal_retrieve_retry_interval):

psql -c "SELECT application_name, state, pg_size_pretty(pg_wal_lsn_diff(
  pg_current_wal_lsn(), replay_lsn)) AS rueckstand FROM pg_stat_replication"
psql -c "SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(
  pg_current_wal_lsn(), restart_lsn)) AS behalten FROM pg_replication_slots"

Erwartet:

 application_name |   state   | rueckstand
------------------+-----------+------------
 18/tag19         | streaming | 0 bytes
(1 row)

 slot_name | active | behalten
-----------+--------+----------
 tag19_pg2 | t      | 0 bytes
(1 row)

Auf pg2:

sudo tail -1 /var/log/postgresql/postgresql-18-tag19.log
sudo -u postgres psql -p 5433 -d tag19 -Atc 'SELECT count(*) FROM auftrag'

Erwartet (gekürzt):

... LOG:  started streaming WAL from primary at 2/28000000 on timeline 3
7

Sieben Aufträge – inklusive Dora aus Runde 5. Wer den Standby neu aufbaut, statt die Regel zu finden, hat den Fehler noch im Netz: null Punkte.

Fortschritt speichern? Kostenlos registrieren

Runde 7: Shop nach der Rotation

Lage: Im Namespace tag19 auf ost läuft der Shop (shop-api, eine notes-api) gegen den CNPG-Cluster shop über den Pooler shop-pooler-rw. Heute früh hat der Rotations-Job das Datenbankpasswort erneuert und den Shop neu ausgerollt. Jetzt startet der neue Pod nicht, und der alte liefert beim Bestellen nur Fehler. Repariere den Shop so, dass Lesen und Schreiben gehen.

Spielleitung auf cp1 (etwa 2 min):

mkdir -p ~/pg/tag19 && cp ~/.kube/config ~/pg/tag19/.kc
export KUBECONFIG=~/pg/tag19/.kc
kubectl create namespace tag19
cat > ~/pg/tag19/shop.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: shop
  namespace: tag19
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}
  bootstrap:
    initdb:
      database: shop
      owner: shop
---
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
  name: shop-pooler-rw
  namespace: tag19
spec:
  cluster:
    name: shop
  instances: 1
  type: ro
  pgbouncer:
    poolMode: transaction
  template:
    spec:
      containers:
      - name: pgbouncer
        resources:
          requests: {cpu: 10m, memory: 32Mi}
          limits: {memory: 64Mi}
EOF
kubectl apply -f ~/pg/tag19/shop.yaml
kubectl wait --for=condition=Ready cluster/shop -n tag19 --timeout=300s
kubectl cnpg psql shop -n tag19 -- -d shop -c 'CREATE TABLE notes (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(), text text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now())' -c 'ALTER TABLE notes OWNER TO shop'
PW=$(kubectl get secret shop-app -n tag19 -o jsonpath='{.data.password}' | base64 -d)
kubectl create secret generic shop-db -n tag19 --from-literal=uri=\
"postgresql://shop:$PW@shop-pooler-rw:5432/shop?sslmode=require"
kubectl create secret generic shop-key -n tag19 --from-literal=key=lab-key-123

Weiter mit Shop und Client-Pod:

cat > ~/pg/tag19/shop-api.yaml <<'EOF'
apiVersion: apps/v1
kind: Deployment
metadata:
  name: shop-api
  namespace: tag19
spec:
  replicas: 1
  selector:
    matchLabels: {app: shop-api}
  template:
    metadata:
      labels: {app: shop-api}
    spec:
      containers:
      - name: api
        image: ghcr.io/thomaszachmann/notes-api:0.5.1
        ports:
        - {name: http, containerPort: 8080}
        env:
        - {name: NOTES_DB_URL_FILE, value: /etc/shop-db/uri}
        - {name: NOTES_API_KEY_FILE, value: /etc/shop-key/key}
        volumeMounts:
        - {name: db, mountPath: /etc/shop-db, readOnly: true}
        - {name: key, mountPath: /etc/shop-key, readOnly: true}
        readinessProbe:
          httpGet: {path: /healthz, port: http}
          periodSeconds: 5
        resources:
          requests: {cpu: 10m, memory: 16Mi}
          limits: {memory: 64Mi}
      volumes:
      - name: db
        secret: {secretName: shop-db}
      - name: key
        secret: {secretName: shop-key}
---
apiVersion: v1
kind: Service
metadata:
  name: shop-api
  namespace: tag19
spec:
  selector: {app: shop-api}
  ports:
  - {name: http, port: 80, targetPort: http}
EOF
kubectl apply -f ~/pg/tag19/shop-api.yaml
kubectl run curl -n tag19 --image=curlimages/curl:8.17.0 --restart=Never \
  --command -- sleep infinity
kubectl rollout status deploy/shop-api -n tag19
kubectl wait --for=condition=Ready pod/curl -n tag19

Und die „Rotation“:

kubectl patch secret shop-app -n tag19 --type merge \
  -p '{"stringData":{"password":"lab-shop-rotiert-789"}}'
sleep 15
kubectl rollout restart deploy/shop-api -n tag19

Zeitziel: 7 min

Hinweis: Zwei Fehler. Vergleiche Passwörter, ohne sie auszugeben – und lies, was der Pooler wirklich als Ziel eingetragen hat.

Lösung

Nach etwa 40 s:

kubectl get pods -n tag19 -l app=shop-api
kubectl logs -n tag19 -l app=shop-api --tail=1

Erwartet (gekürzt):

NAME                       READY   STATUS             RESTARTS     AGE
shop-api-b548ffbf4-562b6   0/1     CrashLoopBackOff   3 (3s ago)   41s
shop-api-d98c4d778-lpc7r   1/1     Running            0            74s
...
time=2026-11-19T07:23:37.816Z level=ERROR msg="database unreachable" source=file
err="failed to connect to `user=shop database=shop`: 10.96.108.61:5432
(shop-pooler-rw): failed SASL auth: FATAL: SASL authentication failed (SQLSTATE 08P01)"

Der neue Pod scheitert an der Anmeldung, der alte läuft dank Rolling Update weiter (mit einer längst offenen Verbindung). Passt das Passwort im Secret der App zu dem im Cluster-Secret? Nur Prüfsummen vergleichen, nie Passwörter ins Terminal:

kubectl get secret shop-db -n tag19 -o jsonpath='{.data.uri}' | base64 -d \
  | sed -E 's/^.*shop:([^@]+)@.*$/\1/' | sha256sum | cut -c1-12
kubectl get secret shop-app -n tag19 -o jsonpath='{.data.password}' | base64 -d \
  | sha256sum | cut -c1-12

Erwartet (deine Werte sind andere):

97f2e8938032
6e9380cf5cff

Verschieden: shop-db ist eine Kopie von gestern (Tag 18, Drill 2 – kopierte Secrets folgen keiner Rotation). CNPG hat das neue Passwort aus shop-app übernommen (das Secret trägt cnpg.io/reload: "true"). Aber Vorsicht beim Neubau: Die Schlüssel uri und pgpass in shop-app enthalten im Labor weiter das alte Passwort – der Patch hat nur password geändert. Die URI deshalb aus password bauen:

PW=$(kubectl get secret shop-app -n tag19 -o jsonpath='{.data.password}' | base64 -d)
kubectl create secret generic shop-db -n tag19 --dry-run=client -o yaml \
  --from-literal=uri="postgresql://shop:$PW@shop-pooler-rw:5432/shop?sslmode=require" \
  | kubectl apply -f -
kubectl rollout restart deploy/shop-api -n tag19
kubectl rollout status deploy/shop-api -n tag19
kubectl exec -n tag19 curl -- curl -s -X POST -H 'X-API-Key: lab-key-123' \
  -d '{"text":"Bestellung 2"}' http://shop-api/notes; echo
kubectl logs -n tag19 -l app=shop-api --tail=1

Erwartet (gekürzt):

Warning: resource secrets/shop-db is missing the kubectl.kubernetes.io/last-applied-...
secret/shop-db configured
deployment.apps/shop-api restarted
deployment "shop-api" successfully rolled out
{"error":"storage"}
time=... level=ERROR msg="add note" err="ERROR: cannot execute INSERT in a read-only
transaction (SQLSTATE 25006)"

Die Warnung ist harmlos (das Secret entstand per create). Der Shop startet, schreibt aber nicht: Er landet auf einer Replica. Der Pooler heißt -rw, aber was ist er?

kubectl get pooler shop-pooler-rw -n tag19
kubectl exec -n tag19 deploy/shop-pooler-rw -c pgbouncer -- grep '^\* =' \
  /controller/configs/pgbouncer.ini

Erwartet:

NAME             AGE    CLUSTER   TYPE   PHASE
shop-pooler-rw   4m9s   shop      ro     active
* = host=shop-ro

Der Name ist nur ein Name; type: ro hängt den Pooler an den Service shop-ro (Tag 17). Typ korrigieren – der Operator schreibt die Konfiguration neu, ohne den Pooler-Pod zu ersetzen:

kubectl patch pooler shop-pooler-rw -n tag19 --type merge -p '{"spec":{"type":"rw"}}'
sleep 15
kubectl exec -n tag19 deploy/shop-pooler-rw -c pgbouncer -- grep '^\* =' \
  /controller/configs/pgbouncer.ini
kubectl exec -n tag19 curl -- curl -s -X POST -H 'X-API-Key: lab-key-123' \
  -d '{"text":"Bestellung 3"}' http://shop-api/notes; echo
kubectl exec -n tag19 curl -- curl -s http://shop-api/notes; echo

Erwartet (gekürzt):

pooler.postgresql.cnpg.io/shop-pooler-rw patched
* = host=shop-rw
{"id":"b757ff21-a4b4-4190-af2a-0d17d98197a3","text":"Bestellung 3"}
[{"id":"b757ff21-a4b4-4190-af2a-0d17d98197a3","text":"Bestellung 3"}]

Lesen und schreiben. Wer das alte Passwort zurück ins Cluster-Secret schreibt, macht die Rotation rückgängig: null Punkte für diesen Teil.

Fortschritt speichern? Kostenlos registrieren

Runde 8: Backups ins Leere

Lage: Der Shop soll ins Backup. Die Plattform hat einen ObjectStore tag19-s3 auf den RustFS auf pg2 angelegt und am Cluster eingetragen. Seitdem meldet der Cluster eine kaputte WAL-Archivierung, und das erste Backup ist gescheitert. Bucket cnpg-backups und Zugangsdaten stimmen laut Plattform-Team. Repariere es und weise ein erfolgreiches Backup nach.

Spielleitung auf cp1 (der Neustart des Primary dauerte im Labor rund 3 min):

kubectl create secret generic s3-creds -n tag19 \
  --from-literal=ACCESS_KEY_ID=pglab \
  --from-literal=ACCESS_SECRET_KEY=lab-s3-secret-123
cat > ~/pg/tag19/objectstore.yaml <<'EOF'
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
  name: tag19-s3
  namespace: tag19
spec:
  retentionPolicy: "1d"
  configuration:
    destinationPath: s3://cnpg-backups/
    endpointURL: https://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
  instanceSidecarConfiguration:
    resources:
      requests: {cpu: 10m, memory: 64Mi}
      limits: {memory: 256Mi}
EOF
kubectl apply -f ~/pg/tag19/objectstore.yaml
kubectl patch cluster shop -n tag19 --type merge -p '{"spec":{"plugins":[
  {"name":"barman-cloud.cloudnative-pg.io","isWALArchiver":true,
   "parameters":{"barmanObjectName":"tag19-s3"}}]}}'
sleep 30
kubectl wait --for=condition=Ready cluster/shop -n tag19 --timeout=400s
kubectl cnpg backup shop -n tag19 --backup-name shop-test1 \
  --method plugin --plugin-name barman-cloud.cloudnative-pg.io

Zeitziel: 5 min

Hinweis: Das Sidecar plugin-barman-cloud loggt jeden Archivierungsversuch samt der URL, die es benutzt.

Lösung

kubectl get backup -n tag19
kubectl cnpg status shop -n tag19 | sed -n '/Continuous/,/^$/p'

Erwartet (gekürzt):

NAME         AGE   CLUSTER   METHOD   PHASE    ERROR
shop-test1   33s   shop      plugin   failed   rpc error: code = Unknown desc = exit ...
Continuous Backup status (Barman Cloud Plugin)
No recovery window information found in ObjectStore 'tag19-s3' for server 'shop'
Working WAL archiving:        Failing
WALs waiting to be archived:  1
Last Failed WAL:              000000010000000000000004   @   2026-11-19T07:28:52...

Die Fehlermeldung des Backups ist nichtssagend, das Sidecar-Log nicht:

kubectl logs shop-1 -n tag19 -c plugin-barman-cloud --since=5m \
  | grep 'WAL archiver exception' | tail -1 | jq -r .msg

Erwartet (gekürzt):

... ERROR: Barman cloud WAL archiver exception: SSL validation failed for
https://172.16.0.123:9000/cnpg-backups/shop/wals/0000000100000000/0000000100000000000
00004.gz [SSL: WRONG_VERSION_NUMBER] wrong version number (_ssl.c:1029)

WRONG_VERSION_NUMBER heißt: Der Client spricht TLS, der Server antwortet im Klartext. RustFS auf pg2 lauscht per http (Tag 15), der ObjectStore sagt https. Endpunkt korrigieren – die Sidecars lesen den ObjectStore neu, ohne Neustart:

kubectl get objectstore tag19-s3 -n tag19 \
  -o jsonpath='{.spec.configuration.endpointURL}{"\n"}'
kubectl patch objectstore tag19-s3 -n tag19 --type merge \
  -p '{"spec":{"configuration":{"endpointURL":"http://172.16.0.123:9000"}}}'

Nach etwa einer Minute:

kubectl cnpg status shop -n tag19 | grep -E 'Working WAL|Last Archived'
kubectl cnpg backup shop -n tag19 --backup-name shop-test2 \
  --method plugin --plugin-name barman-cloud.cloudnative-pg.io
kubectl get backup -n tag19

Erwartet (gekürzt, get backup nach etwa 20 s):

Working WAL archiving:          OK
Last Archived WAL:              000000010000000000000004   @   2026-11-19T07:30:56...
backup/shop-test2 created
NAME         AGE   CLUSTER   METHOD   PHASE       ERROR
shop-test1   2m    shop      plugin   failed      rpc error: code = Unknown desc = ...
shop-test2   14s   shop      plugin   completed

Auf pg2 liegen die Daten unter dem Präfix shop/:

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=shop/" | grep -oE '<Key>[^<]*</Key>' \
  | sed -E 's/<[^>]+>//g'

Erwartet (gekürzt):

shop/base/20261119T073102/backup.info
shop/base/20261119T073102/data.tar
shop/wals/0000000100000000/000000010000000000000004.gz

Ein Bucket cnpg-backups auf einem anderen Weg anzulegen oder TLS auf RustFS einzuschalten, ändert die Vorgabe statt den Fehler: null Punkte.

Fortschritt speichern? Kostenlos registrieren

Wertung

Runde Fehler Zeitziel Punkte
1 ident-Zeile vor der App-Zeile + USAGE auf public fehlt 6 min 3
2 idle in transaction vor einer Migration 4 min 2
3 INVALID-Index nach abgebrochenem CONCURRENTLY 4 min 2
4 verwaister Replikations-Slot hält WAL 5 min 2
5 Tippfehler im archive_command 5 min 2
6 Firewall-Regel trennt den Standby 6 min 2
7 kopiertes Secret nach Rotation + Pooler type: ro 7 min 3
8 ObjectStore mit https statt http 5 min 2

Volle Punkte nur innerhalb des Zeitziels, ohne Hinweis und mit Beweis-Befehl; sonst die Hälfte (abrunden). Wer statt der Ursache das Symptom beseitigt (Rechte an PUBLIC, Migration abbrechen, neues Archivverzeichnis, Standby neu aufbauen, altes Passwort zurück), bekommt für die Runde 0. Gesamtzeitziel: 42 min, Maximum: 18 Punkte.

Punkte Einschätzung
15–18 bereit für die Abschlussprüfung (Anhang B)
10–14 gut; die Tage der verpassten Runden wiederholen
0–9 Tag 2–5, 9, 10, 15 und 17 noch einmal durcharbeiten

Challenge

Die Bestell-App kommt von einem Moment auf den anderen nicht mehr in tag19. Am Passwort hat niemand gedreht, pg_hba.conf ist unverändert. Spielleitung auf pg1:

psql -c "ALTER ROLE tag19_app VALID UNTIL '2026-11-18 00:00+00'"

Auf pg2 sieht die App:

psql "$A" -Atc 'SELECT 1'

Erwartet:

psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "tag19_app"

Hinweis: Der Client erfährt aus Sicherheitsgründen nie den echten Grund.

Lösung

Auf pg1:

sudo grep -A1 'tag19_app' /var/log/postgresql/postgresql-18-main.log \
  | grep -E 'FATAL|DETAIL' | tail -2
psql -c 'SELECT rolname, rolvaliduntil FROM pg_roles WHERE rolvaliduntil < now()'
psql -c "ALTER ROLE tag19_app VALID UNTIL 'infinity'"

Erwartet (gekürzt):

... tag19_app@tag19 FATAL:  password authentication failed for user "tag19_app"
... tag19_app@tag19 DETAIL:  User "tag19_app" has an expired password.
  rolname  |     rolvaliduntil
-----------+------------------------
 tag19_app | 2026-11-18 00:00:00+00
(1 row)

ALTER ROLE

Danach meldet psql "$A" -Atc 'SELECT current_user' auf pg2 wieder tag19_app. VALID UNTIL gilt nur für das Passwort, nicht für die Rolle – Anmeldungen ohne Passwort (peer, cert) funktionieren weiter. Eine Abfrage auf rolvaliduntil gehört ins Monitoring.

Fortschritt speichern? Kostenlos registrieren

Aufräumen

Auf cp1 zuerst den Namespace – sonst archiviert der Shop weiter in den Bucket:

kubectl delete namespace tag19

Auf pg2 Standby, Archiv, Passwortdatei und die Objekte des Shops:

sudo pg_dropcluster --stop 18 tag19
sudo -u postgres rm -rf /var/lib/postgresql/.pgpass /var/lib/postgresql/wal-archive
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=shop/" | grep -oE '<Key>[^<]*</Key>' \
  | sed -E 's/<[^>]+>//g'); do
  curl -s -o /dev/null -w "%{http_code} $k\n" $S3 -X DELETE "$U/$k"
done

Erwartet (gekürzt): je Objekt eine Zeile mit 204.

Auf pg1: Bericht-Prozess aus Runde 2, Slot aus Runde 6, Konfiguration zurück aus der Sicherung (der Neustart schaltet archive_mode ab), Datenbank und Rollen weg:

pkill -f 'sleep 1900'
B=/var/lib/postgresql/backup-tag19
psql -Atc "SELECT pg_drop_replication_slot('tag19_pg2')"
sudo cp $B/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf
sudo rm /etc/postgresql/18/main/conf.d/tag19-archiv.conf \
  /etc/postgresql/18/main/conf.d/tag19-slots.conf
sudo systemctl restart postgresql@18-main
dropdb tag19
psql -c 'DROP ROLE tag19_app, tag19_bericht, tag19_repl'

Erwartet: eine leere Zeile, dann DROP ROLE. Die Sicherung in $B brauchst du noch für den Kontrollpunkt.

Kontrollpunkt

Auf pg1:

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

Erwartet: dreimal IDENTISCH.

ls /etc/postgresql/18/main/conf.d
psql -Atc 'SHOW archive_mode' -c 'SHOW max_slot_wal_keep_size'

Erwartet: keine Datei, dann off und -1.

psql -Atc 'SELECT count(*) FROM pg_replication_slots' \
  -c "SELECT count(*) FROM pg_roles WHERE rolname LIKE 'tag19%'"
sudo iptables -S INPUT

Erwartet: 0, 0 und nur -P INPUT ACCEPT.

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

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=shop/" | grep -c '<Key>'

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

Auf cp1:

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

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

Bleibt für die Abschlussprüfung: Operator, Plugin kubectl cnpg, Barman-Cloud-Plugin, RustFS auf pg2 mit Bucket cnpg-backups, Cluster pg-notes in tag14.

Interview-Notiz

  • „Ein Client kommt nicht rein – wie gehst du vor?“ – Meldung einer Schicht zuordnen (Netz, pg_hba.conf, Anmeldung, Rechte), dann das Serverlog: Es nennt die pg_hba.conf-Zeile und den echten Grund (falsche Methode, abgelaufenes Passwort).
  • „Die Datenbank hängt, aber hat keine Last – was ist los?“ – Fast immer eine Sperrkette: pg_blocking_pids() bis zum Anfang verfolgen, meist eine idle in transaction-Sitzung; danach Timeouts pro Rolle setzen.
  • „pg_wal läuft voll – woran liegt es?“ – Inaktiver Replikations-Slot oder scheiternde Archivierung (.ready-Dateien, pg_stat_archiver); nie Dateien in pg_wal von Hand löschen.

Enterprise-Notiz

  • Alarme genau auf die Ursachen dieses Tages: inaktive Slots mit Rückstand, failed_count und Alter von last_archived_time, INVALID-Indizes, älteste offene Transaktion, Replikationsverzug in Bytes, rolvaliduntil in den nächsten 14 Tagen.
  • pg_hba.conf und conf.d aus Git mit Review und automatischem pg_hba_file_rules-Check; Härtungen werden vorher gegen die Anwendungsrollen getestet.
  • Passwort-Rotation über einen Secret-Operator, der alle Konsumenten aktualisiert und die Pods neu startet – oder gleich dynamische Zugangsdaten.
  • Firewall-Changes mit Abhängigkeitsliste: Replikation, Archiv, Backup und Monitoring stehen darin, nicht nur die Anwendungen.
  • Solche Game Days regelmäßig mit der Bereitschaft üben – aus den Hinweisen dieses Tages werden Runbooks.