.\" Automatically generated by Pod::Man 4.14 (Pod::Simple 3.43)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is >0, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{\
.    if \nF \{\
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{\
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "PGBADGER 1p"
.TH PGBADGER 1p "2024-01-04" "perl v5.36.0" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
pgBadger \- a fast PostgreSQL log analysis report
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
Usage: pgbadger [options] logfile [...]
.PP
.Vb 1
\&    PostgreSQL log analyzer with fully detailed reports and graphs.
.Ve
.PP
Arguments:
.PP
.Vb 3
\&    logfile can be a single log file, a list of files, or a shell command
\&    returning a list of files. If you want to pass log content from stdin
\&    use \- as filename. Note that input from stdin will not work with csvlog.
.Ve
.PP
Options:
.PP
.Vb 10
\&    \-a | \-\-average minutes : number of minutes to build the average graphs of
\&                             queries and connections. Default 5 minutes.
\&    \-A | \-\-histo\-average min: number of minutes to build the histogram graphs
\&                             of queries. Default 60 minutes.
\&    \-b | \-\-begin datetime  : start date/time for the data to be parsed in log
\&                             (either a timestamp or a time)
\&    \-c | \-\-dbclient host   : only report on entries for the given client host.
\&    \-C | \-\-nocomment       : remove comments like /* ... */ from queries.
\&    \-d | \-\-dbname database : only report on entries for the given database.
\&    \-D | \-\-dns\-resolv      : client ip addresses are replaced by their DNS name.
\&                             Be warned that this can really slow down pgBadger.
\&    \-e | \-\-end datetime    : end date/time for the data to be parsed in log
\&                             (either a timestamp or a time)
\&    \-E | \-\-explode         : explode the main report by generating one report
\&                             per database. Global information not related to a
\&                             database is added to the postgres database report.
\&    \-f | \-\-format logtype  : possible values: syslog, syslog2, stderr, jsonlog,
\&                             csv, pgbouncer, logplex, rds and redshift. Use this
\&                             option when pgBadger is not able to detect the log
\&                             format.
\&    \-G | \-\-nograph         : disable graphs on HTML output. Enabled by default.
\&    \-h | \-\-help            : show this message and exit.
\&    \-H | \-\-html\-outdir path: path to directory where HTML report must be written
\&                             in incremental mode, binary files stay on directory
\&                             defined with \-O, \-\-outdir option.
\&    \-i | \-\-ident name      : programname used as syslog ident. Default: postgres
\&    \-I | \-\-incremental     : use incremental mode, reports will be generated by
\&                             days in a separate directory, \-\-outdir must be set.
\&    \-j | \-\-jobs number     : number of jobs to run at same time for a single log
\&                             file. Run as single by default or when working with
\&                             csvlog format.
\&    \-J | \-\-Jobs number     : number of log files to parse in parallel. Process
\&                             one file at a time by default.
\&    \-l | \-\-last\-parsed file: allow incremental log parsing by registering the
\&                             last datetime and line parsed. Useful if you want
\&                             to watch errors since last run or if you want one
\&                             report per day with a log rotated each week.
\&    \-L | \-\-logfile\-list file:file containing a list of log files to parse.
\&    \-m | \-\-maxlength size  : maximum length of a query, it will be restricted to
\&                             the given size. Default truncate size is 100000.
\&    \-M | \-\-no\-multiline    : do not collect multiline statements to avoid garbage
\&                             especially on errors that generate a huge report.
\&    \-N | \-\-appname name    : only report on entries for given application name
\&    \-o | \-\-outfile filename: define the filename for the output. Default depends
\&                             on the output format: out.html, out.txt, out.bin,
\&                             or out.json. This option can be used multiple times
\&                             to output several formats. To use json output, the
\&                             Perl module JSON::XS must be installed, to dump
\&                             output to stdout, use \- as filename.
\&    \-O | \-\-outdir path     : 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 it if you
\&                             aren\*(Aqt using one of the standard prefixes specified
\&                             in the pgBadger documentation, such as if your
\&                             prefix includes additional variables like client IP
\&                             or application name. MUST contain escape sequences
\&                             for time (%t, %m or %n) and processes (%p or %c).
\&                             See examples below.
\&    \-P | \-\-no\-prettify     : disable SQL queries prettify formatter.
\&    \-q | \-\-quiet           : don\*(Aqt print anything to stdout, not even a progress
\&                             bar.
\&    \-Q | \-\-query\-numbering : add numbering of queries to the output when using
\&                             options \-\-dump\-all\-queries or \-\-normalized\-only.
\&    \-r | \-\-remote\-host ip  : set the host where to execute the cat command on
\&                             remote log file to parse the file locally.
\&    \-R | \-\-retention N     : number of weeks to keep in incremental mode. Defaults
\&                             to 0, disabled. Used to set the number of weeks to
\&                             keep in output directory. Older weeks and days
\&                             directories are automatically removed.
\&    \-s | \-\-sample number   : number of query samples to store. Default: 3.
\&    \-S | \-\-select\-only     : only report SELECT queries.
\&    \-t | \-\-top number      : number of queries to store/display. Default: 20.
\&    \-T | \-\-title string    : change title of the HTML page report.
\&    \-u | \-\-dbuser username : only report on entries for the given user.
\&    \-U | \-\-exclude\-user username : exclude entries for the specified user from
\&                             report. Can be used multiple time.
\&    \-v | \-\-verbose         : enable verbose or debug mode. Disabled by default.
\&    \-V | \-\-version         : show pgBadger version and exit.
\&    \-w | \-\-watch\-mode      : only report errors just like logwatch could do.
\&    \-W | \-\-wide\-char       : encode html output of queries into UTF8 to avoid
\&                             Perl message "Wide character in print".
\&    \-x | \-\-extension       : output format. Values: text, html, bin or json.
\&                             Default: html
\&    \-X | \-\-extra\-files     : in incremental mode allow pgBadger to write CSS and
\&                             JS files in the output directory as separate files.
\&    \-z | \-\-zcat exec_path  : set the full path to the zcat program. Use it if
\&                             zcat, bzcat or unzip is not in your path.
\&    \-Z | \-\-timezone +/\-XX  : Set the number of hours from GMT of the timezone.
\&                             Use this to adjust date/time in 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.
\&    \-\-exclude\-query regex  : any query matching the given regex will be 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
\&                             to exclude queries from the report. One regex per
\&                             line.
\&    \-\-include\-query regex  : any query that does not match the given regex will
\&                             be 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 to the
\&                             queries to include from the report. One regex per
\&                             line.
\&    \-\-disable\-error        : do not generate error report.
\&    \-\-disable\-hourly       : do not generate hourly report.
\&    \-\-disable\-type         : do not generate report of queries by type, database
\&                             or user.
\&    \-\-disable\-query        : do not generate query reports (slowest, most
\&                             frequent, queries by users, by database, ...).
\&    \-\-disable\-session      : do not generate session report.
\&    \-\-disable\-connection   : do not generate connection report.
\&    \-\-disable\-lock         : do not generate lock report.
\&    \-\-disable\-temporary    : do not generate temporary report.
\&    \-\-disable\-checkpoint   : do not generate checkpoint/restartpoint report.
\&    \-\-disable\-autovacuum   : do not generate 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 will be
\&                             excluded from the report. Example: "2013\-04\-12 .*"
\&                             You can use this option multiple times.
\&    \-\-include\-time  regex  : only timestamps matching the given regex will be
\&                             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
\&                             report. Example: "pg_dump". Can be used multiple
\&                             times.
\&    \-\-exclude\-appname name : exclude entries for the specified application name
\&                             from report.  Example: "pg_dump".  Can be used
\&                             multiple times.
\&    \-\-exclude\-line regex   : exclude any log entry that will match 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, useful to hide
\&                             confidential data.
\&    \-\-noreport             : no reports will be created in incremental mode.
\&    \-\-log\-duration         : force pgBadger to associate log entries generated
\&                             by both log_duration = on and log_statement = \*(Aqall\*(Aq
\&    \-\-enable\-checksum      : used to add an md5 sum under each query report.
\&    \-\-journalctl command   : command to use to replace PostgreSQL logfile by
\&                             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 to manage concurrent
\&                             execution of pgBadger. Default: pgbadger.pid
\&    \-\-rebuild              : used to rebuild all html reports in incremental
\&                             output directories where there\*(Aqs binary data files.
\&    \-\-pgbouncer\-only       : only show PgBouncer\-related menus in the header.
\&    \-\-start\-monday         : in incremental mode, calendar weeks start on
\&                             Sunday. Use this option to start on a Monday.
\&    \-\-iso\-week\-number      : in incremental mode, calendar weeks start on
\&                             Monday and 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.
\&    \-\-normalized\-only      : only dump all normalized queries to out.txt
\&    \-\-log\-timezone +/\-XX   : Set the number of hours from GMT of the timezone
\&                             that must be used to adjust date/time read from
\&                             log file before beeing parsed. 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.
\&    \-\-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.
\&    \-\-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() || \*(Aq/tmp\*(Aq
\&    \-\-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.
\&    \-\-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.
\&    \-\-dump\-raw\-csv         : parse the log and dump the information into CSV
\&                             format. No further processing is done, no report.
\&    \-\-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.
.Ve
.PP
pgBadger is able to parse a remote log file using a passwordless ssh connection.
Use \-r or \-\-remote\-host to set the host \s-1IP\s0 address or hostname. There are also
some additional options to fully control the ssh connection.
.PP
.Vb 9
\&    \-\-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
.Ve
.PP
Log file to parse can also be specified using an \s-1URI,\s0 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.
.PP
Return codes:
.PP
.Vb 5
\&    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
.Ve
.PP
Examples:
.PP
.Vb 10
\&    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 \*(Aq%t [%p]: user=%u,db=%d,client=%h\*(Aq /pglog/postgresql\-2012\-08\-21*
\&    pgbadger \-\-prefix \*(Aq%m %u@%d %p %r %a : \*(Aq /pglog/postgresql.log
\&    # Log line prefix with syslog log output
\&    pgbadger \-\-prefix \*(Aquser=%u,db=%d,client=%h,appname=%a\*(Aq /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
.Ve
.PP
Use \s-1URI\s0 notation for remote log file:
.PP
.Vb 3
\&    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*
.Ve
.PP
You can use together a local PostgreSQL log and a remote pgbouncer log file to parse:
.PP
.Vb 1
\&    pgbadger /var/log/postgresql/postgresql\-10.1\-main.log ssh://username@172.12.110.14/pgbouncer.log
.Ve
.PP
Reporting errors every week by cron job:
.PP
.Vb 1
\&    30 23 * * 1 /usr/bin/pgbadger \-q \-w /var/log/postgresql.log \-o /var/reports/pg_errors.html
.Ve
.PP
Generate report every week using incremental behavior:
.PP
.Vb 1
\&    0 4 * * 1 /usr/bin/pgbadger \-q \`find /var/log/ \-mtime \-7 \-name "postgresql.log*"\` \-o /var/reports/pg_errors\-\`date +\e%F\`.html \-l /var/reports/pgbadger_incremental_file.dat
.Ve
.PP
This supposes that your log file and \s-1HTML\s0 report are also rotated every week.
.PP
Or better, use the auto-generated incremental reports:
.PP
.Vb 1
\&    0 4 * * * /usr/bin/pgbadger \-I \-q /var/log/postgresql/postgresql.log.1 \-O /var/www/pg_reports/
.Ve
.PP
will generate a report per day and per week.
.PP
In incremental mode, you can also specify the number of weeks to keep in the
reports:
.PP
.Vb 1
\&    /usr/bin/pgbadger \-\-retention 2 \-I \-q /var/log/postgresql/postgresql.log.1 \-O /var/www/pg_reports/
.Ve
.PP
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:
.PP
.Vb 1
\&    pgbadger \-\-exclude\-time "2013\-09\-.* (23|13):.*" postgresql.log
.Ve
.PP
This will help avoid having \s-1COPY\s0 statements, as generated by pg_dump, on top of
the list of slowest queries. You can also use \-\-exclude\-appname \*(L"pg_dump\*(R" to
solve this problem in a simpler way.
.PP
You can also parse journalctl output just as if it was a log file:
.PP
.Vb 1
\&    pgbadger \-\-journalctl \*(Aqjournalctl \-u postgresql\-9.5\*(Aq
.Ve
.PP
or worst, call it from a remote host:
.PP
.Vb 1
\&    pgbadger \-r 192.168.1.159 \-\-journalctl \*(Aqjournalctl \-u postgresql\-9.5\*(Aq
.Ve
.PP
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.
.PP
To rebuild all incremental html reports after, proceed as follow:
.PP
.Vb 3
\&    rm /path/to/reports/*.js
\&    rm /path/to/reports/*.css
\&    pgbadger \-X \-I \-O /path/to/reports/ \-\-rebuild
.Ve
.PP
it will also update all resource files (\s-1JS\s0 and \s-1CSS\s0). Use \-E or \-\-explode
if the reports were built using this option.
.PP
pgBadger also supports Heroku PostgreSQL logs using logplex format:
.PP
.Vb 1
\&    heroku logs \-p postgres | pgbadger \-f logplex \-o heroku.html \-
.Ve
.PP
this will stream Heroku PostgreSQL log to pgbadger through stdin.
.PP
pgBadger can auto detect \s-1RDS\s0 and cloudwatch PostgreSQL logs using
rds format:
.PP
.Vb 1
\&    pgbadger \-f rds \-o rds_out.html rds.log
.Ve
.PP
Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated
in \s-1JSON\s0 format. It is autodetected by pgBadger but in case you need to force
the log format use `jsonlog`:
.PP
.Vb 1
\&    pgbadger \-f jsonlog \-o cloudsql_out.html cloudsql.log
.Ve
.PP
This is the same as with the jsonlog extension, the json format is different
but pgBadger can parse both formats.
.PP
pgBadger also supports logs produced by CloudNativePG Postgres operator for Kubernetes:
.PP
.Vb 1
\&    pgbadger \-f jsonlog \-o cnpg_out.html cnpg.log
.Ve
.PP
To create a cumulative report over a month use command:
.PP
.Vb 1
\&    pgbadger \-\-month\-report 2919\-05 /path/to/incremental/reports/
.Ve
.PP
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.
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
pgBadger is a PostgreSQL log analyzer built for speed providing fully
detailed reports based on your PostgreSQL log files. It's a small standalone
Perl script that outperforms any other PostgreSQL log analyzer.
.PP
It is written in pure Perl and uses a JavaScript library (flotr2) to draw
graphs so that you don't need to install any additional Perl modules or
other packages. Furthermore, this library gives us more features such
as zooming. pgBadger also uses the Bootstrap JavaScript library and
the FontAwesome webfont for better design. Everything is embedded.
.PP
pgBadger is able to autodetect your log file format (syslog, stderr, csvlog
or jsonlog) if the file is long enough. It is 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.
.PP
All charts are zoomable and can be saved as \s-1PNG\s0 images.
.PP
You can also limit pgBadger to only report errors or remove any part of the
report using command-line options.
.PP
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 \f(CW%t\fR and \f(CW%p\fR patterns.
.PP
pgBadger allows parallel processing of a single log file or multiple
files through the use of the \-j option specifying the number of CPUs.
.PP
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.
.SH "FEATURE"
.IX Header "FEATURE"
pgBadger reports everything about your \s-1SQL\s0 queries:
.PP
.Vb 10
\&        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
.Ve
.PP
The following reports are also available with hourly charts divided into
periods of five minutes:
.PP
.Vb 7
\&        SQL queries statistics.
\&        Temporary file statistics.
\&        Checkpoints statistics.
\&        Autovacuum and autoanalyze statistics.
\&        Cancelled queries.
\&        Error events (panic, fatal, error and warning).
\&        Error class distribution.
.Ve
.PP
There are also some pie charts about distribution of:
.PP
.Vb 7
\&        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.
.Ve
.PP
All charts are zoomable and can be saved as \s-1PNG\s0 images. \s-1SQL\s0 queries reported are
highlighted and beautified automatically.
.PP
pgBadger is also able to parse PgBouncer log files and to create the following
reports:
.PP
.Vb 10
\&        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
.Ve
.PP
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.
.PP
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.
.PP
pgBadger can also be used in a central place to parse remote log files using a
passwordless \s-1SSH\s0 connection. This mode can be used with compressed files and in
the multiprocess per file mode (\-J), but cannot be used with the \s-1CSV\s0 log format.
.PP
Examples of reports can be found here: https://pgbadger.darold.net/#reports
.SH "REQUIREMENT"
.IX Header "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.
.PP
If you plan to parse PostgreSQL \s-1CSV\s0 log files, you might need some Perl Modules:
.PP
.Vb 1
\&        Text::CSV_XS \- to parse PostgreSQL CSV log files.
.Ve
.PP
This module is optional, if you don't have PostgreSQL log in the \s-1CSV\s0 format, you don't
need to install it.
.PP
If you want to export statistics as \s-1JSON\s0 file, you need an additional Perl module:
.PP
.Vb 1
\&        JSON::XS \- JSON serialising/deserialising, done correctly and fast
.Ve
.PP
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:
.PP
.Vb 1
\&        sudo apt\-get install libjson\-xs\-perl
.Ve
.PP
and on RPM-like system using:
.PP
.Vb 1
\&        sudo yum install perl\-JSON\-XS
.Ve
.PP
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.
.PP
If those utilities are not found in the \s-1PATH\s0 environment variable, then use the \-\-zcat
command-line option to change this path. For example:
.PP
.Vb 2
\&        \-\-zcat="/usr/local/bin/gunzip \-c" or \-\-zcat="/usr/local/bin/bzip2 \-dc"
\&        \-\-zcat="C:\etools\eunzip \-p"
.Ve
.PP
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.
.PP
Note that multiprocessing cannot be used with compressed files or \s-1CSV\s0 files as
well as under Windows platform.
.SH "INSTALLATION"
.IX Header "INSTALLATION"
Download the tarball from GitHub and unpack the archive as follow:
.PP
.Vb 4
\&        tar xzf pgbadger\-11.x.tar.gz
\&        cd pgbadger\-11.x/
\&        perl Makefile.PL
\&        make && sudo make install
.Ve
.PP
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.
.PP
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.
.PP
For example, to install everything just like Debian does, proceed as follows:
.PP
.Vb 1
\&        perl Makefile.PL INSTALLDIRS=vendor
.Ve
.PP
By default, \s-1INSTALLDIRS\s0 is set to site.
.SH "POSTGRESQL CONFIGURATION"
.IX Header "POSTGRESQL CONFIGURATION"
You must enable and set some configuration directives in your postgresql.conf
before starting.
.PP
You must first enable \s-1SQL\s0 query logging to have something to parse:
.PP
.Vb 1
\&        log_min_duration_statement = 0
.Ve
.PP
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.
.PP
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, \f(CW%m\fR or \f(CW%n\fR) and a process-related escape sequence (%p or \f(CW%c\fR).
.PP
For example, with 'stderr' log format, log_line_prefix must be at least:
.PP
.Vb 1
\&        log_line_prefix = \*(Aq%t [%p]: \*(Aq
.Ve
.PP
Log line prefix could add user, database name, application name and client ip
address as follows:
.PP
.Vb 1
\&        log_line_prefix = \*(Aq%t [%p]: user=%u,db=%d,app=%a,client=%h \*(Aq
.Ve
.PP
or for syslog log file format:
.PP
.Vb 1
\&        log_line_prefix = \*(Aquser=%u,db=%d,app=%a,client=%h \*(Aq
.Ve
.PP
Log line prefix for stderr output could also be:
.PP
.Vb 1
\&        log_line_prefix = \*(Aq%t [%p]: db=%d,user=%u,app=%a,client=%h \*(Aq
.Ve
.PP
or for syslog output:
.PP
.Vb 1
\&        log_line_prefix = \*(Aqdb=%d,user=%u,app=%a,client=%h \*(Aq
.Ve
.PP
You need to enable other parameters in postgresql.conf to get more information from your log files:
.PP
.Vb 7
\&        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
.Ve
.PP
Do not enable log_statement as its log format will not be parsed by pgBadger.
.PP
Of course your log messages should be in English with or without locale support:
.PP
.Vb 2
\&        lc_messages=\*(Aqen_US.UTF\-8\*(Aq
\&        lc_messages=\*(AqC\*(Aq
.Ve
.PP
pgBadger parser does not support other locales, like 'fr_FR.UTF\-8' for example.
.SH "LOG STATEMENTS"
.IX Header "LOG STATEMENTS"
Considerations about log_min_duration_statement, log_duration and log_statement
configuration directives.
.PP
If you want the query statistics to include the actual query strings, you
must set log_min_duration_statement to 0 or more milliseconds.
.PP
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.
.PP
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.
.PP
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.
.SH "PARALLEL PROCESSING"
.IX Header "PARALLEL PROCESSING"
To enable parallel processing you just have to use the \-j N option where N is
the number of cores you want to use.
.PP
pgBadger will then proceed as follow:
.PP
.Vb 10
\&        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.
.Ve
.PP
With that method, at start/end of chunks pgBadger may truncate or omit a
maximum of N queries per log file, which is an insignificant gap if you have
millions of queries in your log file. The chance that the query that you were
looking for is lost is near 0, this is why I think this gap is livable. Most
of the time the query is counted twice but truncated.
.PP
When you have many small log files and many CPUs, it is speedier to dedicate
one core to one log file at a time. To enable this behavior, you have to use
option \-J N instead. With 200 log files of 10MB each, the use of the \-J option
starts being really interesting with 8 cores. Using this method you will be
sure not to lose any queries in the reports.
.PP
Here is a benchmark done on a server with 8 CPUs and a single file of 9.5GB.
.PP
.Vb 4
\&         Option |  1 CPU  | 2 CPU | 4 CPU | 8 CPU
\&        \-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-
\&           \-j   | 1h41m18 | 50m25 | 25m39 | 15m58
\&           \-J   | 1h41m18 | 54m28 | 41m16 | 34m45
.Ve
.PP
With 200 log files of 10MB each, so 2GB in total, the results are slightly
different:
.PP
.Vb 4
\&         Option | 1 CPU | 2 CPU | 4 CPU | 8 CPU
\&        \-\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-\-+\-\-\-\-\-\-
\&           \-j   | 20m15 |  9m56 |  5m20 | 4m20
\&           \-J   | 20m15 |  9m49 |  5m00 | 2m40
.Ve
.PP
So it is recommended to use \-j unless you have hundreds of small log files
and can use at least 8 CPUs.
.PP
\&\s-1IMPORTANT:\s0 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.
.SH "INCREMENTAL REPORTS"
.IX Header "INCREMENTAL REPORTS"
pgBadger includes an automatic incremental report mode using option \-I or
\&\-\-incremental. When running in this mode, pgBadger will generate 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 \s-1HTML\s0 format for daily and weekly reports with a main index file.
.PP
The main index file will show a dropdown menu per week with a link to each
week report and links to daily reports of each week.
.PP
For example, if you run pgBadger as follows based on a daily rotated file:
.PP
.Vb 1
\&    0 4 * * * /usr/bin/pgbadger \-I \-q /var/log/postgresql/postgresql.log.1 \-O /var/www/pg_reports/
.Ve
.PP
you will have all daily and weekly reports for the full running period.
.PP
In this mode, pgBadger will create 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. This means that you can run pgBadger in
this mode each day on a log file rotated each week, and it will not count
the log entries twice.
.PP
To save disk space, you may want to use the \-X or \-\-extra\-files command-line
option to force pgBadger to write JavaScript and \s-1CSS\s0 to separate files in
the output directory. The resources will then be loaded using script and
link tags.
.SS "Rebuilding reports"
.IX Subsection "Rebuilding reports"
Incremental reports can be rebuilt after a pgbadger report fix or a new
feature to update all \s-1HTML\s0 reports. To rebuild all reports where a binary
file is still present, proceed as follow:
.PP
.Vb 3
\&    rm /path/to/reports/*.js
\&    rm /path/to/reports/*.css
\&    pgbadger \-X \-I \-O /path/to/reports/ \-\-rebuild
.Ve
.PP
it will also update all resource files (\s-1JS\s0 and \s-1CSS\s0). Use \-E or \-\-explode
if the reports were built using this option.
.SS "Monthly reports"
.IX Subsection "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:
.PP
.Vb 1
\&    pgbadger \-X \-\-month\-report 2019\-08 /var/www/pg_reports/
.Ve
.PP
this will add a link to the month name into the calendar view of incremental
reports to look at 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.
.PP
If reports were built with the per-database option ( \-E | \-\-explode ), it must
be used too when calling pgbadger to build monthly report:
.PP
.Vb 1
\&    pgbadger \-E \-X \-\-month\-report 2019\-08 /var/www/pg_reports/
.Ve
.PP
This is the same when using the rebuild option ( \-R | \-\-rebuild ).
.SH "BINARY FORMAT"
.IX Header "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:
.PP
.Vb 1
\&    pgbadger \-\-last\-parsed .pgbadger_last_state_file \-o sunday/hourX.bin /var/log/pgsql/postgresql\-Sun.log
.Ve
.PP
to generate the incremental data files in binary format. And to generate the fresh \s-1HTML\s0
report from that binary file:
.PP
.Vb 1
\&    pgbadger sunday/*.bin
.Ve
.PP
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:
.PP
.Vb 4
\&        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
\&        ...
.Ve
.PP
When you want to refresh the \s-1HTML\s0 report, for example, each time after a new binary file
is generated, just do the following:
.PP
.Vb 1
\&        pgbadger \-o day1_report.html day1/*.bin
.Ve
.PP
Adjust the commands to suit your particular needs.
.SH "JSON FORMAT"
.IX Header "JSON FORMAT"
\&\s-1JSON\s0 format is good for sharing data with other languages, which makes it
easy to integrate pgBadger result into other monitoring tools, like Cacti
or Graphite.
.SH "AUTHORS"
.IX Header "AUTHORS"
pgBadger is an original work from Gilles Darold.
.PP
The pgBadger logo is an original creation of Damien Cazeils.
.PP
The pgBadger v4.x design comes from the \*(L"Art is code\*(R" company.
.PP
This web site is a work of Gilles Darold.
.PP
pgBadger is maintained by Gilles Darold and everyone who wants to contribute.
.PP
Many people have contributed to pgBadger, they are all quoted in the Changelog file.
.SH "LICENSE"
.IX Header "LICENSE"
pgBadger is free software distributed under the PostgreSQL Licence.
.PP
Copyright (c) 2012\-2023, Gilles Darold
.PP
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.