Skip to content
Hands-on IT IT Drills

Day 7: Monitoring and Logs

Goal: You see what the server is doing right now and what it’s waiting for, find the most expensive queries, read the logs in a targeted way and deliver metrics to Prometheus. · Duration: approx. 70 min · Prerequisite: Day 3

Briefing

  • pg_stat_activity is the snapshot: who, what, since when, waiting on what.
  • pg_stat_statements aggregates all queries – the most important extension in operations. It needs shared_preload_libraries and therefore a restart.
  • The cumulative views (pg_stat_database, pg_stat_checkpointer, pg_stat_io) count since the last reset – what’s interesting is the change over time.
  • Logs tell what the views forget: slow queries, locks, logins.
  • postgres_exporter translates all of this into Prometheus metrics.
  pg1: PostgreSQL 18 --socket--> postgres_exporter :9187 <-- Prometheus (Day 17)

All commands run on pg1 unless stated otherwise.

Drill 1: pg_stat_activity and wait events

Test data for today – pgbench with scale factor 10 means one million accounts:

createdb tag07
pgbench -i -s 10 -q tag07

Expected (abridged):

creating primary keys...
done in 4.62 s (drop tables 0.00 s, create tables 0.01 s, client-side generate ...

Now provoke a lock. Terminal 1:

psql -d tag07 -c 'BEGIN' \
  -c 'UPDATE pgbench_branches SET bbalance = 0 WHERE bid = 1' \
  -c 'SELECT pg_sleep(60)'

Terminal 2 (hangs):

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;"

Expected:

  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 means: this session is waiting for another one, and pg_blocking_pids() names the culprit. Other common types: IO (disk), LWLock (internal locks), Client (waiting for the application). The io worker processes are new in PostgreSQL 18 (io_method = worker, asynchronous reads). Terminate the blocker:

psql -c "SELECT pg_terminate_backend(21235)"

Expected: t, Terminal 2 immediately reports UPDATE 1.

Save your progress? Sign up for free

Drill 2: pg_stat_statements

psql -d tag07 -c 'CREATE EXTENSION pg_stat_statements'
psql -d tag07 -c 'SELECT count(*) FROM pg_stat_statements'

Expected:

CREATE EXTENSION
ERROR:  pg_stat_statements must be loaded via "shared_preload_libraries"

The extension needs shared memory, which is only allocated at startup:

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.%';"

Expected:

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)

Warning: shared_preload_libraries is a list. If you later add auto_explain or pg_cron, write all names into it – otherwise pg_stat_statements is gone. A typo in it prevents the server from starting.

The statistics are the same for the whole cluster, but the view must exist in every database you query it from (here tag07, and postgres for the exporter). Generate load and analyze it:

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;"

Expected (abridged):

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)

Two perspectives: by total_exec_time (what costs the server the most overall) and by mean_exec_time (what is slow individually):

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;"

Expected (abridged):

 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)

The count query reads 10,399 blocks from outside the cache – a case for an index (Day 5). The UPDATE pgbench_branches statements, on the other hand, are fast but numerous and wait for each other: ten rows, eight clients.

Save your progress? Sign up for free

Drill 3: Cumulative statistics

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');"

Expected (abridged):

 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: sorts didn’t fit into work_mem and spilled to disk (here while creating the indexes). Since PostgreSQL 17, checkpoints have their own view; pg_stat_bgwriter only counts the 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'

Expected (abridged):

-[ 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)

Many num_requested compared to num_timed means: max_wal_size is too small for the write load, checkpoints don’t come on schedule but are forced. pg_stat_io (since 16) shows who reads and writes how much; PostgreSQL 18 adds columns in bytes and WAL access:

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;"

Expected (abridged, values vary):

   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)

Timings (read_time, write_time) only fill up with track_io_timing = on; on modern hardware this costs hardly anything and is worth it in production.

Save your progress? Sign up for free

Drill 4: Log parameters

Everything with context sighup or superuser – a reload is enough. As of PostgreSQL 18, log_connections is no longer a switch but a list of phases:

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'

On 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'

Back on pg1:

sudo tail -n 6 /var/log/postgresql/postgresql-18-main.log

Expected (abridged):

... [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 in the prefix writes the application_name into every line – worth its weight in gold when several services share the same role. setup_durations shows how long connection setup takes (considerably longer over the network with TLS and SCRAM than on the socket). Now a lock that waits longer than deadlock_timeout (1 s):

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

Expected (abridged):

... 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 writes the plan of slow queries to the log. As a superuser, you can load it for one session without a restart:

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

Expected (abridged):

... 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 ...

To apply permanently to all sessions, auto_explain belongs in shared_preload_libraries (restart). log_analyze instruments every affected query – use it with care in operations.

Save your progress? Sign up for free

Drill 5: Find and read logs

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

Expected (abridged):

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
...

On Ubuntu, pg_ctlcluster writes stderr to /var/log/postgresql/postgresql-18-main.log (column “Log file”), and logrotate rotates weekly. The journal only contains start and stop. Typical searches:

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

Expected (abridged):

6
... ubuntu@tag07 FATAL:  terminating connection due to administrator command

Save your progress? Sign up for free

Drill 6: postgres_exporter

The exporter comes as a static binary from GitHub. Version and architecture as variables, and the checksum gets verified too:

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

Expected:

postgres_exporter-0.21.1.linux-amd64.tar.gz: OK
postgres_exporter, version 0.21.1 (branch: HEAD, revision: 05ec47a115a2c5d8...)

A dedicated Linux user and a role of the same name with the role pg_monitor – then peer on the socket is enough, no password, no 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

Warning: The quotes around Environment= are mandatory. Without them, systemd splits the value at the spaces, the exporter only sees host=… and reports database "postgres_exporter" does not exist. The \ at the end of the line joins the lines with exactly one space – if there’s another space before it, the exporter reports 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

Expected (abridged):

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 means: the exporter reaches the database. If the extension is missing in the database postgres, the journal shows function pg_stat_statements(boolean) does not exist – the rest still works. The exporter stays in place: on Day 17, the Prometheus in the cluster scrapes the metrics from pg1.

Save your progress? Sign up for free

Exercise: Speed Drills

  1. Show all sessions currently waiting for a lock, along with their blocker. Target time: 45 s
  2. Show the three queries with the highest total runtime in the entire cluster. Target time: 45 s
  3. Show the cache hit ratio of the database notes. Target time: 45 s
  4. Show whether pg_stat_statements is loaded. Target time: 10 s

Solution

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'

Expected (abridged):

 pid | pg_blocking_pids | left
-----+------------------+------
(0 rows)
...
96.4
pg_stat_statements

Save your progress? Sign up for free

Checkpoint

psql -Atc 'SHOW shared_preload_libraries'

Expected: pg_stat_statements

psql -d postgres -Atc "SELECT extversion FROM pg_extension
  WHERE extname = 'pg_stat_statements'"

Expected: 1.12

psql -Atc 'SHOW log_min_duration_statement'

Expected: 200ms (Drill 4; the cleanup resets it).

systemctl is-active postgres_exporter

Expected: active

curl -s localhost:9187/metrics | grep '^pg_up '

Expected: pg_up 1

Challenge

Slow queries from 100 ms on should go to the log only for the database tag07 – globally, log_min_duration_statement stays off, and there is no reload.

Hint: Database-wide settings apply from the next connection.

Solution

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

Expected (abridged):

1487
10004|
... ubuntu@tag07 LOG:  duration: 142.231 ms  statement: SELECT count(*) FROM
pgbench_accounts WHERE bid = 2 AND abalance < 0

The 300 ms query in notes doesn’t show up. That way a suspicious application gets a more detailed log without everyone else flooding the log.

Save your progress? Sign up for free

Cleanup

Reset the log parameters; pg_stat_statements and the exporter stay:

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 Note

  • “The database is slow – what do you look at first?” – pg_stat_activity (who is waiting on what, pg_blocking_pids), then pg_stat_statements by total and per-call time, then logs with log_min_duration_statement.
  • “Why does pg_stat_statements need a restart?” – It reserves shared memory at startup via shared_preload_libraries; CREATE EXTENSION only creates the view.
  • “How do you measure cache efficiency?” – blks_hit / (blks_hit + blks_read) from pg_stat_database or per query from pg_stat_statements; for OLTP usually > 99 %.

Enterprise Note

  • pg_stat_statements and track_io_timing = on on every server from the start; query texts can contain data – restrict access to pg_read_all_stats.
  • Collect logs centrally (Vector, Fluent Bit, Loki/Elastic), log_line_prefix with time, PID, user, database and application; log_destination = 'jsonlog' (from 15) makes parsing easier.
  • Alerts on pg_up, replication lag, connection utilization (numbackends versus max_connections), long transactions, wraparound distance and disk usage.
  • Don’t expose the exporter endpoint openly to the network: firewall or --web.config.file with TLS and basic auth.
  • log_connections generates a lot of volume; with a pooler in front, it often only makes sense on the pooler.