Zum Inhalt springen
Trainingslager IT-Trainingslager

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_activity ist die Momentaufnahme: wer, was, seit wann, worauf wartend.
  • pg_stat_statements summiert alle Abfragen – die wichtigste Erweiterung im Betrieb. Sie braucht shared_preload_libraries und 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 tag07

Erwartet (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_libraries ist eine Liste. Wer später auto_explain oder pg_cron ergänzt, schreibt alle Namen hinein – sonst ist pg_stat_statements weg. 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.log

Erwartet (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 -2

Erwartet (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 -7

Erwartet (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-common

Erwartet (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 -3

Erwartet (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 -1

Erwartet:

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_exporter

Achtung: Die Anführungszeichen um Environment= sind Pflicht. Ohne sie zerlegt systemd den Wert an den Leerzeichen, der Exporter sieht nur host=… und meldet database "postgres_exporter" does not exist. Der \ am Zeilenende verbindet die Zeilen mit genau einem Leerzeichen – steht davor noch eins, meldet der Exporter malformed 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 -4

Erwartet (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

  1. Zeige alle Sitzungen, die gerade auf eine Sperre warten, mit ihrem Blocker. Zeitziel: 45 s
  2. Zeige die drei Abfragen mit der höchsten Gesamtlaufzeit im ganzen Cluster. Zeitziel: 45 s
  3. Zeige die Cache-Trefferquote der Datenbank notes. Zeitziel: 45 s
  4. Zeige, ob pg_stat_statements geladen 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_exporter

Erwartet: 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.log

Erwartet (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 tag07

Interview-Notiz

  • „Die Datenbank ist langsam – was schaust du zuerst an?“ – pg_stat_activity (wer wartet worauf, pg_blocking_pids), dann pg_stat_statements nach Gesamt- und Einzelzeit, dann Logs mit log_min_duration_statement.
  • „Warum braucht pg_stat_statements einen Neustart?“ – Es reserviert Shared Memory beim Start über shared_preload_libraries; CREATE EXTENSION legt nur die Sicht an.
  • „Wie misst du Cache-Effizienz?“ – blks_hit / (blks_hit + blks_read) aus pg_stat_database oder pro Abfrage aus pg_stat_statements; bei OLTP meist > 99 %.

Enterprise-Notiz

  • pg_stat_statements und track_io_timing = on auf jedem Server von Anfang an; Abfragetexte können Daten enthalten – Zugriff auf pg_read_all_stats beschränken.
  • Logs zentral sammeln (Vector, Fluent Bit, Loki/Elastic), log_line_prefix mit Zeit, PID, Benutzer, Datenbank und Anwendung; log_destination = 'jsonlog' ab 15 erleichtert das Parsen.
  • Alarme auf pg_up, Replikationsverzug, Verbindungsauslastung (numbackends gegen max_connections), lange Transaktionen, Wraparound-Abstand und Plattenfüllstand.
  • Exporter-Endpunkt nicht offen ins Netz: Firewall oder --web.config.file mit TLS und Basic Auth.
  • log_connections erzeugt viel Volumen; mit Pooler davor ist es oft nur noch auf dem Pooler sinnvoll.