diff -Nru pgbadger-11.8/ChangeLog pgbadger-12.0/ChangeLog --- pgbadger-11.8/ChangeLog 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/ChangeLog 2022-09-13 18:17:52.000000000 +0000 @@ -1,3 +1,55 @@ +2022-09-13 - v12.0 + +This major release of pgBadger fixes some issues reported by users since +past five months. As usual there is also new features and improvements: + + * Remove support to Tsung output. + * Improve pgbadger performances when there are hundred of bind parameters + to replace. + * Remove option -n | --nohighlight which is no more used since upgrade to + pgFormatter 4. + * Use POST method to send auto_explain plan to explain.depesz.com to avoid + GET length parameter limit. + * Apply --exclude-query and --include-query to bind/parse traces. + * Add link to pgBadger report examples to documentation. + +Here is the complete list of changes and acknowledgments: + + - Fix monthly reports that was failing on "log file ... must exists". Thanks + to Jaume Sabater for the report. + - Fix pgbouncer start parsing debug message when input is stdin. Thanks to + aleszeleny for the report. + - Remove support to Tsung output. + - Drastically improve pgbadger performances for bind parameters + replacement that could make pgbadger run infinitely when there was + hundred of parameters. Thanks to Monty Mobile for the report. + - Fix documentation about pgBadger return codes and also some wrong return + code at some places. Thanks to Jaume Sabater for the report. + - Fix several typo. Thanks to David Gilman for the patch. + - Remove option -n | --nohighlight which is no more used since upgrade to + pgFormatter 4. Thanks to Elena Indrupskaya for the report. + - Lot of pgbadger documentation fixes. Thanks to Elena Indrupskay from + Postgres Pro for the patch. + - Allow half hour in --log-timezone and --timezone, value can be an integer, + ex: 2 or a float, ex: 2.5. Thanks to Mujjamil-K for the feature request. + - Allow use of regexp for --exclude-app and --exclude-client. Thanks to + rdnkrkmz for the feature request. + - Allow use of --explain-url with previous commit and restore the limitation + to explain text format. + - Use POST method to send auto_explain plan to explain.depesz.com to avoid + GET length parameter limit. Thanks to hvisage for the report. + - Apply --exclude-query and --include-query to bind/parse traces. Thanks to + Alec Lazarescu for the report. + - Fix parsing of autovacuum stats from RDS logs. Thanks to David Gilman for + the report. + - Fix passing of log format when parsing remote log. Thanks to spookypeanut + the report. + - Add link to pgBadger report examples to documentation. + - Fix Session per user reports. Thanks to vitalca for the report. + - Fix jsonlog parsing from PG15 ouput + - Fix text-based error/events reporting. Thanks to Michael Banck for the patch + - Fix regexp typo in normalize_error(). Thanks to Michael Banck for the patch. + 2022-04-08 - v11.8 This release of pgBadger fix some issues reported by users since past diff -Nru pgbadger-11.8/debian/changelog pgbadger-12.0/debian/changelog --- pgbadger-11.8/debian/changelog 2022-05-02 14:23:23.000000000 +0000 +++ pgbadger-12.0/debian/changelog 2022-09-21 09:03:53.000000000 +0000 @@ -1,3 +1,9 @@ +pgbadger (12.0-1) unstable; urgency=medium + + * New upstream version 12.0. + + -- Christoph Berg Wed, 21 Sep 2022 11:03:53 +0200 + pgbadger (11.8-1) unstable; urgency=medium * New upstream version 11.8. diff -Nru pgbadger-11.8/debian/watch pgbadger-12.0/debian/watch --- pgbadger-11.8/debian/watch 2021-09-08 13:54:50.000000000 +0000 +++ pgbadger-12.0/debian/watch 2022-09-21 09:03:03.000000000 +0000 @@ -1,2 +1,2 @@ version=4 -https://github.com/darold/pgbadger/releases .*/v([0-9.]+).tar.gz +https://github.com/darold/pgbadger/tags .*/v([0-9.]+).tar.gz diff -Nru pgbadger-11.8/doc/pgBadger.pod pgbadger-12.0/doc/pgBadger.pod --- pgbadger-11.8/doc/pgBadger.pod 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/doc/pgBadger.pod 2022-09-13 18:17:52.000000000 +0000 @@ -31,9 +31,9 @@ (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 are added to the postgres database report. + database is added to the postgres database report. -f | --format logtype : possible values: syslog, syslog2, stderr, jsonlog, - cvs, pgbouncer, logplex, rds and redshift. Use this + 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. @@ -44,28 +44,28 @@ -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. Run as single - by default or when working with csvlog. - -J | --Jobs number : number of log file to parse in parallel. Process one - file at a time by default or when csvlog is used. + -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 file to parse. + -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 statement to avoid garbage + -M | --no-multiline : do not collect multiline statements 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. This option can be used - multiple time to output several format. 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 file must be saved. + 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't using one of the standard prefixes specified @@ -78,11 +78,11 @@ -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 logfile to parse locally the file. - -R | --retention N : number of weeks to keep in incremental mode. Default + 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 - directory are automatically removed. + 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. @@ -95,27 +95,29 @@ -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, json or - tsung. Default: html + -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 or bzcat or unzip is not in your path. + 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 which contains all the regex to - use to exclude queries from the report. One regex - per line. + --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 which contains all the regex of - the queries to include from the report. One regex - per line. + --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 @@ -139,22 +141,20 @@ 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 - time. + times. --exclude-appname name : exclude entries for the specified application name from report. Example: "pg_dump". Can be used - multiple time. - --exclude-line regex : pgBadger will start to exclude any log entry that - will match the given regex. Can be used multiple - time. + 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 time. + Can be used multiple times. --anonymize : obscure all literals in queries, useful to hide confidential data. - --noreport : prevent pgBadger to create reports in incremental - mode. + --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 = 'all' - --enable-checksum : used to add a md5 sum under each query report. + --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 @@ -164,18 +164,20 @@ execution of pgBadger. Default: pgbadger.pid --rebuild : used to rebuild all html reports in incremental output directories where there's binary data files. - --pgbouncer-only : only show PgBouncer related menu in the header. - --start-monday : in incremental mode, calendar's weeks start on - a sunday. Use this option to start on a monday. - --iso-week-number : in incremental mode, calendar's weeks start on a - monday and respect the ISO 8601 week number, range + --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 query to out.txt + --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 - make more difficult log search with a date/time. + 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 @@ -190,38 +192,46 @@ --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: http://explain.depesz.com/?is_public=0&is_anon=0&plan= + tool. Default: http://explain.depesz.com/ --tempdir DIR : set directory where temporary files will be written Default: File::Spec->tmpdir() || '/tmp' --no-process-info : disable changing process title to help identify pgbadger process, some system do not support it. --dump-all-queries : dump all queries found in the log file replacing - bind parameters are included in the queries at - their respective placeholders position. + 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. 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 +Use -r or --remote-host to set the host IP address or hostname. There are also some additional options to fully control the ssh connection. --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. Default to running user. + --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 secs. + --ssh-timeout second timeout to ssh connection failure. Default: 10 sec. --ssh-option options list of -o options to use for the ssh connection. Options always used: -o ConnectTimeout=$ssh_timeout -o PreferredAuthentications=hostbased,publickey -Log file to parse can also be specified using an URI, supported protocol are -http[s] and [s]ftp. The curl command will be used to download the file and the +Log file to parse can also be specified using an URI, supported protocols are +http[s] and [s]ftp. The curl command will be used to download the file, and the file will be parsed during download. The ssh protocol is also supported and will use the ssh command like with the remote host use. See examples bellow. +Return codes: + + 0: on success + 1: die on error + 2: if it has been interrupted using ctr+c for example + 3: the pid file already exists or can not be created + 4: no log file was given at command line + Examples: pgbadger /var/log/postgresql.log @@ -230,7 +240,7 @@ 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 + # Log line prefix with stderr log output pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21* pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log # Log line prefix with syslog log output @@ -248,17 +258,13 @@ pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username@172.12.110.14/pgbouncer.log -Generate Tsung sessions XML file with select queries only: - - pgbadger -S -o sessions.tsung --prefix '%t [%p]: user=%u,db=%d ' /pglog/postgresql-10.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_report-`date +\%F`.html -l /var/reports/pgbadger_incremental_file.dat + 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. @@ -268,13 +274,13 @@ will generate a report per day and per week. -In incremental mode, you can also specify the number of week to keep in the +In incremental mode, you can also specify the number of weeks to keep in the reports: /usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/ If you have a pg_dump at 23:00 and 13:00 each day during half an hour, you can -use pgBadger as follow to exclude these period from the report: +use pgBadger as follow to exclude these periods from the report: pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log @@ -291,7 +297,7 @@ pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5' you don't need to specify any log file at command line, but if you have other -PostgreSQL log file to parse, you can add them as usual. +PostgreSQL log files to parse, you can add them as usual. To rebuild all incremental html reports after, proceed as follow: @@ -302,7 +308,7 @@ it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option. -pgBadger also support Heroku PostgreSQL logs using logplex format: +pgBadger also supports Heroku PostgreSQL logs using logplex format: heroku logs -p postgres | pgbadger -f logplex -o heroku.html - @@ -313,14 +319,14 @@ pgbadger -f rds -o rds_out.html rds.log -CloudSQL Postgresql logs it's fairly normal PostgreSQL log but encapsulated in -JSON format. It is auto detected too by pgBagder but in case you need to force -the log format, use `jsonlog` +Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated +in JSON format. It is autodetected by pgBadger but in case you need to force +the log format use `jsonlog`: pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log -This is the same than with the jsonlog extension, the json format is different -but pgBadger can parse both format. +This is the same as with the jsonlog extension, the json format is different +but pgBadger can parse both formats. To create a cumulative report over a month use command: @@ -344,20 +350,20 @@ 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 format are gzip, +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 -upper than 5.05 that supports the --robot option. In order pgbadger determine -uncompressed file size with lz4, file must be compressed with --content-size -option. For the complete list of features see below. +higher than 5.05 that supports the --robot option. lz4 files must be +compressed with the --content-size option for pgbadger to determine the +uncompressed file size. For the complete list of features, see below. All charts are zoomable and can be saved as PNG images. You can also limit pgBadger to only report errors or remove any part of the -report using command line options. +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 supports any custom format set in the log_line_prefix directive of +your postgresql.conf file as long as it at least specifies the %t 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. @@ -433,58 +439,59 @@ 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 +Histogram granularity can be adjusted using the -A command-line option. By default, they will report the mean of each top queries/errors occurring per hour, but you can specify the granularity down to the minute. pgBadger can also be used in a central place to parse remote log files using a passwordless SSH connection. This mode can be used with compressed files and in -the multiprocess per file mode (-J) but can not be used with the CSV log format. +the multiprocess per file mode (-J), but cannot be used with the CSV log format. +Examples of reports can be found here: https://pgbadger.darold.net/#reports =head1 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 +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: +If you plan to parse PostgreSQL CSV log files, you might need some Perl Modules: Text::CSV_XS - to parse PostgreSQL CSV log files. -This module is optional, if you don't have PostgreSQL log in the CSV format you don't +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: +If you want to export statistics as JSON file, you need an additional Perl module: JSON::XS - JSON serialising/deserialising, done correctly and fast -This module is optional, if you don't select the json output format you don't -need to install it. You can install it on a Debian like system using: +This module is optional, if you don't select the json output format, you don't +need to install it. You can install it on a Debian-like system using: sudo apt-get install libjson-xs-perl -and in RPM like system using: +and on RPM-like system using: sudo yum install perl-JSON-XS -Compressed log file format is autodetected from the file extension. If pgBadger find -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 and if the file extension -is zip or xz then the unzip or xz utilities will be used. +Compressed log file format is autodetected from the file extension. If pgBadger finds +a gz extension, it will use the zcat utility; with a bz2 extension, it will use bzcat; +with lz4, it will use lz4cat; with zst, it will use zstdcat; if the file extension +is zip or xz, then the unzip or xz utility will be used. -If those utilities are not found in the PATH environment variable then use the --zcat -command line option to change this path. For example: +If those utilities are not found in the PATH environment variable, then use the --zcat +command-line option to change this path. For example: --zcat="/usr/local/bin/gunzip -c" or --zcat="/usr/local/bin/bzip2 -dc" --zcat="C:\tools\unzip -p" -By default pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities -following the file extension. If you use the default autodetection compress format -you can mixed gz, bz2, lz4, xz, zip or zstd files. Specifying a custom value to ---zcat option will remove this feature of mixed compressed format. +By default, pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities +following the file extension. If you use the default autodetection of compression format, +you can mix gz, bz2, lz4, xz, zip or zstd files. Specifying a custom value of +--zcat option will remove the possibility of mixed compression format. -Note that multiprocessing can not be used with compressed files or CSV files as +Note that multiprocessing cannot be used with compressed files or CSV files as well as under Windows platform. =head1 INSTALLATION @@ -508,7 +515,7 @@ perl Makefile.PL INSTALLDIRS=vendor -By default INSTALLDIRS is set to site. +By default, INSTALLDIRS is set to site. =head1 POSTGRESQL CONFIGURATION @@ -521,12 +528,12 @@ 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 +log_statement set to 'all', nothing will be logged through the log_min_duration_statement directive. See the next chapter for more information. -pgBadger supports any custom format set into the log_line_prefix directive of -your postgresql.conf file as long as it at least specify a time escape sequence -(%t, %m or %n) and the process related escape sequence (%p or %c). +pgBadger supports any custom format set in the log_line_prefix directive of +your postgresql.conf file as long as it at least specifies a time escape sequence +(%t, %m or %n) and a process-related escape sequence (%p or %c). For example, with 'stderr' log format, log_line_prefix must be at least: @@ -566,7 +573,7 @@ lc_messages='en_US.UTF-8' lc_messages='C' -pgBadger parser do not support other locale like 'fr_FR.UTF-8' for example. +pgBadger parser does not support other locales, like 'fr_FR.UTF-8' for example. =head1 LOG STATEMENTS @@ -579,12 +586,12 @@ 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. +common query report, you can either choose to set log_min_duration_statement +to a higher value or to enable log_statement. Enabling log_min_duration_statement will add reports about slowest queries and queries that took up the most time. Take care that if you have log_statement -set to 'all' nothing will be logged with log_min_duration_statement. +set to 'all', nothing will be logged with log_min_duration_statement. Warning: Do not enable both log_min_duration_statement, log_duration and log_statement all together, this will result in wrong counter values. Note @@ -610,15 +617,15 @@ 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 +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 +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. Here is a benchmark done on a server with 8 CPUs and a single file of 9.5GB. @@ -639,7 +646,7 @@ 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 +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 @@ -662,13 +669,13 @@ 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 +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 +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. @@ -677,7 +684,7 @@ Incremental reports can be rebuilt after a pgbadger report fix or a new feature to update all HTML reports. To rebuild all reports where a binary -file is still present proceed as follow: +file is still present, proceed as follow: rm /path/to/reports/*.js rm /path/to/reports/*.css @@ -688,18 +695,18 @@ =head2 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: +By default, pgBadger in incremental mode only computes daily and weekly reports. +If you want monthly cumulative reports, you will have to use a separate command +to specify the report to build. For example, to build a report for August 2019: pgbadger -X --month-report 2919-08 /var/www/pg_reports/ 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 lot of time following the amount of data. +every day, it is entirely rebuilt each time. The monthly report is not built by +default because it could take a lot of time following the amount of data. -If reports were built with the per database option ( -E | --explode ) it must +If reports were built with the per-database option ( -E | --explode ), it must be used too when calling pgbadger to build monthly report: pgbadger -E -X --month-report 2919-08 /var/www/pg_reports/ @@ -711,7 +718,7 @@ 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: +running the following commands each hour: pgbadger --last-parsed .pgbadger_last_state_file -o sunday/hourX.bin /var/log/pgsql/postgresql-Sun.log @@ -729,7 +736,7 @@ 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 +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 @@ -739,7 +746,7 @@ =head1 JSON FORMAT JSON format is good for sharing data with other languages, which makes it -easy to integrate pgBadger result into other monitoring tools like Cacti +easy to integrate pgBadger result into other monitoring tools, like Cacti or Graphite. =head1 AUTHORS @@ -752,7 +759,7 @@ This web site is a work of Gilles Darold. -pgBadger is maintained by Gilles Darold and every one who wants to contribute. +pgBadger is maintained by Gilles Darold and everyone who wants to contribute. Many people have contributed to pgBadger, they are all quoted in the Changelog file. diff -Nru pgbadger-11.8/META.yml pgbadger-12.0/META.yml --- pgbadger-11.8/META.yml 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/META.yml 2022-09-13 18:17:52.000000000 +0000 @@ -1,5 +1,5 @@ name: pgBadger -version: 11.8 +version: 12.0 version_from: pgbadger installdirs: site recommends: diff -Nru pgbadger-11.8/pgbadger pgbadger-12.0/pgbadger --- pgbadger-11.8/pgbadger 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/pgbadger 2022-09-13 18:17:52.000000000 +0000 @@ -52,7 +52,7 @@ use constant EBCDIC => "\t" ne "\011"; use Encode qw(encode decode); -$VERSION = '11.8'; +$VERSION = '12.0'; $SIG{'CHLD'} = 'DEFAULT'; @@ -71,7 +71,8 @@ my $terminate = 0; my %CACHE_DNS = (); my $DNSLookupTimeout = 1; # (in seconds) -my $EXPLAIN_URL = 'http://explain.depesz.com/?is_public=0&is_anon=0&plan='; +my $EXPLAIN_URL = 'http://explain.depesz.com/'; +my $EXPLAIN_POST = qq{
}; my $PID_DIR = $TMP_DIR; my $PID_FILE = undef; my %DBLIST = (); @@ -290,7 +291,6 @@ my $graph = 1; my $nograph = 0; my $debug = 0; -my $nohighlight = 0; my $noprettify = 0; my $from = ''; my $to = ''; @@ -327,7 +327,6 @@ my $t_max = 0; my $remove_comment = 0; my $select_only = 0; -my $tsung_queries = 0; my $queue_size = 0; my $job_per_file = 0; my $charset = 'utf-8'; @@ -356,7 +355,6 @@ my $log_timezone = 0; my $opt_log_timezone = 0; my $json_prettify = 0; -my $is_tsung_output = 0; my $report_per_database = 0; my $html_outdir = ''; my $param_size_limit = 24; @@ -451,7 +449,7 @@ { unlink("$PID_FILE"); } - _exit(0); + _exit(2); } $SIG{INT} = \&wait_child; $SIG{TERM} = \&wait_child; @@ -485,7 +483,6 @@ "m|maxlength=i" => \$maxlength, "M|no-multiline!" => \$nomultiline, "N|appname=s" => \@dbappname, - "n|nohighlight!" => \$nohighlight, "o|outfile=s" => \@outfiles, "O|outdir=s" => \$outdir, "p|prefix=s" => \$log_line_prefix, @@ -507,7 +504,7 @@ "x|extension=s" => \$extension, "X|extra-files!" => \$extra_files, "z|zcat=s" => \$zcat, - "Z|timezone=s" => \$opt_timezone, + "Z|timezone=f" => \$opt_timezone, "pie-limit=i" => \$pie_percentage_limit, "image-format=s" => \$img_format, "exclude-query=s" => \@exclude_query, @@ -553,7 +550,7 @@ 'start-monday!' => \$week_start_monday, 'iso-week-number!' => \$iso_week_number, 'normalized-only!' => \$dump_normalized_only, - 'log-timezone=i' => \$opt_log_timezone, + 'log-timezone=f' => \$opt_log_timezone, 'prettify-json!' => \$json_prettify, 'month-report=s' => \$month_report, 'day-report=s' => \$day_report, @@ -600,7 +597,7 @@ } } -# Check if an other process is already running +# Check if another process is already running unless ($PID_FILE) { $PID_FILE = $PID_DIR . '/pgbadger.pid'; } @@ -614,7 +611,7 @@ if (!$@ && ($is_running <= 1)) { unlink("$PID_FILE"); } else { - print "FATAL: an other process is already started or remove the file, see $PID_FILE\n"; + print "FATAL: another process is already started or remove the file, see $PID_FILE\n"; exit 3; } } @@ -663,8 +660,12 @@ my $empty_files = 1; if ($#ARGV >= 0) { - foreach my $file (@ARGV) { - push(@log_files, &set_file_list($file)); + if (!$month_report) { + foreach my $file (@ARGV) { + push(@log_files, &set_file_list($file)); + } + } elsif (!$outdir) { + $outdir = $ARGV[0]; } } @@ -707,7 +708,7 @@ if ( !$rebuild && ($#log_files < 0) && !$journalctl_cmd && !$log_command) { if (!$quiet) { - localdie("FATAL: you must give a log file at command line parameter.\n\n"); + localdie("FATAL: you must give a log file at command line parameter.\n\n", 4); } else { @@ -744,6 +745,9 @@ push(@histo_avgs, sprintf("%02d", $i)); } +# Set the URL to submit explain plan +$EXPLAIN_POST = sprintf($EXPLAIN_POST, $EXPLAIN_URL); + # Set error like log level regex my $parse_regex = qr/^(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|HINT|STATEMENT|CONTEXT|LOCATION)/; my $full_error_regex = qr/^(WARNING|ERROR|FATAL|PANIC|DETAIL|HINT|STATEMENT|CONTEXT)/; @@ -877,47 +881,13 @@ } } -if ($extens eq 'tsung') { - - # Open filehandle - my $fh = new IO::File ">$current_out_file"; - if (not defined $fh) { - localdie("FATAL: can't write to $current_out_file, $!\n"); - } - print $fh qq{ - - - - - - - - - - - - - - - - - - - - -}; - $fh->close(); - -} else { - - # Test file creation before going to parse log - my $tmpfh = new IO::File ">$current_out_file"; - if (not defined $tmpfh) { - localdie("FATAL: can't write to $current_out_file, $!\n"); - } - $tmpfh->close(); - unlink($current_out_file) if (-e $current_out_file); +# Test file creation before going to parse log +my $tmpfh = new IO::File ">$current_out_file"; +if (not defined $tmpfh) { + localdie("FATAL: can't write to $current_out_file, $!\n"); } +$tmpfh->close(); +unlink($current_out_file) if (-e $current_out_file); # -w and --disable-error can't go together if ($error_only && $disable_error) { @@ -1099,7 +1069,6 @@ my %pgb_last_line = (); our %saved_last_line = (); our %pgb_saved_last_line= (); -my %tsung_session = (); my %top_locked_info = (); my %top_tempfile_info = (); my %top_cancelled_info = (); @@ -1129,7 +1098,7 @@ # Ensure this is not a relative path if ($outdir && dirname($outdir) eq '.') { - localdie("FATAL: output directory ($outdir) is not an absolute path.\n"); + localdie("FATAL3: output directory ($outdir) is not an absolute path.\n"); } if ($html_outdir && dirname($html_outdir) eq '.') { @@ -1387,16 +1356,18 @@ } } } - foreach my $p (@obsolete_days) { + foreach my $p (@obsolete_days) + { unless(opendir(DIR, "$p")) { localdie("FATAL: can't opendir $p: $!\n"); } - my @hfiles = grep { $_ =~ /\.(html|txt|tsung|json)$/i } readdir(DIR); + my @hfiles = grep { $_ =~ /\.(html|txt|json)$/i } readdir(DIR); next if ($#hfiles == -1); # do not remove files if report file has not been generated seekdir(DIR, 0); my @bfiles = grep { $_ =~ /\.bin$/i } readdir(DIR); closedir DIR; - foreach my $f (@bfiles) { + foreach my $f (@bfiles) + { &logmsg('DEBUG', "Removing obsolete binary file: $p/$f"); unlink("$p/$f"); } @@ -2068,9 +2039,9 @@ (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 are added to the postgres database report. + database is added to the postgres database report. -f | --format logtype : possible values: syslog, syslog2, stderr, jsonlog, - cvs, pgbouncer, logplex, rds and redshift. Use this + 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. @@ -2081,28 +2052,28 @@ -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. Run as single - by default or when working with csvlog. - -J | --Jobs number : number of log file to parse in parallel. Process one - file at a time by default or when csvlog is used. + -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 file to parse. + -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 $maxlength. - -M | --no-multiline : do not collect multiline statement to avoid garbage + -M | --no-multiline : do not collect multiline statements 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. This option can be used - multiple time to output several format. 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 file must be saved. + 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't using one of the standard prefixes specified @@ -2115,11 +2086,11 @@ -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 logfile to parse locally the file. - -R | --retention N : number of weeks to keep in incremental mode. Default + 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 - directory are automatically removed. + 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. @@ -2132,27 +2103,29 @@ -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, json or - tsung. Default: html + -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 or bzcat or unzip is not in your path. + 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 which contains all the regex to - use to exclude queries from the report. One regex - per line. + --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 which contains all the regex of - the queries to include from the report. One regex - per line. + --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 @@ -2176,22 +2149,20 @@ 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 - time. + times. --exclude-appname name : exclude entries for the specified application name from report. Example: "pg_dump". Can be used - multiple time. - --exclude-line regex : pgBadger will start to exclude any log entry that - will match the given regex. Can be used multiple - time. + 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 time. + Can be used multiple times. --anonymize : obscure all literals in queries, useful to hide confidential data. - --noreport : prevent pgBadger to create reports in incremental - mode. + --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 = 'all' - --enable-checksum : used to add a md5 sum under each query report. + --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 @@ -2201,18 +2172,20 @@ execution of pgBadger. Default: pgbadger.pid --rebuild : used to rebuild all html reports in incremental output directories where there's binary data files. - --pgbouncer-only : only show PgBouncer related menu in the header. - --start-monday : in incremental mode, calendar's weeks start on - a sunday. Use this option to start on a monday. - --iso-week-number : in incremental mode, calendar's weeks start on a - monday and respect the ISO 8601 week number, range + --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 query to out.txt + --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 - make more difficult log search with a date/time. + 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 @@ -2233,32 +2206,40 @@ --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 are included in the queries at - their respective placeholders position. + 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. 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 +Use -r or --remote-host to set the host IP address or hostname. There are also some additional options to fully control the ssh connection. --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. Default to running user. + --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 secs. + --ssh-timeout second timeout to ssh connection failure. Default: 10 sec. --ssh-option options list of -o options to use for the ssh connection. Options always used: -o ConnectTimeout=\$ssh_timeout -o PreferredAuthentications=hostbased,publickey -Log file to parse can also be specified using an URI, supported protocol are -http[s] and [s]ftp. The curl command will be used to download the file and the +Log file to parse can also be specified using an URI, supported protocols are +http[s] and [s]ftp. The curl command will be used to download the file, and the file will be parsed during download. The ssh protocol is also supported and will use the ssh command like with the remote host use. See examples bellow. +Return codes: + + 0: on success + 1: die on error + 2: if it has been interrupted using ctr+c for example + 3: the pid file already exists or can not be created + 4: no log file was given at command line + Examples: pgbadger /var/log/postgresql.log @@ -2267,7 +2248,7 @@ 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 + # Log line prefix with stderr log output pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21* pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log # Log line prefix with syslog log output @@ -2285,10 +2266,6 @@ pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username\@172.12.110.14/pgbouncer.log -Generate Tsung sessions XML file with select queries only: - - pgbadger -S -o sessions.tsung --prefix '%t [%p]: user=%u,db=%d ' /pglog/postgresql-10.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 @@ -2305,13 +2282,13 @@ will generate a report per day and per week. -In incremental mode, you can also specify the number of week to keep in the +In incremental mode, you can also specify the number of weeks to keep in the reports: /usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/ If you have a pg_dump at 23:00 and 13:00 each day during half an hour, you can -use pgBadger as follow to exclude these period from the report: +use pgBadger as follow to exclude these periods from the report: pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log @@ -2328,7 +2305,7 @@ pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5' you don't need to specify any log file at command line, but if you have other -PostgreSQL log file to parse, you can add them as usual. +PostgreSQL log files to parse, you can add them as usual. To rebuild all incremental html reports after, proceed as follow: @@ -2339,7 +2316,7 @@ it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option. -pgBadger also support Heroku PostgreSQL logs using logplex format: +pgBadger also supports Heroku PostgreSQL logs using logplex format: heroku logs -p postgres | pgbadger -f logplex -o heroku.html - @@ -2350,14 +2327,14 @@ pgbadger -f rds -o rds_out.html rds.log -CloudSQL Postgresql logs it's fairly normal PostgreSQL log but encapsulated in -JSON format. It is auto detected too by pgBagder but in case you need to force -the log format, use `jsonlog` +Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated +in JSON format. It is autodetected by pgBadger but in case you need to force +the log format use `jsonlog`: pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log -This is the same than with the jsonlog extension, the json format is different -but pgBadger can parse both format. +This is the same as with the jsonlog extension, the json format is different +but pgBadger can parse both formats. To create a cumulative report over a month use command: @@ -2397,57 +2374,35 @@ &logmsg('LOG', "Ok, generating $extens report..."); } - if ($extens ne 'tsung') + $fh = new IO::File ">$current_out_file"; + if (not defined $fh) { + localdie("FATAL: can't write to $current_out_file, $!\n"); + } + if (($extens eq 'text') || ($extens eq 'txt')) { - $fh = new IO::File ">$current_out_file"; - if (not defined $fh) - { - localdie("FATAL: can't write to $current_out_file, $!\n"); - } - if (($extens eq 'text') || ($extens eq 'txt')) - { - if ($error_only) - { - &dump_error_as_text($db); - } - else - { - &dump_as_text($db); - } - } - elsif ($extens eq 'json') - { - if ($error_only) - { - &dump_error_as_json($db); - } - else - { - &dump_as_json($db); - } - } - elsif ($extens eq 'binary') - { - &dump_as_binary($fh, $db); + if ($error_only) { + &dump_error_as_text($db); + } else { + &dump_as_text($db); } - else - { - &dump_as_html('.', $db); + } + elsif ($extens eq 'json') + { + if ($error_only) { + &dump_error_as_json($db); + } else { + &dump_as_json($db); } - $fh->close; + } + elsif ($extens eq 'binary') + { + &dump_as_binary($fh, $db); } else { - - # Open filehandle - $fh = new IO::File ">>$current_out_file"; - if (not defined $fh) - { - localdie("FATAL: can't write to $current_out_file, $!\n"); - } - print $fh "\n\n"; - $fh->close(); + &dump_as_html('.', $db); } + $fh->close; } } @@ -2727,7 +2682,7 @@ elsif ($fmt =~ /^rds$/) { # The output format of the RDS pg logs is as follows: %t:%r:%u@%d:[%p]: message - # With Cloudwatch it is prefixed with an other timestamp + # With Cloudwatch it is prefixed with another timestamp $llp = '^' . $llp . '(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)'; @@ -2781,7 +2736,7 @@ elsif ($fmt eq 'rds') { # The output format of the RDS pg logs is as follows: %t:%r:%u@%d:[%p]: message - # With Cloudwatch it is prefixed with an other timestamp + # With Cloudwatch it is prefixed with another timestamp $compiled_prefix = qr/^(?:\d+-\d+-\d+T\d+:\d+:\d+\.\d+Z)?\s*(\d+)-(\d+)-(\d+) (\d+):(\d+):(\d+)\s*[^:]*:([^:]*):([^\@]*)\@([^:]*):\[(\d+)\]:(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)/; @@ -3347,7 +3302,6 @@ @graph_values = (); %cur_info = (); $nlines = 0; - %tsung_session = (); } #### @@ -3578,7 +3532,9 @@ { $has_exclusion = 1; } - &logmsg('DEBUG', "Start parsing pgbouncer log at offset $start_offset of file $logfile to " . ($stop_offset || $totalsize)); + &logmsg('DEBUG', "Start parsing pgbouncer log at offset $start_offset of " . + ( ($logfile ne '-') ? " file $logfile" : "stdin" ) . " to " . + ($stop_offset || ( ($totalsize > 0) ? $totalsize : 'end' ) )); if ($start_offset) { # Move to the starting offset position in file @@ -4004,6 +3960,7 @@ } } delete $prefix_vars{'t_textPayload'}; + $prefix_vars{'t_query'} = unescape_jsonlog($prefix_vars{'t_query'}); $prefix_vars{'t_pid'} = $prefix_vars{'t_session_id'} if ($use_sessionid_as_pid); @@ -4431,12 +4388,6 @@ &store_temporary_and_lock_infos($pid); } - if ($extens eq 'tsung') { - foreach my $pid (sort {$a <=> $b} keys %tsung_session) { - &store_tsung_session($pid); - } - } - if ($progress && ($getout != 1)) { @@ -4621,7 +4572,7 @@ # Extract the date - if ($str =~ m/[\{,]"timestamp":\s*"(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)\.(\d+)/) + if ($str =~ m/[\{,]"timestamp":\s*"(\d+)-(\d+)-(\d+).(\d+):(\d+):(\d+)/) { $infos{'t_year'} = $1; $infos{'t_month'} = $2; @@ -4629,10 +4580,8 @@ $infos{'t_hour'} = $4; $infos{'t_min'} = $5; $infos{'t_sec'} = $6; - my $milli = $7 || 0; $infos{'t_timestamp'} = "$infos{'t_year'}-$infos{'t_month'}-$infos{'t_day'} $infos{'t_hour'}:$infos{'t_min'}:$infos{'t_sec'}"; $infos{'t_time'} = "$infos{'t_hour'}:$infos{'t_min'}:$infos{'t_sec'}"; - } # Set query parameters as global variables @@ -4726,7 +4675,7 @@ } # Store vacuum related information - if ($cur_info{$cur_pid}{vacuum} && ($line =~ /^\t*(pages|tuples|buffer usage|avg read rate|system usage|WAL usage):/)) + if ($cur_info{$cur_pid}{vacuum} && ($line =~ /^[\s\t]*(pages|tuples|buffer usage|avg read rate|system usage|WAL usage):/)) { if ($line =~ /(pages|tuples): (\d+) removed, (\d+) remain/) { $autovacuum_info{$curdb}{tables}{$cur_info{$cur_pid}{vacuum}}{$1}{removed} += $2; @@ -4834,11 +4783,6 @@ $curdb = $cur_info{$t_pid}{cur_db}; } - if ($is_tsung_output) { - $prefix_vars{'t_date'} = $t_timestamp; - $prefix_vars{'t_date'} =~ s/\D+//g; - } - if (!$overall_stat{$curdb}{'first_log_ts'} || ($overall_stat{$curdb}{'first_log_ts'} gt $t_timestamp)) { $overall_stat{$curdb}{'first_log_ts'} = $t_timestamp; } @@ -5172,13 +5116,6 @@ foreach my $pid (keys %cur_lock_info) { &store_temporary_and_lock_infos($pid); } - # Stores tsung sessions - if ($extens eq 'tsung') - { - foreach my $pid (sort {$a <=> $b} keys %tsung_session) { - &store_tsung_session($pid); - } - } # set path and create subdirectories if ($incr_date =~ /^(\d+)-(\d+)-(\d+)/) @@ -6558,19 +6495,19 @@ for (my $i = 0 ; $i <= $#{$error_info{$curdb}{$k}{date}} ; $i++) { last if ($i == $sample); - ($error_info{$curdb}{$k}{error}[$i], $ret) = &revert_log_level($error_info{$k}{error}[$i]); + ($error_info{$curdb}{$k}{error}[$i], $ret) = &revert_log_level($error_info{$curdb}{$k}{error}[$i]); if ($msg && ($logs_type{$curdb}{ERROR} > 0)) { $logs_type{$curdb}{ERROR}--; $logs_type{$curdb}{LOG}++; } - print $fh "\t- Example $j: $error_info{$curdb}{$k}{date}[$i] - $error_info{$k}{error}[$i]\n"; - print $fh "\t\tDetail: $error_info{$curdb}{$k}{detail}[$i]\n" if ($error_info{$k}{detail}[$i]); - print $fh "\t\tContext: $error_info{$curdb}{$k}{context}[$i]\n" if ($error_info{$k}{context}[$i]); - print $fh "\t\tHint: $error_info{$curdb}{$k}{hint}[$i]\n" if ($error_info{$k}{hint}[$i]); - print $fh "\t\tStatement: ", &anonymize_query($error_info{$curdb}{$k}{statement}[$i]), "\n" if ($error_info{$k}{statement}[$i]); - print $fh "\t\tQueryid: $error_info{$curdb}{$k}{queryid}[$i]\n" if ($error_info{$k}{queryid}[$i]); - print $fh "\t\tDatabase: $error_info{$curdb}{$k}{db}[$i]\n" if ($error_info{$k}{db}[$i]); + print $fh "\t- Example $j: $error_info{$curdb}{$k}{date}[$i] - $error_info{$curdb}{$k}{error}[$i]\n"; + print $fh "\t\tDetail: $error_info{$curdb}{$k}{detail}[$i]\n" if ($error_info{$curdb}{$k}{detail}[$i]); + print $fh "\t\tContext: $error_info{$curdb}{$k}{context}[$i]\n" if ($error_info{$curdb}{$k}{context}[$i]); + print $fh "\t\tHint: $error_info{$curdb}{$k}{hint}[$i]\n" if ($error_info{$curdb}{$k}{hint}[$i]); + print $fh "\t\tStatement: ", &anonymize_query($error_info{$curdb}{$k}{statement}[$i]), "\n" if ($error_info{$curdb}{$k}{statement}[$i]); + print $fh "\t\tQueryid: $error_info{$curdb}{$k}{queryid}[$i]\n" if ($error_info{$curdb}{$k}{queryid}[$i]); + print $fh "\t\tDatabase: $error_info{$curdb}{$k}{db}[$i]\n" if ($error_info{$curdb}{$k}{db}[$i]); $j++; } } @@ -6718,6 +6655,7 @@ @tmpjscode +$EXPLAIN_POST
@@ -8845,10 +8783,10 @@ my @main_user = ('unknown',0); foreach my $u (sort keys %{$session_info{$curdb}{user}}) { - $session_info{user}{$u}{count} ||= 1; - $sess_user_info .= "$u" . &comma_numbers($session_info{user}{$u}{count}) . - "" . &convert_time($session_info{user}{$u}{duration}) . "" . - &convert_time($session_info{user}{$u}{duration} / $session_info{user}{$u}{count}) . + $session_info{$curdb}{user}{$u}{count} ||= 1; + $sess_user_info .= "$u" . &comma_numbers($session_info{$curdb}{user}{$u}{count}) . + "" . &convert_time($session_info{$curdb}{user}{$u}{duration}) . "" . + &convert_time($session_info{$curdb}{user}{$u}{duration} / $session_info{$curdb}{user}{$u}{count}) . ""; $total_count += $session_info{$curdb}{user}{$u}{count}; if ($main_user[1] < $session_info{$curdb}{user}{$u}{count}) { @@ -9317,7 +9255,6 @@ Database - User Count Total Duration Average Duration @@ -11462,13 +11399,11 @@ { my ($id, $plan) = @_; - my $url = $EXPLAIN_URL . url_escape($plan); - # Only TEXT format plan can be sent to Depesz site. if ($plan !~ /Node Type:|"Node Type":|Node-Type/s) { - return "\n
\n
" . $plan . "
\n
\n"; + return "\n
\n
" . $plan . "
\n
\n"; } else { - return "
Explain plan
\n
\n
" . $plan . "
\n
\n"; + return "
Explain plan
\n
\n
" . $plan . "
\n
\n"; } } @@ -15645,7 +15580,6 @@ $sql_prettified->beautify(); $code = $sql_prettified->content(); } - return $code if ($nohighlight); if ($wide_char) { @@ -15837,24 +15771,30 @@ return 0; } } - if ($#exclude_appname >= 0) + if ($#exclude_appname >= 0 && $prefix_vars{'t_appname'}) { # Log line matches the excluded appname - if ($prefix_vars{'t_appname'} && grep(/^\Q$prefix_vars{'t_appname'}\E$/i, @exclude_appname)) + foreach my $p (@exclude_appname) { - delete $current_sessions{$prefix_vars{'t_pid'}}; - delete $last_execute_stmt{$prefix_vars{'t_pid'}}; - return 0; + if ($prefix_vars{'t_appname'} =~ /^$p$/i) + { + delete $current_sessions{$prefix_vars{'t_pid'}}; + delete $last_execute_stmt{$prefix_vars{'t_pid'}}; + return 0; + } } } - if ($#exclude_client >= 0) + if ($#exclude_client >= 0 && $prefix_vars{'t_client'}) { # Log line matches the excluded dbclient - if ($prefix_vars{'t_client'} && grep(/^$prefix_vars{'t_client'}$/i, @exclude_client)) + foreach my $p (@exclude_client) { - delete $current_sessions{$prefix_vars{'t_pid'}}; - delete $last_execute_stmt{$prefix_vars{'t_pid'}}; - return 0; + if ($prefix_vars{'t_client'} =~ /^$p$/i) + { + delete $current_sessions{$prefix_vars{'t_pid'}}; + delete $last_execute_stmt{$prefix_vars{'t_pid'}}; + return 0; + } } } if ($#exclude_queryid >= 0) @@ -15916,6 +15856,36 @@ } } +sub is_excluded_query +{ + my $query = shift; + + foreach (@exclude_query) + { + if ($query =~ /$_/i) + { + $query = ''; + return 1; + } + } + + return 0; +} + +sub is_included_query +{ + my $query = shift; + foreach (@include_query) + { + if ($query =~ /$_/i) + { + $query = ''; + return 1; + } + } + return 0; +} + sub parse_query { my $fmt = shift; @@ -15947,17 +15917,18 @@ # Force some LOG messages to be ERROR messages so that they will appear # in the event/error/warning messages report. #### - if ($prefix_vars{'t_loglevel'} eq 'LOG') { + if ($prefix_vars{'t_loglevel'} eq 'LOG') + { if (&change_log_level($prefix_vars{'t_query'})) { $prefix_vars{'t_loglevel'} = 'ERROR'; } else { $is_log_level = 1; } - - } elsif (($prefix_vars{'t_loglevel'} eq 'ERROR') && !$error_only) { - - if ($prefix_vars{'t_query'} =~ /canceling statement due to statement timeout/) { - + } + elsif (($prefix_vars{'t_loglevel'} eq 'ERROR') && !$error_only) + { + if ($prefix_vars{'t_query'} =~ /canceling statement due to statement timeout/) + { # Stores cancelled queries return if ($disable_query); $cancelled_info{$curdb}{count}++; @@ -15983,19 +15954,17 @@ } # Remove session failure from current workload because there is no disconnection entry - if (!$disable_session && ($prefix_vars{'t_loglevel'} eq 'FATAL')) { - delete $current_sessions{$prefix_vars{'t_pid'}}; - delete $last_execute_stmt{$prefix_vars{'t_pid'}}; - if ($is_tsung_output) { - delete $tsung_session{$prefix_vars{'t_pid'}} - } - } elsif (!$disable_session && ($prefix_vars{'t_loglevel'} eq 'WARNING')) { - if ($prefix_vars{'t_query'} =~ /terminating connection/) { - delete $current_sessions{$prefix_vars{'t_pid'}}; - delete $last_execute_stmt{$prefix_vars{'t_pid'}}; - if ($is_tsung_output) { - delete $tsung_session{$prefix_vars{'t_pid'}} - } + if (!$disable_session && ($prefix_vars{'t_loglevel'} eq 'FATAL')) + { + delete $current_sessions{$t_pid}; + delete $last_execute_stmt{$t_pid}; + } + elsif (!$disable_session && ($prefix_vars{'t_loglevel'} eq 'WARNING')) + { + if ($prefix_vars{'t_query'} =~ /terminating connection/) + { + delete $current_sessions{$t_pid}; + delete $last_execute_stmt{$t_pid}; } } @@ -16043,7 +16012,9 @@ } # Stores the error's detail if previous line was an error - if (exists $cur_info{$t_pid} && ($prefix_vars{'t_loglevel'} =~ /(DETAIL|STATEMENT|CONTEXT|HINT)/) && ($cur_info{$t_pid}{loglevel} =~ $main_error_regex)) { + if (exists $cur_info{$t_pid} && ($prefix_vars{'t_loglevel'} =~ /(DETAIL|STATEMENT|CONTEXT|HINT)/) + && ($cur_info{$t_pid}{loglevel} =~ $main_error_regex)) + { if (($prefix_vars{'t_loglevel'} ne 'DETAIL') || ($prefix_vars{'t_query'} !~ /parameters: (.*)/)) { # Store error details only if this is not a parameter list (never present with errors) $cur_info{$t_pid}{"\L$prefix_vars{'t_loglevel'}\E"} .= $prefix_vars{'t_query'}; @@ -16055,16 +16026,19 @@ } # Special cases when a STATEMENT is parsed - if ($prefix_vars{'t_loglevel'} eq 'STATEMENT') { + if ($prefix_vars{'t_loglevel'} eq 'STATEMENT') + { # Stores temporary file statement if a temporary record with the same pid exists - if ( exists $cur_temp_info{$t_pid}{size}) { + if ( exists $cur_temp_info{$t_pid}{size}) + { # Store query of the last temporary file found. $cur_temp_info{$t_pid}{query} = $prefix_vars{'t_query'}; return; } # Stores query related to last lock information - if (exists $cur_lock_info{$t_pid}{wait}) { + if (exists $cur_lock_info{$t_pid}{wait}) + { $cur_lock_info{$t_pid}{query} = $prefix_vars{'t_query'}; $cur_lock_info{$t_pid}{timestamp} = $prefix_vars{'t_timestamp'}; $cur_lock_info{$t_pid}{dbname} = $prefix_vars{'t_dbname'}; @@ -16077,7 +16051,8 @@ } # Stores query related to cancelled queries information - if (exists $cur_cancel_info{$t_pid}{count}) { + if (exists $cur_cancel_info{$t_pid}{count}) + { $cur_cancel_info{$t_pid}{query} = $prefix_vars{'t_query'}; $cur_cancel_info{$t_pid}{timestamp} = $prefix_vars{'t_timestamp'}; $cur_cancel_info{$t_pid}{dbname} = $prefix_vars{'t_dbname'}; @@ -16088,6 +16063,7 @@ $cur_cancel_info{$t_pid}{cur_db} = $curdb; return; } + } # set current session workload @@ -16163,7 +16139,7 @@ # Stores pre-connection activity if ($is_log_level && ($prefix_vars{'t_query'} =~ /connection received: host=([^\s]+)(?: port=(\d+))?/)) { - $current_sessions{$prefix_vars{'t_pid'}}{host} = $1; + $current_sessions{$t_pid}{host} = $1; return if ($disable_connection); $conn_received{$t_pid} = $1; $conn_received{$t_pid} = _gethostbyaddr($conn_received{$t_pid}) if ($dns_resolv); @@ -16174,21 +16150,21 @@ if ( $is_log_level && ($prefix_vars{'t_query'} =~ /connection authorized: user=([^\s]+)(?: database=([^\s]+))?/)) { - $current_sessions{$prefix_vars{'t_pid'}}{user} = $1; - $current_sessions{$prefix_vars{'t_pid'}}{database} = $2; + $current_sessions{$t_pid}{user} = $1; + $current_sessions{$t_pid}{database} = $2; if ($prefix_vars{'t_query'} =~ /application_name=(.*)/) { if ($1) { $prefix_vars{'t_appname'} = $1; - $current_sessions{$prefix_vars{'t_pid'}}{appname} = $1; + $current_sessions{$t_pid}{appname} = $1; } } return if ($disable_connection); - my $usr = $current_sessions{$prefix_vars{'t_pid'}}{user} || ''; - my $db = $current_sessions{$prefix_vars{'t_pid'}}{database} || 'unknown'; + my $usr = $current_sessions{$t_pid}{user} || ''; + my $db = $current_sessions{$t_pid}{database} || 'unknown'; my $host = ''; if ($prefix_vars{'t_query'} =~ / database=([^\s]+)/) { $db = $1; @@ -16202,12 +16178,6 @@ $host = $prefix_vars{'t_client'} || $prefix_vars{'t_dbclient'}; $host = _gethostbyaddr($host) if ($dns_resolv); } - if ($is_tsung_output) { - $tsung_session{$prefix_vars{'t_pid'}}{connection}{database} = $db; - $tsung_session{$prefix_vars{'t_pid'}}{connection}{user} = $usr; - $tsung_session{$prefix_vars{'t_pid'}}{connection}{date} = $prefix_vars{'t_date'}; - return; - } $overall_stat{$curdb}{'peak'}{$cur_last_log_timestamp}{connection}++; $connection_info{$curdb}{count}++; @@ -16244,15 +16214,8 @@ my $host = $4; $host = _gethostbyaddr($host) if ($dns_resolv); - delete $current_sessions{$prefix_vars{'t_pid'}}; - delete $last_execute_stmt{$prefix_vars{'t_pid'}}; - - if ($is_tsung_output) - { - $tsung_session{$prefix_vars{'t_pid'}}{disconnection}{date} = $prefix_vars{'t_timestamp'}; - &store_tsung_session($prefix_vars{'t_pid'}); - return; - } + delete $current_sessions{$t_pid}; + delete $last_execute_stmt{$t_pid}; # Store time in milliseconds $time =~ /(\d+):(\d+):(\d+\.\d+)/; @@ -16354,9 +16317,7 @@ # Store autoanalyze information if ( $is_log_level - && ($prefix_vars{'t_query'} =~ -/automatic analyze of table "([^\s]+)"/ - ) + && ($prefix_vars{'t_query'} =~ /automatic analyze of table "([^\s]+)"/) ) { return if ($disable_autovacuum); @@ -16524,29 +16485,30 @@ delete $conn_received{$cur_info{$t_pid}{pid}}; # The query is complete but we are missing some debug/info/bind parameter logs - if ($cur_info{$t_pid}{loglevel} eq 'LOG' && $prefix_vars{'t_loglevel'} eq 'DETAIL') { - + if ($cur_info{$t_pid}{loglevel} eq 'LOG' && $prefix_vars{'t_loglevel'} eq 'DETAIL') + { # Apply bind parameters if any - if ($prefix_vars{'t_query'} =~ /parameters: (.*)/) { + if ($prefix_vars{'t_query'} =~ /parameters: (.*)/) + { $cur_bind_info{$t_pid}{'bind'}{'parameters'} = $1 if (exists $cur_bind_info{$t_pid}); $cur_info{$t_pid}{parameters} = "$1"; # go look at other params return; # replace the execute statements with the prepared query and set the parameters - } elsif ($prefix_vars{'t_query'} =~ s/prepare: PREPARE\s+([^\s]+)(\s*\([^\)]*\))?\s+AS\s*(.*)/[ EXECUTE $1 (PARAM) ]/is) { + } + elsif ($prefix_vars{'t_query'} =~ s/prepare: PREPARE\s+([^\s]+)(\s*\([^\)]*\))?\s+AS\s*(.*)/[ EXECUTE $1 (PARAM) ]/is) + { my $q_name = $1; my $real_query = $3; - if ($cur_info{$t_pid}{query} =~ /\b$q_name\b/) { + if ($cur_info{$t_pid}{query} =~ /\b$q_name\b/) + { $cur_info{$t_pid}{query} =~ s/EXECUTE\s+$q_name\s*\((.*)\)[;]*$//is; $cur_info{$t_pid}{parameters} = $1; $prefix_vars{'t_query'} =~ s/\(PARAM\)/($cur_info{$t_pid}{parameters})/; $cur_info{$t_pid}{query} = $prefix_vars{'t_query'} . ' - ' . $real_query; $cur_info{$t_pid}{'bind'} = 1; my @t_res = split(/[\s]*,[\s]*/, $cur_info{$t_pid}{parameters}); - for (my $i = 0 ; $i <= $#t_res ; $i++) { - my $num = $i + 1; - $cur_info{$t_pid}{query} =~ s/\$$num\b/$t_res[$i]/s; - } + $cur_info{$t_pid}{query} =~ s/\$(\d+)/$t_res[$1-1]/gs; } # go look at other params return; @@ -16578,14 +16540,6 @@ return; } - # Store info as tsung session following the output file extension - if ($is_tsung_output && !exists $tsung_session{$prefix_vars{'t_pid'}}{connection} && $prefix_vars{'t_dbname'}) - { - $tsung_session{$prefix_vars{'t_pid'}}{connection}{database} = $prefix_vars{'t_dbname'}; - $tsung_session{$prefix_vars{'t_pid'}}{connection}{user} = $prefix_vars{'t_dbuser'}; - $tsung_session{$prefix_vars{'t_pid'}}{connection}{date} = $prefix_vars{'t_date'}; - } - my $t_action = ''; # Store query duration generated by log_min_duration >= 0 in postgresql.conf if ($prefix_vars{'t_query'} =~ s/duration: ([0-9\.]+) ms\s+(?:rows: \d+\s+size: \d+ bytes\s+)?(query|statement):\s*//s) @@ -16602,9 +16556,9 @@ if ($prefix_vars{'t_query'} =~ /^PREPARE$/i) { $t_action = 'prepare'; $overall_stat{$curdb}{'queries_duration'}{$t_action} += $prefix_vars{'t_duration'} if ($prefix_vars{'t_duration'}); - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'query'} = $prefix_vars{'t_query'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'duration'} = $prefix_vars{'t_duration'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'timestamp'} = $cur_last_log_timestamp, + $cur_bind_info{$t_pid}{$t_action}{'query'} = $prefix_vars{'t_query'}; + $cur_bind_info{$t_pid}{$t_action}{'duration'} = $prefix_vars{'t_duration'}; + $cur_bind_info{$t_pid}{$t_action}{'timestamp'} = $cur_last_log_timestamp, return; } } @@ -16616,15 +16570,22 @@ $t_action = $2; $t_action =~ s/ from fetch//; $t_action = 'prepare' if ($t_action eq 'parse'); + + # Should we have to exclude some queries + return 1 if ($#exclude_query >= 0 && &is_excluded_query($prefix_vars{'t_query'})); + + # Should we have to include only some queries + return 1 if ($#include_query >= 0 && !&is_included_query($prefix_vars{'t_query'})); + $overall_stat{$t_action}++; $per_minute_info{$curdb}{$date_part}{$prefix_vars{'t_hour'}}{$prefix_vars{'t_min'}}{$t_action}++; if ($t_action !~ /query|statement|execute/) { if ($prefix_vars{'t_duration'}) { $overall_stat{$curdb}{'queries_duration'}{$t_action} += $prefix_vars{'t_duration'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'query'} = $prefix_vars{'t_query'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'duration'} = $prefix_vars{'t_duration'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'timestamp'} = $cur_last_log_timestamp, + $cur_bind_info{$t_pid}{$t_action}{'query'} = $prefix_vars{'t_query'}; + $cur_bind_info{$t_pid}{$t_action}{'duration'} = $prefix_vars{'t_duration'}; + $cur_bind_info{$t_pid}{$t_action}{'timestamp'} = $cur_last_log_timestamp, } return; } @@ -16637,15 +16598,15 @@ # the risk is to fill memory with this storage. if (scalar keys %session_info) { - $last_execute_stmt{$prefix_vars{'t_pid'}}{query} = $prefix_vars{'t_query'}; - $last_execute_stmt{$prefix_vars{'t_pid'}}{timestamp} = $cur_last_log_timestamp; + $last_execute_stmt{$t_pid}{query} = $prefix_vars{'t_query'}; + $last_execute_stmt{$t_pid}{timestamp} = $cur_last_log_timestamp; } } # Activate storage of the explain plan generated by auto_explain elsif (!$noexplain && $prefix_vars{'t_query'} =~ s/duration: ([0-9\.]+) ms\s+(?:rows: \d+\s+size: \d+ bytes\s+)?plan://s) { - $cur_plan_info{$prefix_vars{'t_pid'}}{duration} = $1; - $cur_plan_info{$prefix_vars{'t_pid'}}{plan} = ''; + $cur_plan_info{$t_pid}{duration} = $1; + $cur_plan_info{$t_pid}{plan} = ''; $prefix_vars{'t_duration'} = $1; # needed to be saved later } # Log line without duration at all @@ -16663,13 +16624,20 @@ $t_action = $1; $t_action =~ s/ from fetch//; $t_action = 'prepare' if ($t_action eq 'parse'); + + # Should we have to exclude some queries + return 1 if ($#exclude_query >= 0 && &is_excluded_query($prefix_vars{'t_query'})); + + # Should we have to include only some queries + return 1 if ($#include_query >= 0 && !&is_included_query($prefix_vars{'t_query'})); + $overall_stat{$t_action}++; $per_minute_info{$curdb}{$date_part}{$prefix_vars{'t_hour'}}{$prefix_vars{'t_min'}}{$t_action}++; # Skipping parse and bind logs but after storing their duration time return if ($t_action !~ /query|statement|execute/); $prefix_vars{'t_bind'} = 1; # Stores execute query in case of temporary file - $last_execute_stmt{$prefix_vars{'t_pid'}} = $prefix_vars{'t_query'}; + $last_execute_stmt{$t_pid} = $prefix_vars{'t_query'}; } # Log line without duration at all elsif ($fmt eq 'redshift' and $prefix_vars{'t_query'}) @@ -16686,8 +16654,8 @@ if ($prefix_vars{'t_query'} =~ /^PREPARE$/i) { $t_action = 'prepare'; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'query'} = $prefix_vars{'t_query'}; - $cur_bind_info{$prefix_vars{'t_pid'}}{$t_action}{'timestamp'} = $cur_last_log_timestamp, + $cur_bind_info{$t_pid}{$t_action}{'query'} = $prefix_vars{'t_query'}; + $cur_bind_info{$t_pid}{$t_action}{'timestamp'} = $cur_last_log_timestamp, return; } } @@ -16704,7 +16672,8 @@ return; } - if ( ($fmt =~ /csv|jsonlog/) && ($prefix_vars{'t_loglevel'} ne 'LOG')) { + if (($fmt =~ /csv|jsonlog/) && ($prefix_vars{'t_loglevel'} ne 'LOG')) + { $cur_info{$t_pid}{detail} = $prefix_vars{'t_detail'}; $cur_info{$t_pid}{hint} = $prefix_vars{'t_hint'}; $cur_info{$t_pid}{context} = $prefix_vars{'t_context'}; @@ -16919,64 +16888,11 @@ $cur_info{$t_pid}{dbuser} = $prefix_vars{'t_dbuser'} if (!$cur_info{$t_pid}{dbuser}); $cur_info{$t_pid}{dbclient} = $prefix_vars{'t_client'} || $prefix_vars{'t_dbclient'} if (!$cur_info{$t_pid}{dbclient}); $cur_info{$t_pid}{dbappname} = $prefix_vars{'t_appname'} if (!$cur_info{$t_pid}{dbappname}); - $cur_info{$t_pid}{date} = $prefix_vars{'t_date'} if ($is_tsung_output && !$cur_info{$t_pid}{date}); $cur_info{$t_pid}{bind} = $prefix_vars{'t_bind'} if (!$cur_info{$t_pid}{bind}); $cur_info{$t_pid}{sqlstate} = $prefix_vars{'t_sqlstate'} if (!$cur_info{$t_pid}{sqlstate}); $cur_info{$t_pid}{backendtype} = $prefix_vars{'t_backend_type'} if (!$cur_info{$t_pid}{backendtype}); # Store current report name and list of database $cur_info{$t_pid}{cur_db} = set_current_db($cur_info{$t_pid}{dbname}); - -} - -sub store_tsung_session -{ - my $pid = shift; - - return if ($#{$tsung_session{$pid}{dates}} < 0); - - # Open filehandle - my $fh = new IO::File ">>$current_out_file"; - if (not defined $fh) { - localdie("FATAL: can't write to $current_out_file, $!\n"); - } - if ($pid) { - print $fh " \n"; - if (exists $tsung_session{$pid}{connection}{database}) { - print $fh qq{ - - - -}; - } - if ($#{$tsung_session{$pid}{dates}} >= 0) { - my $sec = 0; - if ($tsung_session{$pid}{connection}{date}) { - $sec = $tsung_session{$pid}{dates}[0] - $tsung_session{$pid}{connection}{date}; - } - print $fh " \n" if ($sec > 0); - print $fh " \n"; - for (my $i = 0 ; $i <= $#{$tsung_session{$pid}{queries}} ; $i++) { - $tsung_queries++; - $sec = 0; - if ($i > 0) { - $sec = $tsung_session{$pid}{dates}[$i] - $tsung_session{$pid}{dates}[$i - 1]; - print $fh " \n" if ($sec > 0); - } - print $fh " \n"; - } - print $fh " \n"; - } - if ($#{$tsung_session{$pid}{dates}} >= 0) { - my $sec = $tsung_session{$pid}{disconnection}{date} - $tsung_session{$pid}{dates}[-1]; - print $fh " \n" if ($sec > 0); - } - if (exists $tsung_session{$pid}{connection}{database}) { - print $fh " \n"; - } - print $fh " \n\n"; - delete $tsung_session{$pid}; - } - $fh->close; } sub store_queries @@ -17002,21 +16918,14 @@ # Replace bind parameters values in the query if any if (exists $cur_info{$t_pid}{parameters} && ($cur_info{$t_pid}{parameters} =~ /[,\s]*\$(\d+)\s=\s/)) { - my @t_res = split(/[,\s]*\$(\d+)\s=\s/, $cur_info{$t_pid}{parameters}); - shift(@t_res); - for (my $i = 0 ; $i < $#t_res ; $i += 2) { - $cur_info{$t_pid}{query} =~ s/\$$t_res[$i]\b/$t_res[$i+1]/gs; - } + my @t_res = $cur_info{$t_pid}{parameters} =~ /\$\d+\s=\s(.*?)(?:,|$)/g; + $cur_info{$t_pid}{query} =~ s/\$(\d+)/$t_res[$1-1]/gs; } else { # parameters from EXECUTE statements my @t_res = split(/[\s]*,[\s]*/, $cur_info{$t_pid}{parameters}); - for (my $i = 0 ; $i <= $#t_res ; $i++) - { - my $num = $i + 1; - $cur_info{$t_pid}{query} =~ s/\$$num\b/$t_res[$i]/gs; - } + $cur_info{$t_pid}{query} =~ s/\$(\d+)/$t_res[$1-1]/gs; } return if ($cur_info{$t_pid}{query} =~ /^parameters:/); print $fh "# Query #", $query_numbering++, "\n" if ($query_numbering); @@ -17048,21 +16957,14 @@ # Replace bind parameters values in the query if any if (exists $cur_info{$t_pid}{parameters} && ($cur_info{$t_pid}{parameters} =~ /[,\s]*\$(\d+)\s=\s/)) { - my @t_res = split(/[,\s]*\$(\d+)\s=\s/, $cur_info{$t_pid}{parameters}); - shift(@t_res); - for (my $i = 0 ; $i < $#t_res ; $i += 2) { - $cur_info{$t_pid}{query} =~ s/\$$t_res[$i]\b/$t_res[$i+1]/gs; - } + my @t_res = $cur_info{$t_pid}{parameters} =~ /\$\d+\s=\s(.*?)(?:,|$)/g; + $cur_info{$t_pid}{query} =~ s/\$(\d+)/$t_res[$1-1]/gs; } else { # parameters from EXECUTE statements my @t_res = split(/[\s]*,[\s]*/, $cur_info{$t_pid}{parameters}); - for (my $i = 0 ; $i <= $#t_res ; $i++) - { - my $num = $i + 1; - $cur_info{$t_pid}{query} =~ s/\$$num\b/$t_res[$i]/gs; - } + $cur_info{$t_pid}{query} =~ s/\$(\d+)/$t_res[$1-1]/gs; } # We only process stored object with query here @@ -17074,43 +16976,10 @@ } # Should we have to exclude some queries - if ($#exclude_query >= 0) { - foreach (@exclude_query) { - if ($cur_info{$t_pid}{query} =~ /$_/i) { - $cur_info{$t_pid}{query} = ''; - return 1; - } - } - } + return 1 if ($#exclude_query >= 0 && &is_excluded_query($cur_info{$t_pid}{query})); # Should we have to include only some queries - if ($#include_query >= 0) { - foreach (@include_query) { - if ($cur_info{$t_pid}{query} !~ /$_/i) { - $cur_info{$t_pid}{query} = ''; - return 1; - } - } - } - - # Dump queries as tsung request and return - if ($is_tsung_output) { - if ($cur_info{$t_pid}{loglevel} eq 'LOG') { - push(@{$tsung_session{$t_pid}{queries}}, $cur_info{$t_pid}{query}); - push(@{$tsung_session{$t_pid}{dates}}, $cur_info{$t_pid}{date}); - if (!exists $tsung_session{$t_pid}{connection} && $cur_info{$t_pid}{dbname}) { - $tsung_session{$t_pid}{connection}{database} = $cur_info{$t_pid}{dbname}; - $tsung_session{$t_pid}{connection}{user} = $cur_info{$t_pid}{dbuser}; - $tsung_session{$t_pid}{connection}{date} = $cur_info{$t_pid}{date}; - } - } - - # Stores global statistics - $overall_stat{'queries_number'}++; - $overall_stat{$cur_info{$t_pid}{cur_db}}{'queries_number'}++; - $overall_stat{$cur_info{$t_pid}{cur_db}}{'queries_duration'}{'execute'} += $cur_info{$t_pid}{duration} if ($cur_info{$t_pid}{duration}); - return 1; - } + return 1 if ($#include_query >= 0 && !&is_included_query($cur_info{$t_pid}{query})); # Truncate the query if requested by the user if ($maxlength > 0) { @@ -17173,7 +17042,7 @@ elsif ($cur_info{$t_pid}{loglevel} eq 'LOG') { - # Store auto explain plan when switching to an other log entry + # Store auto explain plan when switching to another log entry if (exists $cur_plan_info{$t_pid}{plan}) { $cur_plan_info{$t_pid}{plan} =~ s/^\s+//s; @@ -17730,7 +17599,7 @@ $orig_query =~ s/(unterminated quoted string at or near) "[^"]+"/$1 "..."/; $orig_query =~ s/on page \d+ of relation/on page ... of relation/; $orig_query =~ s/(side location conflict at ).*/$1 .../; - $orig_query =~ s/(permission denied for ^\s]+) .*/$1 .../; + $orig_query =~ s/(permission denied for [^\s]+) .*/$1 .../; $orig_query =~ s/(database) (?:.*)? (must be vacuumed within) \d+ (transactions)/$1 "..." $2 ... $3/; # Need more normalization stuff here @@ -18051,15 +17920,7 @@ my $lbl = 'queries'; $lbl = 'stats' if ($fmt =~ /pgbouncer/); - if ($is_tsung_output) - { - sprintf( - "[%-${width}s] Parsed %${num_width}s bytes of %s (%.2f%%), $lbl: %d\r", - $char x $nchars . '>', - $got, $total, 100 * $got / +$total, ($queries || $overall_stat{'queries_number'}) - ); - } - elsif ($format eq 'binary') + if ($format eq 'binary') { my $file = $_[-1]; sprintf( @@ -18341,6 +18202,7 @@ my $logf = shift; # Remove log format from log file if any + $logf =~ s/[\r\n]+//sg; $logf =~ s/:(stderr|csv|syslog|pgbouncer|jsonlog|logplex|rds|redshift)\d*$//i; my $http_download = ($logf =~ /^(http[s]*:|[s]*ftp:)/i) ? 1 : 0; @@ -18389,6 +18251,8 @@ } elsif ($ssh_download && $logf =~ m#^ssh:\/\/([^\/]+)/(.*)#i) { my $host_info = $1; my $file = $2; + $file =~ s/[\r\n]+//sg; + $file =~ s/:(stderr|csv|syslog|pgbouncer|jsonlog|logplex|rds|redshift)\d*$//i; my $ssh = $ssh_command || 'ssh'; if ($host_info =~ s/:(\d+)$//) { $host_info = "-p $1 $host_info"; @@ -19049,13 +18913,13 @@ sub localdie { - my $msg = shift; + my ($msg, $code) = @_; my ($package, $filename, $line) = caller; print STDERR "$msg - Error at line $line\n"; unlink("$PID_FILE"); - exit 1; + exit($code || 1); } #### @@ -19132,8 +18996,6 @@ { my ($of, $ext, $db) = @_; - $is_tsung_output = 0; - if (!$ext) { if ($of =~ /\.bin/i) @@ -19152,10 +19014,6 @@ } $ext = 'json'; } - elsif ($of =~ /\.tsung/i) - { - $ext = 'tsung'; - } elsif ($of =~ /\.htm[l]*/i) { $ext = 'html'; @@ -19181,9 +19039,8 @@ } } - $is_tsung_output = 1 if ($ext eq 'tsung'); - - if ($dump_normalized_only || $dump_all_queries) { + if ($dump_normalized_only || $dump_all_queries) + { $ext = 'txt'; $incremental = 0; $report_title = 'Normalized query report' if (!$report_title && !$dump_all_queries); @@ -19273,6 +19130,18 @@ return 'LF'; } +sub html_escape +{ + my $toencode = shift; + + return undef unless defined($toencode); + + utf8::encode($toencode) if utf8::is_utf8($toencode); + $toencode=~s/([^a-zA-Z0-9_.~-])/uc sprintf("%%%02x",ord($1))/eg; + + return $toencode; +} + # Inclusion of Perl package pgFormatter::Beautify. { @@ -19414,7 +19283,7 @@ =item * wrap - wraps given keywords in pre- and post- markup. Specific docs in SQL::Beautify -=item * format_type - try an other formatting +=item * format_type - try another formatting =item * wrap_limit - wrap queries at a certain length @@ -23290,7 +23159,7 @@ if ($#dynsep == -1) { # if a dollar sign is found after EXECUTE then the following string - # until an other dollar is found will be understand as a text delimiter + # until another dollar is found will be understand as a text delimiter @dynsep = $$str =~ /EXECUTE\s+(\$[^\$\s]*\$)/igs; } diff -Nru pgbadger-11.8/README pgbadger-12.0/README --- pgbadger-11.8/README 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/README 2022-09-13 18:17:52.000000000 +0000 @@ -29,9 +29,9 @@ (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 are added to the postgres database report. + database is added to the postgres database report. -f | --format logtype : possible values: syslog, syslog2, stderr, jsonlog, - cvs, pgbouncer, logplex, rds and redshift. Use this + 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. @@ -42,28 +42,28 @@ -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. Run as single - by default or when working with csvlog. - -J | --Jobs number : number of log file to parse in parallel. Process one - file at a time by default or when csvlog is used. + -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 file to parse. + -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 statement to avoid garbage + -M | --no-multiline : do not collect multiline statements 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. This option can be used - multiple time to output several format. 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 file must be saved. + 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't using one of the standard prefixes specified @@ -76,11 +76,11 @@ -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 logfile to parse locally the file. - -R | --retention N : number of weeks to keep in incremental mode. Default + 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 - directory are automatically removed. + 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. @@ -93,27 +93,29 @@ -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, json or - tsung. Default: html + -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 or bzcat or unzip is not in your path. + 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 which contains all the regex to - use to exclude queries from the report. One regex - per line. + --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 which contains all the regex of - the queries to include from the report. One regex - per line. + --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 @@ -137,22 +139,20 @@ 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 - time. + times. --exclude-appname name : exclude entries for the specified application name from report. Example: "pg_dump". Can be used - multiple time. - --exclude-line regex : pgBadger will start to exclude any log entry that - will match the given regex. Can be used multiple - time. + 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 time. + Can be used multiple times. --anonymize : obscure all literals in queries, useful to hide confidential data. - --noreport : prevent pgBadger to create reports in incremental - mode. + --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 = 'all' - --enable-checksum : used to add a md5 sum under each query report. + --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 @@ -162,18 +162,20 @@ execution of pgBadger. Default: pgbadger.pid --rebuild : used to rebuild all html reports in incremental output directories where there's binary data files. - --pgbouncer-only : only show PgBouncer related menu in the header. - --start-monday : in incremental mode, calendar's weeks start on - a sunday. Use this option to start on a monday. - --iso-week-number : in incremental mode, calendar's weeks start on a - monday and respect the ISO 8601 week number, range + --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 query to out.txt + --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 - make more difficult log search with a date/time. + 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 @@ -188,39 +190,47 @@ --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: http://explain.depesz.com/?is_public=0&is_anon=0&plan= + tool. Default: http://explain.depesz.com/ --tempdir DIR : set directory where temporary files will be written Default: File::Spec->tmpdir() || '/tmp' --no-process-info : disable changing process title to help identify pgbadger process, some system do not support it. --dump-all-queries : dump all queries found in the log file replacing - bind parameters are included in the queries at - their respective placeholders position. + 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. 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. + connection. Use -r or --remote-host to set the host IP address or + hostname. There are also some additional options to fully control the + ssh connection. --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. Default to running user. + --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 secs. + --ssh-timeout second timeout to ssh connection failure. Default: 10 sec. --ssh-option options list of -o options to use for the ssh connection. Options always used: -o ConnectTimeout=$ssh_timeout -o PreferredAuthentications=hostbased,publickey - Log file to parse can also be specified using an URI, supported protocol - 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. + Log file to parse can also be specified using an URI, supported + protocols are http[s] and [s]ftp. The curl command will be used to + download the file, and the file will be parsed during download. The ssh + protocol is also supported and will use the ssh command like with the + remote host use. See examples bellow. + + Return codes: + + 0: on success + 1: die on error + 2: if it has been interrupted using ctr+c for example + 3: the pid file already exists or can not be created + 4: no log file was given at command line Examples: @@ -230,7 +240,7 @@ 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 + # Log line prefix with stderr log output pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21* pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log # Log line prefix with syslog log output @@ -249,10 +259,6 @@ pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username@172.12.110.14/pgbouncer.log - Generate Tsung sessions XML file with select queries only: - - pgbadger -S -o sessions.tsung --prefix '%t [%p]: user=%u,db=%d ' /pglog/postgresql-10.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 @@ -270,13 +276,13 @@ will generate a report per day and per week. - In incremental mode, you can also specify the number of week to keep in + In incremental mode, you can also specify the number of weeks to keep in the reports: /usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/ If you have a pg_dump at 23:00 and 13:00 each day during half an hour, - you can use pgBadger as follow to exclude these period from the report: + you can use pgBadger as follow to exclude these periods from the report: pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log @@ -293,7 +299,7 @@ pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5' you don't need to specify any log file at command line, but if you have - other PostgreSQL log file to parse, you can add them as usual. + other PostgreSQL log files to parse, you can add them as usual. To rebuild all incremental html reports after, proceed as follow: @@ -304,7 +310,7 @@ it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option. - pgBadger also support Heroku PostgreSQL logs using logplex format: + pgBadger also supports Heroku PostgreSQL logs using logplex format: heroku logs -p postgres | pgbadger -f logplex -o heroku.html - @@ -315,14 +321,14 @@ pgbadger -f rds -o rds_out.html rds.log - CloudSQL Postgresql logs it's fairly normal PostgreSQL log but - encapsulated in JSON format. It is auto detected too by pgBagder but in - case you need to force the log format, use `jsonlog` + Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but + encapsulated in JSON format. It is autodetected by pgBadger but in case + you need to force the log format use `jsonlog`: pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log - This is the same than with the jsonlog extension, the json format is - different but pgBadger can parse both format. + This is the same as with the jsonlog extension, the json format is + different but pgBadger can parse both formats. To create a cumulative report over a month use command: @@ -347,21 +353,21 @@ 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 format + 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 upper than 5.05 that supports the --robot option. In order - pgbadger determine uncompressed file size with lz4, file must be - compressed with --content-size option. For the complete list of features + an xz version higher than 5.05 that supports the --robot option. lz4 + files must be compressed with the --content-size option for pgbadger to + determine the uncompressed file size. For the complete list of features, see below. All charts are zoomable and can be saved as PNG images. You can also limit pgBadger to only report errors or remove any part of - the report using command line options. + 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 supports any custom format set in the log_line_prefix directive + of your postgresql.conf file as long as it at least specifies the %t 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. @@ -438,64 +444,67 @@ 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 + Histogram granularity can be adjusted using the -A command-line option. + By default, they will report the mean of each top queries/errors occurring per hour, but you can specify the granularity down to the minute. pgBadger can also be used in a central place to parse remote log files using a passwordless SSH connection. This mode can be used with - compressed files and in the multiprocess per file mode (-J) but can not + compressed files and in the multiprocess per file mode (-J), but cannot be used with the CSV log format. + Examples of reports can be found here: + https://pgbadger.darold.net/#reports + REQUIREMENT pgBadger comes as a single Perl script - you do not need anything other than a modern Perl distribution. Charts are rendered using a JavaScript - library so you don't need anything other than a web browser. Your + 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: + If you plan to parse PostgreSQL CSV log files, you might need some Perl + Modules: Text::CSV_XS - to parse PostgreSQL CSV log files. This module is optional, if you don't have PostgreSQL log in the CSV - format you don't need to install it. + format, you don't need to install it. - If you want to export statistics as JSON file you need an additional + If you want to export statistics as JSON file, you need an additional Perl module: JSON::XS - JSON serialising/deserialising, done correctly and fast - This module is optional, if you don't select the json output format you - don't need to install it. You can install it on a Debian like system + This module is optional, if you don't select the json output format, you + don't need to install it. You can install it on a Debian-like system using: sudo apt-get install libjson-xs-perl - and in RPM like system using: + and on RPM-like system using: sudo yum install perl-JSON-XS Compressed log file format is autodetected from the file extension. If - pgBadger find 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 and if the file extension is zip or xz then the unzip - or xz utilities will be used. + pgBadger finds a gz extension, it will use the zcat utility; with a bz2 + extension, it will use bzcat; with lz4, it will use lz4cat; with zst, it + will use zstdcat; if the file extension is zip or xz, then the unzip or + xz utility will be used. - If those utilities are not found in the PATH environment variable then - use the --zcat command line option to change this path. For example: + If those utilities are not found in the PATH environment variable, then + use the --zcat command-line option to change this path. For example: --zcat="/usr/local/bin/gunzip -c" or --zcat="/usr/local/bin/bzip2 -dc" --zcat="C:\tools\unzip -p" - By default pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip + By default, pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities following the file extension. If you use the default - autodetection compress format you can mixed gz, bz2, lz4, xz, zip or - zstd files. Specifying a custom value to --zcat option will remove this - feature of mixed compressed format. + autodetection of compression format, you can mix gz, bz2, lz4, xz, zip + or zstd files. Specifying a custom value of --zcat option will remove + the possibility of mixed compression format. - Note that multiprocessing can not be used with compressed files or CSV + Note that multiprocessing cannot be used with compressed files or CSV files as well as under Windows platform. INSTALLATION @@ -519,7 +528,7 @@ perl Makefile.PL INSTALLDIRS=vendor - By default INSTALLDIRS is set to site. + By default, INSTALLDIRS is set to site. POSTGRESQL CONFIGURATION You must enable and set some configuration directives in your @@ -531,14 +540,14 @@ 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 + that if you have log_statement set to 'all', nothing will be logged through the log_min_duration_statement directive. See the next chapter for more information. - pgBadger supports any custom format set into the log_line_prefix - directive of your postgresql.conf file as long as it at least specify a - time escape sequence (%t, %m or %n) and the process related escape - sequence (%p or %c). + pgBadger supports any custom format set in the log_line_prefix directive + of your postgresql.conf file as long as it at least specifies a time + escape sequence (%t, %m or %n) and a process-related escape sequence (%p + or %c). For example, with 'stderr' log format, log_line_prefix must be at least: @@ -581,7 +590,7 @@ lc_messages='en_US.UTF-8' lc_messages='C' - pgBadger parser do not support other locale like 'fr_FR.UTF-8' for + pgBadger parser does not support other locales, like 'fr_FR.UTF-8' for example. LOG STATEMENTS @@ -594,13 +603,12 @@ 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. + want to add the most common query report, you can either choose to set + log_min_duration_statement to a higher value or to enable log_statement. Enabling log_min_duration_statement will add reports about slowest queries and queries that took up the most time. Take care that if you - have log_statement set to 'all' nothing will be logged with + have log_statement set to 'all', nothing will be logged with log_min_duration_statement. Warning: Do not enable both log_min_duration_statement, log_duration and @@ -626,17 +634,18 @@ 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 + 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. + 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. Here is a benchmark done on a server with 8 CPUs and a single file of 9.5GB. @@ -657,7 +666,7 @@ 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 + 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 @@ -681,21 +690,21 @@ 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 + 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. + To save disk space, you may want to use the -X or --extra-files + command-line option to force pgBadger to write JavaScript and CSS to + separate files in the output directory. The resources will then be + loaded using script and link tags. Rebuilding reports Incremental reports can be rebuilt after a pgbadger report fix or a new feature to update all HTML reports. To rebuild all reports where a - binary file is still present proceed as follow: + binary file is still present, proceed as follow: rm /path/to/reports/*.js rm /path/to/reports/*.css @@ -705,21 +714,21 @@ if the reports were built using this option. Monthly reports - By default pgBadger in incremental mode only computes daily and weekly - reports. If you want monthly cumulative reports you will have to use a - separate command to specify the report to build. For example to build a + By default, pgBadger in incremental mode only computes daily and weekly + reports. If you want monthly cumulative reports, you will have to use a + separate command to specify the report to build. For example, to build a report for August 2019: pgbadger -X --month-report 2919-08 /var/www/pg_reports/ 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 lot of time - following the amount of data. + month can be run every day, it is entirely rebuilt each time. The + monthly report is not built by default because it could take a lot of + time following the amount of data. - If reports were built with the per database option ( -E | --explode ) it - must be used too when calling pgbadger to build monthly report: + If reports were built with the per-database option ( -E | --explode ), + it must be used too when calling pgbadger to build monthly report: pgbadger -E -X --month-report 2919-08 /var/www/pg_reports/ @@ -729,7 +738,7 @@ 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: + running the following commands each hour: pgbadger --last-parsed .pgbadger_last_state_file -o sunday/hourX.bin /var/log/pgsql/postgresql-Sun.log @@ -747,7 +756,7 @@ 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 + 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 @@ -756,7 +765,7 @@ JSON FORMAT JSON format is good for sharing data with other languages, which makes - it easy to integrate pgBadger result into other monitoring tools like + it easy to integrate pgBadger result into other monitoring tools, like Cacti or Graphite. AUTHORS @@ -768,7 +777,7 @@ This web site is a work of Gilles Darold. - pgBadger is maintained by Gilles Darold and every one who wants to + pgBadger is maintained by Gilles Darold and everyone who wants to contribute. Many people have contributed to pgBadger, they are all quoted in the diff -Nru pgbadger-11.8/README.md pgbadger-12.0/README.md --- pgbadger-11.8/README.md 2022-04-08 11:14:19.000000000 +0000 +++ pgbadger-12.0/README.md 2022-09-13 18:17:52.000000000 +0000 @@ -48,9 +48,9 @@ (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 are added to the postgres database report. + database is added to the postgres database report. -f | --format logtype : possible values: syslog, syslog2, stderr, jsonlog, - cvs, pgbouncer, logplex, rds and redshift. Use this + 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. @@ -61,28 +61,28 @@ -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. Run as single - by default or when working with csvlog. - -J | --Jobs number : number of log file to parse in parallel. Process one - file at a time by default or when csvlog is used. + -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 file to parse. + -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 statement to avoid garbage + -M | --no-multiline : do not collect multiline statements 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. This option can be used - multiple time to output several format. 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 file must be saved. + 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't using one of the standard prefixes specified @@ -95,11 +95,11 @@ -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 logfile to parse locally the file. - -R | --retention N : number of weeks to keep in incremental mode. Default + 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 - directory are automatically removed. + 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. @@ -117,22 +117,24 @@ -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 or bzcat or unzip is not in your path. + 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 which contains all the regex to - use to exclude queries from the report. One regex - per line. + --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 which contains all the regex of - the queries to include from the report. One regex - per line. + --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 @@ -156,22 +158,20 @@ 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 - time. + times. --exclude-appname name : exclude entries for the specified application name from report. Example: "pg_dump". Can be used - multiple time. - --exclude-line regex : pgBadger will start to exclude any log entry that - will match the given regex. Can be used multiple - time. + 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 time. + Can be used multiple times. --anonymize : obscure all literals in queries, useful to hide confidential data. - --noreport : prevent pgBadger to create reports in incremental - mode. + --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 = 'all' - --enable-checksum : used to add a md5 sum under each query report. + --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 @@ -181,18 +181,20 @@ execution of pgBadger. Default: pgbadger.pid --rebuild : used to rebuild all html reports in incremental output directories where there's binary data files. - --pgbouncer-only : only show PgBouncer related menu in the header. - --start-monday : in incremental mode, calendar's weeks start on - a sunday. Use this option to start on a monday. - --iso-week-number : in incremental mode, calendar's weeks start on a - monday and respect the ISO 8601 week number, range + --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 query to out.txt + --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 - make more difficult log search with a date/time. + 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 @@ -207,38 +209,46 @@ --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: http://explain.depesz.com/?is_public=0&is_anon=0&plan= + tool. Default: http://explain.depesz.com/ --tempdir DIR : set directory where temporary files will be written Default: File::Spec->tmpdir() || '/tmp' --no-process-info : disable changing process title to help identify pgbadger process, some system do not support it. --dump-all-queries : dump all queries found in the log file replacing - bind parameters are included in the queries at - their respective placeholders position. + 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. 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 +Use -r or --remote-host to set the host IP address or hostname. There are also some additional options to fully control the ssh connection. --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. Default to running user. + --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 secs. + --ssh-timeout second timeout to ssh connection failure. Default: 10 sec. --ssh-option options list of -o options to use for the ssh connection. Options always used: -o ConnectTimeout=$ssh_timeout -o PreferredAuthentications=hostbased,publickey -Log file to parse can also be specified using an URI, supported protocol are -http\[s\] and \[s\]ftp. The curl command will be used to download the file and the +Log file to parse can also be specified using an URI, supported protocols are +http\[s\] and \[s\]ftp. The curl command will be used to download the file, and the file will be parsed during download. The ssh protocol is also supported and will use the ssh command like with the remote host use. See examples bellow. +Return codes: + + 0: on success + 1: die on error + 2: if it has been interrupted using ctr+c for example + 3: the pid file already exists or can not be created + 4: no log file was given at command line + Examples: pgbadger /var/log/postgresql.log @@ -247,7 +257,7 @@ 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 + # Log line prefix with stderr log output pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21* pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log # Log line prefix with syslog log output @@ -285,13 +295,13 @@ will generate a report per day and per week. -In incremental mode, you can also specify the number of week to keep in the +In incremental mode, you can also specify the number of weeks to keep in the reports: /usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/ If you have a pg\_dump at 23:00 and 13:00 each day during half an hour, you can -use pgBadger as follow to exclude these period from the report: +use pgBadger as follow to exclude these periods from the report: pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log @@ -308,7 +318,7 @@ pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5' you don't need to specify any log file at command line, but if you have other -PostgreSQL log file to parse, you can add them as usual. +PostgreSQL log files to parse, you can add them as usual. To rebuild all incremental html reports after, proceed as follow: @@ -319,7 +329,7 @@ it will also update all resource files (JS and CSS). Use -E or --explode if the reports were built using this option. -pgBadger also support Heroku PostgreSQL logs using logplex format: +pgBadger also supports Heroku PostgreSQL logs using logplex format: heroku logs -p postgres | pgbadger -f logplex -o heroku.html - @@ -330,14 +340,14 @@ pgbadger -f rds -o rds_out.html rds.log -CloudSQL Postgresql logs it's fairly normal PostgreSQL log but encapsulated in -JSON format. It is auto detected too by pgBagder but in case you need to force -the log format, use \`jsonlog\` +Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated +in JSON format. It is autodetected by pgBadger but in case you need to force +the log format use \`jsonlog\`: pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log -This is the same than with the jsonlog extension, the json format is different -but pgBadger can parse both format. +This is the same as with the jsonlog extension, the json format is different +but pgBadger can parse both formats. To create a cumulative report over a month use command: @@ -361,19 +371,19 @@ 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 format are gzip, +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 -upper than 5.05 that supports the --robot option. In order pgbadger determine -uncompressed file size with lz4, file must be compressed with --content-size -option. For the complete list of features see below. +higher than 5.05 that supports the --robot option. lz4 files must be +compressed with the --content-size option for pgbadger to determine the +uncompressed file size. For the complete list of features, see below. All charts are zoomable and can be saved as PNG images. You can also limit pgBadger to only report errors or remove any part of the -report using command line options. +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 supports any custom format set in the log\_line\_prefix directive of +your postgresql.conf file as long as it at least specifies the %t 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. @@ -449,57 +459,59 @@ 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 +Histogram granularity can be adjusted using the -A command-line option. By default, they will report the mean of each top queries/errors occurring per hour, but you can specify the granularity down to the minute. pgBadger can also be used in a central place to parse remote log files using a passwordless SSH connection. This mode can be used with compressed files and in -the multiprocess per file mode (-J) but can not be used with the CSV log format. +the multiprocess per file mode (-J), but cannot be used with the CSV log format. + +Examples of reports can be found here: https://pgbadger.darold.net/#reports ### REQUIREMENT pgBadger comes as a single Perl script - you do not need anything other than a modern -Perl distribution. Charts are rendered using a JavaScript library so you don't need +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: +If you plan to parse PostgreSQL CSV log files, you might need some Perl Modules: Text::CSV_XS - to parse PostgreSQL CSV log files. -This module is optional, if you don't have PostgreSQL log in the CSV format you don't +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: +If you want to export statistics as JSON file, you need an additional Perl module: JSON::XS - JSON serialising/deserialising, done correctly and fast -This module is optional, if you don't select the json output format you don't -need to install it. You can install it on a Debian like system using: +This module is optional, if you don't select the json output format, you don't +need to install it. You can install it on a Debian-like system using: sudo apt-get install libjson-xs-perl -and in RPM like system using: +and on RPM-like system using: sudo yum install perl-JSON-XS -Compressed log file format is autodetected from the file extension. If pgBadger find -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 and if the file extension -is zip or xz then the unzip or xz utilities will be used. +Compressed log file format is autodetected from the file extension. If pgBadger finds +a gz extension, it will use the zcat utility; with a bz2 extension, it will use bzcat; +with lz4, it will use lz4cat; with zst, it will use zstdcat; if the file extension +is zip or xz, then the unzip or xz utility will be used. -If those utilities are not found in the PATH environment variable then use the --zcat -command line option to change this path. For example: +If those utilities are not found in the PATH environment variable, then use the --zcat +command-line option to change this path. For example: --zcat="/usr/local/bin/gunzip -c" or --zcat="/usr/local/bin/bzip2 -dc" --zcat="C:\tools\unzip -p" -By default pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities -following the file extension. If you use the default autodetection compress format -you can mixed gz, bz2, lz4, xz, zip or zstd files. Specifying a custom value to -\--zcat option will remove this feature of mixed compressed format. +By default, pgBadger will use the zcat, bzcat, lz4cat, zstdcat and unzip utilities +following the file extension. If you use the default autodetection of compression format, +you can mix gz, bz2, lz4, xz, zip or zstd files. Specifying a custom value of +\--zcat option will remove the possibility of mixed compression format. -Note that multiprocessing can not be used with compressed files or CSV files as +Note that multiprocessing cannot be used with compressed files or CSV files as well as under Windows platform. ### INSTALLATION @@ -523,7 +535,7 @@ perl Makefile.PL INSTALLDIRS=vendor -By default INSTALLDIRS is set to site. +By default, INSTALLDIRS is set to site. ### POSTGRESQL CONFIGURATION @@ -536,12 +548,12 @@ 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 +log\_statement set to 'all', nothing will be logged through the log\_min\_duration\_statement directive. See the next chapter for more information. -pgBadger supports any custom format set into the log\_line\_prefix directive of -your postgresql.conf file as long as it at least specify a time escape sequence -(%t, %m or %n) and the process related escape sequence (%p or %c). +pgBadger supports any custom format set in the log\_line\_prefix directive of +your postgresql.conf file as long as it at least specifies a time escape sequence +(%t, %m or %n) and a process-related escape sequence (%p or %c). For example, with 'stderr' log format, log\_line\_prefix must be at least: @@ -581,7 +593,7 @@ lc_messages='en_US.UTF-8' lc_messages='C' -pgBadger parser do not support other locale like 'fr\_FR.UTF-8' for example. +pgBadger parser does not support other locales, like 'fr\_FR.UTF-8' for example. ### LOG STATEMENTS @@ -594,12 +606,12 @@ 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. +common query report, you can either choose to set log\_min\_duration\_statement +to a higher value or to enable log\_statement. Enabling log\_min\_duration\_statement will add reports about slowest queries and queries that took up the most time. Take care that if you have log\_statement -set to 'all' nothing will be logged with log\_min\_duration\_statement. +set to 'all', nothing will be logged with log\_min\_duration\_statement. Warning: Do not enable both log\_min\_duration\_statement, log\_duration and log\_statement all together, this will result in wrong counter values. Note @@ -625,15 +637,15 @@ 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 +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 +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. Here is a benchmark done on a server with 8 CPUs and a single file of 9.5GB. @@ -654,7 +666,7 @@ 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 +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 @@ -677,13 +689,13 @@ 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 +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 +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. @@ -692,7 +704,7 @@ Incremental reports can be rebuilt after a pgbadger report fix or a new feature to update all HTML reports. To rebuild all reports where a binary -file is still present proceed as follow: +file is still present, proceed as follow: rm /path/to/reports/*.js rm /path/to/reports/*.css @@ -703,18 +715,18 @@ #### 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: +By default, pgBadger in incremental mode only computes daily and weekly reports. +If you want monthly cumulative reports, you will have to use a separate command +to specify the report to build. For example, to build a report for August 2019: pgbadger -X --month-report 2919-08 /var/www/pg_reports/ 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 lot of time following the amount of data. +every day, it is entirely rebuilt each time. The monthly report is not built by +default because it could take a lot of time following the amount of data. -If reports were built with the per database option ( -E | --explode ) it must +If reports were built with the per-database option ( -E | --explode ), it must be used too when calling pgbadger to build monthly report: pgbadger -E -X --month-report 2919-08 /var/www/pg_reports/ @@ -726,7 +738,7 @@ 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: +running the following commands each hour: pgbadger --last-parsed .pgbadger_last_state_file -o sunday/hourX.bin /var/log/pgsql/postgresql-Sun.log @@ -744,7 +756,7 @@ 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 +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 @@ -754,7 +766,7 @@ ### JSON FORMAT JSON format is good for sharing data with other languages, which makes it -easy to integrate pgBadger result into other monitoring tools like Cacti +easy to integrate pgBadger result into other monitoring tools, like Cacti or Graphite. ### AUTHORS @@ -767,7 +779,7 @@ This web site is a work of Gilles Darold. -pgBadger is maintained by Gilles Darold and every one who wants to contribute. +pgBadger is maintained by Gilles Darold and everyone who wants to contribute. Many people have contributed to pgBadger, they are all quoted in the Changelog file.