Which PostgreSQL logging settings must be in place before pgBadger can produce a useful report, and which one should you leave off?
answer
- a timestamp and a process ID
- durations are off by default
- one setting per report section
- English messages only
- the statement-logging trap
basics
~10 spgBadger 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 sFirst, 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
Recall that pgBadger only sees the log: durations need log_min_duration_statement, and the prefix needs a timestamp and a process ID.
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.
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.
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.