Monitoring, Logging & Chẩn đoánMonitoring, Logging & Diagnostics

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

Every production incident starts with the same question: “what is the database actually doing right now?” PostgreSQL answers that question at three different layers, and a competent DBA needs fluency in all three. The innermost layer is the statistics collector, a set of counters and per-session state that PostgreSQL maintains internally and exposes through the pg_stat_* family of system views — this is where you look first, because it requires no extra tooling and reflects the exact instant you query it. The middle layer is the log file, which captures things the statistics views don’t (the literal slow query text, error messages, connection/disconnection events, checkpoint activity) and which specialized tools like pgBadger turn into readable reports. The outermost layer is the operating system, because PostgreSQL is, after all, a collection of ordinary Unix processes competing for CPU, memory, and disk I/O — top, iotop, and sar tell you things no amount of SQL querying can, such as whether the box itself is out of headroom.

This note walks through all three layers and adds a fourth: the last-resort tools (strace, gdb, core dumps, perf) you reach for only when the first three don’t explain what a stuck or crashing backend is doing. The goal is not to memorize every flag of every tool, but to build the diagnostic reflex — given a symptom, know which layer to check first and what question that layer answers. This note assumes you’re already familiar with ./09-transactions-and-concurrency-control.md (locks, snapshots, long transactions) and ./10-query-planning-and-performance-tuning.md (EXPLAIN, indexes, pg_stat_statements), since diagnostics is where that theory gets applied under time pressure. It also mirrors, at database scope, the general SRE monitoring model covered in ../devops/en/15-monitoring-and-observability.md — the “four golden signals” framing translates almost one-to-one onto what a DBA watches.

Fundamentals

The statistics collector and the pg_stat_* views

PostgreSQL continuously tracks activity — how many rows were inserted into each table, how many index scans ran, how many transactions committed or rolled back, what every current backend is doing — and exposes all of it through system views prefixed pg_stat_ (cumulative, mostly monotonically increasing counters) and pg_statio_ (I/O-specific counters: block hits vs. reads). Historically this was implemented by a separate statistics collector background process that backends sent UDP messages to; as of PostgreSQL 15 the counters live directly in shared memory, which removed the old collector process and its associated latency/reliability quirks, but the view names and semantics you query are unchanged.

A few of these views matter enough to know by name:

Because most of these counters are cumulative since the last stats reset or server restart, a raw number rarely means much on its own — you either compare a rate over time (snapshot now, snapshot again in five minutes, subtract) or reset the counters at a known point with SELECT pg_stat_reset(); (whole database) or SELECT pg_stat_reset_single_table_counters(oid); before starting a controlled test.

Logging configuration

PostgreSQL’s log output is configured in postgresql.conf (or with ALTER SYSTEM, or per-role/per-database with ALTER ROLE ... SET) and is, in practice, the single richest diagnostic artifact you have for anything the stats views don’t capture — the literal SQL text of a slow statement, a deadlock’s full detail, an authentication failure, a crash’s last words before the process died.

The setting that matters most for performance work is log_min_duration_statement, which logs the text of any statement that takes longer than the configured threshold (in milliseconds):

# postgresql.conf
log_min_duration_statement = 250    # log any statement taking >= 250 ms

Set it too low (e.g. 0, which logs every statement) and you’ll drown the log and add I/O overhead to every query; set it too high and you’ll miss the queries that matter. A common pattern is to run it at a moderate threshold (100–500 ms) permanently in production, then temporarily set it to 0 on a session or role for a focused investigation:

-- log everything for this session only, for the next few minutes
SET log_min_duration_statement = 0;
-- ... reproduce the issue ...
RESET log_min_duration_statement;

log_statement is a coarser, complementary setting that controls which kinds of statements get logged regardless of duration — none (default), ddl (schema changes only — a good permanent baseline for audit purposes), mod (DDL plus INSERT/UPDATE/DELETE), or all (everything, very noisy, rarely appropriate outside short-lived debugging).

log_line_prefix controls what metadata precedes each log line, and getting it right up front saves enormous pain later when you’re trying to correlate a slow query back to a specific session, user, or application. A solid production baseline:

log_line_prefix = '%m [%p] %q%u@%d/%a '

After changing any of these, pg_ctl reload or SELECT pg_reload_conf(); is enough — none of them require a restart, since they’re all classified context = sighup in pg_settings. Where the log actually lands is controlled separately by logging_collector, log_destination (stderr, csvlog, or both — csvlog is what pgBadger and most parsers prefer, since it’s unambiguous to parse), and log_directory/log_filename.

pg_stat_activity: the one view to check first

If you can only remember one diagnostic query, make it this one. pg_stat_activity has one row per server process (backend), and it is simultaneously the connection list, the current-query monitor, and the lock-wait indicator:

SELECT pid, usename, datname, application_name, client_addr,
       state, wait_event_type, wait_event,
       backend_start, xact_start, query_start,
       state_change, query
FROM pg_stat_activity
WHERE datname = 'myapp'
ORDER BY query_start;

The state column is the first thing to read, and each value means something specific:

idle in transaction deserves special attention because it is one of the most common causes of mysterious production degradation. A connection sitting in this state is holding an open transaction — and therefore an open MVCC snapshot — indefinitely, even though it isn’t doing anything. Two concrete harms follow directly from this: first, any row locks that transaction acquired earlier (from an UPDATE, SELECT ... FOR UPDATE, etc.) stay held, so other sessions trying to touch those rows simply queue up and wait, often invisibly until someone notices a pile of blocked queries. Second, and more insidiously, VACUUM cannot remove dead tuples that are newer than the oldest snapshot any session still holds — this is the xmin horizon problem covered in ./09-transactions-and-concurrency-control.md. A single forgotten idle in transaction connection from a bug in application connection-pool handling (a classic case: an ORM opens a transaction, an exception is thrown before commit/rollback, and the connection is returned to the pool still “open”) can silently prevent autovacuum from making progress on a hot table for hours, and the first visible symptom is often unrelated-looking bloat or a sudden increase in sequential scan cost — not an obvious “transaction stuck” alert.

To find the offenders, filter specifically on that state and sort by how long it’s been open:

SELECT pid, usename, datname, application_name, client_addr,
       state, now() - xact_start AS xact_age, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start ASC;

More generally, to find long-running active queries (not just idle-in-transaction sessions):

SELECT pid, usename, datname, now() - query_start AS running_for, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < now() - interval '5 minutes'
ORDER BY query_start ASC;

Once you’ve identified a problem backend by its pid, PostgreSQL gives you two escalating ways to intervene. pg_cancel_backend(pid) sends the equivalent of a cancel request — it interrupts the currently running query but leaves the session itself connected and its transaction state intact (so if it was mid-transaction, that transaction is now aborted and waiting for a ROLLBACK, not gone). pg_terminate_backend(pid) is the blunter tool: it forcibly closes the entire connection, as if the client had disconnected, rolling back any open transaction with it.

-- polite: cancel the running statement, keep the connection
SELECT pg_cancel_backend(12345);

-- forceful: kill the whole session/connection
SELECT pg_terminate_backend(12345);

Both require superuser, or being the same role that owns the target backend (with the exception of certain background/replication processes, which refuse termination for safety). A practical guardrail worth setting in production rather than relying on manual intervention is idle_in_transaction_session_timeout, which makes PostgreSQL automatically terminate sessions that sit in idle in transaction past a threshold:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();

pg_stat_statements: the first stop for “what’s slow”

pg_stat_activity tells you what’s running right now; it says nothing about which queries are slow on average across the whole workload. That’s the job of the pg_stat_statements extension, which aggregates execution statistics (calls, total/mean/max time, rows, shared buffer hits) per normalized query (constants stripped out, so WHERE id = 1 and WHERE id = 2 count as the same entry). It is covered in depth, including setup and the canonical “top N slow queries” query, in ./10-query-planning-and-performance-tuning.md; the short version for this note is that when someone says “the database feels slow” without a specific symptom, pg_stat_statements ordered by total_exec_time is almost always the first query you run, because it immediately tells you whether the problem is one runaway query or genuinely systemic load spread across many.

Key Concepts

The four golden signals, applied to a database

The general SRE monitoring vocabulary — latency, traffic, errors, saturation — maps onto a PostgreSQL instance almost exactly, and organizing your dashboards and alerts around these four categories (rather than an unstructured pile of metrics) is the fastest way to reason about “is the database healthy” at a glance. See ../devops/en/15-monitoring-and-observability.md for the general framing this specializes.

A concrete saturation check every DBA should have on a dashboard is connection headroom, since exhausting max_connections produces a hard failure (FATAL: sorry, too many clients already) rather than graceful degradation:

SELECT count(*) AS current_connections,
       (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections
FROM pg_stat_activity;

In practice, most teams don’t let application connections get anywhere near max_connections directly — a connection pooler (PgBouncer, or the pool built into the application driver) sits in front, and pool saturation (clients waiting for a pooled connection) is the earlier, more actionable warning sign than PostgreSQL’s own connection limit.

Log analysis tooling

Raw PostgreSQL logs are complete but not friendly to read at scale — a busy server produces gigabytes a day, and picking out patterns by eye doesn’t work. A handful of purpose-built tools exist specifically to turn that firehose into something a human (or an alerting system) can act on.

pgBadger is the standard log-analysis tool in the PostgreSQL ecosystem: a single Perl script that parses PostgreSQL log files (ideally in csvlog format, which it parses unambiguously) and produces a self-contained HTML report covering slow queries (ranked by total time, by count, by average duration), query type breakdown, connection and session patterns over time, error and cancellation rates, checkpoint and vacuum activity, and lock waits. It requires no server-side extension and no ongoing daemon — you just point it at a log file (or a whole rotated directory) after the fact.

# ensure csvlog is enabled first (postgresql.conf):
#   log_destination = 'csvlog'
#   logging_collector = on

pgbadger /var/lib/postgresql/data/log/postgresql-*.csv -o /tmp/pgbadger-report.html

# incremental mode: run daily via cron, building one report over time
pgbadger --incremental --outdir /var/www/pgbadger /var/log/postgresql/postgresql-*.csv

pgcenter and pgcluu cover a different niche — live, top-like visibility rather than after-the-fact log parsing. pgcenter top gives a curses-based, auto-refreshing dashboard of pg_stat_activity, per-table/index I/O, and system resource usage side by side, which is exactly what you want open during an active incident. pgcluu runs as a two-part tool: a lightweight sampling collector you leave running (cron or daemon) that periodically snapshots the stats views and OS metrics, and a separate report generator that turns the collected history into browsable HTML graphs — useful when you need to answer “what did connection count / cache hit ratio / disk usage look like over the last two weeks” rather than just right now.

check_pgactivity and check_pgbackrest are Nagios-compatible check plugins — they don’t render dashboards, they return a Nagios-style exit code (OK/WARNING/CRITICAL) plus a status line, designed to be wired into whatever alerting system your team already runs (Nagios, Icinga, or anything that speaks the Nagios plugin API). check_pgactivity covers a broad catalog of checks (connection count, bloat, replication lag, backend states, autovacuum status, and dozens more, each independently configurable with thresholds); check_pgbackrest specifically checks the health and freshness of pgBackRest backups, closing the common gap where a backup job silently stops succeeding and nobody notices until a restore is needed.

temBoard takes a different shape again: a persistent, web-based UI (agent on each PostgreSQL instance, central web application aggregating them) that combines live activity monitoring, historical graphs, configuration management, and built-in advisory checks (unused indexes, missing primary keys, etc.) in one place, aimed at teams who want a dashboard product rather than a collection of separate scripts.

Feeding PostgreSQL metrics into general-purpose monitoring

Purpose-built PostgreSQL tools are excellent for PostgreSQL-specific depth, but most organizations also want database health visible in the same place as everything else — the same Grafana dashboard, the same on-call paging pipeline. Prometheus is the most common target: the postgres_exporter runs alongside PostgreSQL (or as a sidecar), periodically queries the pg_stat_* views itself, and exposes the results on a /metrics HTTP endpoint in Prometheus’s text format for scraping — the same pull-based exporter pattern used for every other _exporter in that ecosystem. Zabbix plays a similar integration role via its own PostgreSQL monitoring template, more common in shops that standardized on Zabbix before Prometheus existed. Either way, the mechanics of scraping, storing, graphing, and alerting on these metrics are general monitoring-stack concerns, not PostgreSQL-specific ones — see ../devops/en/15-monitoring-and-observability.md for how Prometheus, exporters, and alerting pipelines fit together in general.

OS-level diagnostic tools

PostgreSQL backends are, at the end of the day, ordinary OS processes, and a large fraction of “the database is slow” incidents turn out to be resource contention that no SQL query will ever show you directly.

top (or the friendlier htop) is the first command to run when something feels globally wrong, because it answers “is one process hogging the CPU, or is everything just generally loaded”:

top -c    # -c shows the full command line, so you can see which is which backend

Each PostgreSQL backend shows up as its own OS process (postgres: user dbname client_addr(port)), so a runaway backend chewing 100% CPU (a bad query plan doing a huge sort or hash, a runaway recursive CTE, a tight loop in a PL/pgSQL function) is immediately visible by process, and you can cross-reference its PID directly against pg_stat_activity.pid to see exactly which query it’s running.

iotop narrows the same idea to disk I/O — essential when the symptom is “everything is slow” but CPU looks fine, which usually means something is disk-bound:

iotop -o    # -o: only show processes actually doing I/O

A backend running a large sequential scan, a VACUUM on a bloated table, or a checkpoint flushing a large batch of dirty buffers will show up here as heavy I/O, letting you distinguish “one query is I/O-heavy” from “the whole disk subsystem is saturated” (in which case everything shows elevated I/O wait, not just one process).

sysstat (which provides sar, iostat, and mpstat) is what you reach for when you need historical system resource data rather than a live snapshot — critical because by the time you’re investigating, the spike that caused the problem may already be over.

# CPU utilization over the last 24 hours (sar keeps rotating history by default)
sar -u

# disk I/O stats history
sar -d

# live device I/O stats, refreshed every 2 seconds
iostat -dx 2

Deep diagnostics: strace, gdb, and core dumps — the last resort

When pg_stat_activity shows a backend active with a query that simply never advances, and the logs show nothing new being written, you’ve exhausted the normal toolkit and need to ask the operating system directly what that process is doing.

strace traces every system call a running process makes, which is exactly the right tool for “is this backend actually stuck, and on what”:

# attach to a specific backend PID and watch its syscalls live
strace -p 12345

# focus on I/O-related calls only, with timestamps
strace -T -e trace=read,write,pread64,pwrite64,fsync -p 12345

A backend truly blocked waiting on a heavyweight lock typically shows a syscall (often a semop or futex wait) that simply never returns — confirming it’s genuinely blocked on another session’s lock, not silently spinning in userspace. A backend blocked on disk I/O instead shows a stream of read/pread64/fsync calls, pointing you toward storage rather than locking as the culprit. Note that strace measurably slows the traced process, so use it deliberately and briefly on a genuinely stuck backend, not as a routine monitoring habit.

gdb goes a level deeper still: attaching a debugger directly to a live PostgreSQL backend process to inspect its actual call stack and internal state.

gdb -p 12345
(gdb) bt        # print the backend's current call stack
(gdb) detach    # let it continue running afterward
(gdb) quit

This is genuinely advanced territory — you generally need PostgreSQL debug symbols installed, and reading the resulting backtrace requires familiarity with PostgreSQL’s C source (which function is waiting inside LWLockAcquire, or stuck in a particular executor node). Most DBAs will go months or years without needing it, but it’s the tool of last resort when a backend is stuck in a way strace alone doesn’t explain, and it’s valuable simply to know it exists and what it’s for rather than to be fluent in it day to day.

Core dumps are the post-mortem equivalent: a full memory snapshot of a process written to disk at the moment it crashes (typically on a segfault or assertion failure), which you can later load into gdb to reconstruct exactly what the process was doing when it died — invaluable for reporting a genuine PostgreSQL bug upstream, or for diagnosing a crash caused by a broken extension. Core dumps are usually disabled or size-limited by default and need explicit enabling:

# raise the core dump size limit for the postgres process's shell (and persist via limits.conf / systemd unit)
ulimit -c unlimited

# tell the kernel where to write core files (Linux)
echo '/var/crash/core.%e.%p.%t' | sudo tee /proc/sys/kernel/core_pattern

# after a crash, load the resulting core file with the matching postgres binary
gdb /usr/lib/postgresql/16/bin/postgres /var/crash/core.postgres.12345.1700000000
(gdb) bt

perf: CPU profiling under load

For performance problems that are real but too diffuse for strace or EXPLAIN to pin down — “the server is CPU-bound under this workload, but which function is actually burning the cycles” — Linux’s perf can sample a running PostgreSQL process (or the whole system) and produce a profile of where CPU time is actually going, down to the function level if debug symbols are available:

# sample a specific backend for 30 seconds at 99 Hz
perf record -p 12345 -F 99 -g -- sleep 30
perf report

This is advanced, low-level performance work — most slow-query problems are solved far earlier by EXPLAIN (ANALYZE, BUFFERS) and indexing (see ./10-query-planning-and-performance-tuning.md) — but perf is the right escalation when a query’s plan looks fine yet it still burns disproportionate CPU, and you need to know whether that time is going into sorting, hashing, expression evaluation, or something unexpected like lock spinning.

Troubleshooting workflow: symptom → first tool → what it tells you

Tying all of the above together, here’s the practical decision path most incidents follow, roughly in the order you’d actually reach for each tool:

Best Practices

• Enable csvlog in production, not just plain-text stderr logs. It’s what pgBadger and most parsers expect, and it eliminates ambiguity when a logged query itself contains newlines or special characters.
• Set a permanent, moderate log_min_duration_statement (100–500 ms is a common starting point) rather than leaving it off entirely or logging everything — you want the slow-query signal always on, without drowning the log.
• Invest in a good log_line_prefix from day one. Include timestamp, PID, user, database, and application name; retrofitting this after you need to correlate an old incident is much harder than setting it up up front.
• Treat pg_stat_activity as a routine dashboard, not just an incident tool. Watch connection count against max_connections, and specifically watch for idle in transaction sessions as an ongoing health check, since they’re a leading indicator of both lock contention and vacuum problems.
• Set idle_in_transaction_session_timeout as an automatic safety net rather than relying on catching stuck transactions manually every time.
• Run pgBadger on a schedule (daily/weekly incremental reports), not only reactively after an incident — a lot of slow-query drift is only visible when you compare reports over time.
• Feed PostgreSQL metrics into the same monitoring stack as the rest of your infrastructure (Prometheus via postgres_exporter, or Zabbix) so database health sits next to application and infrastructure health on the same dashboards and paging pipeline, rather than living in a PostgreSQL-only silo.
• Reach for strace/gdb/core dumps only after pg_stat_activity and the logs have failed you — they’re powerful but disruptive (performance overhead, requires care with production processes) and rarely the fastest path to an answer.
• Practice the escalation path before you need it under pressure: know in advance which tool answers which question, so a 3 a.m. incident is a lookup, not a research project.

References

• PostgreSQL Docs — The Cumulative Statistics System
• PostgreSQL Docs — Viewing Statistics (pg_stat_activity and friends)
• PostgreSQL Docs — Error Reporting and Logging
• pgBadger — GitHub project and documentation
• pgcenter — GitHub project
• pgCluu — official site
• temBoard — official site
• postgres_exporter — Prometheus community project
• PostgreSQL Docs — pg_stat_statements