Zum Inhalt springen
Trainingslager IT-Trainingslager

Anhang A: Spickzettel

Die wichtigsten Befehle und Schnipsel aus Tag 0 bis 18, nach Thema geordnet. Die Tage stehen in Klammern. Platzhalter in spitzen Klammern ersetzt du; IPs sind die des Labors (pg1 172.16.0.182, pg2 .123, cp1 .126). Passwörter wie lab-notes-123 sind Lab-Werte. Ohne Hostangabe läuft ein Befehl auf pg1, ab Tag 14 auf cp1.

Installation und Cluster-Verwaltung (Tag 0, 3, 13)

PGDG-Repository architekturneutral einbinden und nur Server plus Client installieren (nicht postgresql-contrib – das zieht Ubuntus PG 16 nach):

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, Pfade
sudo pg_createcluster 18 test --start         # nächster freier Port (5433 ...)
sudo pg_ctlcluster 18 main restart            # oder: start | stop
sudo systemctl restart postgresql@18-main     # dasselbe per 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   # nur Labor
createdb                                      # DB mit Rollennamen für psql ohne -d
sudo tail -n 3 /var/log/postgresql/postgresql-18-main.log
Pfad (Debian/Ubuntu) Inhalt
/etc/postgresql/18/main/ postgresql.conf, pg_hba.conf, conf.d/
/var/lib/postgresql/18/main/ Datenverzeichnis, postgresql.auto.conf
/var/log/postgresql/postgresql-18-main.log Server-Log
/usr/lib/postgresql/18/bin/ pg_upgrade, pg_rewind, pg_verifybackup …

psql-Handwerk (Tag 1)

Meta-Befehl Wirkung Tag
\l, \dn, \dt+ s.*, \d+ t Datenbanken, Schemas, Tabellen, Details 1
\du, \drg, \df s.*, \sv v Rollen, Mitgliedschaften, Funktionen, View 1, 2
\dp t, \ddp, \drds, \dn+ Rechte, Default-Rechte, Rollen-Settings 2, 3
\di+ ix_*, \dx, \dRp+ p Indizes, Extensions, Publication 5, 11, 13
\x auto, \timing on, \conninfo Ausgabe, Laufzeit, Verbindung samt TLS 0, 1
\i, \o, \!, \e Datei ausführen, umleiten, Shell, Editor 1
\gset, \echo :var, \if … \endif Ergebnis in Variable, Bedingungen 1
\watch i=1 c=3 Abfrage wiederholen (Intervall, Anzahl) 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: ohne ~/.psqlrc
psql -X -v ON_ERROR_STOP=1 -d tag01 -f pruefung.sql   # Abbruch beim ersten Fehler
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 = nimmt an, 2 = keine Antwort

Passwort- und Service-Datei (auf pg2; PGPASSWORD schlägt .pgpass):

echo '172.16.0.182:5432:notes:notes_app:lab-notes-123' > ~/.pgpass
chmod 600 ~/.pgpass                           # sonst ignoriert libpq die Datei
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'   # Einzelwert überschreiben

Datenbanken, Rollen und Rechte (Tag 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: Rechte erst nach 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;   -- vordefinierte Rollen
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;                -- Rechte und Default-Rechte weg
DROP ROLE tag02_schreiben;
  • Default Privileges gelten nur für Objekte der Rolle aus FOR ROLE.
  • Seit PG 15 darf PUBLIC im Schema public nichts anlegen; Owner ist pg_database_owner. Darum scheitert notes-api migrate als Nicht-Owner.

Konfiguration (Tag 3)

context sagt, was eine Änderung braucht: user (sofort, pro Sitzung), sighup (Reload), postmaster (Neustart).

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';"  # landet 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()'   # Neustart nachweisen
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';             -- nur die laufende Abfrage
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
  WHERE usename = 'tag03_report';             -- ganze Verbindung

pg_hba.conf, SCRAM und TLS (Tag 0, 3)

Erste passende Zeile gewinnt; ein Reload reicht:

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 = '*';"   # Neustart nötig
psql -Atc "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%'"
psql -c "ALTER ROLE tag03_alt PASSWORD 'lab-tag03'"  # neu setzen = SCRAM-Hash

Eigene CA, Server-Zertifikat, Prüfung von 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-Dateien: Reload reicht
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'

Transaktionen, MVCC und Sperren (Tag 4)

SELECT ctid, xmin, xmax, * FROM konto ORDER BY id;   -- Zeilenversionen
SELECT pg_current_xact_id(), pg_current_snapshot();
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET lock_timeout = '2s';                     -- DDL wartet nicht ewig
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   -- nur Wurzel-Blocker
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 und Indizes (Tag 5)

EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM bestellung WHERE kunde_id = 4711;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) SELECT ...;   -- BUFFERS ab 18 automatisch
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);   -- ohne Schreibsperre
DROP INDEX CONCURRENTLY ix_kunde;
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;   -- Reste
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;                                -- Schätzung falsch? Statistik neu
  • CREATE INDEX nimmt ShareLock (Lesen ja, Schreiben nein), CONCURRENTLY nur ShareUpdateExclusiveLock.
  • kunde_id + 0 = 4711 oder lower(email) ohne Ausdrucksindex: kein Index-Scan.

VACUUM, Autovacuum und Bloat (Tag 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;                      -- Platz wiederverwendbar, keine Sperre
VACUUM (FULL, VERBOSE) lager;                -- schrumpft, AccessExclusiveLock!
ALTER TABLE lager SET (autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000);
ALTER TABLE lager SET (fillfactor = 80);     -- Platz für 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;  -- bremst 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  # online schrumpfen
psql -d tag06 -c 'CREATE EXTENSION pg_repack'
pg_repack -d tag06 -t lager

Monitoring und Logs (Tag 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 braucht shared_preload_libraries und damit einen Neustart:

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                    # Testdaten, eine Million Konten
pgbench -c 8 -j 4 -T 30 tag07 | grep -E '^tps|latency average'

Logs (alles per Reload) und 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';   -- nur eine DB
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 mit $ARCH, eigene Unit
curl -s localhost:9187/metrics | grep '^pg_up '

Logische Backups (Tag 8)

pg_dump -d tag08 -f tag08.sql                 # Plain-SQL, Restore mit psql
pg_dump -d tag08 -Fc -f tag08.dump            # Custom, Restore mit pg_restore
pg_dump -d tag08 -Fd -j 4 -f tag08.dir        # Verzeichnis, parallel
pg_dump -d tag08 -Fc -Z zstd -f tag08-zstd.dump
pg_dumpall --globals-only -f globals.sql      # Rollen und Tablespaces
pg_dumpall --roles-only --no-role-passwords -f /tmp/roles.sql
pg_restore -l tag08.dump | grep -v '^;'       # Inhaltsverzeichnis
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     # eine Tabelle
pg_restore -l tag08.dump | grep -E 'SCHEMA - audit|audit log' > audit.list
pg_restore -d tag08_restore -L audit.list tag08.dump       # nach Liste
pg_restore -d tag08_restore --clean --if-exists -j 4 tag08.dump   # überschreiben
pg_restore -d tag08_schema -s -L app.list tag08.dump       # nur Schema
sudo -u postgres pg_restore -d tag08_notes --no-owner --no-acl < notes.dump
  • Rollen sind global und nicht im Dump: erst globals.sql, dann der Restore – oder --no-owner --no-acl. pg_dump 18 sichert Statistiken nur mit --statistics.

Physische Backups und PITR (Tag 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, danach 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: WAL nicht prüfen

WAL-Archiv (Neustart für 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 in einen eigenen Cluster (auf 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()'   # befördern
  • Zielzeit mit Bruchteilen übernehmen; Recovery stoppt vor dem ersten Commit danach.
  • Die postgresql.auto.conf reist mit – fremde ssl_-Zeilen vor dem Start entfernen.

Inkrementell (PG 17+, summarize_wal = on per 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 (Tag 9)

Repository auf pg2, Datenbank auf pg1, SSH zwischen den postgres-Benutzern. Auf 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

Auf pg1 (/etc/pgbackrest.conf, dazu 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    # auf 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-Replikation (Tag 10)

psql -c "CREATE ROLE tag10_repl LOGIN REPLICATION PASSWORD 'lab-repl-123';"
# für pg_rewind ohne Superuser: GRANT EXECUTE auf pg_ls_dir, pg_stat_file,
# pg_read_binary_file (beide Varianten); pg_hba: replication und 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()'

Synchron, Failover, Rücktausch:

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')"     # Notausgang
sudo -u postgres psql -p 5433 -Atc 'SELECT pg_promote()'   # pg2 wird 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 braucht Prüfsummen (ab 18 Standard) oder wal_log_hints; fehlt dem alten Primary WAL, erst per rsync aus pg_wal der Quelle holen.
  • Ein verwaister Slot sammelt WAL, bis die Platte voll ist.

Logische Replikation (Tag 11)

-- pg1, nach wal_level = logical (Neustart)
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;    -- oder USING INDEX <unique-index>
ALTER PUBLICATION tag11_best ADD TABLE notiz;
-- pg2, Schema vorher per pg_dump -s ... | psql anlegen
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 = fertig
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 aus dem Log
ALTER SUBSCRIPTION tag11_sub ENABLE;          -- oder DISABLE
ALTER SUBSCRIPTION tag11_sub REFRESH PUBLICATION;
DROP SUBSCRIPTION tag11_sub;                  -- entfernt den Slot auf 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"
  • Repliziert werden nur Daten: Schema, Sequenzen und DDL bleiben Handarbeit.

PgBouncer (Tag 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   # Klartext für 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'     # zur Laufzeit
psql "$A" -c 'RECONNECT tag12'               # Server-Verbindungen erneuern
sudo systemctl reload pgbouncer
pgbench -h 127.0.0.1 -p 6432 -U tag12_app -C -S -c 20 -j 4 -T 20 tag12
Modus Server-Verbindung gehört dem Client Bricht
session für die ganze Sitzung nichts, bündelt aber kaum
transaction pro Transaktion SET, SQL-PREPARE, Advisory Locks
statement pro Anweisung jede Transaktion mit mehreren Anweisungen

Major-Upgrade (Tag 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   # conf.d/ von Hand nachziehen
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  # dann --link
sudo -u postgres /usr/lib/postgresql/17/bin/pg_checksums --enable \
  -D /var/lib/postgresql/17/zwei             # Quelle ohne Checksums, Ziel 18 mit
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
  • Ab 18 übernimmt pg_upgrade die Planerstatistiken (ohne erweiterte); die Zähler in pg_stat_user_tables beginnen bei 0.
  • --link: kein Rückweg nach dem Start des neuen Clusters – vorher ein Backup.

CloudNativePG: Grundlagen (Tag 14)

mkdir -p ~/pg/tag14 && cp ~/.kube/config ~/pg/tag14/.kc   # Kopie pro Tag
export KUBECONFIG=~/pg/tag14/.kc
cmctl check api                               # cert-manager bereit?
helm list -A                                  # cert-manager und 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 (gekürzt, vollständig in Tag 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 und aktueller 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 weg: Failover in ~15 s
kubectl get cluster pg-notes -n tag14 \
  -o jsonpath='{.status.currentPrimary} {.status.phase}{"\n"}'
Service Ziel Tag
pg-notes-rw nur der Primary 14
pg-notes-ro nur die Replicas 14
pg-notes-r alle Instanzen 14
pg-notes-pooler-rw / -ro PgBouncer vor -rw / -ro 17

CNPG: Backups und Wiederherstellung (Tag 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 braucht 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 und ScheduledBackup (gekürzt, vollständig in Tag 15, Drill 3 und 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 * * *"                    # sechs Felder, zuerst Sekunden
  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 & Co.

PITR immer in einen neuen Cluster (gekürzt, vollständig in Tag 15, Drill 6):

  bootstrap:
    recovery:
      source: pg-notes-backup
      recoveryTarget:
        targetTime: "2026-11-19 05:17:04.88945+00"
        # oder 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}
  • Den ObjectStore samt Secret im Ziel-Namespace kopieren – er wirkt nur lokal.
  • Restore-Point: SELECT pg_create_restore_point('vor-preisupdate'), danach pg_switch_wal(). Bei targetName/targetXID verlangt der Webhook backupID.
  • Soll ein Klon selbst archivieren, braucht er einen anderen serverName.

CNPG: Hochverfügbarkeit (Tag 16)

kubectl cnpg promote pg-notes pg-notes-2 -n tag14        # Switchover
kubectl cnpg restart pg-notes pg-notes-1 -n tag14        # eine Instanz
kubectl cnpg fencing on pg-notes pg-notes-3 -n tag14     # Postgres aus, Pod bleibt
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- und Major-Upgrade = neues Image derselben Distribution; Major läuft offline per pg_upgrade --link, Replicas werden neu geklont.
  • Blue/green: neuer Cluster mit bootstrap.initdb.import (type: microservice) aus einem externalClusters-Eintrag (Challenge von Tag 16).
Ereignis (Labor) Schreiblücke Tag
Pod des Primary gelöscht rund 12 s 16
Node des Primary tot rund 1:14 min (50 s Erkennung) 16
Primary und Replica auf totem Node über 6 min (tolerationSeconds: 300) 16
Switchover, Rolling Update, Drain rund 13 s 16

CNPG: Betrieb (Tag 17)

Pooler (gekürzt, vollständig in Tag 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                                   # zweiter Pooler mit 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"}}}}'

Metriken (Instanz 9187, Pooler 9127) und Prometheus über den 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       # Ziel pg1:9187, Label release: kps
  • PodMonitor für Instanzen mit cnpg.io/cluster und cnpg.io/podRole: instance (die Pooler tragen auch cnpg.io/cluster), für Pooler cnpg.io/poolerName mit operator: Exists; jeweils port: metrics.

Rollen und Datenbanken deklarativ (gekürzt, vollständig in Tag 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}      # Typ basic-auth, username = Rolle
# Database: spec {cluster, name: tag17_shop, owner: tag17_shop,
#           databaseReclaimPolicy: delete}
kubectl get databaserole,database -n tag14   # Spalten APPLIED und 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
  • Eigenes Server-Zertifikat: certificates.serverTLSSecret und serverCASecret (cert-manager-Secret mit Label cnpg.io/reload).
  • Im JSON-Log ist level immer info; der Schweregrad steht in record.error_severity. Feste Parameter (listen_addresses) lehnt der Webhook ab.

Anwendung anbinden (Tag 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   # neues Zertifikat übernehmen
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 als Job mit dem Owner (notes_app), App mit Laufzeitrolle (GRANT SELECT, INSERT); notes-api liest die URI aus NOTES_DB_URL_FILE.
  • Über den Pooler braucht das Server-Zertifikat die Pooler-Namen (certificates.serverAltDNSNames).
  • NetworkPolicy auf cnpg.io/cluster: pg-notes: eigener Namespace, cnpg-system, Monitoring auf 9187/9127, App-Pods auf 5432. Mit Cilium braucht kubectl cnpg status zusätzlich eine CiliumNetworkPolicy für Port 8000 aus kube-apiserver und remote-node.

Symptom → erster Befehl

Symptom Erster Befehl Tag
Verbindung abgelehnt / Timeout pg_isready -h <ip>, dann sudo ss -tlnp 0, 1
Anmeldung scheitert pg_hba_file_rules (Spalte error), Server-Log 3
Änderung wirkt nicht SELECT name FROM pg_settings WHERE pending_restart 3
Abfrage hängt pg_blocking_pids(pid) in pg_stat_activity 4, 7
Deadlock gemeldet sudo grep -A5 'deadlock detected' <log> 4
Abfrage langsam EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF) … 5
Schätzung weit daneben ANALYZE <tabelle>, dann pg_stats 5
Index ungültig SELECT … FROM pg_index WHERE NOT indisvalid 5
Tabelle wächst trotz VACUUM backend_xmin in pg_stat_activity 6
Viele tote Zeilen n_dead_tup in pg_stat_user_tables, pgstattuple 6
Wer kostet die Zeit? pg_stat_statements nach total_exec_time 7
Restore scheitert an Rollen pg_dumpall --globals-only einspielen 8
pg_wal läuft voll pg_stat_archiver, dann pg_replication_slots 9, 10
Backup unklar, ob heil pg_verifybackup <verzeichnis> 9
Standby hängt hinterher replay_lag in pg_stat_replication 10
Commit hängt auf SyncRep synchronous_standby_names prüfen 10
Alter Primary will nicht folgen pg_rewind … --source-server=… 10
Subscription steht pg_subscription, pg_stat_subscription_stats, Log 11
Zu viele Verbindungen SHOW POOLS in der PgBouncer-Konsole 12
Upgrade bricht ab pg_upgrade … --check 13
CNPG-Cluster nicht gesund kubectl cnpg status <cluster> -n <ns> 14
WAL-Archiv Failing kubectl logs <pod> -c plugin-barman-cloud 15
Recovery-Cluster abgelehnt Webhook-Meldung lesen (backupID?) 15
Failover dauert Minuten kubectl get nodes, kubectl get pods -o wide 16
INSERT hängt bei synchronous fencedInstances, wait_event = 'SyncRep' 16
DatabaseRole nicht angewendet kubectl get databaserole -n <ns> (MESSAGE) 17
App: certificate is valid for … openssl x509 -ext subjectAltName auf -server 18
kubectl cnpg status: kein Primary NetworkPolicy für Port 8000 prüfen 18