PGBADGER(1p)

📦
version 13.2 · 9 versions
, in ubuntu resolute, ubuntu stonking

💖 PGBADGER(1p) 💖

User Contributed Perl Documentation · PGBADGER(1p)

okay bestie, sit down, hydrate, and let me tell you about the most iconic little Perl script in the PostgreSQL universe 🫶

✨ NAME ✨

pgBadger — a fast PostgreSQL log analysis report. literally that's the whole name. short, sweet, and it delivers 💅

✨ SYNOPSIS ✨

Here's how you summon her:

Usage: pgbadger [options] logfile [...]

PostgreSQL log analyzer with fully detailed reports and graphs. no cap, it really does the most and I'm obsessed 😭✨

🌸 Arguments 🌸

  • logfile — okay so this one is flexible in the best way: it can be a single log file, a list of files, or even a shell command returning a list of files. main character energy honestly 👑
Want to pipe log content in from stdin instead? Use - as the filename. But heads up bestie: input from stdin will not work with csvlog. that's just the rules, periodt 💀

✨ Options ✨

(and yes, there are a lot of them — more parts incoming, stay with me 🎀)

✨ OPTIONS ✨

okay bestie, deep breath, this is the big one. every flag pgBadger takes, in order, no skips. i'm not letting you scroll past a single one 💅

  • -a, --average minutes — how many minutes each bucket covers when it builds the average graphs for queries and connections. default is 5 minutes.
  • -A, --histo-average min — how many minutes each bucket covers for the histogram graphs of queries. default is 60 minutes.
  • -b, --begin datetime — the start date/time of the data to parse in the log. it can be a timestamp or a time, your call ✨
  • -c, --dbclient host — only report on entries coming from that client host, nothing else.
  • -C, --nocomment — strip comments like /* ... */ out of queries. bye clutter 💖
  • -d, --dbname database — only report on entries for the database you name.
  • -D, --dns-resolv — swap client ip addresses for their DNS names.
ngl bestie, this one can really slow pgBadger down. consider yourself warned 😭
  • -e, --end datetime — the end date/time of the data to parse in the log (timestamp or a time, same deal as -b).
  • -E, --explode — explodes the main report into one report per database 🔥. global info that isn't tied to any database gets added to the postgres database report.
  • -f, --format logtype — the possible values are: syslog, syslog2, stderr, jsonlog, csv, pgbouncer, logplex, rds and redshift. reach for this when pgBadger can't figure out the log format on its own.
  • -G, --nograph — turns the graphs OFF in the HTML output. (they're enabled by default, so this is the "no thank you" button)
  • -h, --help — shows this message and exits. classic.
  • -H, --html-outdir path — the directory where the HTML report gets written in incremental mode. the binary files stay put in the directory you set with the -O, --outdir option.
  • -i, --ident name — the programname used as the syslog ident. default: postgres.
  • -I, --incremental — use incremental mode: reports get generated per day, in a separate directory. you must set --outdir for this one, no cap.
  • -j, --jobs number — how many jobs run at the same time for a single log file. it runs as single by default, and also when you're working with csvlog format.
  • -J, --Jobs number — how many log files get parsed in parallel. by default it processes one file at a time. (yes, lowercase j and uppercase J are different things, i know, i know 🙈)
  • -l, --last-parsed file — lets you do incremental log parsing by registering the last datetime and the last line parsed. lowkey perfect if you wanna watch errors since the last run, or if you want one report per day with a log that rotates every week 🗓️
  • -L, --logfile-list file — a file containing a list of the log files to parse.
  • -m, --maxlength size — the maximum length of a query; anything longer gets restricted to the size you give. the default truncate size is 100000.
  • -M, --no-multiline — don't collect multiline statements, which avoids garbage — especially on errors that would otherwise generate a gigantic report 💀
  • -N, --appname name — only report on entries for the given application name.
  • -o, --outfile filename — set the filename for the output. the default depends on the output format: out.html, out.txt, out.bin, or out.json. you can use this option multiple times to output several formats at once 👑. to use json output the Perl module JSON::XS must be installed, and to dump the output to stdout, use - as the filename.
  • -O, --outdir path — the directory where out files must be saved.
  • -p, --prefix string — the value of your custom log_line_prefix as defined in your postgresql.conf. only use this if you aren't using one of the standard prefixes spelled out in the pgBadger documentation — like when your prefix has extra variables such as client IP or application name. it MUST contain escape sequences for time (%t, %m or %n) and for processes (%p or %c). see examples below ✨
  • -P, --no-prettify — disables the SQL queries prettify formatter. so, un-pretty. chaotic but valid.
  • -q, --quiet — don't print anything to stdout. not even a progress bar. silence, bestie 🤫
  • -Q, --query-numbering — adds numbering of queries to the output when you're using the --dump-all-queries or --normalized-only options.
  • -r, --remote-host ip — set the host where the cat command gets executed on the remote log file, so the file gets parsed locally.
  • -R, --retention N — how many weeks to keep in incremental mode. defaults to 0, disabled. it sets the number of weeks kept in the output directory, and older weeks and days directories get removed automatically 🧹
  • -s, --sample number — how many query samples to store. default: 3.
  • -S, --select-only — only report SELECT queries. nothing else makes the cut 💅
  • -t, --top number — how many queries to store/display. default: 20.
  • -T, --title string — changes the title of the HTML page report. main character energy for your dashboard 💖
  • -u, --dbuser username — only report on entries for the given user.
  • -U, --exclude-user username — excludes entries for the specified user from the report. can be used multiple times.
  • -v, --verbose — enable verbose or debug mode. disabled by default.
  • -V, --version — show the pgBadger version and exit.
  • -w, --watch-mode — only report errors, just like logwatch could do.
  • -W, --wide-char — encode the html output of queries into UTF8, which avoids the Perl message "Wide character in print". we love a clean print 🌈
  • -x, --extension — output format. values: text, html, bin or json. default: html.
  • -X, --extra-files — in incremental mode, lets pgBadger write the CSS and JS files into the output directory as separate files.
  • -z, --zcat exec_path — set the full path to the zcat program. use this if zcat, bzcat or unzip isn't in your path.
  • -Z, --timezone +/-XX — set the number of hours from GMT of the timezone. use it to adjust the date/time in the JavaScript graphs. the value can be an integer, ex.: 2, or a float, ex.: 2.5.
  • --pie-limit num — pie data lower than num% will show a sum instead. tiny slices get merged, we love efficiency 🥧
  • --exclude-query regex — any query matching the given regex gets excluded from the report. for example: "^(VACUUM|COMMIT)". you can use this option multiple times.
  • --exclude-file filename — path of the file that contains each regex to use for excluding queries from the report. one regex per line.
  • --include-query regex — any query that does not match the given regex gets excluded from the report. you can use this option multiple times. for example: "(tbl1|tbl2)".
  • --include-file filename — path of the file that contains each regex for the queries to include in the report. one regex per line.
  • --disable-error — don't generate the error report.
  • --disable-hourly — don't generate the hourly report.
  • --disable-type — don't generate the report of queries by type, database or user.
  • --disable-query — don't generate the query reports (slowest, most frequent, queries by users, by database, ...). heads up, this is the slowest one to build in the first place.
  • --disable-session — don't generate the session report.
  • --disable-connection — don't generate the connection report.
  • --disable-lock — don't generate the lock report.
  • --disable-temporary — don't generate the temporary report.
  • --disable-checkpoint — don't generate the checkpoint/restartpoint report.
  • --disable-autovacuum — don't generate the autovacuum report.
  • --charset — used to set the HTML charset to be used. default: utf-8.
  • --csv-separator — used to set the CSV field separator, default: ,
  • --exclude-time regex — any timestamp matching the given regex gets excluded from the report. example: "2013-04-12 .*". you can use this option multiple times.
  • --include-time regex — only timestamps matching the given regex get included in the report. example: "2013-04-12 .*". you can use this option multiple times.
  • --exclude-db name — exclude entries for the specified database from the report. example: "pg_dump". can be used multiple times.
  • --exclude-appname name — exclude entries for the specified application name from the report. example: "pg_dump". can be used multiple times.
  • --exclude-line regex — exclude any log entry that matches the given regex. can be used multiple times.
  • --exclude-client name — exclude log entries for the specified client ip. can be used multiple times.
  • --anonymize — obscure all literals in queries. so useful for hiding confidential data 🔒
  • --noreport — no reports will be created in incremental mode.
  • --log-duration — forces pgBadger to associate log entries generated by both log_duration = on and log_statement = 'all'.
  • --enable-checksum — adds an md5 sum under each query report. little integrity moment 🫶
  • --journalctl command — the command to use to replace the PostgreSQL logfile with a call to journalctl. basically it might be:
journalctl -u postgresql-9.5
  • --pid-dir path — set the path where the pid file must be stored. default /tmp.
  • --pid-file file — set the name of the pid file used to manage concurrent execution of pgBadger. default: pgbadger.pid.
  • --rebuild — used to rebuild all html reports in the incremental output directories where there are binary data files. glow-up time ✨
  • --pgbouncer-only — only show the PgBouncer-related menus in the header.
  • --start-monday — so in incremental mode, calendar weeks start on Sunday by default. Slap this on if you want your weeks to start on a Monday instead. 🗓️
  • --iso-week-number — incremental mode again, but now weeks start on Monday and they respect the ISO 8601 week number, range 01 to 53, where week 1 is the first week that has at least 4 days in the new year. ISO girlies rise ✨
  • --normalized-only — only dump all normalized queries to out.txt. That's it, that's the whole job. 🎀
  • --log-timezone +/-XX — sets the number of hours from GMT of the timezone that must be used to adjust date/time read from the log file before being parsed. Heads up tho: using this option makes log search with a date/time more difficult. The value can be an integer, ex.: 2, or a float, ex.: 2.5. 🔥
  • --prettify-json — use it if you want json output to be prettified. aesthetic json only 💅
  • --month-report YYYY-MM — create a cumulative HTML report over the specified month. Requires incremental output directories and the presence of all necessary binary data files. 👑
  • --day-report YYYY-MM-DD — create an HTML report over the specified day. Requires incremental output directories and the presence of all necessary binary data files. 🌸
  • --noexplain — do not process lines generated by auto_explain. 🙈
  • --command CMD — command to execute to retrieve log entries on stdin. pgBadger will open a pipe to the command and parse log entries generated by the command. 🦋
  • --no-week — inform pgbadger to not build weekly reports in incremental mode. Useful if it takes too much time. we respect a busy schedule ⭐
  • --explain-url URL — use it to override the url of the graphical explain tool. Default: https://explain.depesz.com/ 🔍
  • --tempdir DIR — set directory where temporary files will be written. Default: File::Spec->tmpdir() || '/tmp' 💖
  • --no-process-info — disable changing process title to help identify pgbadger process, some system do not support it. 💀
  • --dump-all-queries — dump all queries found in the log file replacing bind parameters included in the queries at their respective placeholders positions. all of them. every single one. 🔥
  • --keep-comments — do not remove comments from normalized queries. It can be useful if you want to distinguish between same normalized queries. 🎀
  • --no-progressbar — disable progressbar. no little bar, just vibes ✨
  • --dump-raw-csv — parse the log and dump the information into CSV format. No further processing is done, no report. periodt. 📄
  • --include-pid PID — only report events related to the session pid (%p). Can be used multiple time. 🧚
  • --include-session ID — only report events related to the session id (%c). Can be used multiple time. 🧚
  • --histogram-query VAL — use custom inbound for query times histogram. Default inbound in milliseconds: 0,1,5,10,25,50,100,500,1000,10000 📊
  • --histogram-session VAL — use custom inbound for session times histogram. Default inbound in milliseconds: 0,500,1000,30000,60000,600000,1800000,3600000,28800000 📈
  • --no-fork — do not fork any process, for debugging purpose. one process, one main character 💅

pgBadger is able to parse a remote log file using a passwordless ssh connection. Use -r or --remote-host to set the host IP address or hostname. There are also some additional options to fully control the ssh connection. remote bestie access, unlocked 🔐

  • --ssh-program ssh — path to the ssh program to use. Default: ssh. 🖥️
  • --ssh-port port — ssh port to use for the connection. Default: 22. 🔌
  • --ssh-user username — connection login name. Defaults to running user. 👤
  • --ssh-identity file — path to the identity file to use. 🗝️
  • --ssh-timeout second — timeout to ssh connection failure. Default: 10 sec. ⏳
  • --ssh-option options — list of -o options to use for the ssh connection. Options always used:
-o ConnectTimeout=$ssh_timeout
-o PreferredAuthentications=hostbased,publickey

Log file to parse can also be specified using an URI, supported protocols are http[s] and [s]ftp. The curl command will be used to download the file, and the file will be parsed during download. The ssh protocol is also supported and will use the ssh command like with the remote host use. See examples bellow. 🌈

✨ Return codes ✨

0: on success
1: die on error
2: if it has been interrupted using ctr+c for example
3: the pid file already exists or can not be created
4: no log file was given at command line

🎀 Examples 🎀

pgbadger /var/log/postgresql.log
pgbadger /var/log/postgres.log.2.gz /var/log/postgres.log.1.gz /var/log/postgres.log
pgbadger /var/log/postgresql/postgresql-2012-05-*
pgbadger --exclude-query="^(COPY|COMMIT)" /var/log/postgresql.log
pgbadger -b "2012-06-25 10:56:11" -e "2012-06-25 10:59:11" /var/log/postgresql.log
cat /var/log/postgres.log | pgbadger -
# Log line prefix with stderr log output
pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21*
pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log
# Log line prefix with syslog log output
pgbadger --prefix 'user=%u,db=%d,client=%h,appname=%a' /pglog/postgresql-2012-08-21*
# Use my 8 CPUs to parse my 10GB file faster, much faster
pgbadger -j 8 /pglog/postgresql-10.1-main.log

Use URI notation for remote log file:

pgbadger http://172.12.110.1//var/log/postgresql/postgresql-10.1-main.log
pgbadger ftp://username@172.12.110.14/postgresql-10.1-main.log
pgbadger ssh://username@172.12.110.14:2222//var/log/postgresql/postgresql-10.1-main.log*

You can use together a local PostgreSQL log and a remote pgbouncer log file to parse:

pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username@172.12.110.14/pgbouncer.log

Reporting errors every week by cron job:

30 23 * * 1 /usr/bin/pgbadger -q -w /var/log/postgresql.log -o /var/reports/pg_errors.html

Generate report every week using incremental behavior:

0 4 * * 1 /usr/bin/pgbadger -q `find /var/log/ -mtime -7 -name "postgresql.log*"` -o /var/reports/pg_errors-`date +\%F`.html -l /var/reports/pgbadger_incremental_file.dat

This supposes that your log file and HTML report are also rotated every week. 🗓️

Or better, use the auto-generated incremental reports:

0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/

will generate a report per day and per week. ✨

In incremental mode, you can also specify the number of weeks to keep in the reports:

/usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/

If you have a pg_dump at 23:00 and 13:00 each day during half an hour, you can use pgBadger as follow to exclude these periods from the report:

pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log

This will help avoid having COPY statements, as generated by pg_dump, on top of the list of slowest queries. You can also use --exclude-appname "pg_dump" to solve this problem in a simpler way. so much easier bestie, take the shortcut 💖

You can also parse journalctl output just as if it was a log file:

pgbadger --journalctl 'journalctl -u postgresql-9.5'

or worst, call it from a remote host:

pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5'

you don't need to specify any log file at command line, but if you have other PostgreSQL log files to parse, you can add them as usual. 🫶

To rebuild all incremental html reports after, proceed as follow:

rm /path/to/reports/*.js
rm /path/to/reports/*.css
pgbadger -X -I -O /path/to/reports/ --rebuild

it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option. 🔥

pgBadger also supports Heroku PostgreSQL logs using logplex format:

heroku logs -p postgres | pgbadger -f logplex -o heroku.html -

this will stream Heroku PostgreSQL log to pgbadger through stdin. 🦋

pgBadger can auto detect RDS and cloudwatch PostgreSQL logs using rds format:

pgbadger -f rds -o rds_out.html rds.log

Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated in JSON format. It is autodetected by pgBadger but in case you need to force the log format use jsonlog:

pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log

This is the same as with the jsonlog extension, the json format is different but pgBadger can parse both formats. ✨

pgBadger also supports logs produced by CloudNativePG Postgres operator for Kubernetes:

pgbadger -f jsonlog -o cnpg_out.html cnpg.log

To create a cumulative report over a month use command:

pgbadger --month-report 2919-05 /path/to/incremental/reports/

this will add a link to the month name into the calendar view in incremental reports to look at report for month 2019 May. Use -E or --explode if the reports were built using this option. 👑

✨ DESCRIPTION ✨

Okay bestie, listen up because this is literally the whole vibe of the page 💅

pgBadger is a PostgreSQL log analyzer built for speed — it gives you fully detailed reports based on your PostgreSQL log files. It's a small standalone Perl script that outperforms any other PostgreSQL log analyzer. No cap. 🔥

It's written in pure Perl and uses a JavaScript library (flotr2) to draw graphs, so you don't need to install any additional Perl modules or other packages. And bonus: that library gives us more features like zooming. pgBadger also uses the Bootstrap JavaScript library and the FontAwesome webfont for a better design. Everything is embedded. 🎀

pgBadger is able to autodetect your log file format (syslog, stderr, csvlog or jsonlog) if the file is long enough. It's designed to parse huge log files as well as compressed files. Supported compressed formats are gzip, bzip2, lz4, xz, zip and zstd. For the xz format you must have an xz version higher than 5.05 that supports the --robot option. lz4 files must be compressed with the --content-size option for pgbadger to determine the uncompressed file size. For the complete list of features, see below. 👇

All charts are zoomable and can be saved as PNG images. 🖼️

You can also limit pgBadger to only report errors or remove any part of the report using command-line options.

pgBadger supports any custom format set in the log_line_prefix directive of your postgresql.conf file as long as it at least specifies the %t, %m or %n and %p or %c patterns.

pgBadger allows parallel processing of a single log file or multiple files through the use of the -j option specifying the number of CPUs. ⚡

If you want to save system performance you can also use log_duration instead of log_min_duration_statement to have reports on duration and number of queries only.

🌈 FEATURE 🌈

pgBadger reports everything about your SQL queries:

  • Overall statistics.
  • The most frequent waiting queries.
  • Queries that waited the most.
  • Queries generating the most temporary files.
  • Queries generating the largest temporary files.
  • The slowest queries.
  • Queries that took up the most time.
  • The most frequent queries.
  • The most frequent errors.
  • Histogram of query times.
  • Histogram of sessions times.
  • Users involved in top queries.
  • Applications involved in top queries.
  • Queries generating the most cancellation.
  • Queries most cancelled.
  • The most time consuming prepare/bind queries

The following reports are also available with hourly charts divided into periods of five minutes:

  • SQL queries statistics.
  • Temporary file statistics.
  • Checkpoints statistics.
  • Autovacuum and autoanalyze statistics.
  • Cancelled queries.
  • Error events (panic, fatal, error and warning).
  • Error class distribution.

There are also some pie charts about distribution of:

  • Locks statistics.
  • Queries by type (select/insert/update/delete).
  • Distribution of queries type per database/application
  • Sessions per database/user/client/application.
  • Connections per database/user/client/application.
  • Autovacuum and autoanalyze per table.
  • Queries per user and total duration per user.

All charts are zoomable and can be saved as PNG images. SQL queries reported are highlighted and beautified automatically. ✨

pgBadger is also able to parse PgBouncer log files and to create the following reports:

  • Request Throughput
  • Bytes I/O Throughput
  • Average Query Duration
  • Simultaneous sessions
  • Histogram of sessions times
  • Sessions per database
  • Sessions per user
  • Sessions per host
  • Established connections
  • Connections per database
  • Connections per user
  • Connections per host
  • Most used reserved pools
  • Most Frequent Errors/Events

You can also have incremental reports with one report per day and a cumulative report per week. Two multiprocess modes are available to speed up log parsing, one using one core per log file, and the second using multiple cores to parse a single file. These modes can be combined. 🧚

Histogram granularity can be adjusted using the -A command-line option. By default, they will report the mean of each top queries/errors occurring per hour, but you can specify the granularity down to the minute.

pgBadger can also be used in a central place to parse remote log files using a passwordless SSH connection. This mode can be used with compressed files and in the multiprocess per file mode (-J), but cannot be used with the CSV log format. 🚫

Examples of reports can be found here: https://pgbadger.darold.net/#reports

🎀 REQUIREMENT 🎀

pgBadger comes as a single Perl script — you do not need anything other than a modern Perl distribution. Charts are rendered using a JavaScript library, so you don't need anything other than a web browser. Your browser will do all the work. 💅

If you plan to parse PostgreSQL CSV log files, you might need some Perl Modules:

  • Text::CSV_XS - to parse PostgreSQL CSV log files.

This module is optional, if you don't have PostgreSQL log in the CSV format, you don't need to install it.

If you want to export statistics as JSON file, you need an additional Perl module:

  • JSON::XS - JSON serialising/deserialising, done correctly and fast

This module is optional, if you don't select the json output format, you don't need to install it. You can install it on a Debian-like system using:

sudo apt-get install libjson-xs-perl

and on RPM-like system using:

sudo yum install perl-JSON-XS

Compressed log file format is autodetected from the file extension. If pgBadger finds a gz extension, it will use the zcat utility; with a bz2 extension, it will use bzcat; with lz4, it will use lz4cat; with zst, it will use zstdcat; if the file extension is zip or xz, then the unzip or xz utility will be used. 🗜️

If those utilities are not found in the PATH environment variable, then use the --zcat command-line option to change this path. For example:

--zcat="/usr/local/bin/gunzip -c" or --zcat="/usr/local/bin/bzip2 -dc"
--zcat="C:\tools\unzip -p"

By default, pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities following the file extension. If you use the default autodetection of compression format, you can mix gz, bz2, lz4, xz, zip or zstd files. Specifying a custom value of --zcat option will remove the possibility of mixed compression format.

Note that multiprocessing cannot be used with compressed files or CSV files as well as under Windows platform. 😭

💖 INSTALLATION 💖

Download the tarball from GitHub and unpack the archive as follow:

tar xzf pgbadger-11.x.tar.gz
cd pgbadger-11.x/
perl Makefile.PL
make && sudo make install

This will copy the Perl script pgbadger to /usr/local/bin/pgbadger by default and the man page into /usr/local/share/man/man1/pgbadger.1. Those are the default installation directories for 'site' install.

If you want to install all under /usr/ location, use INSTALLDIRS='perl' as an argument of Makefile.PL. The script will be installed into /usr/bin/pgbadger and the manpage into /usr/share/man/man1/pgbadger.1.

For example, to install everything just like Debian does, proceed as follows:

perl Makefile.PL INSTALLDIRS=vendor

By default, INSTALLDIRS is set to site.

🦋 POSTGRESQL CONFIGURATION 🦋

You must enable and set some configuration directives in your postgresql.conf before starting. 👑

You must first enable SQL query logging to have something to parse:

log_min_duration_statement = 0

Here every statement will be logged, on a busy server you may want to increase this value to only log queries with a longer duration. Note that if you have log_statement set to 'all', nothing will be logged through the log_min_duration_statement directive. See the next chapter for more information.

pgBadger supports any custom format set in the log_line_prefix directive of your postgresql.conf file as long as it at least specifies a time escape sequence (%t, %m or %n) and a process-related escape sequence (%p or %c).

For example, with 'stderr' log format, log_line_prefix must be at least:

log_line_prefix = '%t [%p]: '

Log line prefix could add user, database name, application name and client ip address as follows:

log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

or for syslog log file format:

log_line_prefix = 'user=%u,db=%d,app=%a,client=%h '

Log line prefix for stderr output could also be:

log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h '

or for syslog output:

log_line_prefix = 'db=%d,user=%u,app=%a,client=%h '

You need to enable other parameters in postgresql.conf to get more information from your log files:

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0
log_error_verbosity = default
Do not enable log_statement as its log format will not be parsed by pgBadger. 🚫

Of course your log messages should be in English with or without locale support:

lc_messages='en_US.UTF-8'
lc_messages='C'

pgBadger parser does not support other locales, like 'fr_FR.UTF-8' for example.

🌸 LOG STATEMENTS 🌸

Considerations about log_min_duration_statement, log_duration and log_statement configuration directives.

If you want the query statistics to include the actual query strings, you must set log_min_duration_statement to 0 or more milliseconds.

If you just want to report duration and number of queries and don't want all details about queries, set log_min_duration_statement to -1 to disable it and enable log_duration in your postgresql.conf file. If you want to add the most common query report, you can either choose to set log_min_duration_statement to a higher value or to enable log_statement.

Enabling log_min_duration_statement will add reports about slowest queries and queries that took up the most time. Take care that if you have log_statement set to 'all', nothing will be logged with log_min_duration_statement.

Warning: Do not enable both log_min_duration_statement, log_duration and log_statement all together, this will result in wrong counter values. Note that this will also increase drastically the size of your log. log_min_duration_statement should always be preferred. 💀

🌈 PARALLEL PROCESSING 🌈

okay bestie, want your report to go zoom? just pass -j N where N is how many cores you wanna throw at it. that's it. that's the whole vibe. 💅

here's exactly what pgBadger does when you do that:

for each log file
    chunk size = int(file size / N)
    look at start/end offsets of these chunks
    fork N processes and seek to the start offset of each chunk
        each process will terminate when the parser reach the end offset
        of its chunk
        each process write stats into a binary temporary file
    wait for all children processes to terminate
All binary temporary files generated will then be read and loaded into
memory to build the html output.

so with that method, at the start/end of each chunk pgBadger may truncate or omit a maximum of N queries per log file — which is honestly an insignificant little gap if you've got millions of queries sitting in that log file. the chance that the exact query you were hunting for is the one that got lost? near 0. that's why the author thinks this gap is livable. most of the time the query is just counted twice, but truncated. 🫶

now, when you have many small log files and many CPUs, it's speedier to dedicate one core to one log file at a time. to get that behavior you use option -J N instead. with 200 log files of 10MB each, the -J option starts being really interesting at 8 cores. and the glow-up? using this method you are sure you won't lose any queries in the reports. no cap. ✨

here's a benchmark done on a server with 8 CPUs and a single file of 9.5GB:

         Option |  1 CPU  | 2 CPU | 4 CPU | 8 CPU
        --------+---------+-------+-------+------
           -j   | 1h41m18 | 50m25 | 25m39 | 15m58
           -J   | 1h41m18 | 54m28 | 41m16 | 34m45

and with 200 log files of 10MB each, so 2GB in total, the results are slightly different:

         Option | 1 CPU | 2 CPU | 4 CPU | 8 CPU
        --------+-------+-------+-------+------
           -j   | 20m15 |  9m56 |  5m20 | 4m20
           -J   | 20m15 |  9m49 |  5m00 | 2m40

so yeah: it is recommended to use -j unless you have hundreds of small log files AND can use at least 8 CPUs. 👑

IMPORTANT: when you are using parallel parsing, pgBadger will generate a lot of temporary files in the /tmp directory and will remove them at the end, so do not remove those files unless pgBadger is not running. they are all named with the following template tmp_pgbadgerXXXX.bin so they can be easily identified. 🙈

💖 INCREMENTAL REPORTS 💖

pgBadger includes an automatic incremental report mode using option -I or --incremental. when you're running in this mode, pgBadger generates one report per day and a cumulative report per week. output is first done in binary format into the mandatory output directory (see option -O or --outdir), then in HTML format for daily and weekly reports, with a main index file.

that main index file shows a dropdown menu per week, with a link to each week report and links to the daily reports of each week. cute, right? 🎀

for example, if you run pgBadger like this based on a daily rotated file:

0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/

you will have all daily and weekly reports for the full running period. slay. ✨

in this mode, pgBadger creates an automatic incremental file in the output directory, so you don't have to use the -l option unless you want to change the path of that file. which means: you can run pgBadger in this mode every day on a log file that's rotated every week, and it will not count the log entries twice. 💅

to save disk space, you may want to use the -X or --extra-files command-line option to force pgBadger to write JavaScript and CSS to separate files in the output directory. the resources will then be loaded using script and link tags.

🦋 Rebuilding reports

incremental reports can be rebuilt after a pgbadger report fix, or after a new feature lands, to update all HTML reports. to rebuild all reports where a binary file is still present, proceed as follows:

rm /path/to/reports/*.js
rm /path/to/reports/*.css
pgbadger -X -I -O /path/to/reports/ --rebuild

it will also update all resource files (JS and CSS). and use -E or --explode if the reports were built using this option. 🫶

🌸 Monthly reports

by default, pgBadger in incremental mode only computes daily and weekly reports. if you want monthly cumulative reports, you will have to use a separate command to specify the report to build. for example, to build a report for August 2019:

pgbadger -X --month-report 2019-08 /var/www/pg_reports/

this will add a link to the month name into the calendar view of incremental reports so you can look at the monthly report. the report for a current month can be run every day — it is entirely rebuilt each time. the monthly report is not built by default because it could take a lot of time following the amount of data. 😭

if reports were built with the per-database option ( -E | --explode ), it must be used too when calling pgbadger to build the monthly report:

pgbadger -E -X --month-report 2019-08 /var/www/pg_reports/

same thing when using the rebuild option ( -R | --rebuild ). 💜

⭐ BINARY FORMAT ⭐

using the binary format it is possible to create custom incremental and cumulative reports. for example, if you want to refresh a pgBadger report each hour from a daily PostgreSQL log file, you can proceed by running the following commands each hour:

pgbadger --last-parsed .pgbadger_last_state_file -o sunday/hourX.bin /var/log/pgsql/postgresql-Sun.log

to generate the incremental data files in binary format. and to generate the fresh HTML report from that binary file:

pgbadger sunday/*.bin

or, as another example, if you generate one log file per hour and you want reports to be rebuilt each time the log file is rotated, proceed as follows:

pgbadger -o day1/hour01.bin /var/log/pgsql/pglog/postgresql-2012-03-23_10.log
pgbadger -o day1/hour02.bin /var/log/pgsql/pglog/postgresql-2012-03-23_11.log
pgbadger -o day1/hour03.bin /var/log/pgsql/pglog/postgresql-2012-03-23_12.log
...

when you want to refresh the HTML report, for example each time after a new binary file is generated, just do the following:

pgbadger -o day1_report.html day1/*.bin

adjust the commands to suit your particular needs. you got this. 🧚

💜 JSON FORMAT 💜

JSON format is good for sharing data with other languages, which makes it easy to integrate pgBadger results into other monitoring tools, like Cacti or Graphite. iykyk. 🌈

👑 AUTHORS 👑

pgBadger is an original work from Gilles Darold.

the pgBadger logo is an original creation of Damien Cazeils.

the pgBadger v4.x design comes from the "Art is code" company.

this web site is a work of Gilles Darold.

pgBadger is maintained by Gilles Darold and everyone who wants to contribute.

many people have contributed to pgBadger, they are all quoted in the Changelog file. 🫶

🌟 LICENSE 🌟

pgBadger is free software distributed under the PostgreSQL Licence.

Copyright (c) 2012-2026, Gilles Darold

a modified version of the SQL::Beautify Perl Module is embedded in pgBadger with copyright (C) 2009 by Jonas Kramer and is published under the terms of the Artistic License 2.0.

and that's the whole page bestie, we made it to the end 🎉 this write-up is from the perl v5.40.1 docs, dated 2025-12-30, and you'll find it filed under PGBADGER(1p) if you ever need to come back to it. go forth and parse those logs, main character. 💅✨🦋

📦 where this page comes from