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_activityis the snapshot: who, what, since when, waiting on what.pg_stat_statementsaggregates all queries – the most important extension in operations. It needsshared_preload_librariesand 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_exportertranslates 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 tag07Expected (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_librariesis a list. If you later addauto_explainorpg_cron, write all names into it – otherwisepg_stat_statementsis 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.logExpected (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 -2Expected (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 -7Expected (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-commonExpected (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 -3Expected (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 -1Expected:
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_exporterWarning: The quotes around
Environment=are mandatory. Without them, systemd splits the value at the spaces, the exporter only seeshost=…and reportsdatabase "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 reportsmalformed 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 -4Expected (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
- Show all sessions currently waiting for a lock, along with their blocker. Target time: 45 s
- Show the three queries with the highest total runtime in the entire cluster. Target time: 45 s
- Show the cache hit ratio of the database
notes. Target time: 45 s - Show whether
pg_stat_statementsis 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_exporterExpected: 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.logExpected (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 tag07Interview Note
- “The database is slow – what do you look at first?” –
pg_stat_activity(who is waiting on what,pg_blocking_pids), thenpg_stat_statementsby total and per-call time, then logs withlog_min_duration_statement. - “Why does pg_stat_statements need a restart?” – It reserves shared
memory at startup via
shared_preload_libraries;CREATE EXTENSIONonly creates the view. - “How do you measure cache efficiency?” –
blks_hit / (blks_hit + blks_read)frompg_stat_databaseor per query frompg_stat_statements; for OLTP usually > 99 %.
Enterprise Note
pg_stat_statementsandtrack_io_timing = onon every server from the start; query texts can contain data – restrict access topg_read_all_stats.- Collect logs centrally (Vector, Fluent Bit, Loki/Elastic),
log_line_prefixwith time, PID, user, database and application;log_destination = 'jsonlog'(from 15) makes parsing easier. - Alerts on
pg_up, replication lag, connection utilization (numbackendsversusmax_connections), long transactions, wraparound distance and disk usage. - Don’t expose the exporter endpoint openly to the network: firewall
or
--web.config.filewith TLS and basic auth. log_connectionsgenerates a lot of volume; with a pooler in front, it often only makes sense on the pooler.