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-18pg_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 responsePassword 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 valueDatabases, 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 ispg_database_owner. That’s whynotes-api migratefails 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 connectionpg_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-256psql -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 hashYour 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 statisticsCREATE INDEXtakes aShareLock(reads yes, writes no),CONCURRENTLYonly aShareUpdateExclusiveLock.kunde_id + 0 = 4711orlower(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 lagerMonitoring 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_dump18 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 WALWAL 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_statusPoint-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.conftravels along – remove foreignssl_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/kombipgBackRest (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/mainOn 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/mainsudo -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 restoreStreaming 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_conninfopsql -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_rewindneeds checksums (default from 18) orwal_log_hints; if the old primary is missing WAL, first fetch it viarsyncfrom the source’spg_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 pg1sudo 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=sessionecho '"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_upgradecarries over the planner statistics (without extended ones); the counters inpg_stat_user_tablesstart 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-123ObjectStore 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
ObjectStoretogether with its Secret into the target namespace – it only works locally. - Restore point:
SELECT pg_create_restore_point('vor-preisupdate'), thenpg_switch_wal(). WithtargetName/targetXID, the webhook requiresbackupID. - 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 anexternalClustersentry (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: kpsPodMonitorfor instances withcnpg.io/clusterandcnpg.io/podRole: instance(the poolers carrycnpg.io/clustertoo), for poolerscnpg.io/poolerNamewithoperator: Exists; each withport: 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.serverTLSSecretandserverCASecret(cert-manager Secret with the labelcnpg.io/reload). - In the JSON log,
levelis alwaysinfo; the severity is inrecord.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 fromNOTES_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 statusadditionally needs aCiliumNetworkPolicyfor port 8000 fromkube-apiserverandremote-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 |