Skip to content
Hands-on IT IT Drills

Appendix A: Cheat Sheet

The most important commands and snippets from Days 0 to 18, sorted by topic. The days are in parentheses. You replace placeholders in angle brackets; IPs are those of the lab (pg1 172.16.0.182, pg2 .123, cp1 .126). Passwords like lab-notes-123 are lab values. Without a host, a command runs on pg1, from Day 14 on on cp1.

Installation and cluster management (Days 0, 3, 13)

Add the PGDG repository in an architecture-neutral way and install only server plus client (not postgresql-contrib – that pulls in Ubuntu’s PG 16):

sudo install -d /usr/share/postgresql-common/pgdg
K=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc
sudo curl -fsSL -o $K https://www.postgresql.org/media/keys/ACCC4CF8.asc
cat <<EOF | sudo tee /etc/apt/sources.list.d/pgdg.sources
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: $(lsb_release -cs)-pgdg
Architectures: $(dpkg --print-architecture)
Components: main
Signed-By: $K
EOF
sudo apt-get update
sudo apt-get install -y postgresql-18 postgresql-client-18
pg_lsclusters                                 # version, name, port, status, paths
sudo pg_createcluster 18 test --start         # next free port (5433 ...)
sudo pg_ctlcluster 18 main restart            # or: start | stop
sudo systemctl restart postgresql@18-main     # the same via systemd
sudo pg_dropcluster --stop 18 test
sudo -u postgres psql -Atc 'SHOW data_directory' -c 'SHOW config_file' \
  -c 'SHOW hba_file' -c 'SHOW data_checksums'
sudo -u postgres createuser --superuser ubuntu   # lab only
createdb                                      # DB named after the role, psql without -d
sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log
Path (Debian/Ubuntu) Contents
/etc/postgresql/18/main/ postgresql.conf, pg_hba.conf, conf.d/
/var/lib/postgresql/18/main/ data directory, postgresql.auto.conf
/var/log/postgresql/postgresql-18-main.log server log
/usr/lib/postgresql/18/bin/ pg_upgrade, pg_rewind, pg_verifybackup …

psql craft (Day 1)

Meta-command Effect Day
\l, \dn, \dt+ s.*, \d+ t databases, schemas, tables, details 1
\du, \drg, \df s.*, \sv v roles, memberships, functions, view 1, 2
\dp t, \ddp, \drds, \dn+ privileges, default privileges, role settings 2, 3
\di+ ix_*, \dx, \dRp+ p indexes, extensions, publication 5, 11, 13
\x auto, \timing on, \conninfo output, timing, connection incl. TLS 0, 1
\i, \o, \!, \e run file, redirect, shell, editor 1
\gset, \echo :var, \if … \endif result into variable, conditionals 1
\watch i=1 c=3 repeat query (interval, count) 1, 10
echo "SELECT now()::time(0), count(*) FROM pg_stat_activity \watch i=1 c=3" \
  | psql -d tag01
psql -X -d tag01 -Atc 'SELECT 1'              # -X: without ~/.psqlrc
psql -X -v ON_ERROR_STOP=1 -d tag01 -f pruefung.sql   # stop at the first error
psql "postgresql://[email protected]:5432/notes?application_name=tag01" \
  -c '\conninfo'
export PGHOST=172.16.0.182 PGUSER=notes_app PGDATABASE=notes PGAPPNAME=tag01-env
psql "postgresql://[email protected],172.16.0.182/notes?connect_timeout=2\
&target_session_attrs=read-write" -Atc 'SELECT inet_server_addr()'
pg_isready -h 172.16.0.182; echo "rc=$?"      # 0 = accepting, 2 = no response

Password and service file (on pg2; PGPASSWORD beats .pgpass):

echo '172.16.0.182:5432:notes:notes_app:lab-notes-123' > ~/.pgpass
chmod 600 ~/.pgpass                           # otherwise libpq ignores the file
cat > ~/.pg_service.conf <<'EOF'
[notes]
host=172.16.0.182
dbname=notes
user=notes_app
sslmode=require
EOF
psql service=notes -Atc 'SELECT current_user, current_database()'
psql "service=notes dbname=postgres" -c 'SELECT 1'   # override a single value

Databases, roles and privileges (Day 2)

CREATE DATABASE tag02 TEMPLATE tag02_vorlage OWNER tag02_owner;
ALTER DATABASE tag02 SET search_path = app, public;
CREATE ROLE tag02_lesen NOLOGIN;
CREATE ROLE tag02_schreiben NOLOGIN IN ROLE tag02_lesen;
CREATE ROLE tag02_bob LOGIN NOINHERIT PASSWORD 'lab-bob-123' IN ROLE tag02_schreiben;
SET ROLE tag02_schreiben;                    -- NOINHERIT: privileges need SET ROLE
GRANT USAGE ON SCHEMA app TO tag02_lesen;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO tag02_lesen;
GRANT USAGE ON SEQUENCE app.protokoll_id_seq TO tag02_schreiben;
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO tag02_lesen;
REVOKE CONNECT, TEMPORARY ON DATABASE tag02 FROM PUBLIC;
GRANT pg_read_all_data, pg_monitor TO tag02_gast;   -- predefined roles
SELECT has_schema_privilege('tag02_gast', 'public', 'CREATE'),
       has_table_privilege('tag02_alice', 'app.kunden', 'SELECT');
ALTER TABLE app.notizen ENABLE ROW LEVEL SECURITY;
CREATE POLICY nur_eigene ON app.notizen USING (besitzer = current_user);
DROP OWNED BY tag02_schreiben;                -- privileges and default privileges gone
DROP ROLE tag02_schreiben;
  • Default privileges only apply to objects of the role from FOR ROLE.
  • Since PG 15, PUBLIC may not create anything in the schema public; the owner is pg_database_owner. That’s why notes-api migrate fails as a non-owner.

Configuration (Day 3)

context tells you what a change needs: user (immediately, per session), sighup (reload), postmaster (restart).

psql -c "SELECT name, setting, unit, context, source FROM pg_settings
  WHERE name IN ('shared_buffers', 'work_mem', 'max_connections') ORDER BY name;"
psql -c "SELECT sourcefile, sourceline, name, setting, applied, error
  FROM pg_file_settings WHERE name = 'shared_buffers';"
psql -c "ALTER SYSTEM SET work_mem = '16MB';"  # ends up in postgresql.auto.conf
psql -Atc 'SELECT pg_reload_conf()'
psql -Atc 'SELECT name FROM pg_settings WHERE pending_restart'
psql -Atc 'SELECT pg_postmaster_start_time()'   # prove the restart
psql -c 'ALTER SYSTEM RESET shared_buffers'
ALTER ROLE tag03_report SET work_mem = '256MB';
ALTER ROLE tag03_report SET idle_in_transaction_session_timeout = '5s';
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report';             -- only the running query
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report';             -- the whole connection

pg_hba.conf, SCRAM and TLS (Days 0, 3)

The first matching line wins; a reload is enough:

host     notes        notes_app     172.16.0.0/24    scram-sha-256
host     notes        tag03_report  172.16.0.123/32  reject
hostssl  notes        tag03_report  172.16.0.0/24    scram-sha-256
host     replication  tag09_backup  172.16.0.123/32  scram-sha-256
psql -c "SELECT line_number AS nr, type, database, user_name, address, auth_method
  FROM pg_hba_file_rules WHERE type = 'host';"
psql -Atc 'SELECT count(*) FROM pg_hba_file_rules WHERE error IS NOT NULL'
psql -c "ALTER SYSTEM SET listen_addresses = '*';"   # restart required
psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -c "ALTER ROLE tag03_alt PASSWORD 'lab-tag03'"  # set again = SCRAM hash

Your own CA, server certificate, verification from pg2 (CA in ~/.postgresql/root.crt):

openssl req -x509 -newkey rsa:4096 -nodes -days 3650 -subj "/CN=Lab PostgreSQL CA" \
  -keyout ca.key -out ca.crt
openssl req -newkey rsa:2048 -nodes -subj "/CN=172.16.0.182" \
  -keyout server.key -out server.csr
printf 'subjectAltName = IP:172.16.0.182\nextendedKeyUsage = serverAuth\n' \
  > server.ext
openssl x509 -req -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial \
  -days 825 -extfile server.ext -out server.crt
D=/etc/postgresql/18/main
sudo install -o postgres -g postgres -m 600 ~/pg-ca/server.key $D/server.key
psql -c "ALTER SYSTEM SET ssl_cert_file = '$D/server.crt';" \
  -c "ALTER SYSTEM SET ssl_key_file = '$D/server.key';"
psql -Atc 'SELECT pg_reload_conf()'          # TLS files: a reload is enough
psql "host=172.16.0.182 dbname=notes user=notes_app sslmode=verify-full" \
  -Atc 'SELECT ssl, version FROM pg_stat_ssl WHERE pid = pg_backend_pid()'
openssl s_client -starttls postgres -connect 172.16.0.182:5432 \
  -CAfile ~/.postgresql/root.crt </dev/null 2>/dev/null | grep 'Verify return'

Transactions, MVCC and locks (Day 4)

SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;   -- row versions
SELECT pg_current_xact_id(), pg_current_snapshot();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET lock_timeout = '2s';                     -- DDL doesn't wait forever
SET statement_timeout = '1s';
SELECT pid, state, wait_event, pg_blocking_pids(pid) AS blockiert_von,
       left(query, 25) AS query
FROM pg_stat_activity WHERE datname = 'tag04' AND pid <> pg_backend_pid();
SELECT l.pid, l.locktype, l.relation::regclass, l.mode, l.granted
FROM pg_locks l JOIN pg_stat_activity a USING (pid) WHERE a.datname = 'tag04';
SELECT pg_terminate_backend(pid) FROM pg_stat_activity   -- root blockers only
WHERE pid IN (SELECT unnest(pg_blocking_pids(pid)) FROM pg_stat_activity)
  AND cardinality(pg_blocking_pids(pid)) = 0;
SELECT pid, now() - xact_start AS dauer, backend_xmin FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%';
L=/var/log/postgresql/postgresql-18-main.log
sudo grep -E 'still waiting|acquired ShareLock' -A1 $L | tail -5   # log_lock_waits
sudo grep -A5 'deadlock detected' $L | tail -6
psql -d tag04 -Atc "SELECT deadlocks FROM pg_stat_database WHERE datname = 'tag04'"

EXPLAIN and indexes (Day 5)

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT ...;   -- BUFFERS automatic from 18
CREATE INDEX ix_status_zeit ON bestellung (status, angelegt);
CREATE INDEX ix_offen ON bestellung (angelegt) WHERE status = 'offen';
CREATE INDEX ix_email_lower ON bestellung (lower(email));
CREATE INDEX ix_daten ON bestellung USING gin (daten jsonb_path_ops);
CREATE INDEX ix_notiz_fts ON bestellung USING gin (to_tsvector('german', notiz));
CREATE INDEX CONCURRENTLY ix_betrag ON bestellung (betrag);   -- without a write lock
DROP INDEX CONCURRENTLY ix_kunde;
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;   -- leftovers
SELECT phase, blocks_done, blocks_total FROM pg_stat_progress_create_index;
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'bestellung' ORDER BY idx_scan;
SELECT attname, n_distinct, most_common_vals FROM pg_stats
WHERE tablename = 'bestellung' AND attname = 'status';
ANALYZE kopie;                                -- estimate wrong? refresh statistics
  • CREATE INDEX takes a ShareLock (reads yes, writes no), CONCURRENTLY only a ShareUpdateExclusiveLock.
  • kunde_id + 0 = 4711 or lower(email) without an expression index: no index scan.

VACUUM, autovacuum and bloat (Day 6)

SELECT n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'lager';
CREATE EXTENSION pgstattuple;
SELECT tuple_percent, dead_tuple_percent, free_percent FROM pgstattuple('lager');
VACUUM (VERBOSE) lager;                      -- space reusable, no lock
VACUUM (FULL, VERBOSE) lager;                -- shrinks, AccessExclusiveLock!
ALTER TABLE lager SET (autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000);
ALTER TABLE lager SET (fillfactor = 80);     -- room for HOT updates
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'lager';
SELECT pid, age(backend_xmin), now() - xact_start FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;  -- holds back VACUUM
SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC LIMIT 3;
VACUUM (FREEZE) lager;
sudo apt-get install -y postgresql-18-repack  # shrink online
psql -d tag06 -c 'CREATE EXTENSION pg_repack'
pg_repack -d tag06 -t lager

Monitoring and logs (Day 7)

SELECT pid, state, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity WHERE datname = 'tag07' AND pid <> pg_backend_pid();
SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;
SELECT datname, round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1),
       deadlocks, temp_bytes FROM pg_stat_database WHERE datname = 'notes';
SELECT num_timed, num_requested, write_time FROM pg_stat_checkpointer;
SELECT backend_type, object, context, read_bytes, write_bytes FROM pg_stat_io;

pg_stat_statements needs shared_preload_libraries and therefore a restart:

psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"
sudo systemctl restart postgresql@18-main
psql -d postgres -c 'CREATE EXTENSION pg_stat_statements'
psql -Atc 'SELECT pg_stat_statements_reset()'
psql -c "SELECT calls, round(total_exec_time) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 40) AS query
  FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
pgbench -i -s 10 -q tag07                    # test data, one million accounts
pgbench -c 8 -j 4 -T 30 tag07 | grep -E '^tps|latency average'

Logs (all via reload) and auto_explain:

ALTER SYSTEM SET log_min_duration_statement = '200ms';
ALTER SYSTEM SET log_connections = 'authorization,setup_durations';
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d %a ';
SELECT pg_reload_conf();
ALTER DATABASE tag07 SET log_min_duration_statement = '100ms';   -- one DB only
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = on;
L=/var/log/postgresql/postgresql-18-main.log
sudo grep -cE 'duration: [0-9.]+ ms  statement' $L
sudo grep -E 'FATAL|ERROR' $L | tail -3
sudo grep -A6 'plan:' $L | tail -7
psql -c 'CREATE ROLE postgres_exporter LOGIN' -c 'GRANT pg_monitor TO postgres_exporter'
systemctl is-active postgres_exporter        # download with $ARCH, own unit
curl -s localhost:9187/metrics | grep '^pg_up '

Logical backups (Day 8)

pg_dump -d tag08 -f tag08.sql                 # plain SQL, restore with psql
pg_dump -d tag08 -Fc -f tag08.dump            # custom, restore with pg_restore
pg_dump -d tag08 -Fd -j 4 -f tag08.dir        # directory, parallel
pg_dump -d tag08 -Fc -Z zstd -f tag08-zstd.dump
pg_dumpall --globals-only -f globals.sql      # roles and tablespaces
pg_dumpall --roles-only --no-role-passwords -f /tmp/roles.sql
pg_restore -l tag08.dump | grep -v '^;'       # table of contents
createdb -T template0 tag08_restore
pg_restore -d tag08_restore -j 4 tag08.dump; echo rc=$?
psql -X -q -v ON_ERROR_STOP=1 -d tag08_plain -f tag08.sql >/dev/null; echo rc=$?
pg_restore -d tag08_restore -n audit -t log tag08.dump     # one table
pg_restore -l tag08.dump | grep -E 'SCHEMA - audit|audit log' > audit.list
pg_restore -d tag08_restore -L audit.list tag08.dump       # by list
pg_restore -d tag08_restore --clean --if-exists -j 4 tag08.dump   # overwrite
pg_restore -d tag08_schema -s -L app.list tag08.dump       # schema only
sudo -u postgres pg_restore -d tag08_notes --no-owner --no-acl < notes.dump
  • Roles are global and not in the dump: first globals.sql, then the restore – or --no-owner --no-acl. pg_dump 18 only backs up statistics with --statistics.

Physical backups and PITR (Day 9)

psql -c "CREATE ROLE tag09_backup LOGIN REPLICATION PASSWORD 'lab-backup-123';"
echo '172.16.0.182:5432:replication:tag09_backup:lab-backup-123' \
  | sudo -u postgres tee /var/lib/postgresql/.pgpass  # pg2, then chmod 600
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
  -D /var/lib/postgresql/backup/tar1 -Ft -z -X stream -c fast -P -v
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup \
  -D /var/lib/postgresql/backup/plain1 -Fp -X stream -c fast \
  --manifest-checksums=SHA256
V=/usr/lib/postgresql/18/bin/pg_verifybackup
sudo -u postgres $V /var/lib/postgresql/backup/plain1
sudo -u postgres $V -n /var/lib/postgresql/backup/tar1     # -n: don't check WAL

WAL archive (restart for archive_mode):

cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag09-archiv.conf
archive_mode = on
archive_command = 'rsync -a --ignore-existing %p 172.16.0.123:wal-archive/%f'
CONF
sudo systemctl restart postgresql@18-main
psql -Atc 'SELECT pg_switch_wal()'
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

Point-in-time recovery into a separate cluster (on pg2):

sudo pg_createcluster 18 pitr
sudo -u postgres rm -rf /var/lib/postgresql/18/pitr
sudo -u postgres cp -a /var/lib/postgresql/backup/base2 /var/lib/postgresql/18/pitr
cat <<'CONF' | sudo -u postgres tee /etc/postgresql/18/pitr/conf.d/recovery.conf
restore_command = 'cp /var/lib/postgresql/wal-archive/%f %p'
recovery_target_time = '2026-11-19 04:57:13.492875+00'
CONF
sudo -u postgres touch /var/lib/postgresql/18/pitr/recovery.signal
sudo -u postgres sed -i '/^ssl_/d' /var/lib/postgresql/18/pitr/postgresql.auto.conf
sudo pg_ctlcluster 18 pitr start
sudo -u postgres psql -p 5433 -Atc \
  'SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state()'
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_wal_replay_resume()'   # promote
  • Take the target time including fractions; recovery stops before the first commit after it.
  • The postgresql.auto.conf travels along – remove foreign ssl_ lines before the start.

Incremental (PG 17+, summarize_wal = on via reload):

B=/var/lib/postgresql/backup
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag09_backup -D $B/inkr1 \
  -c fast --incremental=$B/voll/backup_manifest
sudo -u postgres /usr/lib/postgresql/18/bin/pg_combinebackup $B/voll $B/inkr1 \
  -o $B/kombi

pgBackRest (Day 9)

Repository on pg2, database on pg1, SSH between the postgres users. On pg2 (/etc/pgbackrest.conf):

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
start-fast=y
[main]
pg1-host=172.16.0.182
pg1-path=/var/lib/postgresql/18/main

On pg1 (/etc/pgbackrest.conf, plus archive_command = 'pgbackrest --stanza=main archive-push %p'):

[global]
repo1-host=172.16.0.123
repo1-host-user=postgres
[main]
pg1-path=/var/lib/postgresql/18/main
sudo -u postgres pgbackrest --stanza=main stanza-create    # on pg2
sudo -u postgres pgbackrest --stanza=main check
sudo -u postgres pgbackrest --stanza=main --type=full backup   # diff | incr
sudo -u postgres pgbackrest --stanza=main info
sudo -u postgres pgbackrest --stanza=main --reset-pg1-host \
  --pg1-path=/var/lib/postgresql/18/pitr --delta --type=time \
  --target='2026-11-19 05:03:34.798387+00' --target-action=promote restore

Streaming replication (Day 10)

psql -c "CREATE ROLE tag10_repl LOGIN REPLICATION PASSWORD 'lab-repl-123';"
# for pg_rewind without superuser: GRANT EXECUTE on pg_ls_dir, pg_stat_file,
# pg_read_binary_file (both variants); pg_hba: replication and postgres
sudo pg_createcluster 18 standby             # pg2
sudo -u postgres rm -rf /var/lib/postgresql/18/standby
sudo -u postgres pg_basebackup -h 172.16.0.182 -U tag10_repl \
  -D /var/lib/postgresql/18/standby -X stream -c fast -C -S tag10_pg2 -R -v
sudo pg_ctlcluster 18 standby start          # -R: standby.signal, primary_conninfo
psql -x -c 'SELECT application_name, state, sync_state, sent_lsn, replay_lsn,
  write_lag, replay_lag FROM pg_stat_replication'
psql -c "SELECT slot_name, active, wal_status, pg_size_pretty(pg_wal_lsn_diff(
  pg_current_wal_lsn(), restart_lsn)) AS behalten FROM pg_replication_slots"
sudo -u postgres psql -p 5433 -x -c 'SELECT status, sender_host, slot_name,
  received_tli FROM pg_stat_wal_receiver'
sudo -u postgres psql -p 5433 -Atc \
  'SELECT pg_is_in_recovery(), now() - pg_last_xact_replay_timestamp()'

Synchronous, failover, swapping back:

echo "synchronous_standby_names = '\"18/standby\"'" \
  | sudo -u postgres tee /etc/postgresql/18/main/conf.d/tag10-sync.conf
PGOPTIONS='-c synchronous_commit=remote_apply' pgbench -c 4 -T 15 tag10
PGOPTIONS='-c synchronous_commit=local' psql -d tag10 \
  -c "INSERT INTO ping (txt) VALUES ('lokal')"     # emergency exit
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_promote()'   # pg2 becomes primary
sudo -u postgres /usr/lib/postgresql/18/bin/pg_rewind \
  -D /var/lib/postgresql/18/main -R -P \
  --source-server='host=172.16.0.123 port=5433 user=tag10_repl dbname=postgres'
sudo -u postgres /usr/lib/postgresql/18/bin/pg_controldata \
  /var/lib/postgresql/18/standby | grep -E 'cluster state|Latest checkpoint location'
psql -Atc "SELECT pg_create_physical_replication_slot('tag10_pg2')"
psql -Atc "SELECT pg_drop_replication_slot('tag10_pg2')"
  • pg_rewind needs checksums (default from 18) or wal_log_hints; if the old primary is missing WAL, first fetch it via rsync from the source’s pg_wal.
  • An orphaned slot collects WAL until the disk is full.

Logical replication (Day 11)

-- pg1, after wal_level = logical (restart)
CREATE PUBLICATION tag11_de FOR TABLE kunden (id, name, land) WHERE (land = 'DE');
CREATE PUBLICATION tag11_best FOR TABLE bestellungen;
SELECT pubname, tablename, attnames, rowfilter FROM pg_publication_tables;
ALTER TABLE kunden REPLICA IDENTITY FULL;    -- or USING INDEX <unique-index>
ALTER PUBLICATION tag11_best ADD TABLE notiz;
-- pg2, create the schema beforehand via pg_dump -s ... | psql
CREATE SUBSCRIPTION tag11_sub
  CONNECTION 'host=172.16.0.182 dbname=tag11 user=tag11_repl'
  PUBLICATION tag11_de, tag11_best;
SELECT srrelid::regclass, srsubstate FROM pg_subscription_rel;   -- r = ready
ALTER SUBSCRIPTION tag11_sub SET (disable_on_error = true);
SELECT apply_error_count, confl_insert_exists FROM pg_stat_subscription_stats;
ALTER SUBSCRIPTION tag11_sub SKIP (lsn = '1/874CBDF0');   -- LSN from the log
ALTER SUBSCRIPTION tag11_sub ENABLE;          -- or DISABLE
ALTER SUBSCRIPTION tag11_sub REFRESH PUBLICATION;
DROP SUBSCRIPTION tag11_sub;                  -- removes the slot on pg1
sudo grep -A3 'conflict detected' /var/log/postgresql/postgresql-18-main.log
psql -d tag11 -Atc "SELECT format('SELECT setval(%L, %s);', schemaname || '.' ||
  sequencename, last_value) FROM pg_sequences WHERE last_value IS NOT NULL"
  • Only data is replicated: schema, sequences and DDL stay manual work.

PgBouncer (Day 12)

CREATE FUNCTION pgbouncer.get_auth(p_usename text)
  RETURNS TABLE (username text, password text)
  LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
$$ SELECT rolname::text, rolpassword::text FROM pg_authid
   WHERE rolname = p_usename AND rolcanlogin $$;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO tag12_pgbouncer;
[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
auth_user = tag12_pgbouncer
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)
admin_users = tag12_pgbouncer
pool_mode = transaction
default_pool_size = 10

[databases]
tag12 = host=127.0.0.1 port=5432 dbname=tag12
tag12_session = host=127.0.0.1 port=5432 dbname=tag12 pool_mode=session
echo '"tag12_pgbouncer" "lab-tag12-bouncer"' \
  | sudo -u postgres tee /etc/pgbouncer/userlist.txt   # plain text for SCRAM
A="host=127.0.0.1 port=6432 dbname=pgbouncer user=tag12_pgbouncer"
psql "$A" -Atc 'SHOW CONFIG' | grep -E '^(pool_mode|max_prepared_statements)\|'
psql "$A" -c 'SHOW POOLS' | cut -d'|' -f1-4,7,10   # cl_active, cl_waiting, sv_*
psql "$A" -c 'SHOW STATS'
psql "$A" -c 'SET default_pool_size = 5'     # at runtime
psql "$A" -c 'RECONNECT tag12'               # renew server connections
sudo systemctl reload pgbouncer
pgbench -h 127.0.0.1 -p 6432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12
Mode Server connection belongs to the client Breaks
session for the whole session nothing, but barely pools
transaction per transaction SET, SQL PREPARE, advisory locks
statement per statement every transaction with several statements

Major upgrade (Day 13)

ARCH=$(dpkg --print-architecture)
P=https://apt.postgresql.org/pub/repos/apt/dists/noble-pgdg/19/binary-$ARCH/Packages
curl -s $P | grep -A1 '^Package: postgresql-19$' | grep Version
sudo pg_upgradecluster --check -v 18 17 upgrade
sudo pg_upgradecluster -v 18 -m upgrade 17 upgrade   # carry over conf.d/ by hand
L=$(sudo ls -d /var/log/postgresql/pg_upgradecluster-17-18-upgrade.*)
sudo cat $L/update_extensions.sql | sudo -u postgres psql -p 5433
cd /tmp
sudo -u postgres /usr/lib/postgresql/18/bin/pg_upgrade \
  -b /usr/lib/postgresql/17/bin -B /usr/lib/postgresql/18/bin \
  -d /var/lib/postgresql/17/zwei -D /var/lib/postgresql/18/zwei \
  -o '-c config_file=/etc/postgresql/17/zwei/postgresql.conf' \
  -O '-c config_file=/etc/postgresql/18/zwei/postgresql.conf' --check  # then --link
sudo -u postgres /usr/lib/postgresql/17/bin/pg_checksums --enable \
  -D /var/lib/postgresql/17/zwei             # source without checksums, target 18 with
sudo -u postgres /usr/lib/postgresql/18/bin/vacuumdb -p 5436 --all \
  --analyze-in-stages --missing-stats-only
sudo -u postgres psql -p 5436 -f /tmp/update_extensions.sql
  • From 18 on, pg_upgrade carries over the planner statistics (without extended ones); the counters in pg_stat_user_tables start at 0.
  • --link: no way back after the new cluster has started – take a backup first.

CloudNativePG: basics (Day 14)

mkdir -p ~/pg/tag14 && cp ~/.kube/config ~/pg/tag14/.kc   # one copy per day
export KUBECONFIG=~/pg/tag14/.kc
cmctl check api                               # cert-manager ready?
helm list -A                                  # cert-manager and kps (monitoring)
V=1.30.2
kubectl apply --server-side -f https://raw.githubusercontent.com/\
cloudnative-pg/cloudnative-pg/release-1.30/releases/cnpg-$V.yaml
ARCH=$(uname -m | sed s/aarch64/arm64/)
U=https://github.com/cloudnative-pg/cloudnative-pg/releases/download/v$V
curl -fsSLO $U/kubectl-cnpg_${V}_linux_${ARCH}.tar.gz
curl -fsSLO $U/cnpg-$V-checksums.txt
sha256sum --ignore-missing -c cnpg-$V-checksums.txt
tar -xzf kubectl-cnpg_${V}_linux_${ARCH}.tar.gz kubectl-cnpg
sudo install -m 0755 kubectl-cnpg /usr/local/bin/

Cluster (abridged, complete in Day 14, Drill 2):

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata: {name: pg-notes, namespace: tag14}
spec:
  instances: 3
  imageName: ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie
  storage: {storageClass: local-path, size: 1Gi}
  postgresql:
    parameters: {shared_buffers: 64MB}
  bootstrap:
    initdb: {database: notes, owner: notes_app}
kubectl get cluster -n tag14                  # status and current primary
kubectl get pods -n tag14 -o wide -L cnpg.io/instanceRole
kubectl cnpg status pg-notes -n tag14
kubectl cnpg psql pg-notes -n tag14 -- -d notes -Atc 'SELECT count(*) FROM notes'
kubectl cnpg psql pg-notes -n tag14 --replica -- -Atc 'SELECT pg_is_in_recovery()'
kubectl get secret pg-notes-app -n tag14 -o jsonpath='{.data.password}' \
  | base64 -d; echo
kubectl get secret pg-notes-ca -n tag14 -o jsonpath='{.data.ca\.crt}' \
  | base64 -d > ~/pg/tag14/pg-notes-ca.crt
kubectl port-forward -n tag14 --address 0.0.0.0 svc/pg-notes-rw 15432:5432 &
kubectl delete pod pg-notes-1 -n tag14        # primary gone: failover in ~15 s
kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}{"\n"}'
Service Target Day
pg-notes-rw only the primary 14
pg-notes-ro only the replicas 14
pg-notes-r all instances 14
pg-notes-pooler-rw / -ro PgBouncer in front of -rw / -ro 17

CNPG: backups and recovery (Day 15)

S3="--aws-sigv4 aws:amz:us-east-1:s3 --user pglab:lab-s3-secret-123"   # pg2
curl -s -w '%{http_code}\n' $S3 -X PUT http://172.16.0.123:9000/cnpg-backups
cmctl check api                               # plugin needs cert-manager
kubectl apply -f https://github.com/cloudnative-pg/plugin-barman-cloud/\
releases/download/v0.16.0/manifest.yaml
kubectl create secret generic s3-creds -n tag14 \
  --from-literal=ACCESS_KEY_ID=pglab \
  --from-literal=ACCESS_SECRET_KEY=lab-s3-secret-123

ObjectStore and ScheduledBackup (abridged, complete in Day 15, Drills 3 and 5):

apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata: {name: pg2-s3, namespace: tag14}
spec:
  retentionPolicy: "7d"
  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}
---
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata: {name: pg-notes-nachts, namespace: tag14}
spec:
  cluster: {name: pg-notes}
  schedule: "0 0 2 * * *"                    # six fields, seconds first
  immediate: true
  backupOwnerReference: self
  method: plugin
  pluginConfiguration: {name: barman-cloud.cloudnative-pg.io}
kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{"plugins":[
  {"name":"barman-cloud.cloudnative-pg.io","isWALArchiver":true,
   "parameters":{"barmanObjectName":"pg2-s3"}}]}}'
kubectl cnpg status pg-notes -n tag14 | sed -n '/Continuous/,/^$/p'
kubectl cnpg backup pg-notes -n tag14 --backup-target primary \
  --method plugin --plugin-name barman-cloud.cloudnative-pg.io
kubectl get backup -n tag14 -o custom-columns=NAME:.metadata.name,\
PHASE:.status.phase,POD:.status.instanceID.podName,ID:.status.backupId
kubectl patch scheduledbackup pg-notes-nachts -n tag14 --type merge \
  -p '{"spec":{"suspend":true}}'
kubectl logs pg-notes-2 -n tag14 -c plugin-barman-cloud   # NoSuchBucket and co.

PITR always into a new cluster (abridged, complete in Day 15, Drill 6):

  bootstrap:
    recovery:
      source: pg-notes-backup
      recoveryTarget:
        targetTime: "2026-11-19 05:17:04.88945+00"
        # or targetName: vor-preisupdate  plus  backupID: <ID>
  externalClusters:
  - name: pg-notes-backup
    plugin:
      name: barman-cloud.cloudnative-pg.io
      parameters: {barmanObjectName: pg2-s3, serverName: pg-notes}
  • Copy the ObjectStore together with its Secret into the target namespace – it only works locally.
  • Restore point: SELECT pg_create_restore_point('vor-preisupdate'), then pg_switch_wal(). With targetName/targetXID, the webhook requires backupID.
  • If a clone should archive itself, it needs a different serverName.

CNPG: high availability (Day 16)

kubectl cnpg promote pg-notes pg-notes-2 -n tag14        # switchover
kubectl cnpg restart pg-notes pg-notes-1 -n tag14        # one instance
kubectl cnpg fencing on pg-notes pg-notes-3 -n tag14     # Postgres off, Pod stays
kubectl cnpg fencing off pg-notes pg-notes-3 -n tag14
kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.metadata.annotations.cnpg\.io/fencedInstances}{"\n"}'
kubectl cnpg status pg-notes -n tag14 | grep -E 'Primary instance|Timeline'
while sleep 2; do echo "$(date +%T) $(kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}')"; done | uniq -f1
kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"synchronous":{"method":"any","number":1}}}}'
kubectl patch cluster pg-notes -n tag14 --type merge \
  -p '{"spec":{"postgresql":{"synchronous":{"dataDurability":"preferred"}}}}'
kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{
  "primaryUpdateMethod":"switchover",
  "postgresql":{"parameters":{"max_connections":"120"}}}}'
kubectl drain worker1 --pod-selector cnpg.io/cluster=pg-notes \
  --delete-emptydir-data --ignore-daemonsets --timeout 90s
kubectl uncordon worker1
kubectl patch cluster pg-alt -n tag16 --type merge \
  -p '{"spec":{"imageName":"ghcr.io/cloudnative-pg/postgresql:18.7-minimal-trixie"}}'
kubectl get cluster pg-alt -n tag16 -o jsonpath='{.status.pgDataImageInfo}{"\n"}'
  • Minor and major upgrade = new image of the same distribution; a major upgrade runs offline via pg_upgrade --link, replicas get cloned anew.
  • Blue/green: a new cluster with bootstrap.initdb.import (type: microservice) from an externalClusters entry (challenge of Day 16).
Event (lab) Write gap Day
Primary’s Pod deleted about 12 s 16
Primary’s node dead about 1:14 min (50 s detection) 16
Primary and replica on a dead node over 6 min (tolerationSeconds: 300) 16
Switchover, rolling update, drain about 13 s 16

CNPG: operations (Day 17)

Pooler (abridged, complete in Day 17, Drill 1):

apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata: {name: pg-notes-pooler-rw, namespace: tag14}
spec:
  cluster: {name: pg-notes}
  instances: 1
  type: rw                                   # second pooler with type: ro
  pgbouncer:
    poolMode: transaction
    parameters: {max_client_conn: "200", default_pool_size: "10"}
kubectl exec -n tag14 deploy/pg-notes-pooler-rw -c pgbouncer -- \
  grep -E '^\* =|^pool_mode|^auth_(user|query|dbname)' \
  /controller/configs/pgbouncer.ini
kubectl exec -n tag14 deploy/pg-notes-pooler-rw -c pgbouncer -- \
  psql -h /controller/run -U pgbouncer pgbouncer -c 'SHOW POOLS'
kubectl patch pooler pg-notes-pooler-rw -n tag14 --type merge \
  -p '{"spec":{"pgbouncer":{"parameters":{"server_login_retry":"1"}}}}'

Metrics (instance 9187, pooler 9127) and Prometheus via the API server proxy:

kubectl get --raw /api/v1/namespaces/tag14/pods/pg-notes-1:9187/proxy/metrics \
  | grep -E '^cnpg_collector_(up|fencing_on|pg_wal_archive_status)'
P=/api/v1/namespaces/monitoring/services/kps-kube-prometheus-stack-prometheus:9090\
/proxy/api/v1
kubectl get --raw "$P/targets?state=active" | jq -r '.data.activeTargets[]
  | select(.labels.namespace=="tag14") | [.labels.pod, .health] | @tsv'
kubectl get --raw "$P/query?query=cnpg_pg_replication_lag" \
  | jq -r '.data.result[] | [.metric.pod, .value[1]] | @tsv'
kubectl label configmap cnpg-dashboard -n tag14 grafana_dashboard=1
kubectl get scrapeconfig -n monitoring       # target pg1:9187, label release: kps
  • PodMonitor for instances with cnpg.io/cluster and cnpg.io/podRole: instance (the poolers carry cnpg.io/cluster too), for poolers cnpg.io/poolerName with operator: Exists; each with port: metrics.

Declarative roles and databases (abridged, complete in Day 17, Drill 5):

apiVersion: postgresql.cnpg.io/v1
kind: DatabaseRole
metadata: {name: tag17-shop, namespace: tag14}
spec:
  cluster: {name: pg-notes}
  name: tag17_shop
  login: true
  databaseRoleReclaimPolicy: delete
  passwordSecret: {name: tag17-shop-pw}      # type basic-auth, username = role
# Database: spec {cluster, name: tag17_shop, owner: tag17_shop,
#           databaseReclaimPolicy: delete}
kubectl get databaserole,database -n tag14   # columns APPLIED and MESSAGE
kubectl patch cluster pg-notes -n tag14 --type merge -p '{"spec":{"managed":{"roles":[
  {"name":"tag17_monitor","ensure":"present","login":true,
   "inRoles":["pg_monitor"],"comment":"nur lesen"}]}}}'
kubectl patch cluster pg-notes -n tag14 --type merge --dry-run=server \
  -p '{"spec":{"postgresql":{"parameters":{"shared_buffers":"600MB"}}}}'
kubectl logs pg-notes-3 -n tag14 -c postgres --since=10m | jq -r 'select(
  .logger == "postgres" and .record.error_severity == "ERROR")
  | [.record.log_time, .record.user_name, .record.message] | @tsv'
kubectl cnpg logs cluster pg-notes -n tag14 --tail 200 \
  | kubectl cnpg logs pretty --loggers postgres
  • Your own server certificate: certificates.serverTLSSecret and serverCASecret (cert-manager Secret with the label cnpg.io/reload).
  • In the JSON log, level is always info; the severity is in record.error_severity. The webhook rejects fixed parameters (listen_addresses).

Connecting the application (Day 18)

kubectl get secret pg-notes-ca -n tag14 -o jsonpath='{.data.ca\.crt}' | base64 -d \
  > ~/pg/tag18/ca.crt
kubectl create secret generic pg-notes-ca -n tag18 \
  --from-file=ca.crt=$HOME/pg/tag18/ca.crt
TLS='sslmode=verify-full&sslrootcert=/etc/pg-ca/ca.crt'
OWNER=$(kubectl get secret pg-notes-app -n tag14 -o jsonpath='{.data.uri}' | base64 -d)
kubectl create secret generic notes-db-owner -n tag18 --from-literal=uri="$OWNER?$TLS"
kubectl get secret notes-db-api -n tag18 -o jsonpath='{.data.uri}' | base64 -d \
  | sed -E 's/:[^:@]+@/:***@/'; echo
kubectl logs job/notes-migrate -n tag18
kubectl delete pod -n tag14 -l cnpg.io/poolerName   # pick up the new certificate
kubectl cnpg psql pg-notes -n tag14 -- -c "SELECT usename, client_addr, ssl
  FROM pg_stat_activity JOIN pg_stat_ssl USING (pid) WHERE usename = 'tag18_api'"
cilium hubble port-forward >/dev/null &                # Cilium
hubble observe --from-namespace tag18 --verdict DROPPED --last 2
kill %1
  • Migration as a Job with the owner (notes_app), the app with the runtime role (GRANT SELECT, INSERT); notes-api reads the URI from NOTES_DB_URL_FILE.
  • Through the pooler, the server certificate needs the pooler names (certificates.serverAltDNSNames).
  • NetworkPolicy on cnpg.io/cluster: pg-notes: own namespace, cnpg-system, monitoring on 9187/9127, app Pods on 5432. With Cilium, kubectl cnpg status additionally needs a CiliumNetworkPolicy for port 8000 from kube-apiserver and remote-node.

Symptom → first command

Symptom First command Day
Connection refused / timeout pg_isready -h <ip>, then sudo ss -tlnp 0, 1
Login fails pg_hba_file_rules (column error), server log 3
Change has no effect SELECT name FROM pg_settings WHERE pending_restart 3
Query hangs pg_blocking_pids(pid) in pg_stat_activity 4, 7
Deadlock reported sudo grep -A5 'deadlock detected' <log> 4
Query slow EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) … 5
Estimate way off ANALYZE <table>, then pg_stats 5
Index invalid SELECT … FROM pg_index WHERE NOT indisvalid 5
Table grows despite VACUUM backend_xmin in pg_stat_activity 6
Many dead rows n_dead_tup in pg_stat_user_tables, pgstattuple 6
Who is costing the time? pg_stat_statements by total_exec_time 7
Restore fails on roles load pg_dumpall --globals-only 8
pg_wal fills up pg_stat_archiver, then pg_replication_slots 9, 10
Unsure whether a backup is intact pg_verifybackup <directory> 9
Standby lags behind replay_lag in pg_stat_replication 10
Commit hangs on SyncRep check synchronous_standby_names 10
Old primary won’t follow pg_rewind … --source-server=… 10
Subscription stalled pg_subscription, pg_stat_subscription_stats, log 11
Too many connections SHOW POOLS in the PgBouncer console 12
Upgrade aborts pg_upgrade … --check 13
CNPG cluster not healthy kubectl cnpg status <cluster> -n <ns> 14
WAL archive Failing kubectl logs <pod> -c plugin-barman-cloud 15
Recovery cluster rejected read the webhook message (backupID?) 15
Failover takes minutes kubectl get nodes, kubectl get pods -o wide 16
INSERT hangs with synchronous fencedInstances, wait_event = 'SyncRep' 16
DatabaseRole not applied kubectl get databaserole -n <ns> (MESSAGE) 17
App: certificate is valid for … openssl x509 -ext subjectAltName on -server 18
kubectl cnpg status: no primary check the NetworkPolicy for port 8000 18