Skip to content
Hands-on IT IT Drills

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 pg1 and pg2 (database tag19), rounds 7 and 8 on cp1 in the namespace tag19. 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 PUBLIC gets 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-tag19

Expected:

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 &
done

Target 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';
EOF

Expected (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
done

Target 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_wal

Expected:


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-archive

Game 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 -3

Expected (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 -2

Expected:

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

On pg2:

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

Expected:

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 start

Game 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 INPUT

Expected (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 INPUT

Expected: -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-123

Next, 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 tag19

And 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 tag19

Target 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=1

Expected (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-12

Expected (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=1

Expected (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.ini

Expected:

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; echo

Expected (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.io

Target 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 .msg

Expected (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 tag19

Expected (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 tag19

On 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"
done

Expected (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 IDENTISCH

Expected: 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 INPUT

Expected: 0, 0 and only -P INPUT ACCEPT.

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

On 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 tag14

Expected (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 the pg_hba.conf line 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 an idle in transaction session; then set timeouts per role.
  • “pg_wal is filling up – what is the cause?” – An inactive replication slot or failing archiving (.ready files, pg_stat_archiver); never delete files in pg_wal by hand.

Enterprise Note

  • Alerts aimed exactly at the causes of this day: inactive slots with lag, failed_count and age of last_archived_time, INVALID indexes, the oldest open transaction, replication lag in bytes, rolvaliduntil within the next 14 days.
  • pg_hba.conf and conf.d from Git with review and an automatic pg_hba_file_rules check; 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.