Appendix B: Final Exam
Goal: You solve 16 tasks from Part 1 and Part 2 in 120 minutes and grade yourself. · Duration: 120 min · Prerequisite: Days 0–19
Rules of Play
- Time: 120 minutes, set a timer. Each task has a weight in percent, 100 % in total: 61 % for classic operations (tasks 1–10), 39 % for CloudNativePG (tasks 11–16).
- There is no certificate. Your own yardstick: from 70 %, the craft is solid.
- Allowed:
postgresql.org/docs/18,cloudnative-pg.io/docs,\h,\?,--helpandkubectl explain. Not allowed: this book during the exam. - Part 1 runs on
pg1(databasepruef, files in~/pruef) andpg2(files in~/pruef), Part 2 oncp1in the namespacepruef(files in~/pg/pruef). - Roles you create start with
pruef_, Kubernetes objects withpruef-. - The tasks depend on each other where it says so. Otherwise the order is up to you.
- The solutions are at the end. Only read them once the time is up.
Preparation
On pg1: backup of the access configuration, database,
roles and test data (about 20 s):
mkdir -p ~/pruef && cd ~/pruef
sudo -u postgres mkdir -p /var/lib/postgresql/backup-pruef
sudo -u postgres cp -a /etc/postgresql/18/main/pg_hba.conf \
/var/lib/postgresql/18/main/postgresql.auto.conf /var/lib/postgresql/backup-pruef/
createdb pruef
psql -d pruef <<'EOF'
CREATE ROLE pruef_owner NOLOGIN;
CREATE ROLE pruef_anna LOGIN PASSWORD 'lab-anna-123';
CREATE ROLE pruef_report LOGIN PASSWORD 'lab-report-123';
CREATE SCHEMA lager AUTHORIZATION pruef_owner;
SET ROLE pruef_owner;
CREATE TABLE lager.artikel (id int PRIMARY KEY, name text NOT NULL, preis numeric);
INSERT INTO lager.artikel SELECT i, 'Artikel ' || i, i * 0.5
FROM generate_series(1, 5000) i;
RESET ROLE;
CREATE TABLE konto (id int PRIMARY KEY, saldo numeric NOT NULL);
INSERT INTO konto SELECT i, 100 FROM generate_series(1, 10) i;
CREATE TABLE bestellung (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL, status text NOT NULL, angelegt timestamptz NOT NULL);
INSERT INTO bestellung (email, status, angelegt)
SELECT 'Kunde' || (i % 20000) || '@Example.org',
CASE WHEN i % 50 = 0 THEN 'offen' ELSE 'erledigt' END,
now() - i * interval '1 minute'
FROM generate_series(1, 1000000) i;
CREATE TABLE protokoll (id int PRIMARY KEY, eintrag text)
WITH (autovacuum_enabled = off);
INSERT INTO protokoll SELECT i, repeat('p', 200) FROM generate_series(1, 400000) i;
DELETE FROM protokoll WHERE id % 4 <> 0;
VACUUM ANALYZE bestellung;
EOFFor task 5, an inventory that holds on to a table:
cat > inventur.sql <<'EOF'
BEGIN;
LOCK TABLE konto IN SHARE MODE;
\! sleep 3000
EOF
PGAPPNAME=inventur nohup psql -X -d pruef -f inventur.sql >/dev/null 2>&1 &On pg2:
mkdir -p ~/pruefOn cp1: a dedicated kubeconfig, the namespace and,
for task 16, a Pod that tries its luck on pg-notes:
mkdir -p ~/pg/pruef && cp ~/.kube/config ~/pg/pruef/.kc
export KUBECONFIG=~/pg/pruef/.kc
cd ~/pg/pruef
kubectl create namespace pruef
kubectl run -n pruef angreifer \
--image=ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie --restart=Never \
--env=PGPASSWORD=geraten --env=PGCONNECT_TIMEOUT=3 --command -- sh -c \
'for i in 1 2 3 4 5 6 7; do psql -h pg-notes-rw.tag14 -U notes_app -d notes \
-c "SELECT 1"; sleep 2; done; sleep infinity'Start the clock.
Task 1: Inventory (5 %)
On pg1. Write three lines to
~/pruef/01.txt: the server version as a number (such as
180007), the path of the active pg_hba.conf
and the number of error-free rules in it.
Task 2: Readers for today and tomorrow (6 %)
Database pruef. Create the group role
pruef_leser. It may read all tables in the schema
lager – including those that pruef_owner
creates in the future – but not change anything. pruef_anna
becomes a member. Prove it with a new table lager.lieferant
(created by pruef_owner).
Task 3: Encrypted only (6 %)
pruef_anna should be allowed to log in from
pg2 to pruef on pg1 – via SCRAM
and exclusively over TLS. A connection without TLS is rejected. All
other rules stay as they are.
Task 4: Taming the report role (5 %)
pruef_report gets a statement_timeout of 2
s and work_mem = 64MB in the database pruef –
and only there. In other databases the default values apply. No reload
and no restart.
Task 5: Who is holding konto? (6 %)
Database pruef. An
UPDATE konto SET saldo = saldo + 1 WHERE id = 1 hangs. Use
the lock table to find out which application
(application_name) is holding the table, write the name to
~/pruef/05.txt and terminate only that session. The
UPDATE must then go through.
Task 6: A customer’s open orders (7 %)
Database pruef. This query should get by without a Seq
Scan and without a Sort; writes to bestellung must not be
blocked in the process. The index should contain only open orders.
SELECT id, angelegt FROM bestellung
WHERE status = 'offen' AND lower(email) = '[email protected]'
ORDER BY angelegt DESC LIMIT 5;Task 7: Shrinking the log table (6 %)
Database pruef. Three quarters of the rows in
protokoll have been deleted, but the file is still full
size. Shrink it while it stays in use, without locking the table
exclusively for the whole duration. Afterwards the table has:
fillfactor 90, autovacuum on, and kicking in from as little
as 2 % dead rows.
Task 8: Moving a schema (6 %)
On pg2. As pruef_anna (tasks 2 and 3),
back up only the schema lager of the database
pruef from pg1 over the network with verified
TLS into the archive ~/pruef/08-lager.dump. Restore it on
pg2 into a new database pruef_kopie – without
errors, with postgres as the owner.
Task 9: Verifying a physical backup (7 %)
On pg2. As the role pruef_backup
(create it, lab password lab-pruef-backup, no superuser),
take a base backup of pg1 in tar format with gzip to
/var/lib/postgresql/pruef-09, including the WAL of the
backup. Verify it with pg_verifybackup and write the name
of the WAL segment it starts at to ~/pruef/09.txt.
Task 10: The standby with a memory gap (7 %)
On pg2. Build a second cluster 18/pruef
(port 5433) as a standby of pg1 with the slot
pruef_pg2. It receives the WAL immediately, but applies
changes only 5 minutes later – as protection against an
accidental DROP. Prove it: a new table on pg1
is not there on the standby at first, although the WAL has already
arrived.
Task 11: Cluster on two nodes (6 %)
On cp1, namespace pruef. Create the
cluster pruef-db: two instances of PostgreSQL 18.7
(minimal-trixie), database lager with owner
lager, max_connections = 50, storage 512Mi,
requests 50m/128Mi, limit 256Mi. The two instances must
never run on the same node.
Task 12: Read-only role via CRD (6 %)
In lager, as the role lager, create the
table bestand with 300 rows. Then create the role
pruef_leser declaratively (login, at most 5 connections,
lab password lab-leser-123 from a Secret). It may read
bestand. Prove that it reads on a replica via the Service
pruef-db-ro.
Task 13: Backup into the bucket (7 %)
pruef-db archives WAL into the bucket
cnpg-backups on pg2 (RustFS, credentials
pglab/lab-s3-secret-123). Then take a backup
that is guaranteed to run on the primary, and write its backup ID to
~/pg/pruef/13.txt. The data of pg-notes in the
bucket stays untouched.
Task 14: Back to the backup (7 %)
In pruef-db, drop the table bestand (the
accident). In a new cluster pruef-klon (one instance),
restore exactly the state at the end of the backup from task 13 – not
later – and prove that bestand is complete there.
Task 15: Pooler for reports (7 %)
Create the Pooler pruef-db-lesen: one instance, replicas
only, session mode, at most 50 clients. Using the owner role
lager, prove that connections through the Pooler land on
the replica.
Task 16: Who is knocking? (6 %)
Since the start of the exam, someone has been trying to log in to
pg-notes (namespace tag14) as
notes_app. Find the client IP and the number of failed
logins in the primary’s logs, and write both (IP count) to
~/pg/pruef/16.txt.
Stop the clock.
Solutions
Part 1 on pg1 in the folder ~/pruef, except
where it says pg2. Part 2 on cp1 in the
folder ~/pg/pruef with
KUBECONFIG=~/pg/pruef/.kc.
Solution 1
psql -Atc 'SHOW server_version_num' > 01.txt
psql -Atc 'SHOW hba_file' >> 01.txt
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NULL' >> 01.txt
cat 01.txtExpected:
180007
/etc/postgresql/18/main/pg_hba.conf
8
server_version_num is made for scripts: comparable as a
number (>= 180000). The rule count depends on your
setup; pg_hba_file_rules reads the file fresh, as it would
apply at the next reload (Day 3).
Solution 2
psql -d pruef <<'EOF'
CREATE ROLE pruef_leser NOLOGIN;
GRANT USAGE ON SCHEMA lager TO pruef_leser;
GRANT SELECT ON ALL TABLES IN SCHEMA lager TO pruef_leser;
ALTER DEFAULT PRIVILEGES FOR ROLE pruef_owner IN SCHEMA lager
GRANT SELECT ON TABLES TO pruef_leser;
GRANT pruef_leser TO pruef_anna;
SET ROLE pruef_owner;
CREATE TABLE lager.lieferant (id int PRIMARY KEY, name text);
INSERT INTO lager.lieferant VALUES (1, 'Nordholz');
EOF
export PGPASSWORD=lab-anna-123
A="host=127.0.0.1 dbname=pruef user=pruef_anna"
psql "$A" -Atc 'SELECT count(*) FROM lager.artikel' \
-c 'SELECT name FROM lager.lieferant'
psql "$A" -c "INSERT INTO lager.lieferant VALUES (2, 'x')"
unset PGPASSWORDExpected (abridged):
CREATE ROLE
GRANT
GRANT
ALTER DEFAULT PRIVILEGES
GRANT ROLE
SET
CREATE TABLE
INSERT 0 1
5000
Nordholz
ERROR: permission denied for table lieferant
Three levels: USAGE on the schema, SELECT
on what exists, default privileges for the creating
role on what comes later (Day 2). pruef_anna has
CONNECT via PUBLIC.
Solution 3
H=/etc/postgresql/18/main/pg_hba.conf
echo 'hostssl pruef pruef_anna 172.16.0.123/32 scram-sha-256' | sudo tee -a $H
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL' \
-c 'SELECT pg_reload_conf()'Expected:
hostssl pruef pruef_anna 172.16.0.123/32 scram-sha-256
0
t
On pg2:
export PGPASSWORD=lab-anna-123
C="host=172.16.0.182 dbname=pruef user=pruef_anna"
psql "$C sslmode=disable" -Atc 'SELECT 1'
psql "$C sslmode=verify-full" \
-Atc 'SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'
unset PGPASSWORDExpected:
psql: error: connection to server at "172.16.0.182", port 5432 failed: FATAL: no
pg_hba.conf entry for host "172.16.0.123", user "pruef_anna", database "pruef", no
encryption
t|TLSv1.3
hostssl only matches TLS connections. The line sits at
the end – there is no rule before it that matches
pruef_anna from the lab network. verify-full
works because ~/.postgresql/root.crt on pg2
contains the lab CA from Day 3.
Solution 4
psql -c "ALTER ROLE pruef_report IN DATABASE pruef SET statement_timeout = '2s'" \
-c "ALTER ROLE pruef_report IN DATABASE pruef SET work_mem = '64MB'" \
-c '\drds pruef_report'
export PGPASSWORD=lab-report-123
psql "host=127.0.0.1 dbname=pruef user=pruef_report" -Atc 'SHOW work_mem' \
-c 'SELECT pg_sleep(3)'
psql "host=127.0.0.1 dbname=postgres user=pruef_report" -Atc 'SHOW work_mem' \
-c 'SHOW statement_timeout'
unset PGPASSWORDExpected:
ALTER ROLE
ALTER ROLE
List of settings
Role | Database | Settings
--------------+----------+----------------------
pruef_report | pruef | statement_timeout=2s+
| | work_mem=64MB
(1 row)
64MB
ERROR: canceling statement due to statement timeout
4MB
0
The setting takes effect at every new login – no reload is needed. A
bare ALTER ROLE … SET would apply in all databases:
missed.
Solution 5
The UPDATE in the background, then the lock table:
psql -d pruef -c 'UPDATE konto SET saldo = saldo + 1 WHERE id = 1' &
sleep 2
psql -d pruef -c "SELECT a.pid, a.application_name, a.state, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'konto'::regclass ORDER BY l.granted DESC"Expected:
pid | application_name | state | mode | granted
-------+------------------+---------------------+------------------+---------
36925 | inventur | idle in transaction | ShareLock | t
37157 | psql | active | RowExclusiveLock | f
(2 rows)
SHARE allows readers, but no writers
(RowExclusiveLock). Save the name and terminate precisely
that session:
psql -d pruef -Atc "SELECT a.application_name FROM pg_locks l
JOIN pg_stat_activity a USING (pid) WHERE l.relation = 'konto'::regclass
AND l.granted AND l.mode = 'ShareLock'" > 05.txt
psql -d pruef -Atc "SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE application_name = 'inventur'"
wait
cat 05.txtExpected:
t
UPDATE 1
inventur
Solution 6
psql -d pruef -c "CREATE INDEX CONCURRENTLY ix_offen_email
ON bestellung (lower(email), angelegt) WHERE status = 'offen'"
psql -d pruef -c "EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF)
SELECT id, angelegt FROM bestellung
WHERE status = 'offen' AND lower(email) = '[email protected]'
ORDER BY angelegt DESC LIMIT 5"
psql -d pruef -c "SELECT pg_size_pretty(pg_relation_size('ix_offen_email')),
indisvalid FROM pg_index WHERE indexrelid = 'ix_offen_email'::regclass"Expected (abridged):
CREATE INDEX
Limit (actual rows=5.00 loops=1)
-> Index Scan Backward using ix_offen_email on bestellung (actual rows=5.00 ...
Index Cond: (lower(email) = '[email protected]'::text)
Index Searches: 1
Execution Time: 0.322 ms
pg_size_pretty | indisvalid
----------------+------------
984 kB | t
Three techniques from Day 5 in one index: an expression
(lower(email)), the sort column behind it (no Sort, read
backwards) and partial (WHERE status = 'offen', only 2 % of
the rows, 984 kB). Before: a Parallel Seq Scan with a top-N sort, about
95 ms. CONCURRENTLY and checking
indisvalid = t afterwards are part of it.
Solution 7
pg_repack has been installed on pg1 since
Day 6. First the table parameters, then the rewrite – the new
fillfactor already applies to it:
psql -d pruef -Atc "SELECT pg_size_pretty(pg_table_size('protokoll'))"
psql -d pruef -c "ALTER TABLE protokoll SET (fillfactor = 90,
autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0.02)" \
-c 'CREATE EXTENSION pg_repack'
pg_repack -d pruef -t protokoll
psql -d pruef -Atc "SELECT pg_size_pretty(pg_table_size('protokoll'))" \
-c "SELECT unnest(reloptions) FROM pg_class WHERE relname = 'protokoll'"Expected:
92 MB
ALTER TABLE
CREATE EXTENSION
INFO: repacking table "public.protokoll"
25 MB
fillfactor=90
autovacuum_enabled=on
autovacuum_vacuum_scale_factor=0.02
VACUUM FULL shrinks just as well, but holds an
AccessExclusiveLock the whole time: missed.
pg_repack only locks briefly at the start and the end.
Solution 8
On pg2:
cd ~/pruef
export PGPASSWORD=lab-anna-123
pg_dump "host=172.16.0.182 dbname=pruef user=pruef_anna sslmode=verify-full" \
-n lager -Fc -f 08-lager.dump; echo rc=$?
unset PGPASSWORD
pg_restore -l 08-lager.dump | grep -E 'SCHEMA|TABLE DATA'
sudo -u postgres createdb pruef_kopie
sudo -u postgres pg_restore -d pruef_kopie --no-owner --no-acl < 08-lager.dump
echo rc=$?
sudo -u postgres psql -d pruef_kopie -Atc "SELECT (SELECT count(*) FROM lager.artikel),
(SELECT count(*) FROM lager.lieferant)" -c '\dn lager'Expected (abridged):
rc=0
7; 2615 25565 SCHEMA - lager pruef_owner
3482; 0 0 ACL - SCHEMA lager pruef_owner
3473; 0 25566 TABLE DATA lager artikel pruef_owner
3474; 0 25607 TABLE DATA lager lieferant pruef_owner
rc=0
5000|1
lager|postgres
pg_dump -n includes the SCHEMA entry, so
the restore without a filter creates the schema (unlike
pg_restore -n, Day 8). --no-owner --no-acl,
because the roles do not exist on pg2.
pruef_anna is enough for the dump because, thanks to task
2, she may read everything in lager.
Solution 9
On pg1, the role and access:
psql -c "CREATE ROLE pruef_backup LOGIN REPLICATION PASSWORD 'lab-pruef-backup'"
echo 'host replication pruef_backup 172.16.0.123/32 scram-sha-256' \
| sudo tee -a /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'On pg2:
echo '172.16.0.182:5432:replication:pruef_backup:lab-pruef-backup' \
| sudo -u postgres tee /var/lib/postgresql/.pgpass >/dev/null
sudo -u postgres chmod 600 /var/lib/postgresql/.pgpass
sudo -u postgres pg_basebackup -h 172.16.0.182 -U pruef_backup \
-D /var/lib/postgresql/pruef-09 -Ft -z -X stream -c fast
sudo -u postgres ls -lh /var/lib/postgresql/pruef-09
sudo -u postgres /usr/lib/postgresql/18/bin/pg_verifybackup -n \
/var/lib/postgresql/pruef-09
sudo -u postgres tar -xzOf /var/lib/postgresql/pruef-09/base.tar.gz backup_label \
| grep 'START WAL' | grep -o 'file [0-9A-F]*' | cut -d' ' -f2 > ~/pruef/09.txt
cat ~/pruef/09.txtExpected (abridged):
-rw------- 1 postgres postgres 268K Nov 19 07:36 backup_manifest
-rw------- 1 postgres postgres 23M Nov 19 07:36 base.tar.gz
-rw------- 1 postgres postgres 19K Nov 19 07:36 pg_wal.tar.gz
backup successfully verified
000000030000000200000048
pg_verifybackup checks tar backups only with
-n (without WAL parsing, Day 9). The start segment is in
backup_label inside base.tar.gz –
tar -O reads it without unpacking.
Solution 10
The role from task 9 may stream as well; the .pgpass
line (database replication) matches too. On
pg2:
sudo pg_createcluster 18 pruef >/dev/null
sudo -u postgres rm -rf /var/lib/postgresql/18/pruef
sudo -u postgres pg_basebackup -h 172.16.0.182 -U pruef_backup \
-D /var/lib/postgresql/18/pruef -X stream -c fast -C -S pruef_pg2 -R
sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/pruef/postgresql.auto.conf
echo "recovery_min_apply_delay = '5min'" \
| sudo -u postgres tee /etc/postgresql/18/pruef/conf.d/verzoegert.conf
sudo pg_ctlcluster 18 pruef start
pg_lsclusters | grep pruefExpected (abridged):
recovery_min_apply_delay = '5min'
18 pruef 5433 online postgres /var/lib/postgresql/18/pruef ...
On pg1:
psql -d pruef -c 'CREATE TABLE verzoegert (t timestamptz DEFAULT now())' \
-c 'INSERT INTO verzoegert DEFAULT VALUES'
psql -c 'SELECT application_name, state, sent_lsn, replay_lsn FROM pg_stat_replication'Expected:
CREATE TABLE
INSERT 0 1
application_name | state | sent_lsn | replay_lsn
------------------+-----------+------------+------------
18/pruef | streaming | 2/4B400000 | 2/4B01B0D8
(1 row)
On pg2:
sudo -u postgres psql -p 5433 -Atc 'SHOW recovery_min_apply_delay' \
-c 'SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()'
sudo -u postgres psql -p 5433 -d pruef -c 'SELECT count(*) FROM verzoegert'Expected:
5min
2/4C6FE000|2/4B01B0D8
ERROR: relation "verzoegert" does not exist
LINE 1: SELECT count(*) FROM verzoegert
^
Received up to 2/4C6FE000, applied only up to
2/4B01B0D8: the WAL is there, the commits are waiting.
After a good five minutes, the same query returns 1. In an
emergency, pg_wal_replay_pause() stops the standby before
the DROP is applied.
Solution 11
cat > 11-cluster.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pruef-db
namespace: pruef
spec:
instances: 2
imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
storage:
size: 512Mi
resources:
requests: {cpu: 50m, memory: 128Mi}
limits: {memory: 256Mi}
affinity:
enablePodAntiAffinity: true
podAntiAffinityType: required
topologyKey: kubernetes.io/hostname
postgresql:
parameters:
max_connections: "50"
bootstrap:
initdb:
database: lager
owner: lager
EOF
kubectl apply -f 11-cluster.yaml
kubectl wait --for=condition=Ready cluster/pruef-db -n pruef --timeout=300s
kubectl get pods -n pruef -l cnpg.io/cluster=pruef-db -o wide \
-L cnpg.io/instanceRole | awk '{print $1, $3, $7, $NF}'
kubectl cnpg psql pruef-db -n pruef -- -Atc 'SHOW max_connections'Expected:
cluster.postgresql.cnpg.io/pruef-db created
cluster.postgresql.cnpg.io/pruef-db condition met
NAME STATUS NODE INSTANCEROLE
pruef-db-1 Running worker1 primary
pruef-db-2 Running worker2 replica
50
The default is anti-affinity preferred – then both
instances could land on one node if the other one is full.
required forbids that (Day 16, Enterprise Note). With only
two workers, at most two instances fit this way.
Solution 12
kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'SET ROLE lager' \
-c 'CREATE TABLE bestand AS SELECT i AS id, i * 3 AS menge
FROM generate_series(1, 300) i'
kubectl create secret generic pruef-leser-pw -n pruef --type=kubernetes.io/basic-auth \
--from-literal=username=pruef_leser --from-literal=password=lab-leser-123
cat > 12-rolle.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: DatabaseRole
metadata:
name: pruef-leser
namespace: pruef
spec:
cluster:
name: pruef-db
name: pruef_leser
login: true
connectionLimit: 5
databaseRoleReclaimPolicy: delete
passwordSecret:
name: pruef-leser-pw
EOF
kubectl apply -f 12-rolle.yamlWait about 40 s until the operator has applied the Secret to the role in the instances (Day 17, Drill 5), then:
kubectl get databaserole -n pruef
kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'SET ROLE lager' \
-c 'GRANT SELECT ON bestand TO pruef_leser'
kubectl exec -n pruef pruef-db-1 -c postgres -- env PGPASSWORD=lab-leser-123 \
psql "host=pruef-db-ro user=pruef_leser dbname=lager sslmode=require" \
-Atc 'SELECT count(*), pg_is_in_recovery() FROM bestand'
kubectl cnpg psql pruef-db -n pruef -- -c '\du pruef_leser'Expected (abridged):
NAME AGE CLUSTER PG NAME APPLIED MESSAGE
pruef-leser 33s pruef-db pruef_leser true
SET
GRANT
300|t
List of roles
Role name | Attributes
-------------+---------------
pruef_leser | 5 connections
SET ROLE lager makes sure the table belongs to the owner
role; the owner issues the GRANT.
pg_is_in_recovery() = t is the proof for the replica.
Solution 13
kubectl create secret generic s3-creds -n pruef \
--from-literal=ACCESS_KEY_ID=pglab --from-literal=ACCESS_SECRET_KEY=lab-s3-secret-123
cat > 13-objectstore.yaml <<'EOF'
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
name: pruef-s3
namespace: pruef
spec:
retentionPolicy: "1d"
configuration:
destinationPath: s3://cnpg-backups/
endpointURL: http://172.16.0.123:9000
s3Credentials:
accessKeyId: {name: s3-creds, key: ACCESS_KEY_ID}
secretAccessKey: {name: s3-creds, key: ACCESS_SECRET_KEY}
wal:
compression: gzip
data:
compression: gzip
instanceSidecarConfiguration:
resources:
requests: {cpu: 10m, memory: 64Mi}
limits: {memory: 256Mi}
EOF
kubectl apply -f 13-objectstore.yaml
kubectl patch cluster pruef-db -n pruef --type merge -p '{"spec":{"plugins":[
{"name":"barman-cloud.cloudnative-pg.io","isWALArchiver":true,
"parameters":{"barmanObjectName":"pruef-s3"}}]}}'The operator rolls out both instances again (about 40 s in the lab). Then:
kubectl cnpg status pruef-db -n pruef | grep -E 'Working WAL|Primary instance'
kubectl cnpg backup pruef-db -n pruef --backup-name pruef-13 --backup-target primary \
--method plugin --plugin-name barman-cloud.cloudnative-pg.ioAfter about 20 s:
kubectl get backup pruef-13 -n pruef -o jsonpath='{.status.backupId}' > 13.txt
kubectl get backup pruef-13 -n pruef -o custom-columns=PHASE:.status.phase,\
POD:.status.instanceID.podName,ID:.status.backupIdExpected:
Primary instance: pruef-db-1
Working WAL archiving: OK
backup/pruef-13 created
PHASE POD ID
completed pruef-db-1 20261119T073958
Without serverName, the plugin stores everything under
the cluster name (pruef-db/) – pg-notes/ stays
untouched. --backup-target primary overrides the default
prefer-standby (Day 15).
Solution 14
With targetImmediate (end of the backup, as soon as it
is consistent), CNPG needs the backupID (Day 15,
Challenge):
kubectl cnpg psql pruef-db -n pruef -- -d lager -c 'DROP TABLE bestand'
cat > 14-klon.yaml <<EOF
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pruef-klon
namespace: pruef
spec:
instances: 1
imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
storage:
size: 512Mi
resources:
requests: {cpu: 50m, memory: 128Mi}
limits: {memory: 256Mi}
bootstrap:
recovery:
source: pruef-backup
recoveryTarget:
targetImmediate: true
backupID: $(cat 13.txt)
externalClusters:
- name: pruef-backup
plugin:
name: barman-cloud.cloudnative-pg.io
parameters:
barmanObjectName: pruef-s3
serverName: pruef-db
EOF
grep -A2 recoveryTarget 14-klon.yaml
kubectl apply -f 14-klon.yamlExpected:
DROP TABLE
recoveryTarget:
targetImmediate: true
backupID: 20261119T073958
cluster.postgresql.cnpg.io/pruef-klon created
The heredoc without quotes (<<EOF) inserts the ID
from 13.txt. After about 80 s:
kubectl get cluster -n pruef
kubectl cnpg psql pruef-klon -n pruef -- -d lager \
-Atc 'SELECT count(*), sum(menge) FROM bestand'Expected (abridged):
NAME AGE INSTANCES READY STATUS PRIMARY
pruef-db 4m48s 2 2 Cluster in healthy state pruef-db-1
pruef-klon 78s 1 1 Cluster in healthy state pruef-klon-1
300|135450
A targetTime would have worked too – but only if the
time lies safely between the end of the backup and the
DROP. targetImmediate is exactly “state of the
backup”.
Solution 15
cat > 15-pooler.yaml <<'EOF'
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
name: pruef-db-lesen
namespace: pruef
spec:
cluster:
name: pruef-db
instances: 1
type: ro
pgbouncer:
poolMode: session
parameters:
max_client_conn: "50"
template:
spec:
containers:
- name: pgbouncer
resources:
requests: {cpu: 10m, memory: 32Mi}
limits: {memory: 64Mi}
EOF
kubectl apply -f 15-pooler.yamlAfter a few seconds (the Deployment is only created by the operator):
kubectl rollout status deploy/pruef-db-lesen -n pruef
kubectl exec -n pruef deploy/pruef-db-lesen -c pgbouncer -- grep -E \
'^\* =|^pool_mode|^max_client_conn' /controller/configs/pgbouncer.ini
PW=$(kubectl get secret pruef-db-app -n pruef -o jsonpath='{.data.password}' \
| base64 -d)
kubectl exec -n pruef pruef-db-1 -c postgres -- env PGPASSWORD=$PW psql \
"host=pruef-db-lesen user=lager dbname=lager sslmode=require" \
-Atc 'SELECT pg_is_in_recovery(), inet_server_addr()'
kubectl get pods -n pruef -l cnpg.io/cluster=pruef-db,cnpg.io/podRole=instance \
-o custom-columns=NAME:.metadata.name,IP:.status.podIPExpected (abridged):
deployment "pruef-db-lesen" successfully rolled out
* = host=pruef-db-ro
pool_mode = session
max_client_conn = 50
t|10.0.2.186
NAME IP
pruef-db-1 10.0.1.60
pruef-db-2 10.0.2.186
Right after apply, kubectl rollout status
still reports NotFound – the Pooler is briefly
inactive until the operator creates the Deployment.
Solution 16
P=$(kubectl get cluster pg-notes -n tag14 -o jsonpath='{.status.currentPrimary}')
kubectl logs $P -n tag14 -c postgres --since=60m | jq -r 'select(
.record.error_severity == "FATAL" and .record.user_name == "notes_app")
| [.record.connection_from, .record.message] | @tsv' | head -2
kubectl logs $P -n tag14 -c postgres --since=60m | jq -r 'select(
.record.error_severity == "FATAL" and .record.user_name == "notes_app")
| .record.connection_from' | cut -d: -f1 | sort | uniq -cExpected (abridged):
10.0.1.115:40826 password authentication failed for user "notes_app"
10.0.1.115:40838 password authentication failed for user "notes_app"
14 10.0.1.115
echo '10.0.1.115 14' > 16.txt
kubectl get pod angreifer -n pruef -o jsonpath='{.status.podIP}'; echoExpected: 10.0.1.115 – the Pod from the
preparation.
Seven attempts, but 14 log lines: with sslmode=prefer,
psql tries TLS first, then without (Day 0, Drill 7). The
task asked for failed logins in the log – 14 is correct,
7 describes the client’s calls. Whoever only reads
--tail misses the early attempts.
Scoring
Add up the weights of the tasks that are solved completely and
provably – there are no partial points. Whoever bypasses a requirement
(privileges for PUBLIC, VACUUM FULL instead of
online, CREATE INDEX without CONCURRENTLY,
ALTER ROLE for all databases) gets zero for the task.
| Result | Assessment |
|---|---|
| 85–100 % | confident in operations – classic as well as on Kubernetes |
| 70–84 % | the craft is solid; repeat weak spots specifically |
| below 70 % | repeat the days for the tasks you missed and start again |
| Tasks | Area | Days |
|---|---|---|
| 1–4 | access, roles, configuration (22 %) | 1–3 |
| 5–7 | locks, indexes, VACUUM (19 %) | 4–6 |
| 8–10 | backup, recovery, replication (20 %) | 8–10 |
| 11–12 | CNPG cluster and roles (12 %) | 14, 16, 17 |
| 13–14 | CNPG backup and restore (14 %) | 15 |
| 15–16 | Pooler and logs (13 %) | 17, 18 |
Not covered: logical replication (Day 11), major upgrade (Day 13), failover and fencing (Day 16), NetworkPolicies (Day 18) – go through these days once more to finish.
Cleanup
On cp1, the namespace first, so that nothing is archived into the bucket anymore:
kubectl delete namespace pruefOn pg2: the standby, the backup, the copy, the
password file and the objects of pruef-db:
sudo pg_dropcluster --stop 18 pruef
sudo -u postgres rm -rf /var/lib/postgresql/.pgpass /var/lib/postgresql/pruef-09
sudo -u postgres dropdb --if-exists pruef_kopie
rm -rf ~/pruef
S3="--aws-sigv4 aws:amz:us-east-1:s3 --user pglab:lab-s3-secret-123"
U=http://172.16.0.123:9000/cnpg-backups
for k in $(curl -s $S3 "$U?list-type=2&prefix=pruef-db/" \
| grep -oE '<Key>[^<]*</Key>' | sed -E 's/<[^>]+>//g'); do
curl -s -o /dev/null $S3 -X DELETE "$U/$k"
doneOn pg1: the inventory process, the standby’s slot,
pg_hba.conf from the backup, the database and the roles.
The role settings from task 4 disappear with the database:
pkill -f 'sleep 3000'
B=/var/lib/postgresql/backup-pruef
psql -Atc "SELECT pg_drop_replication_slot(slot_name) FROM pg_replication_slots
WHERE slot_name = 'pruef_pg2'"
sudo cp $B/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf
psql -Atc 'SELECT pg_reload_conf()'
dropdb pruef
psql -c 'DROP ROLE IF EXISTS pruef_anna, pruef_report, pruef_leser, pruef_backup,
pruef_owner'Expected: an empty line, t,
DROP ROLE.
Checkpoint
On pg1:
B=/var/lib/postgresql/backup-pruef
sudo diff $B/pg_hba.conf /etc/postgresql/18/main/pg_hba.conf && echo IDENTISCH
sudo diff $B/postgresql.auto.conf /var/lib/postgresql/18/main/postgresql.auto.conf \
&& echo IDENTISCHExpected: IDENTISCH twice.
psql -Atc 'SELECT count(*) FROM pg_replication_slots' \
-c "SELECT count(*) FROM pg_roles WHERE rolname LIKE 'pruef%'" \
-c 'SELECT count(*) FROM pg_db_role_setting'
psql -l | grep -c pruefExpected: 0 four times.
sudo rm -r /var/lib/postgresql/backup-pruef
rm -rf ~/pruefOn pg2:
pg_lsclusters
S3="--aws-sigv4 aws:amz:us-east-1:s3 --user pglab:lab-s3-secret-123"
U=http://172.16.0.123:9000/cnpg-backups
curl -s $S3 "$U?list-type=2&prefix=pruef-db/" | grep -c '<Key>'Expected: only 18 main on port 5432,
then 0.
On cp1:
kubectl get ns | grep -c pruef
kubectl get cluster pg-notes -n tag14Expected (after about one minute): 0,
pg-notes with 3 instances and
Cluster in healthy state.
With that, you have completed the drills.