This release of pgBadger is a maintenance release that adds some new features.

  * Add report of error class distribution when SQLState is available
    in the log_line_prefix (see %e placeholder).
  * Update SQL Beautifier to pgFormatter v1.6 code.
  * Improve error message normalization.
  * Add --normalized-only option to generate a text file containing all
    normalized queries found in a log with count.
  * Allow %c (session id) to replace %p (pid) as unique session id.
  * Add waiting for lock messages to event reports.
  * Add --start-monday option to start calendar weeks in Monday
    instead of default to Sunday.

There's also some bugs fixes and features enhancements.

  - Add report of error class distribution when SQLState is available
    in the log line prefix. Thanks to jacks33 for the feature request.
  - Fix incremental global index on resize. Thanks to clomdd for the
    report.
  - Fix command tag log_line_prefix placeholder %i to allow space
    character.
  - Fix --exclude-line options and removing of obsolete directory
    when retention is enabled and --noreport is used.
  - Fix typo in "vacuum activity table". Thanks to Nicolas Gollet for
    the patch.
  - Fix autovacuum report. Thanks to Nicolas Gollet for the patch.
  - Fix author of pgbadger's logo - Damien Cazeils and English in
    comments. Thanks to Thibaut Madelaine for the patch.
  - Add information about pgbouncer log format in the -f option.
    Thanks to clomdd for the report.
  - Add --normalized-only information in documentation.
  - Fix broken report of date-time introduced in previous patch.
  - Fix duration/query association when log_duration=on and
    log_statement=all. Thanks to Eric Jensen for the report.
  - Fix normalization of messages about advisory lock. Thanks to
    Thibaut Madelaine for the report.
  - Fix report of auto_explain output. Thanks to fch77700 for the
    report.
  - Fix unwanted log format auto detection with log entry from stdin.
    Thanks to Jesus Adolfo Parra for the report.
  - Add left open parentheses to the "stop" chars of regex to look
    for db client in the prefix to handle the PostgreSQL client
    string format that includes source port. Thanks to Jon Nelson
    for the patch.
  - Fix some spelling errors. Thanks to Jon Nelson for the patch.
  - Allow %c (session id) to replace %p (pid) as unique session id.
    Thanks to Jerryliuk for the report.
  - Allow pgbadger to parse default log_line_prefix that will be
    probably used in 10.0: '%m [%p] '
  - Fix missing first line with interpreter call.
  - Fix missing Avg values in CSV report. Thanks to Yosuke Tomita
    for the report.
  - Fix error message in autodetect_format() method.
  - Add --start-monday option to start calendar weeks in Monday
    instead of default to Sunday. Thanks to Joosep Mae for the feature
    request.
  - Fix --histo-average option. Thanks to Yves Martin for the report.
  - Remove plural form of --ssh-option in documentation. Thanks to
    mark-a-s for the report.
  - Fix --exclude-time filter and rewrite code to skip unwanted line
    as well code to update the progress bar. Thanks to Michael
    Chesterton for the report.
  - Fix support to %r placeholder in prefix instead of %h.

This major release of pgBadger is a port to bootstrap 3 and a version upgrade of all resources files (CSS and Javascript). There's also some bugs fixes and features enhancements.

Backward compatibility with old incremental report might be preserved.

  - Sources and licences of resources files are now on a dedicated
    subdirectory. A script to update their minified version embedded
    in pgbager script has been added. Thanks to Christoph Berg for
    the help and feature request.

  - Try to detect user/database/host from connection strings if
    log_connection is enabled and log_line_prefix doesn't include
    them.
    
    Extend the regex to autodetect database name, user name, client
    ip address and application name. The regex now are the following:
    
    	db => qr/(?:db|database)=([^,]*)/;
    	user => qr/(?:user|usr)=([^,]*)/;
    	client => qr/(?:client|remote|ip|host)=([^,]*)/;
    	appname => qr/(?:app|application)=([^,]*)/;
    
  - Add backward compatibility with older version of pgbadger in
    incremental mode by creating a subdirectory for new CSS and
    Javascript files. This subdirectory is named with the major
    version number of pgbadger.
    
  - Increase the size of the pgbadger logo that appears too small
    with the new font size.

  - Normalize detailed information in all reports.

  - Fix duplicate copy icon in locks report.

  - Fix missing chart on histogram of session time. Thanks to
    Guillaume Lelarge for the report.

  - Add LICENSE file noting the licenses used by the resource
    files. Thanks to Christoph Berg for the patch.

  - Add patch to jqplot library to fix an infinite loop when trying
    to download some charts. Thanks to Julien Tachoires for the help
    to solve this issue.
    
  - Script tools/updt_embedded_rsc.pl will apply the patch to resource
    file resources/jquery.jqplot.js and doesn't complain if it has
    already been applied.

  - Remove single last comma at end of pie chart dataset. Thanks to
    Julien Tachoires for the report.

  - Change display of normalized error

  - Remove unused or auto-generated files

  - Update all resources files (js+css) and create a directory to
    include source of javascript libraries used in pgbadger. There is
    also a new script tools/updt_embedded_rsc.pl the can be used to
    generate the minified version of those files and embedded them
    into pgbadger.  This script will also embedded the FontAwesome.otf
    open truetype font into the fontawesome.css file.

This is a maintenance release that fix some minor bugs. This release also adds replication command messages statistics to the Events reports.

   - Fix auto-detection of stderr format with timestamp as epoch (%n).
   - Fix histogram over multiples days to be cumulative per hour, not
     an average of the number of event per day.
   - Fix parsing of remote file that was failing when the file does
     not exists locally. Thanks to clomdd for the report.
   - Detect timezones like GMT+3 on CSV logs. Thanks to jacksonfoz
     for the patch.
   - Add replication command messages statistics to the Events
     reports. Thanks to Michael Paquier for the feature request.

This is the last minor version of the 8.x series, next major version will include an upgrade of boostrap and jquery library which need some major rewrite.

This is a maintenance release that fix some minor bug. There is also some performances improvement up to 20% on huge files and some new interesting features:

   * Multiprocessing can be used with pgbouncer log files.
   * pgBouncer and PostgreSQL log files can be used together in
     incremental mode.
   * With default or same prefix, stderr and syslog file can be
     parsed together, csvlog format can always be used.
   * Use a modal dialog window to download graphs as png images.
   * Add pl/pgSQL function information to queries when available.

Here are the complete list of changes:

    - Fix report of database system messages.
    - Fix multi line statement concatenation after an error.
    - Fix box size for report of queries generating the most
      temporary files and the most waiting queries.
    - Rewrite code to better handle multi-line queries.
    - Fix garbage in examples of event queries with error only mode
      (option -w). Thanks to Thomas Reiss for the report.
    - Fix getting dataset related to query duration with the use of
      auto_explain. Thanks to tom__b for the patch.
    - Use a modal dialog window to download graphs as png images.
    - Huge rewrite of the incremental mechanism applied to log files
      to handle PostgreSQL and pgbouncer logs at the same time.
    - Multiprocess can be used with pgbouncer log.
    - Add code to remove remaining keyword placeholders tags.
    - Fix an other possible case of truncated date in LAST_PARSED file
      Thanks to brafaeloliveira for the report.
    - Set default scale 1 in pretty_print_number() js function.
    - Fix auto-detection of pgbouncer files that contain only stats
      lines. Thanks to Glyn Astill for the patch.
    - Add date to samples of queries generating most temporary files.
    - Do not display warning message of empty log when quiet mode is
      enable.
    - Fix reading from stdin by disabling pgbouncer format detection.
      Thanks to Robert Vargason for the patch.
    - Fix case of duplicate normalized error message with "nonstandard
      use of ...".
    - Fix storage of current temporary file related request.
    - Use the mnemonic rather than a signal number in kill calls.
      Thanks to Komeda Shinji for the patch.

This is a maintenance release that fix a major issue introduced with support to pgbouncer that prevent parsing of compressed PostgreSQL log files and adds some improvements.

Here are the complete list of changes:

  - Fix one case where pid file remain after dying.
  - Add requirement of log_error_verbosity = default to documentation.
  - Report message "LOG: using stale statistics instead of current ones because
    stats collector is not responding" in events view.
  - Remove obsolete days when we are in binary mode with --noreport
  - Fix wrong report of statements responsible of temporary files. Thanks to Luan
    Nicolini Marcondes for the report. This patch also exclude line with log level LOCATION to be parsed.
  - Fix limit on number of sample at report generation and remove pending LAST_PARSED.tmp file.
  - Update load_stat() function and global variables to support pgbouncer statistics. Update version to 2.0.
  - Handle more kind or query types. Thanks to julien Rouhaud for the patch.
  - Fix pgbouncer log parser to handle message: FATAL: the database system is shutting down
  - Fix whitespace placed in between the E and the quote character. Thanks to clijunky for the report.
  - Fix a major issue introduced with support to pgbouncer that prevent parsing of compressed PostgreSQL
    log files. Thanks to Levente Birta for the report.

This is a major release that adds support to pgbouncer log files. New pgbouncer reports are:

  * Request Throughput
  * Bytes I/O Throughput
  * Queries Average 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

pgbouncer log files can be parsed together with PostgreSQL logs.

It also adds a two new command line options:

>

  * --pgbouncer-only to only show pgbouncer related reports.
  * --rebuild to be able to rebuild all html reports in incremental
    output directory where binary data files are still available.

This release fixes a major bug introduced with journalctl code that was prevented the use of multiprocess feature.

Here the complete list of other changes:

  - Fix progress bar with pgbouncer (only events are increased).
  - Sort %SYMBOLE hashtable for remove "!=" / "=" bug. Thanks to
    Nicolas Gollet for the patch.
  - Fix incorrect numbers on positional parameters in report Queries
    generating most temporary files. Thanks to Oskar Wiksten for the
    report.
  - Update operators list in SQL code beautifier with last update in
    pgFormatter. Thanks to Laurenz Albe for the report and the list
    of missing operators.
  - Cosmetic change to code and add some more debug information.

This is a maintenance release to fix a major bug that was breaking the incremental mode in pgBadger. It also adds some more reports and features.

  * Add --timezone=+/-HH to control the timezone used in charts. The
    javascript library runs at client side so the timezone used is
    the browser timezone so the displayed time in the charts can be
    different from the time in the log file.
  * Add /tmp/pgbadger.pid file to prevent cron jobs overlaping on
    same log files.
  * Add command line option --pid-dir to be able to run two pgbadger
    at the same time by setting an alternate path to the pid file.
  * Report information about "LOG:  skipping analyze of ..." into
    events reports.
  * Report message "LOG: sending cancel to blocking autovacuum" into
   events reports. Useful to look for queries generating autovacuum
    kill on account of a lock issue.

Here is the full list of changes/fixes:

  - Automatically remove obsolete pid file when there is no other
    pgbadger process running (unix only)
  - Update documentation about the --timezone command line option.
  - Add --timezone=+/-HH to control the timezone used in charts.
    Thanks to CZAirwolf for the report.
  - Fix Histogram of session times when there is no data.
  - Fix unclosed test file.
  - Fix an other case where pgbadger.pid was not removed.
  - Always display slides part on connections report even if there
    is no data.
  - Fix some label on sessions reports
  - Add remove of pid file at normal ending.
  - Fix wrong size/offset of log files that was breaking incremental
    mode. Thanks a lot to CZAirwolf for the report and the help to
    find the problem.
  - Add command line option --pid-dir to be able to run two pgbadger
    at the same time by setting an alternate path to the directory
    where the pid file will be written.
  - Add /tmp/pgbadger.pid file to prevent cron jobs overlaping on same
    log files.
  - Report information about "LOG:  skipping analyze of ..." into
    events reports.
  - Report message "LOG: sending cancel to blocking autovacuum" into
    events reports. Usefull to know which queries generate autovacuum
    kill on account of a lock issue.
  - Add more debug information about check log parsing decision.

This new release fixes some issues especially in temporary files reports and adds some features.

  * Allow pgBadger to parse natively the journalctl command output
  * Add new keywords from PG 9.5 for code formating
  * Add support to %n log_line_prefix option for Unix epoch (PG 9.6)

There's also some new command line option:

  * Adds --journalctl_cmd option to enable this functionality and
    set the command. Typically:

	--journalctl_cmd "journalctl -u postgresql-9.4"

    to parse output of PG 9.4 log

Here is the full list of changes/fixes:

  - Fix missing detailed information (date, db, etc.) in Queries
    generating the largest temporary files report.
  - Fix label of sessions histogram. Thanks to Guillaume Lelarge
    for the patch.
  - Fix to handle cancelled query that generate more than one
    temporary file and more generally aggregate size on queries with
    multiple (> 1GB) temporary files.
  - Add "Total size" column in Temporary Files Activity table and
    fix size increment when a query have multiple 1GB temporary file.
  - Fix temporary file query normalization and examples.
  - Fix incomplete and wrong queries associated to temporary files
    when STATEMENT level log line was missing. Thanks to Mael
    Rimbault for the report.
  - When -w or --watch-mode is used, message "canceling statement
    due to statement timeout" s now reported with other errors.
  - Allow dot in dbname and user name. Thanks to David Turvey for
    the patch.
  - Remove use of unmaintained flotr2 javascript chart library and
    use of jqflot instead.
  - Fix bad formatting with anonymized values in queries.
  - Display 0ms instead of 0s when qery time is under the millisecond.
    Thanks to venkatabn for the report.
  - Normalize cursor names. Patch from Julien Rouhaud
  - Fix unregistered client host name with default pattern. Thanks to
    Eric S. Lucinger Ruiz for the report.
  - Remove redundant regular expressions.
  - Tweaking awkward phrasing, correcting subject-verb agreements,
    typos, and misspellings. Patch from Josh Kupershmid.
  - Fix potential incorrect creation of subdirectory in incremental mode.
  - Allow single white space after duration even if this should not appear.
  - Update copyright.

This new release fixes some issues and adds a new report:

* Distribution of sessions per application

It also adds Json operators to SQL Beautifier.

Here is the full list of changes/fixes:

  * Fix unwanted seek on old parsing position when log entry is stdin. Thanks to Olivier Schiavo for the report.
  * Try to fix a potential issue in log start/end date parsing. Thanks to gityerhubhere for the report.
  * Fix broken queries with multiline in bind parameters. Thank to Nicolas Thauvin for the report.
  * Add new report Sessions per application. Thanks to Keith Fiske for the feature request.
  * Add Json Operators to SQL Beautifier. Thanks to Tom Burnett and Hubert depesz Lubaczewski.
  * Makefile.PL: changed manpage section from '1' to '1p', fixes #237. Thanks to Cyril Bouthors for the patch.
  * Update Copyright date-range and installation instructions that was still refering to version 5. Thanks to Steve Crawford for the report.
  * Fix typo in changelog

Note that new official releases must now be downloaded from GitHub and no more from SourceForge. Download at https://github.com/dalibo/pgbadger/releases

This major release adds some more useful reports and features.

  * New report about events distribution per 5 minutes.
  * New per application details (total duration and times executed) for each
    query reported in Top Queries reports. The details are visible from a new
    button called "App(s) involved".
  * Add support to auto_explain extension. EXPLAIN plan will be added together
    with top slowest queries when available in log file.
  * Add a link to automatically open the explain plan on http://explain.depesz.com/
  * New report on queries cumulated durations per user.
  * New report about the Number of cancelled queries (graph)
  * New report about Queries generating the most cancellation (N)
  * New report about Queries most cancelled.

Here is the full list of changes/fixes:

  * Update documentation with last reports.
  * Fix number of event samples displayed in event reports.
  * Add new report about events distribution per x minutes.
  * Add app=%a default prefix to documentation.
  * Add reports of "App(s) involved" with top queries. Thanks to Antti Koivisto
    for the feature request.
  * Remove newline between a ) and , in the beautifier.
  * Add link to automatically open the explain plan on http://explain.depesz.com/
  * Add support to auto_explain, EXPLAIN plan will be added together with top
    slowest queries when available in the log file.
  * Add a graph on distributed duration per user. Thanks to Korriliam for the
    patch.
  * Add tree new report: Number of cancelled queries (graph), Queries generating
    the most cancellation (N) and Queries most cancelled lists. Thanks to Thomas
    Reiss for the feature request.
  * Fix case where temporary file statement must be retrieved from the previous
    LOG statement and not in the following STATEMENT log entry. Thanks to Mael
    Rimbault for the report.
  * Add --enable-checksum to show a md5 hash of each reported queries. Thanks
    to Thomas Reiss for the feature request.

This new release fixes a major bug in SQL beautifier which removed operator and adds some useful improvement in anonymization of parameters values. pgBadger will also try to parse the full csvlog when a broken CSV line is encountered.

  * Make anonymization more useful. Thanks to Hubert depesz Lubaczewski for the patch.
  * Fix previous patch for csvlog generated with a PostgreSQL version before 9.0.
  * Try continue CSV parsing after broken CSV line. Thanks to Sergey Burladyan for the patch.
  * Fix bug in SQL beautifier which removed operator. Thanks to Thomas Reiss for the report.
  * Fix loop exit, check terminate quickly and correct comments indentation. Thanks to Sergey
    Burladyan for the patch

Please upgrade.

This new release fixes some bugs and adds some new reports:

  * A new per user details (total duration and times executed) for each query
    reported in Top Queries reports. The details are visible from a new button
    called "User(s) involved".
  * Add "Average queries per session" and "Average queries duration per session"
    in Sessions tab of the Global statistics.
  * Add connection time histogram.
  * Use bar graph for Histogram of query times and sessions times.

There's also some cool new features and options:

  * Add -L | --logfile-list option to read a list of logfiles from an external
    file.
  * Add support to log_timezones with + and - signs for timestamp with
    milliseconds (%m).
  * Add --noreport option to instruct pgbadger to not build any HTML reports
    in incremental mode. pgBadger will only create binary files.
  * Add autodetection of client=%h or remote=%h from the log so that adding
    a prefix is not needed when it respect the default of pgbadger.
  * Redefine sessions duration histogram bound to be more accurate.
  * Add new option -M | --no-multiline to not collect multiline statement
    and avoid storing and reporting garbage when needed.
  * Add --log-duration option to force pgbadger to associate log entries
    generated by both log_duration=on and log_statement=all.

The pgbadger_tools script have also been improve with new features:

  * Add a new tool to pgbadger_tool to output top queries in CSV format for
    follow-up analysis.
  * Add --explain-time-consuming and --explain-normalized options to generate
    explain statement about top time consuming and top normalized slowest
    queries.

Here is the full list of changes/fixes:

	- Update flotr2.min.js to latest github code.
	- Add per user detail information (total duration and times executed)
	  for each query reported in "Time consuming queries", "Most frequent
	  queries" "and Normalized slowest queries". The details are visible
	  from a new button called "User(s) involved" near the "Examples"
	  button. Thanks to Guillaume Le Bihan for the patch and tsn77130 for
	  the feature request.
	- pgbadger_tool: add tool to output top queries to CSV format, for
	  follow-up analysis. Thanks to briklen for the patch.
	- Add geometric operators to SQL beautifier. Thanks to Rodolphe
	  Quiedeville for the report.
	- Fix non closing session when a process crash with message:
	  "terminating connection because of crash of another server process".
	  Thanks to Mael Rimbault for the report.
	- Add -L|--logfile-list command line option to read a list of logfiles
	  from a file. Thanks to Hubert depesz Lubaczewski for the feature
	  request.
	- Automatically remove %q from prefix. Thanks to mbecroft for report.
	- Do not store DEALLOCATE log entries anymore.
	- Fix queries histogram where range was not appears in the right order.
	  Thanks to Grzegorz Garlewicz for the report.
	- Fix min yaxis in histogram graph. Thanks to grzeg1 for the patch.
	- Add --log-duration command line option to force pgbadger to associate
	  log entries generated by both log_duration = on and log_statement=all.
	  Thanks to grzeg1 for the feature request.
	- Small typographical corrections. Thanks to Jefferson Queiroz Venerando
	  and Bill Mitchell the patches.
	- Reformat usage output and add explanation of the --noreport command
	  line option.
	- Fix documentation about minimal pattern in custom log format. Thanks
	  to Julien Rouhaud for the suggestion.
	- Add support to log_timezones with + and - signs to timestamp with
	  milliseconds (%m). Thanks to jacksonfoz for the patch.
	  pgbadger was not recognize log files with timezones like 'GMT+3'.
	- Add --noreport command line option to instruct pgbadger to not build
	  any reports in incremental mode. pgBadger will only create binary
	  files. Thanks to hubert Depesz Lubaczewski for the feature request.
	- Add time consuming information in tables of Queries per database...
	  Thanks to Thomas for the feature request.
	- Add more details about the CSV parser error. It now prints the line
	  number and the last parameter that generate the failure. This should
	  allow to see the malformed log entry.
	- Change substitution markup in apptempt to fix a new look-behind
	  assertions error. Thanks to Paolo Cavallini for the report.
	- Use bar graph for Histogram of query times and sessions times.
	- Fix wrong count of min/max queries per second. Thanks to Guillaume
	  Lelarge for the report. Add COPY statement to SELECT or INSERT
	  statements statistics following the copy direction (stdin or stdout).
	- Fix Illegal division by zero at line 3832. Thanks to MarcoTrek for
	  the report.
	- Add "Average queries per session" and "Average queries duration per
	  session" in Sessions tab of the Global stat. Thanks to Guillaume
	  Lelarge for the feature request.
	- Reformat numbers in pie graph tracker. Thanks to jirihlinka for the
	  report.
	- pgbadger_tools: Add --explain-time-consuming and --explain-normalized
	  to generate explain statement about top time consuming and top
	  normalized slowest queries. Thanks to Josh Kupershmid fot the feature
	  request.
	- Remove everything than error information from json output when -w | 
	  --watch-mode is enable. Thanks to jason.
	- Fix undefined subroutine encode_json when using -x json. Thanks to
	  jason for the report.
	- Add autodetection of client=%h or remote=%h from the log so that
	  adding a prefix is not needed when it respect the default of pgbadger.
	- Redefine sessions duration histogram bound to be more accurate. Thanks
	  to Guillaume Lelarge for the report.
	- Add connection time histogram. Thanks to Guillaume Lelarge for the
	  feature request.
	- Add new option -M | --no-multiline to not collect multiline statement
	  to avoid garbage especially on errors that generate a huge report.
	- Do not return SUCCESS error code 0 when aborted or something fails.
	  Thanks to Bruno Almeida for the patch.

		

This is a maintenance release to fix a regression in SQL traffic graphs and fix some other minor issues.

The release also add a new option -D or --dns-resolv to map client ip addresses to FQDN without having log_hostname enabled on the postgresql's configuration

	- Do not display queries in Slowest individual, Time consuming and
	  Normalized slowest queries reports when there is no duration in
	  log file. Display NO DATASET instead.
	- Fix min/max queries in SQL traffic that was based on duration instead
	  of query count.
	- Fix wrong unit to Synced files in Checkpoints files report. Thanks
	  to Levente Birta for the report.
	- Enable allow_loose_quotes in Text::CSV_XS call to fix CSV parsing
	  error when fields have quote inside an unquoted field. Thanks to
	  Josh Berkus for the report.
	- Add -D | --dns-resolv command line option to replace ip addresses
	  by their DNS name. Be warned that this can slow down pgBagder a lot.
	  Thanks to Jiri Hlinka for the feature request.

This release fix some issues and adds some new features. It adds a new option -B or --bar-graph to use bar instead of line in graphs. It will also keep tick formatting when zooming.

The release also adds a new program: pgbadger_tools to demonstrate how to work with pgBadger binary files to build your own new feature. The first tools 'Explain slowest' allow printing of top slowest queries as EXPLAIN statements. There's also additionnal options to execute automatically the statements with EXPLAIN ANALYZE and get the execution plan. See help of the program for more information or the README file in the tools directory.

Some modifications with behavior changes:

        - The -T | --title text value will now be displayed instead of the
          pgBadger label right after the logo. report. It was previously
          displayed on mouse over the pgBadger label.

This new major release adds some new features like automatic cleanup of binary files in incremental mode or maximum number of weeks for reports retention. It improve the incremental mode with allowing the use of multiprocessing with multiple log file.

It also adds report of query latency percentile on the general activity table (percentiles are 90, 95, 99).

There's also a new output format: JSON. This format is good for sharing data with other languages, which makes it easy to integrate pgBadger's result into other monitoring tools.

You may want to expose your reports but not the data, using the --anonymize option pgBadger will be able to anonymize all literal values in the queries.

Sometime select to copy a query from the report could be a pain. There's now a click-to-select button in front of each query that allow you to just use Ctrl+C to copy it on clipboard

The use of the new -X option also allow pgBadger to write out extra files to the outdir when creating incremental reports. Those files are the CSS and Javascript code normally repeated in each HTLM files.

Warning: the behavior of pgBadger in incremental mode has changed. It will now always cleanup the output directory of all the obsolete binary file. If you were using those files to build your own reports, you can prevent pgBadger to remove them by using the --noclean option. Note that if you use the retention feature, all those files in obsolete directories will be removed too.

See ChangeLog for a complete list of changes.

Official release are published to the GitHub Release page of pgBadger.

Not all release are subject to a notification sent to the pgsql-announce mailling list, this is reserved to the main releases.

pgBadger may have a binary package corresponding to your distribution. For RPM packages, thanks to the great work of Devrim GÜNDÜZ, you can find the pgBadger package at the PostgreSQL yum repository

Packages for Debian and Ubuntu are available in the PostgreSQL apt repository.

If you are the maintainer of other package distribution or you know how to download them, please let me know I will add a link here.

The latest development code can always be found into the pgBadger's GitHub repository

DESCRIPTION

pgBadger is a PostgreSQL log analyzer built for speed with fully reports from your PostgreSQL log file. It's a single and small Perl script Perl script that outperforms any other PostgreSQL log analyzer.

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.

pgBadger is able to autodetect your log file format (syslog, stderr or csvlog). It is designed to parse huge log files as well as gzip compressed files. See a complete list of features below. Supported compressed format are gzip, bzip2 and xz. For the xz format you must have an xz version upper than 5.05 that supports the --robot option.

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 into the log_line_prefix directive of your postgresql.conf file as long as it at least specify the %t and %p 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 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
        Queries Average 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 occuring 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 can not be used with the CSV log format.

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 planned 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.

Compressed log file format is autodetected from the file exension. If pgBadger find a gz extension it will use the zcat utility, with a bz2 extension it will use bzcat and if the file extension is zip or xz then the unzip or xz utilities 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 and unzip utilities following the file extension. If you use the default autodetection compress format you can mixed gz, bz2, xz or zip files. Specifying a custom value to --zcat option will remove this feature of mixed compressed format.

Note that multiprocessing can not 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-7.x.tar.gz
        cd pgbadger-7.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.

With 'stderr' log format, log_line_prefix must be at least:

        log_line_prefix = '%t [%p]: [%l-1] '

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

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

or for syslog log file format:

        log_line_prefix = 'user=%u,db=%d,app=%aclient=%h '

Log line prefix for stderr output could also be:

        log_line_prefix = '%t [%p]: [%l-1] 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 without locale support:

        lc_messages='C'

but this is not only recommended by pgBadger.

Note: the session line [%l-1] is just used to match the default prefix for "stderr". The -1 has no real purpose and basically is not used in pgBadger statistics / graphs. You can safely remove them from the log_line_prefix but you will need to set the --prefix command line option accordingly.

log_min_duration_statement, log_duration and log_statement

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 request report you can either choose to set log_min_duration_statement to a higher value or choose 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_line_prefix.

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.

pgBadger will then proceed as follow:

        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 has terminated
        All binary temporary files generated will then be read and loaded into
        memory to build the html output.

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.

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.

He are a benchmarck 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

With 200 log files of 10MB each and a total of 2GB 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 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 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 HTML format for daily and weekly reports with a main index file.

The main index file will show a dropdown menu per week with a link to each week's report and links to daily reports of each week.

For example, if you run pgBadger as follows 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.

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.

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.

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 each hour the following commands:

    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.

JSON FORMAT

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

USAGE

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

        PostgreSQL log analyzer with fully detailed reports and graphs.

Arguments:

    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.

Options:

    -a | --average N       : number of minutes to build the average graphs
                             of queries and connections. Default 5 minutes.
    -A | --histo-average N : 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.
    -B | --bar-graph       : use bar graph instead of line by default.
    -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 adresses 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.
    -f | --format logtype  : possible values: syslog, syslog2, stderr, csv and
                             pgbouncer. Use this option when pgbadger is not
                             able to auto-detect the log format Default: stderr.
    -G | --nograph         : disable graphs on HTML output. Enabled by default.
    -h | --help            : show this message and exit.
    -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. Default is 1,
                             run as single process.
    -J | --Jobs number     : number of log file to parse in parallel. Default
                             is 1, run as single process.
    -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 file to parse.
    -m | --maxlength size  : maximum length of a query, it will be restricted to
                             the given size. Default: no truncate
    -M | --no-multiline    : do not collect multiline statement to avoid garbage
                             especially on errors that generate a huge report.
    -n | --nohighlight     : disable SQL code highlighting.
    -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,
                             out.json or out.tsung.
                             With module JSON::XS installed, you can output file
                             in JSON format either.
                             To dump output to stdout use - as filename.
    -O | --outdir path     : directory where out file 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't 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. See examples below.
    -P | --no-prettify     : disable SQL queries prettify formatter.
    -q | --quiet           : don't print anything to stdout, not even a progress
                             bar.
    -r | --remote-host ip  : set the host where to execute the cat command on
                             remote logfile to parse localy the file.
    -R | --retention N     : number of week to keep in incremental mode. Default
                             to 0, disabled. Used to set the number of weel to
                             keep in output directory. Older weeks and days
                             directory 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.
    -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.
    -x | --extension       : output format. Values: text, html, bin, json or
                             tsung. Default: html
    -X | --extra-files     : in incremetal 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 or bzcat or unzip is not in your path.
    -Z | --timezone +/-XX  : Set the number of hour(s) from GMT of the timezone.
                             Use this to adjust date/time in javascript graphs.
    --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 which contains all the 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 which contains all the regex of
                             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.
    --exclude-appname name : exclude entries for the specified application name
                             from report. Example: "pg_dump".
    --exclude-line regex   : pgbadger will start to exclude any log entry that
                             will match the given regex. Can be used multiple
                             time.
    --anonymize            : obscure all literals in queries, useful to hide
                             confidential data.
    --noreport             : prevent pgbadger to create reports in incremental
                             mode.
    --log-duration         : force pgbadger to associate log entries generated
                             by both log_duration = on and log_statement = 'all'
    --enable-checksum      : used to add a 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 dirpath      : set the path of the directory where the pid file
                             will be written to be able to run two pgbadger at
                             the same time.
    --rebuild              : used to rebuild all html reports in incremental
                             output directories where there is binary data files.
    --pgbouncer-only       : only show pgbouncer related menu in the header.
    --start-monday         : in incremental mode, calendar's weeks start on
                             sunday. Use this otpion to start on monday.
    --normalized-only      : only dump all normalized query to out.txt

pgBadger is able to parse a remote log file using a passwordless ssh connection. Use the -r or --remote-host to set the host ip address or hostname. There's also some additional options to fully control the ssh connection.

    --ssh-program ssh        path to the ssh program to use. Default: ssh.
    --ssh-user username      connection login name. Default to running user.
    --ssh-identity file      path to the identity file to use.
    --ssh-timeout second     timeout to ssh connection failure. Default 10 secs.
    --ssh-option  options    list of -o options to use for the ssh connection.
                             Options always used:
                                 -o ConnectTimeout=$ssh_timeout
                                 -o PreferredAuthentications=hostbased,publickey

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 prefix with stderr log output
        pgbadger --prefix '%t [%p]: [%l-1] 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-9.1-main.log

Generate Tsung sessions XML file with select queries only:

    pgbadger -S -o sessions.tsung --prefix '%t [%p]: [%l-1] user=%u,db=%d ' /pglog/postgresql-9.1.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 week 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 period 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.

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 others PostgreSQL log files to parse, you can add them as usual.

To rebuild all incremantal 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 ressources file (JS and CSS).

The sample report generated by pbBadger v7.x can be found here.

Sample report generated by pbBadger v6.x with incremental mode can be found here.

Sample report generated by pbBadger v5.x with incremental mode can be found here.

The sample report generated by pbBadger v4.x can be found here.

Please report any bugs, patches, discussion, etc. using the GitHub tools at https://github.com/dalibo/pgbadger/.

If you need new features or you think something is missing, please send your comments and requests. This helps a lot to develop a better/useful tool.

You can also write to: <pgbadger |AT| dalibo |DOT| com> or subscribe to the pgBadger mailing list (pgbadger@googlegroups.com)

Some of you ask me (Gilles Darold) a way to donate to support my all days efforts to build a better tool. You can use the link below and if you need an invoice, just drop me line I will send it back to you.

Any contribution to build a better tool is welcome, you just have to send your ideas, features request or patches using the GitHub tools at https://github.com/dalibo/pgbadger/. If you're interested by this project please subscribe to the pgBadger mailing list.

Dalibo as the maintainer of the project can offer you a high level commercial support.

For a complete list of commercial support near of your place take a look at the PostgreSQL Professional Services page, they all do great job and most of them can help you.