skip to content

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%

answer

  1. binary data first, HTML second
  2. an output directory is mandatory
  3. a bookmark file in that directory
  4. retention counted in weeks

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.

solid answer

~40 s

`pgbadger -I -O /var/www/pg_reports/ <log>` parses the log into **binary data files** in a year/month/day tree under the output directory, then regenerates the HTML for each affected day, a cumulative report for the week, and an index with a calendar. It writes a `LAST_PARSED` file in that directory recording the timestamp and position of the last line read, so a nightly run against a log rotated weekly picks up only new lines (`-l` moves that file). `--retention N` keeps N weeks (default 0, keep all), `-X` writes the CSS and JavaScript once instead of into every page, `--month-report YYYY-MM` builds a month on demand, and `--rebuild` regenerates every HTML page from the binaries after an upgrade.

go deeper

for a junior

Remember -I for incremental, -O for where it writes, and that it produces a page per day plus a weekly summary.

for a middle

Explain the binary-then-HTML pipeline and the LAST_PARSED bookmark that lets a nightly run read a weekly-rotated log without double counting.

for a senior

Cover operations: retention in weeks, -X to save disk, --rebuild after upgrades, the PID-file guard, and timing the 13.0 EVENTLOG change at a week boundary.

for a principal

Decide where reports live and how long: one central host per fleet, retention matching how far back incidents are reviewed, and per-database explosion where teams own databases.

## The problem incremental mode solves A retail chain's database team wants a **weekly slow-query and lock report** for its store-orders database, with a page per day so they can see which day a regression started. Running pgBadger once a week over seven days of logs is slow and gives no daily view; running it every night over the whole week's log double-counts. **Incremental mode** (`-I`, `--incremental`) is pgBadger's answer. ## How it works 1. **Parse only new lines.** pgBadger reads the log from where the previous run stopped. 2. **Store statistics as binary data.** Results go into **binary files** under the output directory, organised by date (year, month, day). 3. **Rebuild the HTML from the binaries.** pgBadger regenerates the report for each day it touched, the **cumulative weekly report**, and a main **index** with a dropdown per week linking to each day. 4. **Record the position.** A file named `LAST_PARSED` in the output directory stores the timestamp and position of the last line parsed. Because the bookmark lives in the output directory, you do not need `-l`/`--last-parsed` in incremental mode; use `-l` only to put the file elsewhere. This is what lets you run nightly against a log the server rotates **weekly** without counting Monday's lines seven times. ## The options around it | Option | Purpose | |---|---| | `-I`, `--incremental` | one report per day plus a cumulative weekly report | | `-O`, `--outdir` | **mandatory** with `-I`: where binary data, the bookmark and reports go | | `-H`, `--html-outdir` | write HTML elsewhere; the binaries stay under `-O` | | `-R`, `--retention N` | keep N weeks; older days and weeks are deleted (default `0`, disabled) | | `-X`, `--extra-files` | write CSS and JavaScript once as separate files to save disk | | `-E`, `--explode` | one report per database | | `--month-report YYYY-MM` | build a cumulative month on demand | | `--rebuild` | regenerate every HTML page from the binaries still present | | `--start-monday`, `--iso-week-number` | change where calendar weeks begin | | `--no-week` | skip weekly reports when they take too long | ## A setup for the weekly report ```bash # nightly at 04:00, after the server rotated its log at midnight 0 4 * * * /usr/bin/pgbadger -I -q -X --retention 8 \ --start-monday -O /var/www/pg_reports/ \ /var/log/postgresql/postgresql.log.1 ``` The team opens the weekly page every Monday: slowest queries, most time-consuming queries and the lock-wait report for the whole week, with each day one click away. Eight weeks are kept, so a regression can be compared with the previous month. Some practical points: - **Monthly reports are not built automatically.** Run `pgbadger -X --month-report 2026-09 /var/www/pg_reports/` (add `-E` if the reports were exploded per database); a current month can be rebuilt daily. - **Upgrading pgBadger** may change how pages render. Delete the old `.js` and `.css` files and run `pgbadger -X -I -O /var/www/pg_reports/ --rebuild` to regenerate every page from the binaries. - **Concurrent runs** are guarded by a PID file (`--pid-file`, default `pgbadger.pid` in `/tmp`); a second run exits with code 3. - **`--noreport`** only accumulates binary data without writing HTML, useful when the pages are built later in a separate step. ## The pgBadger 13 compatibility break: EVENTLOG Before 13.0, some PostgreSQL **LOG-level** messages that pgBadger lists as events (for example "still waiting for" lock lines, "detected deadlock while waiting for", or "parameter ... changed") were still counted as **errors**. **pgBadger 13.0** stores them as `EVENTLOG` entries, so the events report separates them from real errors. The change is stored in the binary data, so in incremental mode it is a **backward-compatibility break**: the week in which you upgrade mixes binaries written both ways, and its weekly report shows the **double behaviour**. From the following week on the reports are consistent. Plan the upgrade at a week boundary if the weekly numbers matter. Later 13.x releases added features without breaking the data: 13.1 a vacuum-throughput report and `--no-fork` for debugging, 13.2 `--ssh-sudo` for reading remote logs that only root can read.

  • The PostgreSQL log server only lets root read the logs. How does pgBadger 13.2 still build the nightly report from a central host?
    Use remote mode (`-r host` or an `ssh://` URI) with passwordless ssh, and add `--ssh-sudo`, new in 13.2, so the remote `ls` and `cat` commands run under sudo. It does not work with wildcard file names.
  • A bug fix in a new pgBadger release changes a report. How do you apply it to six weeks of incremental history?
    Remove the old `.js` and `.css` files from the report directory and run `pgbadger -X -I -O /var/www/pg_reports/ --rebuild`. It regenerates every HTML page from the binary data still present, and needs `-E` too if the reports were built per database.

  • Incremental mode re-reads the whole log each night and deduplicates
  • You must pass -l in incremental mode or lines are counted twice
  • pgBadger builds monthly reports automatically in incremental mode
  • --retention is counted in days
  • Upgrading to pgBadger 13 needs the old binary history deleted

Assumes pgBadger 13; 13.0 stores LOG-level events as EVENTLOG, which breaks incremental compatibility for the week of the upgrade.