DOCSEARCHbyAlgolia
Submit Docs

pgBadger Documentation

repository·master·Indexed 26 days ago

https://github.com/darold/pgbadger

A high-performance PostgreSQL log analyzer that generates detailed reports and visual graphs to diagnose database performance issues. It supports various log formats (syslog, stderr, jsonlog, csv, pgbouncer, logplex, rds, redshift), parallel processing for large files, and an incremental mode for continuous log parsing and historical data management.

Tokens
11.8K
Snippets
17
Records
50
Agent score
86%

What's inside pgBadger

  1. Overview of pgBadger

    master
    pgBadger is a fast PostgreSQL log analyzer that generates fully detailed reports and graphs from PostgreSQL log files. It helps developers and DBAs understand database performance by analyzing log data.

  2. Use incremental reporting for periodic analysis

    master
    Incremental mode (-I) allows you to generate daily reports and cumulative weekly reports. This is ideal for cron jobs to monitor logs over time without re-parsing everything every time.

  4. Create custom reports using binary format

    master

    You can manually manage incremental and cumulative reports by generating and then reading binary files.

    1. Generate binary data from a log file using -o: pgbadger --last-parsed <state_file> -o <output_bin> <log_file>
    2. Generate HTML reports from the accumulated binary files: pgbadger <output_dir>/*.bin

  5. Generate incremental reports with -I

    master

    The -I or --incremental option enables automatic incremental report mode. pgBadger will generate one report per day and a cumulative report per week.

    • Output: Data is first written in binary format to the directory specified by -O or --outdir, then converted to HTML.
    • Automation: This mode is ideal for cron jobs. It creates an automatic incremental file in the output directory so it won't count log entries twice across runs.
    • Extra Files: Use -X or --extra-files to force JavaScript and CSS into separate files to save disk space.

    Example Cron Job:

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

  6. Use the pgBadger CLI command

    master

    The basic syntax for running pgBadger is to provide the command followed by optional flags and the target log file(s).

    Arguments:

    • logfile: Can be a single log file, a list of files, or a shell command that returns a list of files.
    • To pass log content from stdin, use - as the filename. Note that stdin is not compatible with csvlog format.

  7. Install pgBadger from source

    master

    To install pgBadger, download the tarball from GitHub and run the following commands to build and install it using Perl's Makefile.PL.

    By default, it installs to /usr/local/bin/pgbadger (site install). To install into /usr/ (similar to Debian), use INSTALLDIRS='perl' or INSTALLDIRS=vendor during the Makefile.PL step.

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

# To install like Debian (vendor directory):
perl Makefile.PL INSTALLDIRS=vendor
make && sudo make install

  9. Configure PostgreSQL for pgBadger parsing

    master

    To ensure pgBadger can parse your logs, you must configure specific directives in postgresql.conf.

    Required Settings

    • Enable Query Logging: Set log_min_duration_statement = 0 (or a higher value to log only slow queries).
      • Warning: Do not enable log_statement = 'all', as it will prevent log_min_duration_statement from working and pgBadger will not parse it correctly.
    • Log Line Prefix: The log_line_prefix must include a time escape sequence (%t, %m, or %n) and a process-related sequence (%p or %c).
      • Example for stderr: log_line_prefix = '%t [%p]: '
      • Example with extra metadata: log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '
    • Locale: Ensure logs are in English (e.g., lc_messages='en_US.UTF-8' or lc_messages='C').

    Recommended Settings for Detailed Reports

    Enable these to get more information in your reports:

    • 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

  10. Parse local and remote PostgreSQL log files

    master
    pgBadger can parse local log files, compressed files (gzip, bzip2, lz4, xz, zip, zstd), or remote files via SSH or URI. For remote hosts, use the -r or --remote-host flag. You can also use URI notation for http[s] and [s]ftp protocols, or ssh:// for remote access.

  11. Use incremental mode for continuous log parsing

    master

    Incremental mode allows you to generate reports by days and manage historical data efficiently.

    • -I | --incremental: Enables incremental mode. Requires --outdir to be set.
    • -l | --last-parsed file: Registers the last datetime and line parsed to allow incremental parsing. Useful for watching errors since the last run or handling rotated logs.
    • -R | --retention N: Sets the number of weeks to keep in incremental mode (default is 0, which is disabled). Older weeks/days are automatically removed.
    • --day-report YYYY-MM-DD: Creates an HTML report for a specific day. Requires incremental output directories and necessary binary data files.
    • --iso-week-number: In incremental mode, ensures calendar weeks start on Monday and follow ISO 8601.
    • -X | --extra-files: (See output configuration) Allows writing CSS/JS as separate files in the output directory.

  12. Configure log format and custom prefixes

    master

    If pgBadger cannot automatically detect your log format, use the following:

    • -f | --format logtype: Manually specify the format. Supported values: syslog, syslog2, stderr, jsonlog, csv, pgbouncer, logplex, rds, and redshift.
    • -p | --prefix string: Specify your custom log_line_prefix from postgresql.conf. This is required if your prefix includes non-standard variables like client IP or application name. The prefix MUST contain escape sequences for time (%t, %m, or %n) and processes (%p or %c).