Day 19: Competition – Repairing Broken Databases
Goal: In eight rounds you find and fix ten planted faults from across the book – against the clock, with proof before and after. · Duration: approx. 120 min · Prerequisite: Days 0–18
Briefing
- Eight rounds, all areas: access and privileges, locks, indexes, WAL and slots, archiving, replication, CloudNativePG with Pooler and backup.
- Rounds 1 to 6 run on
pg1andpg2(databasetag19), rounds 7 and 8 oncp1in the namespacetag19. The rounds build on each other: play them in order. - Tools:
pg_stat_activity,pg_locks,pg_hba_file_rules,pg_stat_archiver,pg_replication_slots,pg_stat_replication,EXPLAIN, server logs,kubectl cnpg status, Pod logs. - Every round ends with a proof command: broken before, fixed after.
Round Fault Tool
1 app cannot get into its database server log, pg_hba_file_rules, \dn+
2 everything hangs pg_stat_activity, pg_blocking_pids
3 query suddenly slow pg_stat_statements, EXPLAIN, pg_index
4 pg_wal keeps growing pg_replication_slots, du
5 no WAL in the archive pg_stat_archiver, archive_status
6 standby falls behind pg_stat_wal_receiver, log, iptables
7 shop broken after password rotation Pod logs, Secret, Pooler
8 backups fail cnpg status, sidecar log
Rules of Play
- Per round: run the setup commands of the game master, then start the clock. Do not study the commands – in operations, too, you only see the fault once you go looking.
- Find the cause, fix it, prove it with one command. Only then stop the clock.
- Fix the cause, not the symptom: whoever deletes a rule instead of
correcting it, restarts a service that is not broken, or hands out
privileges to
PUBLICgets zero points. - Only read the Hint once the target time has run out.
On pg1, all files of the day live in
~/tag19. Before the game master changes anything in the
configuration, they back up the three files – you compare against them
at the end:
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-tag19Expected:
pg_hba.conf
postgresql.auto.conf
postgresql.conf
Round 1: Locked out
Situation: The ordering app tag19_app
connects from pg2 to the database tag19 on
pg1 (TLS verify-full). Since a “little
hardening” by a colleague, it can no longer get to its table
auftrag. Bring it back without allowing other roles more
than before.
Game master on 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()'On pg2, the app’s connection is ready from now on (lab password):
export PGPASSWORD=lab-tag19-app
A="host=172.16.0.182 dbname=tag19 user=tag19_app sslmode=verify-full"Target time: 6 min
Hint: There are two faults on two levels. The first one is only fully visible in the server log, the second hides behind a misleading message.
Solution
On pg2, the symptom:
psql "$A" -c 'SELECT count(*) FROM auftrag'Expected:
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL: Ident
authentication failed for user "tag19_app"
ident – but the app uses a password. On
pg1, the log names the line that matched:
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}'"Expected (abridged):
... 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)
The first matching line decides (Day 3). Line 134 matches every role
from the lab network, so the correct line 135 never gets its turn.
ident asks an Ident service on the client – and there is
none on pg2. The line has to go (or be restricted to one
role and moved behind the app line):
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()'Expected: 0 and t. On
pg2 again:
psql "$A" -c 'SELECT count(*) FROM auftrag'
psql "$A" -c 'SELECT count(*) FROM public.auftrag'Expected:
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
^
The table exists. A schema without USAGE is skipped by
the name lookup, hence “does not exist” first (Day 2). On
pg1, a look at the 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')"Expected (abridged):
Name | Owner | Access privileges | ...
--------+-------------------+----------------------------------------+ ...
public | pg_database_owner | pg_database_owner=UC/pg_database_owner | ...
has_schema_privilege | has_table_privilege
----------------------+---------------------
f | t
The entry =U/pg_database_owner for PUBLIC
is missing – the REVOKE ALL ON SCHEMA public FROM PUBLIC of
the hardening. The table privilege is there, the level above it is not.
Give it back to the app only, not to everyone again:
psql -d tag19 -c 'GRANT USAGE ON SCHEMA public TO tag19_app'Proof on 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)'Expected:
INSERT 0 1
3|tag19_app
ERROR: permission denied for schema public
LINE 1: CREATE TABLE x (i int)
^
Reading and writing yes, creating tables still no – the hardening stays in place for everyone else.
Save your progress? Sign up for free
Round 2: Standstill
Situation: For a few minutes now, every request of
the ordering app on auftrag has been hanging, even pure
reads. Nobody deleted anything, pg1 has no load. Get
operations going again and make sure that the same trigger cannot block
everything for hours again.
Game master on 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 &
doneTarget time: 4 min
Hint: Whoever stands at the very front of the queue is doing nothing at all.
Solution
On pg2, the symptom, with a time limit instead of endless waiting:
PGOPTIONS='-c statement_timeout=3s' psql "$A" -c 'SELECT count(*) FROM auftrag'Expected:
ERROR: canceling statement due to statement timeout
On pg1, the chain:
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"Expected (abridged):
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}
The pattern from Day 4: a report holds an
AccessShareLock in an open, idle transaction, the migration
waits for its AccessExclusiveLock, and everyone else –
readers too – queues behind the migration. The culprit is not the
migration, but the head of the chain. A query finds it without typing
PIDs by hand:
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';
EOFExpected (abridged):
pid | usename | app | dauer
-------+---------------+---------------+-----------------
35042 | tag19_bericht | monatsbericht | 00:00:22.319799
(1 row)
pg_terminate_backend
----------------------
t
(1 row)
ALTER ROLE
Proof: no waiting session left, the migration went through, the three orders are there, and the timeout is attached to the role:
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'Expected (abridged):
0
6
status
Role | Database | Settings
---------------+----------+------------------------------------------
tag19_bericht | | idle_in_transaction_session_timeout=1min
Whoever cancels the migration instead also clears the jam – but the migration is missing, and the report blocks again on the next attempt. Zero points.
Save your progress? Sign up for free
Round 3: Suddenly slow
Situation: Yesterday the customer overview ran in a few milliseconds, today every query takes more than 100 ms. According to the ticket, the team “created the index last night”. Make the query fast again without blocking writes.
Game master on pg1 (about 20 s; the error message at the
end is part of it):
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()'On pg2, a few calls from the 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
doneTarget time: 4 min
Hint: “Created” does not mean “usable”.
\d tells you more than \di.
Solution
On pg1: which query is expensive, and what does the
planner do?
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"Expected (abridged):
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
A Seq Scan over 1.5 million rows, although there is supposedly an index:
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"Expected:
index | indisvalid | groesse
---------------------+------------+---------
bestellung_pkey | t | 32 MB
ix_bestellung_kunde | f | 0 bytes
(2 rows)
An INVALID index, 0 bytes in size: the
CREATE INDEX CONCURRENTLY was aborted (here by the
statement_timeout) and left a wreck behind (Day 5). The
planner ignores it. Because indisready is still
f as well, not even the writers maintain it – if the build
aborts later, such a wreck costs on every write. Rebuild it without a
write lock:
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"Expected (abridged):
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
And from the app’s point of view on pg2:
psql "$A" -c '\timing on' \
-c 'SELECT count(*), sum(betrag) FROM bestellung WHERE kunde_id = 4711'Expected (abridged):
count | sum
-------+---------
31 | 8161.46
Time: 7.014 ms
From 155 down to 7 ms (including network and TLS).
DROP INDEX CONCURRENTLY plus
CREATE INDEX CONCURRENTLY is equivalent – a
CREATE INDEX without CONCURRENTLY, on the
other hand, blocks all writers: zero points.
Save your progress? Sign up for free
Round 4: The disk fills up
Situation: Monitoring reports: pg_wal
on pg1 is larger than max_wal_size and keeps
growing with every write load. The old standby pg3 was
decommissioned last week. Get the space back and make sure that
something like this can no longer fill the disk in the future.
Game master on pg1 (about 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'Target time: 5 min
Hint: Whatever holds on to WAL shows up in a view
that starts with pg_replication_.
Solution
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"Expected:
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)
An inactive slot is holding on to 1.7 GB of WAL;
extended means: more than max_wal_size. Its
standby no longer exists, so the slot waits forever (Day 10). Drop the
slot, checkpoint, measure:
psql -Atc "SELECT pg_drop_replication_slot('standby_alt')"
psql -c 'CHECKPOINT'
sudo du -sh /var/lib/postgresql/18/main/pg_walExpected:
CHECKPOINT
961M /var/lib/postgresql/18/main/pg_wal
The empty line is the return value void of
pg_drop_replication_slot. The remaining just under 1 GB is
normal: PostgreSQL keeps recycled segments up to
max_wal_size for reuse. Now the emergency brake –
max_slot_wal_keep_size has the context sighup,
so a reload is enough:
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'Expected:
max_slot_wal_keep_size = '2GB'
t
2GB
From now on, a slot that retains more than 2 GB becomes invalid
(wal_status = lost) instead of bringing the primary to a
halt. Whoever only tries CHECKPOINT or VACUUM
gains nothing: the slot is the cause.
Save your progress? Sign up for free
Round 5: Empty archive
Situation: The backup team reports: nothing has
arrived in the WAL archive on pg2
(/var/lib/postgresql/wal-archive) since yesterday’s
changeover. That makes PITR impossible. Repair the archiving without
restarting pg1, and prove that nothing is lost.
Game master on pg2:
sudo -u postgres mkdir -m 700 /var/lib/postgresql/wal-archiveGame master on 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()'Target time: 5 min
Hint: Read the line before “archive command failed” in the log.
Solution
About one minute after the setup:
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 -3Expected (abridged):
-[ 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
No segment archived, two waiting (.ready).
rsync cannot find the target directory:
wal-archiv instead of wal-archive. The cause
lies in the command, not on pg2 – so correct the command.
archive_command has the context 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()'Expected:
archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archive/%f'
t
After the reload, the archiver retries on its own; in the lab it was done after five seconds:
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 -2Expected:
-[ RECORD 1 ]-----+-------------------------
archived_count | 2
last_archived_wal | 000000030000000200000025
failed_count | 3
000000030000000200000024.done
000000030000000200000025.done
On pg2:
sudo ls /var/lib/postgresql/wal-archiveExpected:
000000030000000200000024
000000030000000200000025
No gaps: as long as a segment is .ready, PostgreSQL does
not recycle it (Day 9). A restart would have been unnecessary; whoever
creates a directory wal-archiv on pg2 repairs
the symptom and ends up with two archives – zero points.
Save your progress? Sign up for free
Round 6: The standby falls behind
Situation: pg2 runs a read-only standby
18/tag19 (port 5433). Since a network change this morning
it no longer sees new data, and on pg1 the slot keeps
growing. PostgreSQL is running on both sides. Bring the standby back to
zero lag.
Game master on pg1 (role and access for the
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()'Game master on pg2 (the standby as on Day 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 startGame master on pg1 (the “change” and some write load;
the pgbench tables come from round 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'Target time: 6 min
Hint: “Connection refused” does not always mean that nobody is listening.
Solution
On pg2, the standby’s view:
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=$?Expected (abridged):
-[ 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
No WAL receiver, the standby is stuck. On pg1, things
look healthy at first:
psql -c 'SELECT application_name, state FROM pg_stat_replication'Expected (right after the setup):
application_name | state
------------------+-----------
18/tag19 | streaming
(1 row)
The primary only notices the disconnect after
wal_sender_timeout (60 s) – until then it shows a standby
that is long gone. After that:
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 INPUTExpected (abridged):
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 is listening, but a firewall rule answers everything from
172.16.0.0/25 – including pg2 (.123) – with a
TCP reset. To the client this looks like a dead server. The slot made
sure that no WAL is missing. Remove the rule:
sudo iptables -D INPUT -p tcp -s 172.16.0.0/25 --dport 5432 \
-j REJECT --reject-with tcp-reset
sudo iptables -S INPUTExpected: -P INPUT ACCEPT
After a few seconds (the standby retries every 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"Expected:
application_name | state | rueckstand
------------------+-----------+------------
18/tag19 | streaming | 0 bytes
(1 row)
slot_name | active | behalten
-----------+--------+----------
tag19_pg2 | t | 0 bytes
(1 row)
On pg2:
sudo tail -1 /var/log/postgresql/postgresql-18-tag19.log
sudo -u postgres psql -p 5433 -d tag19 -Atc 'SELECT count(*) FROM auftrag'Expected (abridged):
... LOG: started streaming WAL from primary at 2/28000000 on timeline 3
7
Seven orders – including Dora from round 5. Whoever
rebuilds the standby instead of finding the rule still has the fault in
the network: zero points.
Save your progress? Sign up for free
Round 7: Shop after the rotation
Situation: In the namespace tag19 on
ost, the shop (shop-api, a notes-api) runs
against the CNPG cluster shop via the Pooler
shop-pooler-rw. Early this morning, the rotation job
renewed the database password and rolled out the shop again. Now the new
Pod does not start, and the old one returns only errors when ordering.
Repair the shop so that reading and writing work.
Game master on cp1 (about 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-123Next, the shop and the 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 tag19And the “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 tag19Target time: 7 min
Hint: Two faults. Compare passwords without printing them – and read what the Pooler has really configured as its target.
Solution
After about 40 s:
kubectl get pods -n tag19 -l app=shop-api
kubectl logs -n tag19 -l app=shop-api --tail=1Expected (abridged):
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)"
The new Pod fails to log in, the old one keeps running thanks to the rolling update (with a connection opened long ago). Does the password in the app’s Secret match the one in the cluster Secret? Compare checksums only, never put passwords in the 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-12Expected (your values will differ):
97f2e8938032
6e9380cf5cff
Different: shop-db is a copy from yesterday (Day 18,
Drill 2 – copied Secrets do not follow a rotation). CNPG picked up the
new password from shop-app (the Secret carries
cnpg.io/reload: "true"). But be careful when rebuilding: in
the lab, the keys uri and pgpass in
shop-app still contain the old password –
the patch only changed password. So build the URI from
password:
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=1Expected (abridged):
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)"
The warning is harmless (the Secret was made with
create). The shop starts, but does not write: it ends up on
a replica. The Pooler is called -rw, but what is it?
kubectl get pooler shop-pooler-rw -n tag19
kubectl exec -n tag19 deploy/shop-pooler-rw -c pgbouncer -- grep '^\* =' \
/controller/configs/pgbouncer.iniExpected:
NAME AGE CLUSTER TYPE PHASE
shop-pooler-rw 4m9s shop ro active
* = host=shop-ro
The name is just a name; type: ro attaches the Pooler to
the Service shop-ro (Day 17). Correct the type – the
operator rewrites the configuration without replacing the Pooler
Pod:
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; echoExpected (abridged):
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"}]
Reading and writing. Whoever writes the old password back into the cluster Secret undoes the rotation: zero points for this part.
Save your progress? Sign up for free
Round 8: Backups into the void
Situation: The shop is supposed to be backed up. The
platform team created an ObjectStore tag19-s3
pointing to RustFS on pg2 and registered it with the
cluster. Since then the cluster reports broken WAL archiving, and the
first backup failed. According to the platform team, the bucket
cnpg-backups and the credentials are correct. Repair it and
prove a successful backup.
Game master on cp1 (the restart of the primary took about 3 min in the lab):
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.ioTarget time: 5 min
Hint: The sidecar plugin-barman-cloud
logs every archiving attempt along with the URL it uses.
Solution
kubectl get backup -n tag19
kubectl cnpg status shop -n tag19 | sed -n '/Continuous/,/^$/p'Expected (abridged):
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...
The backup’s error message says nothing useful, the sidecar log does:
kubectl logs shop-1 -n tag19 -c plugin-barman-cloud --since=5m \
| grep 'WAL archiver exception' | tail -1 | jq -r .msgExpected (abridged):
... 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 means: the client speaks TLS, the
server answers in plain text. RustFS on pg2 listens via
http (Day 15), the ObjectStore says
https. Correct the endpoint – the sidecars reread the
ObjectStore without a restart:
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"}}}'After about one 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 tag19Expected (abridged, get backup after
about 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
On pg2, the data lives under the prefix
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'Expected (abridged):
shop/base/20261119T073102/backup.info
shop/base/20261119T073102/data.tar
shop/wals/0000000100000000/000000010000000000000004.gz
Creating a bucket cnpg-backups some other way or turning
on TLS on RustFS changes the requirement instead of the fault: zero
points.
Save your progress? Sign up for free
Scoring
| Round | Fault | Target time | Points |
|---|---|---|---|
| 1 | ident line before the app line + USAGE on
public missing |
6 min | 3 |
| 2 | idle in transaction in front of a migration |
4 min | 2 |
| 3 | INVALID index after an aborted
CONCURRENTLY |
4 min | 2 |
| 4 | orphaned replication slot holds WAL | 5 min | 2 |
| 5 | typo in the archive_command |
5 min | 2 |
| 6 | firewall rule cuts off the standby | 6 min | 2 |
| 7 | copied Secret after rotation + Pooler type: ro |
7 min | 3 |
| 8 | ObjectStore with https instead of
http |
5 min | 2 |
You only get full points within the target time, without a hint
and with a proof command; otherwise half (round down).
Whoever removes the symptom instead of the cause (privileges for
PUBLIC, cancelling the migration, a new archive directory,
rebuilding the standby, restoring the old password) gets 0 for the
round. Overall target time: 42 min, maximum: 18 points.
| Points | Assessment |
|---|---|
| 15–18 | ready for the final exam (Appendix B) |
| 10–14 | good; repeat the days of the rounds you missed |
| 0–9 | work through Days 2–5, 9, 10, 15 and 17 once more |
Challenge
From one moment to the next, the ordering app can no longer get into
tag19. Nobody touched the password,
pg_hba.conf is unchanged. Game master on
pg1:
psql -c "ALTER ROLE tag19_app VALID UNTIL '2026-11-18 00:00+00'"On pg2, the app sees:
psql "$A" -Atc 'SELECT 1'Expected:
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL:
password authentication failed for user "tag19_app"
Hint: For security reasons, the client never learns the real reason.
Solution
On 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'"Expected (abridged):
... 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
After that, psql "$A" -Atc 'SELECT current_user' on
pg2 reports tag19_app again.
VALID UNTIL applies only to the password, not to the role –
logins without a password (peer, cert) keep
working. A query on rolvaliduntil belongs in
monitoring.
Save your progress? Sign up for free
Cleanup
On cp1, the namespace first – otherwise the shop keeps archiving into the bucket:
kubectl delete namespace tag19On pg2, the standby, the archive, the password file and the shop’s objects:
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"
doneExpected (abridged): one line with 204
per object.
On pg1: the report process from round 2, the slot from
round 6, the configuration restored from the backup (the restart turns
archive_mode off), database and roles gone:
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'Expected: an empty line, then
DROP ROLE. You still need the backup in $B for
the Checkpoint.
Checkpoint
On 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 IDENTISCHExpected: IDENTISCH three times.
ls /etc/postgresql/18/main/conf.d
psql -Atc 'SHOW archive_mode' -c 'SHOW max_slot_wal_keep_size'Expected: no file, then off and
-1.
psql -Atc 'SELECT count(*) FROM pg_replication_slots' \
-c "SELECT count(*) FROM pg_roles WHERE rolname LIKE 'tag19%'"
sudo iptables -S INPUTExpected: 0, 0 and only
-P INPUT ACCEPT.
sudo rm -r /var/lib/postgresql/backup-tag19
rm -rf ~/tag19On 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>'Expected: only 18 main on port 5432,
then 0.
On cp1:
kubectl get ns | grep -c tag19
kubectl get cluster pg-notes -n tag14Expected (after about one minute): 0,
pg-notes with 3 instances and
Cluster in healthy state.
What remains for the final exam: the operator, the
kubectl cnpg plugin, the Barman Cloud Plugin, RustFS on
pg2 with the bucket cnpg-backups, and the
cluster pg-notes in tag14.
Interview Note
- “A client cannot get in – how do you proceed?” – Assign the message
to a layer (network,
pg_hba.conf, authentication, privileges), then check the server log: it names thepg_hba.confline and the real reason (wrong method, expired password). - “The database hangs but has no load – what is going on?” – Almost
always a lock chain: follow
pg_blocking_pids()to the head, usually anidle in transactionsession; then set timeouts per role. - “
pg_walis filling up – what is the cause?” – An inactive replication slot or failing archiving (.readyfiles,pg_stat_archiver); never delete files inpg_walby hand.
Enterprise Note
- Alerts aimed exactly at the causes of this day: inactive slots with
lag,
failed_countand age oflast_archived_time,INVALIDindexes, the oldest open transaction, replication lag in bytes,rolvaliduntilwithin the next 14 days. pg_hba.confandconf.dfrom Git with review and an automaticpg_hba_file_rulescheck; hardening changes are tested against the application roles beforehand.- Password rotation through a Secret operator that updates all consumers and restarts the Pods – or dynamic credentials right away.
- Firewall changes with a dependency list: replication, archive, backup and monitoring are on it, not just the applications.
- Practice game days like these regularly with the on-call team – the hints of this day turn into runbooks.