Skip to content
Hands-on IT IT Drills

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, \?, --help and kubectl explain. Not allowed: this book during the exam.
  • Part 1 runs on pg1 (database pruef, files in ~/pruef) and pg2 (files in ~/pruef), Part 2 on cp1 in the namespace pruef (files in ~/pg/pruef).
  • Roles you create start with pruef_, Kubernetes objects with pruef-.
  • 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;
EOF

For 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 ~/pruef

On 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.txt

Expected:

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 PGPASSWORD

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

Expected:

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 PGPASSWORD

Expected:

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.txt

Expected:

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.txt

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

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

Wait 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.io

After 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.backupId

Expected:

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.yaml

Expected:

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.yaml

After 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.podIP

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

Expected (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}'; echo

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

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

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

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

Expected: 0 four times.

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

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

Expected (after about one minute): 0, pg-notes with 3 instances and Cluster in healthy state.

With that, you have completed the drills.