skip to content

pgBadger

pgBadger parses PostgreSQL logs into HTML reports of slowest and most frequent queries, locks, checkpoints, autovacuum and connections. Interviewers probe which log settings it needs.

on this pageshow

explore

questions

5

Which PostgreSQL logging settings must be in place before pgBadger can produce a useful report, and which one should you leave off?

level: middleimportance: must knowfreq 48%

answer

  1. a timestamp and a process ID
  2. durations are off by default
  3. one setting per report section
  4. English messages only
  5. the statement-logging trap

basics

~10 s

pgBadger needs a log_line_prefix with a time and process escape (or csvlog/jsonlog), log_min_duration_statement set, the checkpoint, connection, lock-wait, temp-file and autovacuum log settings on, English lc_messages, and log_statement left off.

solid answer

~40 s

First, a parseable line: with `stderr` the `log_line_prefix` must contain a time escape (`%t`, `%m` or `%n`) and a process escape (`%p` or `%c`); PostgreSQL 18's default `'%m [%p] '` already qualifies, and a richer prefix with `%u`, `%d`, `%a`, `%h` is passed to pgBadger with `--prefix`. `csvlog` or `jsonlog` avoid the prefix question but need `logging_collector`. Second, durations: `log_min_duration_statement` defaults to `-1`, so set `0` or a threshold. Third, the event sections: `log_checkpoints`, `log_connections`, `log_disconnections`, `log_lock_waits = on`, `log_temp_files = 0`, `log_autovacuum_min_duration = 0`. Keep `lc_messages` English or `'C'`, and leave `log_statement` off: pgBadger does not parse its lines, and it strips the statement text from duration lines.

go deeper

for a junior

Recall that pgBadger only sees the log: durations need log_min_duration_statement, and the prefix needs a timestamp and a process ID.

for a middle

Walk through the settings section by section and say why log_statement is the one to avoid: unparsed lines and duration messages stripped of their statement text.

for a senior

Show you know the operational edges: which settings need only a reload, logging_collector's restart, 18's list-valued log_connections, and lock waits shorter than deadlock_timeout being invisible.

for a principal

Treat logging as a standard across the fleet: one prefix and format so every server's reports are comparable, with the duration threshold chosen against the log-volume budget.

## Why the settings matter pgBadger reads only the **PostgreSQL server log**. Every section of its report corresponds to a kind of log line, and PostgreSQL 18's defaults leave most of those lines switched off. Getting pgBadger working is therefore mostly a `postgresql.conf` task. This answer assumes **PostgreSQL 18** and **pgBadger 13**. ## 1. A line pgBadger can parse Each log entry must tell pgBadger **when** it happened and **which session** wrote it, so that a statement line, its duration line and any error can be tied together. - With `log_destination = 'stderr'` (the default), `log_line_prefix` must contain a **time escape** (`%t`, `%m` or `%n`) and a **process escape** (`%p` for the process ID, or `%c` for the session ID). - PostgreSQL 18's default prefix, `'%m [%p] '`, satisfies both. - A prefix that adds user, database, application and client, such as `'%m [%p] user=%u,db=%d,app=%a,client=%h '`, makes the per-user and per-database sections work; when it is not one of the standard prefixes pgBadger recognises, give pgBadger the same string with `-p`/`--prefix`. - `csvlog` and `jsonlog` have fixed fields, so no prefix is needed, but both require `logging_collector = on`, which only takes effect at **server start**. pgBadger needs the Perl module `Text::CSV_XS` for CSV. ## 2. Statement durations `log_min_duration_statement` defaults to `-1`, which **disables** duration logging; with it, pgBadger has no query sections at all. `0` logs every completed statement with its duration and text; a value such as `250ms` logs only statements at or above it. That choice is a trade-off between log volume and report completeness, covered in its own question. The setting can be changed by a superuser per session, role or database, and on reload for the server. ## 3. One setting per report section | Setting | PostgreSQL 18 default | pgBadger's recommendation | Report it feeds | |---|---|---|---| | `log_checkpoints` | `on` | `on` | checkpoints | | `log_connections` | `''` (nothing) | `on` | connections | | `log_disconnections` | `off` | `on` | sessions and their durations | | `log_lock_waits` | `off` | `on` | waits and locks | | `log_temp_files` | `-1` (off) | `0` | temporary files | | `log_autovacuum_min_duration` | `10min` | `0` | autovacuum and autoanalyze | | `log_error_verbosity` | `default` | `default` | errors | Two 18-specific notes: - `log_connections` became a **list** in 18 (`receipt`, `authentication`, `authorization`, `setup_durations`, `all`). The old boolean spelling still works: `on` means `receipt`, `authentication` and `authorization`. - `log_lock_waits` logs a wait only once it has lasted `deadlock_timeout` (default `1s`), so short waits never reach the lock report. ## 4. English messages pgBadger recognises events by their **message text** ("checkpoint complete", "still waiting for", "temporary file"). Set `lc_messages` to `'C'` or an English locale such as `'en_US.UTF-8'`; a translated server log is not supported. ## 5. The setting to leave off: log_statement `log_statement` (`none`, `ddl`, `mod`, `all`) logs statement text when a statement **starts**, without a duration. pgBadger's documentation says not to enable it: - its lines are not in the format pgBadger parses for query statistics; - when it is combined with `log_min_duration_statement`, PostgreSQL does **not repeat** the statement text in the later duration message, so the durations lose their queries; - enabling `log_min_duration_statement`, `log_duration` and `log_statement` together produces wrong counters and a much larger log. ## A working baseline ```ini # postgresql.conf (PostgreSQL 18) for pgBadger 13 log_line_prefix = '%m [%p] user=%u,db=%d,app=%a,client=%h ' log_min_duration_statement = 250ms log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 lc_messages = 'C' ``` All of these take effect on a configuration **reload**; only switching `logging_collector` on needs a restart. ## Plans in the report If the `auto_explain` contrib module is loaded (for example through `session_preload_libraries`) with `auto_explain.log_min_duration` set, slow statements are logged with their execution plans. pgBadger attaches those plans to the slow-query entries, with a link to a graphical plan viewer (`--explain-url`, default explain.depesz.com); `--noexplain` skips them. Since **pgBadger 13.0** it reads these plans from `csvlog` and `jsonlog` as well as from text logs.

  • Your PostgreSQL log_line_prefix is '%m [%p] user=%u,db=%d,app=%a,client=%h '. How do you run pgBadger against it?
    Pass the same string to pgBadger with `--prefix '%m [%p] user=%u,db=%d,app=%a,client=%h '`. It contains the required time escape (`%m`) and process escape (`%p`), and telling pgBadger the layout lets it extract user, database, application and client for the per-user and per-database sections.
  • How do you get execution plans into a pgBadger report, and at what cost?
    Load `auto_explain` and set `auto_explain.log_min_duration`; pgBadger attaches the logged plans to slow queries (13.0 added csvlog and jsonlog support). Plain plans are cheap; `auto_explain.log_analyze` adds per-node timing to every statement, not only logged ones, which the manual warns can hurt performance badly.
  • Why does a PostgreSQL server with lc_messages set to a French locale break pgBadger?
    pgBadger recognises checkpoints, lock waits, temporary files and many events by matching the English message text. Translated messages no longer match, so whole sections come out empty or wrong; set `lc_messages = 'C'` or an English locale for the server log.

The log is a security camera and pgBadger is the person reviewing the tape: if the camera had no clock and no door number on each frame, or was switched off in a room, no amount of reviewing recovers what happened there.

  • Any log_line_prefix works; pgBadger infers the fields on its own
  • Set log_statement = 'all' so pgBadger can see every query
  • PostgreSQL 18 logs lock waits by default
  • csvlog works without logging_collector, so no restart is needed
  • pgBadger reads pg_stat_statements, so no logging changes are required
  • A translated server log is fine because pgBadger matches SQLSTATE codes

Assumes PostgreSQL 18 (log_connections is a list, 'on' still accepted; log_lock_waits off by default) and pgBadger 13, which also reads auto_explain plans from csvlog and jsonlog.

open as a page

What does pgBadger do for a PostgreSQL server, what input does it read, and what does its report show?

level: juniorimportance: should knowfreq 32%

basics

~20 s

pgBadger is a standalone Perl script that parses PostgreSQL server log files offline and builds a self-contained HTML report of slowest and most frequent queries, errors, lock waits, temporary files, checkpoints, autovacuum activity, connections and sessions.

open as a page

How does pgBadger's incremental mode turn a nightly cron run into daily and weekly reports without counting log lines twice?

level: middleimportance: should knowfreq 24%

basics

~20 s

With -I and a mandatory -O output directory, pgBadger stores each run's statistics as dated binary files, rebuilds that day's and week's HTML from them, and records the last parsed line in a LAST_PARSED file so later runs skip what was already counted.

open as a page

On a busy PostgreSQL primary where logging every statement for pgBadger is too costly, how do you set the logging and what do the reports lose?

level: seniorimportance: should knowfreq 40%

basics

~20 s

Raise log_min_duration_statement to a threshold, sample below it with log_min_duration_sample and log_statement_sample_rate, or log everything only for a window or one role; each loses part of the most-frequent and total-time picture, which pg_stat_statements can cover.

open as a page

When parsing a 40 GB PostgreSQL log with pgBadger, how do the -j and -J options differ, and when does each fail to help?

level: seniorimportance: nice to knowfreq 15%

basics

~20 s

-j N splits one log file into N byte ranges parsed by forked processes, which needs a seekable, uncompressed, non-CSV local file; -J N parses N whole files at once, which helps only when there are many files.

open as a page