Tag 7: Monitoring und Logs
Ziel: Du siehst, was der Server gerade tut und worauf er wartet, findest die teuersten Abfragen, liest die Logs gezielt und stellst Metriken für Prometheus bereit. · Dauer: ca. 70 min · Voraussetzung: Tag 3
Briefing
pg_stat_activityist die Momentaufnahme: wer, was, seit wann, worauf wartend.pg_stat_statementssummiert alle Abfragen – die wichtigste Erweiterung im Betrieb. Sie brauchtshared_preload_librariesund damit einen Neustart.- Die kumulativen Sichten (
pg_stat_database,pg_stat_checkpointer,pg_stat_io) zählen seit dem letzten Reset – interessant ist die Änderung über die Zeit. - Logs erzählen, was die Sichten vergessen: langsame Abfragen, Sperren, Anmeldungen.
postgres_exporterübersetzt das alles in Prometheus-Metriken.
pg1: PostgreSQL 18 --socket--> postgres_exporter :9187 <-- Prometheus (Tag 17)
Alle Befehle laufen auf pg1, außer es steht etwas
anderes dran.
Drill 1: pg_stat_activity und Wait Events
Testdaten für heute – pgbench mit Skalierung 10 sind
eine Million Konten:
createdb tag07
pgbench -i -s 10 -q tag07Erwartet (gekürzt):
creating primary keys...
done in 4.62 s (drop tables 0.00 s, create tables 0.01 s, client-side generate ...
Jetzt eine Sperre provozieren. Terminal 1:
psql -d tag07 -c 'BEGIN' \
-c 'UPDATE pgbench_branches SET bbalance = 0 WHERE bid = 1' \
-c 'SELECT pg_sleep(60)'Terminal 2 (bleibt hängen):
psql -d tag07 -c 'UPDATE pgbench_branches SET bbalance = 1 WHERE bid = 1'Terminal 3:
psql -d tag07 -c "SELECT pid, state, wait_event_type AS wtype, wait_event,
pg_blocking_pids(pid) AS blocked_by, left(query, 30) AS query
FROM pg_stat_activity WHERE datname = 'tag07' AND pid <> pg_backend_pid();"
psql -c "SELECT backend_type, count(*) FROM pg_stat_activity GROUP BY 1
ORDER BY 2 DESC;"Erwartet:
pid | state | wtype | wait_event | blocked_by | query
-------+--------+---------+---------------+------------+--------------------------------
21235 | active | Timeout | PgSleep | {} | SELECT pg_sleep(60)
21239 | active | Lock | transactionid | {21235} | UPDATE pgbench_branches SET bb
(2 rows)
backend_type | count
------------------------------+-------
client backend | 4
io worker | 3
autovacuum launcher | 1
background writer | 1
checkpointer | 1
logical replication launcher | 1
walwriter | 1
(7 rows)
wait_event_type = Lock heißt: Diese Sitzung wartet auf
eine andere, und pg_blocking_pids() nennt den Schuldigen.
Andere häufige Typen: IO (Platte), LWLock
(interne Sperren), Client (wartet auf die Anwendung). Die
io worker sind neu in PostgreSQL 18
(io_method = worker, asynchrones Lesen). Den Blocker
beenden:
psql -c "SELECT pg_terminate_backend(21235)"Erwartet: t, Terminal 2 meldet sofort
UPDATE 1.
Fortschritt speichern? Kostenlos registrieren
Drill 2: pg_stat_statements
psql -d tag07 -c 'CREATE EXTENSION pg_stat_statements'
psql -d tag07 -c 'SELECT count(*) FROM pg_stat_statements'Erwartet:
CREATE EXTENSION
ERROR: pg_stat_statements must be loaded via "shared_preload_libraries"
Die Erweiterung braucht Shared Memory, das nur beim Start verteilt wird:
psql -c "ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';"
sudo systemctl restart postgresql@18-main
psql -Atc 'SHOW shared_preload_libraries'
psql -d postgres -c 'CREATE EXTENSION pg_stat_statements'
psql -c "SELECT name, setting FROM pg_settings
WHERE name LIKE 'pg_stat_statements.%';"Erwartet:
ALTER SYSTEM
pg_stat_statements
CREATE EXTENSION
name | setting
-----------------------------------+---------
pg_stat_statements.max | 5000
pg_stat_statements.save | on
pg_stat_statements.track | top
pg_stat_statements.track_planning | off
pg_stat_statements.track_utility | on
(5 rows)
Achtung:
shared_preload_librariesist eine Liste. Wer späterauto_explainoderpg_cronergänzt, schreibt alle Namen hinein – sonst istpg_stat_statementsweg. Ein Tippfehler darin verhindert den Start des Servers.
Die Statistik ist für den ganzen Cluster gleich, die Sicht muss es
aber in jeder Datenbank geben, in der du sie abfragst (hier
tag07 und postgres für den Exporter). Last
erzeugen und auswerten:
psql -d tag07 -Atc 'SELECT pg_stat_statements_reset()'
pgbench -c 8 -j 4 -T 30 tag07 | grep -E '^tps|latency average'
psql -d tag07 -c 'SELECT count(*) FROM pgbench_accounts WHERE abalance > 0 AND bid = 3'
psql -d tag07 -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
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'tag07')
ORDER BY total_exec_time DESC LIMIT 5;"Erwartet (gekürzt):
latency average = 7.595 ms
tps = 1053.322341 (without initial connection time)
calls | total_ms | mean_ms | query
-------+----------+---------+------------------------------------------
31568 | 34796 | 1.10 | UPDATE pgbench_branches SET bbalance = b
31568 | 8370 | 0.27 | UPDATE pgbench_tellers SET tbalance = tb
31568 | 5506 | 0.17 | UPDATE pgbench_accounts SET abalance = a
31568 | 1299 | 0.04 | SELECT abalance FROM pgbench_accounts WH
31568 | 892 | 0.03 | INSERT INTO pgbench_history (tid, bid, a
(5 rows)
Zwei Blickwinkel: nach total_exec_time (was kostet den
Server insgesamt am meisten) und nach mean_exec_time (was
ist einzeln langsam):
psql -d tag07 -c "SELECT calls, round(mean_exec_time::numeric, 1) AS mean_ms,
shared_blks_hit AS hit, shared_blks_read AS read, left(query, 36) AS query
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = 'tag07')
ORDER BY mean_exec_time DESC LIMIT 3;"Erwartet (gekürzt):
calls | mean_ms | hit | read | query
-------+---------+------+-------+--------------------------------------
1 | 222.9 | 6235 | 10399 | SELECT count(*) FROM pgbench_account
1 | 1.6 | 33 | 6 | vacuum pgbench_branches
1 | 1.2 | 63 | 6 | vacuum pgbench_tellers
(3 rows)
Die Zählabfrage liest 10 399 Blöcke von außerhalb des Caches – ein
Fall für einen Index (Tag 5). Die UPDATE pgbench_branches
sind dagegen schnell, aber zahlreich und warten aufeinander: zehn
Zeilen, acht Clients.
Fortschritt speichern? Kostenlos registrieren
Drill 3: Kumulative Statistiken
psql -c "SELECT datname, numbackends AS conns, xact_commit, xact_rollback AS rollback,
round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS hit_pct,
deadlocks, temp_bytes FROM pg_stat_database WHERE datname IN ('notes', 'tag07');"Erwartet (gekürzt):
datname | conns | xact_commit | rollback | hit_pct | deadlocks | temp_bytes
---------+-------+-------------+----------+---------+-----------+------------
notes | 0 | 155 | 8 | 96.4 | 0 | 0
tag07 | 0 | 31673 | 2 | 93.6 | 0 | 20062208
(2 rows)
temp_bytes > 0: Sortierungen passten nicht in
work_mem und liefen auf Platte (hier beim Anlegen der
Indizes). Seit PostgreSQL 17 stehen die Checkpoints in einer eigenen
Sicht, pg_stat_bgwriter zählt nur noch den Background
Writer:
psql -x -c 'SELECT num_timed, num_requested, write_time, buffers_written
FROM pg_stat_checkpointer'
psql -c 'SELECT buffers_clean, maxwritten_clean, buffers_alloc FROM pg_stat_bgwriter'Erwartet (gekürzt):
-[ RECORD 1 ]---+------
num_timed | 1
num_requested | 27
write_time | 8944
buffers_written | 57223
buffers_clean | maxwritten_clean | buffers_alloc
---------------+------------------+---------------
27386 | 205 | 155308
(1 row)
Viele num_requested gegenüber num_timed
heißt: max_wal_size ist für die Schreiblast zu klein,
Checkpoints kommen nicht nach Zeitplan, sondern erzwungen.
pg_stat_io (seit 16) zeigt, wer wie viel liest und
schreibt; PostgreSQL 18 ergänzt Spalten in Bytes und die
WAL-Zugriffe:
psql -c "SELECT backend_type, object, context, pg_size_pretty(read_bytes) AS read,
pg_size_pretty(write_bytes) AS written FROM pg_stat_io
WHERE coalesce(read_bytes, 0) + coalesce(write_bytes, 0) > 0
ORDER BY coalesce(read_bytes, 0) + coalesce(write_bytes, 0) DESC LIMIT 6;"Erwartet (gekürzt, Werte schwanken):
backend_type | object | context | read | written
-------------------+----------+----------+---------+---------
client backend | wal | normal | 0 bytes | 1318 MB
background worker | relation | bulkread | 1232 MB | 22 MB
client backend | relation | bulkread | 908 MB | 7664 kB
client backend | wal | init | | 800 MB
walwriter | wal | normal | | 735 MB
client backend | relation | vacuum | 555 MB | 161 MB
(6 rows)
Zeiten (read_time, write_time) füllen sich
erst mit track_io_timing = on; das kostet auf moderner
Hardware kaum etwas und lohnt sich in Produktion.
Fortschritt speichern? Kostenlos registrieren
Drill 4: Log-Parameter
Alles mit Kontext sighup oder superuser –
ein Reload genügt. log_connections ist ab PostgreSQL 18
kein Schalter mehr, sondern eine Liste von Phasen:
psql <<'EOF'
ALTER SYSTEM SET log_min_duration_statement = '200ms';
ALTER SYSTEM SET log_connections = 'authorization,setup_durations';
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d %a ';
SELECT pg_reload_conf();
EOF
psql -d tag07 -Atc \
'SELECT count(*) FROM pgbench_accounts WHERE abalance > 0 AND bid = 3'Auf pg2:
PGPASSWORD=lab-notes-123 psql \
"host=172.16.0.182 dbname=notes user=notes_app application_name=tag07-app" \
-Atc 'SELECT count(*) FROM notes'Wieder auf pg1:
sudo tail -n 6 /var/log/postgresql/postgresql-18-main.logErwartet (gekürzt):
... [21559] ubuntu@tag07 psql LOG: duration: 210.510 ms statement: SELECT count(*)
FROM pgbench_accounts WHERE abalance > 0 AND bid = 3
... [21564] notes_app@notes [unknown] LOG: connection authorized: user=notes_app
database=notes application_name=tag07-app SSL enabled (protocol=TLSv1.3, ...)
... [21564] notes_app@notes tag07-app LOG: connection ready: setup total=78.762 ms,
fork=2.193 ms, authentication=40.541 ms
... [21564] notes_app@notes tag07-app LOG: disconnection: session time:
0:00:00.093 user=notes_app database=notes host=172.16.0.123 port=57728
%a im Präfix schreibt den application_name
in jede Zeile – Gold wert, wenn mehrere Dienste dieselbe Rolle nutzen.
setup_durations zeigt, wie lange der Verbindungsaufbau
dauert (übers Netz mit TLS und SCRAM deutlich länger als am Socket).
Jetzt eine Sperre, die länger als deadlock_timeout (1 s)
wartet:
psql -d tag07 -c 'BEGIN' -c 'LOCK pgbench_tellers' -c 'SELECT pg_sleep(3)' &
psql -d tag07 -c 'SELECT count(*) FROM pgbench_tellers'
wait
L=/var/log/postgresql/postgresql-18-main.log
sudo grep -E 'still waiting|Process holding' $L | tail -2Erwartet (gekürzt):
... LOG: process 21739 still waiting for AccessShareLock on relation 16548 of
database 16544 after 1000.495 ms at character 22
... DETAIL: Process holding the lock: 21736. Wait queue: 21739.
auto_explain schreibt den Plan langsamer Abfragen ins
Log. Als Superuser lässt es sich ohne Neustart für eine Sitzung
laden:
psql -d tag07 <<'EOF'
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = on;
SELECT count(*) FROM pgbench_accounts WHERE abalance > 0 AND bid = 3;
EOF
sudo grep -A6 'plan:' /var/log/postgresql/postgresql-18-main.log | tail -7Erwartet (gekürzt):
... ubuntu@tag07 psql LOG: duration: 223.642 ms plan:
Query Text: SELECT count(*) FROM pgbench_accounts WHERE abalance > 0 AND bid = 3;
Aggregate (cost=23975.60..23975.61 rows=1 width=8) (actual time=216.593..223.611 ...
-> Gather (cost=1000.00..23975.60 rows=1 width=0) (actual time=53.566..223.38 ...
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on pgbench_accounts (cost=0.00..22975.50 rows=1 ...
Dauerhaft für alle Sitzungen gehört auto_explain in
shared_preload_libraries (Neustart).
log_analyze misst jede betroffene Abfrage – im Betrieb mit
Bedacht.
Fortschritt speichern? Kostenlos registrieren
Drill 5: Logs finden und lesen
pg_lsclusters
psql -Atc 'SHOW logging_collector' -c 'SHOW log_destination'
journalctl -u postgresql@18-main --no-pager -n 2
cat /etc/logrotate.d/postgresql-commonErwartet (gekürzt):
Ver Cluster Port Status Owner Data directory Log file
18 main 5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/po...
off
stderr
... systemd[1]: Starting [email protected] - PostgreSQL Cluster 18-main...
... systemd[1]: Started [email protected] - PostgreSQL Cluster 18-main.
/var/log/postgresql/*.log {
weekly
rotate 10
copytruncate
...
Auf Ubuntu schreibt pg_ctlcluster stderr in
/var/log/postgresql/postgresql-18-main.log (Spalte „Log
file“), logrotate rotiert wöchentlich. Im Journal stehen nur Start und
Stopp. Typische Suchen:
L=/var/log/postgresql/postgresql-18-main.log
sudo grep -cE 'duration: [0-9.]+ ms statement' $L
sudo grep -E 'FATAL|ERROR' $L | tail -3Erwartet (gekürzt):
6
... ubuntu@tag07 FATAL: terminating connection due to administrator command
Fortschritt speichern? Kostenlos registrieren
Drill 6: postgres_exporter
Der Exporter kommt als statische Binärdatei von GitHub. Version und Architektur als Variablen, die Prüfsumme wird mitgeprüft:
V=0.21.1
ARCH=$(dpkg --print-architecture)
U=https://github.com/prometheus-community/postgres_exporter/releases/download/v$V
cd /tmp
curl -fsSLO $U/postgres_exporter-$V.linux-$ARCH.tar.gz
curl -fsSLO $U/sha256sums.txt
sha256sum --ignore-missing -c sha256sums.txt
tar xzf postgres_exporter-$V.linux-$ARCH.tar.gz
sudo install -m 755 postgres_exporter-$V.linux-$ARCH/postgres_exporter /usr/local/bin/
postgres_exporter --version 2>&1 | head -1Erwartet:
postgres_exporter-0.21.1.linux-amd64.tar.gz: OK
postgres_exporter, version 0.21.1 (branch: HEAD, revision: 05ec47a115a2c5d8...)
Eigener Linux-Benutzer und gleichnamige Rolle mit der Rolle
pg_monitor – dann reicht peer am Socket, kein
Passwort, kein Superuser:
sudo useradd --system --no-create-home --shell /usr/sbin/nologin postgres_exporter
psql -c 'CREATE ROLE postgres_exporter LOGIN' -c 'GRANT pg_monitor TO postgres_exporter'
sudo tee /etc/systemd/system/postgres_exporter.service <<'EOF'
[Unit]
Description=Prometheus exporter for PostgreSQL
After=network-online.target postgresql.service
[Service]
User=postgres_exporter
Environment="DATA_SOURCE_NAME=host=/var/run/postgresql\
user=postgres_exporter dbname=postgres"
ExecStart=/usr/local/bin/postgres_exporter --collector.stat_statements
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now postgres_exporterAchtung: Die Anführungszeichen um
Environment=sind Pflicht. Ohne sie zerlegt systemd den Wert an den Leerzeichen, der Exporter sieht nurhost=…und meldetdatabase "postgres_exporter" does not exist. Der\am Zeilenende verbindet die Zeilen mit genau einem Leerzeichen – steht davor noch eins, meldet der Exportermalformed dsn.
curl -s localhost:9187/metrics | grep -E '^pg_up |datname="tag07"' \
| grep -E 'pg_up|xact_commit'
curl -s localhost:9187/metrics | grep -oE '^pg_[a-z]+_[a-z]+' | sort | uniq -c \
| sort -rn | head -4Erwartet (gekürzt):
pg_stat_database_xact_commit{datid="16544",datname="tag07"} 31702
pg_up 1
480 pg_stat_statements
240 pg_stat_database
120 pg_stat_activity
90 pg_locks_count
pg_up 1 heißt: Der Exporter erreicht die Datenbank.
Fehlt die Erweiterung in der Datenbank postgres, steht im
Journal function pg_stat_statements(boolean) does not exist
– der Rest läuft trotzdem. Der Exporter bleibt stehen: In Tag 17 holt
sich der Prometheus im Cluster die Metriken von pg1 ab.
Fortschritt speichern? Kostenlos registrieren
Übung: Speed-Drills
- Zeige alle Sitzungen, die gerade auf eine Sperre warten, mit ihrem Blocker. Zeitziel: 45 s
- Zeige die drei Abfragen mit der höchsten Gesamtlaufzeit im ganzen Cluster. Zeitziel: 45 s
- Zeige die Cache-Trefferquote der Datenbank
notes. Zeitziel: 45 s - Zeige, ob
pg_stat_statementsgeladen ist. Zeitziel: 10 s
Lösung
psql -c "SELECT pid, pg_blocking_pids(pid), left(query, 40) FROM pg_stat_activity
WHERE wait_event_type = 'Lock';"
psql -d tag07 -c "SELECT round(total_exec_time) AS ms, left(query, 50)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;"
psql -Atc "SELECT round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1)
FROM pg_stat_database WHERE datname = 'notes';"
psql -Atc 'SHOW shared_preload_libraries'Erwartet (gekürzt):
pid | pg_blocking_pids | left
-----+------------------+------
(0 rows)
...
96.4
pg_stat_statements
Fortschritt speichern? Kostenlos registrieren
Kontrollpunkt
psql -Atc 'SHOW shared_preload_libraries'Erwartet: pg_stat_statements
psql -d postgres -Atc "SELECT extversion FROM pg_extension
WHERE extname = 'pg_stat_statements'"Erwartet: 1.12
psql -Atc 'SHOW log_min_duration_statement'Erwartet: 200ms (Drill 4; das Aufräumen
setzt es zurück).
systemctl is-active postgres_exporterErwartet: active
curl -s localhost:9187/metrics | grep '^pg_up 'Erwartet: pg_up 1
Challenge
Langsame Abfragen sollen nur für die Datenbank tag07 ab
100 ms ins Log – global bleibt log_min_duration_statement
aus, und es gibt keinen Reload.
Hinweis: Datenbankweite Einstellungen gelten ab der nächsten Verbindung.
Lösung
psql -c "ALTER DATABASE tag07 SET log_min_duration_statement = '100ms'"
psql -d tag07 -Atc \
'SELECT count(*) FROM pgbench_accounts WHERE bid = 2 AND abalance < 0'
psql -d notes -Atc 'SELECT count(*), pg_sleep(0.3) FROM notes'
sudo tail -n 1 /var/log/postgresql/postgresql-18-main.logErwartet (gekürzt):
1487
10004|
... ubuntu@tag07 LOG: duration: 142.231 ms statement: SELECT count(*) FROM
pgbench_accounts WHERE bid = 2 AND abalance < 0
Die 300-ms-Abfrage in notes taucht nicht auf. So bekommt
eine verdächtige Anwendung ein genaueres Log, ohne dass alle anderen das
Log fluten.
Fortschritt speichern? Kostenlos registrieren
Aufräumen
Log-Parameter zurück, pg_stat_statements und Exporter
bleiben:
psql <<'EOF'
ALTER SYSTEM RESET log_min_duration_statement;
ALTER SYSTEM RESET log_connections;
ALTER SYSTEM RESET log_disconnections;
ALTER SYSTEM RESET log_lock_waits;
ALTER SYSTEM RESET log_line_prefix;
SELECT pg_reload_conf();
EOF
psql -c 'ALTER DATABASE tag07 RESET log_min_duration_statement'
dropdb tag07Interview-Notiz
- „Die Datenbank ist langsam – was schaust du zuerst an?“ –
pg_stat_activity(wer wartet worauf,pg_blocking_pids), dannpg_stat_statementsnach Gesamt- und Einzelzeit, dann Logs mitlog_min_duration_statement. - „Warum braucht pg_stat_statements einen Neustart?“ – Es reserviert
Shared Memory beim Start über
shared_preload_libraries;CREATE EXTENSIONlegt nur die Sicht an. - „Wie misst du Cache-Effizienz?“ –
blks_hit / (blks_hit + blks_read)auspg_stat_databaseoder pro Abfrage auspg_stat_statements; bei OLTP meist > 99 %.
Enterprise-Notiz
pg_stat_statementsundtrack_io_timing = onauf jedem Server von Anfang an; Abfragetexte können Daten enthalten – Zugriff aufpg_read_all_statsbeschränken.- Logs zentral sammeln (Vector, Fluent Bit, Loki/Elastic),
log_line_prefixmit Zeit, PID, Benutzer, Datenbank und Anwendung;log_destination = 'jsonlog'ab 15 erleichtert das Parsen. - Alarme auf
pg_up, Replikationsverzug, Verbindungsauslastung (numbackendsgegenmax_connections), lange Transaktionen, Wraparound-Abstand und Plattenfüllstand. - Exporter-Endpunkt nicht offen ins Netz: Firewall oder
--web.config.filemit TLS und Basic Auth. log_connectionserzeugt viel Volumen; mit Pooler davor ist es oft nur noch auf dem Pooler sinnvoll.