Year $y
| ", &get_calendar($db, $y, $m), " | \n"; print $fh "
-
};
}
sub html_footer
{
print $fh qq{
NO DATASET
';
my $pgbadger_ico =
'data:image/x-icon;base64,
AAABAAEAIyMQAAEABAA8BAAAFgAAACgAAAAjAAAARgAAAAEABAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAgAAGRsZACgqKQA2OTcASEpJAFpdWwBoa2kAeHt5AImMigCeoZ8AsLOxAMTHxQDR1NIA
5enmAPv+/AAAAAAA///////////////////////wAAD///////////H///////////AAAP//////
//9Fq7Yv////////8AAA////////8V7u7qD////////wAAD///////8B7qWN5AL///////AAAP//
///y8Avrc3rtMCH/////8AAA/////xABvbAAAJ6kAA/////wAAD////wAG5tQAAADp6RAP////AA
AP//MQBd7C2lRESOWe5xAD//8AAA//8APO7iC+7e7u4A3uxwBf/wAAD/9Aju7iAAvu7u0QAN7ukA
7/AAAP/wCe7kAAAF7ugAAAHO6xD/8AAA//AK7CAAAAHO1AAAABnrEP/wAAD/8ArAAAAAAc7kAAAA
AIwQ//AAAP/wCjAAAAAC3uQAAAAAHBCf8AAA//AIEBVnIATu5gAXZhAFEP/wAAD/8AIAqxdwBu7p
AFoX0QIQ//AAAP/wAAPsBCAL7u4QBwfmAAD/8AAA//AAA8owAC7u7lAAKbYAAJ/wAAD/8AAAAAAA
fu7uwAAAAAAA//AAAP/wAAAAAADu7u7jAAAAAAD/8AAA//AAAAAABe7u7uoAAAAAAP/wAAD/8AAA
AAAL7u7u7QAAAAAAn/AAAP/wAAAAAB3u7u7uYAAAAAD/8AAA//MAAAAATu7u7u6QAAAAAP/wAAD/
/wAAAAAM7u7u7TAAAAAD//AAAP//IQAAAAKu7u7UAAAAAB//8AAA////IAAAAAju7BAAAAAP///w
AAD////2AAA1je7ulUAAA/////AAAP/////xEAnO7u7pIAH/////8AAA//////9CABju6iACP///
///wAAD////////wAAggAP////////AAAP////////8wAAA/////////8AAA///////////w////
///////wAAD///////////////////////AAAP/////gAAAA//+//+AAAAD//Af/4AAAAP/4A//g
AAAA//AA/+AAAAD/oAA/4AAAAP8AAB/gAAAA/gAAD+AAAADwAAAB4AAAAPAAAADgAAAA4AAAAGAA
AADgAAAA4AAAAOAAAADgAAAA4AAAAOAAAADgAAAAYAAAAOAAAADgAAAA4AAAAOAAAADgAAAA4AAA
AOAAAABgAAAA4AAAAOAAAADgAAAA4AAAAOAAAADgAAAA4AAAAGAAAADgAAAA4AAAAOAAAADgAAAA
8AAAAOAAAADwAAAB4AAAAPwAAAfgAAAA/gAAD+AAAAD/gAA/4AAAAP/AAH/gAAAA//gD/+AAAAD/
/Af/4AAAAP//v//gAAAA/////+AAAAA
';
my %CLASS_ERROR_CODE = (
'00' => 'Successful Completion',
'01' => 'Warning',
'02' => 'No Data (this is also a warning class per the SQL standard)',
'03' => 'SQL Statement Not Yet Complete',
'08' => 'Connection Exception',
'09' => 'Triggered Action Exception',
'0A' => 'Feature Not Supported',
'0B' => 'Invalid Transaction Initiation',
'0F' => 'Locator Exception',
'0L' => 'Invalid Grantor',
'0P' => 'Invalid Role Specification',
'0Z' => 'Diagnostics Exception',
'20' => 'Case Not Found',
'21' => 'Cardinality Violation',
'22' => 'Data Exception',
'23' => 'Integrity Constraint Violation',
'24' => 'Invalid Cursor State',
'25' => 'Invalid Transaction State',
'26' => 'Invalid SQL Statement Name',
'27' => 'Triggered Data Change Violation',
'28' => 'Invalid Authorization Specification',
'2B' => 'Dependent Privilege Descriptors Still Exist',
'2D' => 'Invalid Transaction Termination',
'2F' => 'SQL Routine Exception',
'34' => 'Invalid Cursor Name',
'38' => 'External Routine Exception',
'39' => 'External Routine Invocation Exception',
'3B' => 'Savepoint Exception',
'3D' => 'Invalid Catalog Name',
'3F' => 'Invalid Schema Name',
'40' => 'Transaction Rollback',
'42' => 'Syntax Error or Access Rule Violation',
'44' => 'WITH CHECK OPTION Violation',
'53' => 'Insufficient Resources',
'54' => 'Program Limit Exceeded',
'55' => 'Object Not In Prerequisite State',
'57' => 'Operator Intervention',
'58' => 'System Error (errors external to PostgreSQL itself)',
'72' => 'Snapshot Failure',
'F0' => 'Configuration File Error',
'HV' => 'Foreign Data Wrapper Error (SQL/MED)',
'P0' => 'PL/pgSQL Error',
'XX' => 'Internal Error',
);
####
# method used to fork as many child as wanted
##
sub spawn
{
my $coderef = shift;
unless (@_ == 0 && $coderef && ref($coderef) eq 'CODE') {
print "usage: spawn CODEREF";
exit 0;
}
my $pid;
if (!defined($pid = fork)) {
print STDERR "ERROR: cannot fork: $!\n";
return;
} elsif ($pid) {
$RUNNING_PIDS{$pid} = $pid;
return; # the parent
}
# the child -- go spawn
$< = $>;
$( = $); # suid progs only
exit &$coderef();
}
# Command line options
my $journalctl_cmd = '';
my $zcat_cmd = 'gunzip -c';
my $zcat = $zcat_cmd;
my $bzcat = 'bunzip2 -c';
my $lz4cat = 'lz4cat';
my $ucat = 'unzip -p';
my $xzcat = 'xzcat';
my $zstdcat = 'zstdcat';
my $gzip_uncompress_size = "gunzip -l \"%f\" | grep -E '^\\s*[0-9]+' | awk '{print \$2}'";
# lz4 archive can only contain one file.
# Original size can be retrieved only if --content-size has been used for compression
# it seems lz4 send output to stderr so redirect to stdout
my $lz4_uncompress_size = " lz4 -v -c --list %f 2>&1 |tail -n 2|head -n1 | awk '{print \$6}'";
my $zip_uncompress_size = "unzip -l %f | awk '{if (NR==4) print \$1}'";
my $xz_uncompress_size = "xz --robot -l %f | grep totals | awk '{print \$5}'";
my $zstd_uncompress_size = "zstd -v -l %f |grep Decompressed | awk -F\"[ (]*\" '{print \$5}'";
my $format = '';
my @outfiles = ();
my $outdir = '';
my $incremental = '';
my $extra_files = 0;
my $help = '';
my $ver = '';
my @dbname = ();
my @dbuser = ();
my @dbclient = ();
my @dbappname = ();
my @exclude_user = ();
my @exclude_appname = ();
my @exclude_db = ();
my @exclude_client = ();
my @exclude_line = ();
my $ident = '';
my $top = 0;
my $sample = 3;
my $extension = '';
my $maxlength = 100000;
my $graph = 1;
my $nograph = 0;
my $debug = 0;
my $noprettify = 0;
my $from = '';
my $to = '';
my $from_hour = '';
my $to_hour = '';
my $quiet = 0;
my $progress = 1;
my $error_only = 0;
my @exclude_query = ();
my @exclude_queryid = ();
my @exclude_time = ();
my @include_time = ();
my $exclude_file = '';
my @include_query = ();
my $include_file = '';
my $disable_error = 0;
my $disable_hourly = 0;
my $disable_type = 0;
my $disable_query = 0;
my $disable_session = 0;
my $disable_connection = 0;
my $disable_lock = 0;
my $disable_temporary = 0;
my $disable_checkpoint = 0;
my $disable_autovacuum = 0;
my $avg_minutes = 5;
my $histo_avg_minutes = 60;
my $last_parsed = '';
my $report_title = '';
my $log_line_prefix = '';
my $compiled_prefix = '';
my $project_url = 'http://pgbadger.darold.net/';
my $t_min = 0;
my $t_max = 0;
my $remove_comment = 0;
my $select_only = 0;
my $queue_size = 0;
my $job_per_file = 0;
my $charset = 'utf-8';
my $csv_sep_char = ',';
my %current_sessions = ();
my %pgb_current_sessions = ();
my $incr_date = '';
my $last_incr_date = '';
my $anonymize = 0;
my $noclean = 0;
my $retention = 0;
my $dns_resolv = 0;
my $nomultiline = 0;
my $noreport = 0;
my $log_duration = 0;
my $logfile_list = '';
my $enable_checksum = 0;
my $timezone = 0;
my $opt_timezone = 0;
my $pgbouncer_only = 0;
my $rebuild = 0;
my $week_start_monday = 0;
my $iso_week_number = 0;
my $use_sessionid_as_pid = 0;
my $dump_normalized_only = 0;
my $log_timezone = 0;
my $opt_log_timezone = 0;
my $json_prettify = 0;
my $report_per_database = 0;
my $html_outdir = '';
my $param_size_limit = 24;
my $month_report = 0;
my $day_report = 0;
my $noexplain = 0;
my $log_command = '';
my $wide_char = 0;
my $noweekreport = 0;
my $query_numbering = 0;
my $keep_comments = 0;
my $no_progessbar = 0;
my $NUMPROGRESS = 10;
my @DIMENSIONS = (800, 300);
my $RESRC_URL = '';
my $img_format = 'png';
my @log_files = ();
my %prefix_vars = ();
my $q_prefix = '';
my @prefix_q_params = ();
my %last_execute_stmt = ();
my $disable_process_title = 0;
my $dump_all_queries = 0;
my $dump_raw_csv = 0;
my $header_done = 0;
my @include_pid = ();
my @include_session = ();
my $compress_extensions = qr/\.(zip|gz|xz|bz2|lz4|zst)$/i;
my $remote_host = '';
my $ssh_command = '';
my $ssh_bin = 'ssh';
my $ssh_port = 22;
my $ssh_identity = '';
my $ssh_user = '';
my $ssh_timeout = 10;
my $ssh_options = "-o ConnectTimeout=$ssh_timeout -o PreferredAuthentications=hostbased,publickey";
my $ssh_sudo = 0;
my $force_sample = 0;
my $nofork = 0;
my $curl_command = 'curl -k -s ';
my $sql_prettified = pgFormatter::Beautify->new('colorize' => 1, 'format' => 'html', 'uc_keywords' => 0);
# Flag for logs using UTC, in this case we don't autodetect the timezone
my $isUTC = 0;
# Do not display data in pie where percentage is lower than this value
# to avoid label overlapping.
my $pie_percentage_limit = 2;
# Get the decimal separator
my $n = 5 / 2;
my $num_sep = ',';
$num_sep = ' ' if ($n =~ /,/);
# Set iso datetime pattern
my $time_pattern = qr/(\d{4})-(\d{2})-(\d{2})[\sT](\d{2}):(\d{2}):(\d{2})/;
# Inform the parent that it should stop iterate on parsing other files
sub stop_parsing
{
&logmsg('DEBUG', "Received interrupt signal");
$interrupt = 1;
}
# With multiprocess we need to wait for all children
sub wait_child
{
my $sig = shift;
$interrupt = 2;
print STDERR "Received terminating signal ($sig).\n";
1 while wait != -1;
$SIG{INT} = \&wait_child;
$SIG{TERM} = \&wait_child;
foreach my $f (@tempfiles)
{
unlink("$f->[1]") if (-e "$f->[1]");
}
if ($report_per_database)
{
unlink("$tmp_dblist");
}
if ($last_parsed && -e "$tmp_last_parsed")
{
unlink("$tmp_last_parsed");
}
if ($last_parsed && -e "$last_parsed.tmp")
{
unlink("$last_parsed.tmp");
}
if (-e "$PID_FILE")
{
unlink("$PID_FILE");
}
_exit(2);
}
$SIG{INT} = \&wait_child;
$SIG{TERM} = \&wait_child;
if ($^O !~ /MSWin32|dos/i) {
$SIG{USR2} = \&stop_parsing;
} else {
$nofork = 1;
}
$| = 1;
my $histogram_query = '';
my $histogram_session = '';
# get the command line parameters
my $result = GetOptions(
"a|average=i" => \$avg_minutes,
"A|histo-average=i" => \$histo_avg_minutes,
"b|begin=s" => \$from,
"c|dbclient=s" => \@dbclient,
"C|nocomment!" => \$remove_comment,
"d|dbname=s" => \@dbname,
"D|dns-resolv!" => \$dns_resolv,
"e|end=s" => \$to,
"E|explode!" => \$report_per_database,
"f|format=s" => \$format,
"G|nograph!" => \$nograph,
"h|help!" => \$help,
"H|html-outdir=s" => \$html_outdir,
"i|ident=s" => \$ident,
"I|incremental!" => \$incremental,
"j|jobs=i" => \$queue_size,
"J|Jobs=i" => \$job_per_file,
"l|last-parsed=s" => \$last_parsed,
"L|logfile-list=s" => \$logfile_list,
"m|maxlength=i" => \$maxlength,
"M|no-multiline!" => \$nomultiline,
"N|appname=s" => \@dbappname,
"o|outfile=s" => \@outfiles,
"O|outdir=s" => \$outdir,
"p|prefix=s" => \$log_line_prefix,
"P|no-prettify!" => \$noprettify,
"q|quiet!" => \$quiet,
"Q|query-numbering!" => \$query_numbering,
"r|remote-host=s" => \$remote_host,
'R|retention=i' => \$retention,
"s|sample=i" => \$sample,
"S|select-only!" => \$select_only,
"t|top=i" => \$top,
"T|title=s" => \$report_title,
"u|dbuser=s" => \@dbuser,
"U|exclude-user=s" => \@exclude_user,
"v|verbose!" => \$debug,
"V|version!" => \$ver,
"w|watch-mode!" => \$error_only,
"W|wide-char!" => \$wide_char,
"x|extension=s" => \$extension,
"X|extra-files!" => \$extra_files,
"z|zcat=s" => \$zcat,
"Z|timezone=f" => \$opt_timezone,
"pie-limit=i" => \$pie_percentage_limit,
"image-format=s" => \$img_format,
"exclude-query=s" => \@exclude_query,
"exclude-queryid=s" => \@exclude_queryid,
"exclude-file=s" => \$exclude_file,
"exclude-db=s" => \@exclude_db,
"exclude-client=s" => \@exclude_client,
"exclude-appname=s" => \@exclude_appname,
"include-query=s" => \@include_query,
"exclude-line=s" => \@exclude_line,
"include-file=s" => \$include_file,
"disable-error!" => \$disable_error,
"disable-hourly!" => \$disable_hourly,
"disable-type!" => \$disable_type,
"disable-query!" => \$disable_query,
"disable-session!" => \$disable_session,
"disable-connection!" => \$disable_connection,
"disable-lock!" => \$disable_lock,
"disable-temporary!" => \$disable_temporary,
"disable-checkpoint!" => \$disable_checkpoint,
"disable-autovacuum!" => \$disable_autovacuum,
"charset=s" => \$charset,
"csv-separator=s" => \$csv_sep_char,
"include-time=s" => \@include_time,
"exclude-time=s" => \@exclude_time,
'ssh-command=s' => \$ssh_command,
'ssh-program=s' => \$ssh_bin,
'ssh-port=i' => \$ssh_port,
'ssh-identity=s' => \$ssh_identity,
'ssh-option=s' => \$ssh_options,
'ssh-sudo!' => \$ssh_sudo,
'ssh-user=s' => \$ssh_user,
'ssh-timeout=i' => \$ssh_timeout,
'anonymize!' => \$anonymize,
'noclean!' => \$noclean,
'noreport!' => \$noreport,
'log-duration!' => \$log_duration,
'enable-checksum!' => \$enable_checksum,
'journalctl=s' => \$journalctl_cmd,
'pid-dir=s' => \$PID_DIR,
'pid-file=s' => \$PID_FILE,
'rebuild!' => \$rebuild,
'pgbouncer-only!' => \$pgbouncer_only,
'start-monday!' => \$week_start_monday,
'iso-week-number!' => \$iso_week_number,
'normalized-only!' => \$dump_normalized_only,
'log-timezone=f' => \$opt_log_timezone,
'prettify-json!' => \$json_prettify,
'month-report=s' => \$month_report,
'day-report=s' => \$day_report,
'noexplain!' => \$noexplain,
'command=s' => \$log_command,
'no-week!' => \$noweekreport,
'explain-url=s' => \$EXPLAIN_URL,
'tempdir=s' => \$TMP_DIR,
'no-process-info!' => \$disable_process_title,
'dump-all-queries!' => \$dump_all_queries,
'keep-comments!' => \$keep_comments,
'no-progressbar!' => \$no_progessbar,
'dump-raw-csv!' => \$dump_raw_csv,
'include-pid=i' => \@include_pid,
'include-session=s' => \@include_session,
'histogram-query=s' => \$histogram_query,
'histogram-session=s' => \$histogram_session,
'no-fork' => \$nofork,
);
die "FATAL: use pgbadger --help\n" if (not $result);
# Force rebuild mode when a month report is asked
$rebuild = 1 if ($month_report);
$rebuild = 2 if ($day_report);
# Set report title
$report_title = &escape_html($report_title) if $report_title;
# Show version and exit if asked
if ($ver) {
print "pgBadger version $VERSION\n";
exit 0;
}
&usage() if ($help);
# Create temporary file directory if not exists
mkdir("$TMP_DIR") if (!-d "$TMP_DIR");
if (!-d "$TMP_DIR")
{
die("Can not use temporary directory $TMP_DIR.\n");
}
# Try to load Digest::MD5 when asked
if ($enable_checksum)
{
if (eval {require Digest::MD5;1} ne 1) {
die("Can not load Perl module Digest::MD5.\n");
} else {
Digest::MD5->import('md5_hex');
}
}
# Check if another process is already running
unless ($PID_FILE) {
$PID_FILE = $PID_DIR . '/pgbadger.pid';
}
if (-e "$PID_FILE")
{
my $is_running = 2;
if ($^O !~ /MSWin32|dos/i) {
eval { $is_running = `ps auwx | grep pgbadger | grep -v grep | wc -l`; chomp($is_running); };
}
if (!$@ && ($is_running <= 1)) {
unlink("$PID_FILE");
} else {
print "FATAL: another process is already started or remove the file, see $PID_FILE\n";
exit 3;
}
}
# Create pid file
if (open(my $out, '>', $PID_FILE))
{
print $out $$;
close($out);
}
else
{
print "FATAL: can't create pid file $PID_FILE, $!\n";
exit 3;
}
# Rewrite some command line arguments as lists
&compute_arg_list();
# If pgBadger must parse remote files set the ssh command
# If no user defined ssh command have been set
my $remote_command = '';
if ($remote_host && !$ssh_command) {
$remote_command = &set_ssh_command($ssh_command, $remote_host);
}
# Add journalctl command to the file list if not already found
if ($journalctl_cmd)
{
if (!grep(/^\Q$journalctl_cmd\E$/, @ARGV))
{
$journalctl_cmd .= " --output='short-iso'";
push(@ARGV, $journalctl_cmd);
}
}
# Add custom command to file list
if ($log_command)
{
if (!grep(/^\Q$log_command\E$/, @ARGV)) {
push(@ARGV, $log_command);
}
}
# Log files to be parsed are passed as command line arguments
my $empty_files = 1;
if ($#ARGV >= 0)
{
if (!$month_report) {
foreach my $file (@ARGV) {
push(@log_files, &set_file_list($file));
}
} elsif (!$outdir) {
$outdir = $ARGV[0];
}
}
if (!$incremental && $html_outdir) {
localdie("FATAL: parameter -H, --html-outdir can only be used with incremental mode.\n");
}
# Read list of log file to parse from a file
if ($logfile_list)
{
if (!-e $logfile_list) {
localdie("FATAL: logfile list $logfile_list must exist!\n");
}
my $in = undef;
if (not open($in, "<", $logfile_list)) {
localdie("FATAL: can not read logfile list $logfile_list, $!.\n");
}
my @files = <$in>;
close($in);
foreach my $file (@files)
{
chomp($file);
$file =~ s/\r//;
if ($file eq '-') {
localdie("FATAL: stdin input - can not be used with logfile list.\n");
}
push(@log_files, &set_file_list($file));
}
}
# Do not warn if all log files are empty
if (!$rebuild && $empty_files)
{
&logmsg('DEBUG', "All log files are empty, exiting...");
unlink("$PID_FILE");
exit 0;
}
# Logfile is a mandatory parameter when journalctl command is not set.
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", 4);
}
else
{
unlink("$PID_FILE");
exit 4;
}
}
# Check that binary format is not mixed with other formats in multiple output mode
if ($#outfiles >= 1) {
my @has_binary = grep { /\.bin$/i } @outfiles;
if (@has_binary && @outfiles > 1) {
localdie("FATAL: binary format (.bin) cannot be used with multiple output formats.\n\n");
}
if ($dump_normalized_only || $dump_all_queries) {
localdie("FATAL: dump of normalized queries cannot be used with multiple output.\n\n");
}
}
# Remove follow option from journalctl command to prevent infinit loop
if ($journalctl_cmd) {
$journalctl_cmd =~ s/(-f|--follow)\b//;
}
# Quiet mode is forced with progress bar
$progress = 0 if ($quiet || $no_progessbar);
# Set the default number minutes for queries and connections average
$avg_minutes ||= 5;
$avg_minutes = 60 if ($avg_minutes > 60);
$avg_minutes = 1 if ($avg_minutes < 1);
$histo_avg_minutes ||= 60;
$histo_avg_minutes = 60 if ($histo_avg_minutes > 60);
$histo_avg_minutes = 1 if ($histo_avg_minutes < 1);
my @avgs = ();
for (my $i = 0 ; $i < 60 ; $i += $avg_minutes) {
push(@avgs, sprintf("%02d", $i));
}
my @histo_avgs = ();
for (my $i = 0 ; $i < 60 ; $i += $histo_avg_minutes) {
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)/;
my $main_error_regex = qr/^(WARNING|ERROR|FATAL|PANIC)/;
my $main_log_regex = qr/^(LOG|WARNING|ERROR|FATAL|PANIC)/;
# Set syslog prefix regex
my $other_syslog_line = '';
my $pgbouncer_log_format = '';
my $pgbouncer_log_parse1 = '';
my $pgbouncer_log_parse2 = '';
my $pgbouncer_log_parse3 = '';
# Variable to store parsed data following the line prefix
my @prefix_params = ();
my @pgb_prefix_params = ();
my @pgb_prefix_parse1 = ();
my @pgb_prefix_parse2 = ();
my @pgb_prefix_parse3 = ();
# Force incremental mode when rebuild mode is used
if ($rebuild && !$incremental) {
print STDERR "WARNING: --rebuild require incremental mode, activating it.\n" if (!$month_report || !$day_report);
$incremental = 1;
}
&logmsg('DEBUG', "pgBadger version $VERSION." );
# set timezone to use
&set_timezone(1);
# Set default top query
$top ||= 20;
# Set output file
my $outfile = '';
$outfile = $outfiles[0] if ($#outfiles >= 0);
if (($dump_normalized_only || $dump_all_queries) && $outfile && $outfile !~ /\.txt$/){
localdie("FATAL: dump of normalized queries can be done in text output format, please use .txt extension.\n\n");
}
# With multiple output format we must use a temporary binary file
my $dft_extens = '';
if ($#outfiles >= 1)
{
# In non-incremental mode, use a temporary binary file.
# In incremental mode, each format will be generated from the binary data.
if (!$incremental)
{
# Set temporary binary file for non-incremental mode.
$outfile = $TMP_DIR . "/pgbadger_tmp_$$.bin";
# Remove the default output format for the moment
# otherwise all dump will have the same output
$dft_extens = $extension;
$extension = '';
}
}
elsif ($#outfiles == -1)
{
$extension = 'txt' if ($dump_normalized_only || $dump_all_queries);
if ($extension) {
push(@outfiles, 'out.' . $extension);
} elsif ($incremental) {
push(@outfiles, 'index.html');
} else {
push(@outfiles, 'out.html');
}
map { s/\.text/.txt/; } @outfiles;
}
# Set the default extension and output format, load JSON Perl module if required
# Force text output with normalized query list only and disable incremental report
# Set default filename of the output file
my ($current_out_file, $extens) = &set_output_extension($outdir, $outfile, $extension);
# Set default syslog ident name
$ident ||= 'postgres';
# Set default pie percentage limit or fix value
$pie_percentage_limit = 0 if ($pie_percentage_limit < 0);
$pie_percentage_limit = 2 if ($pie_percentage_limit eq '');
$pie_percentage_limit = 100 if ($pie_percentage_limit > 100);
# Set default download image format
$img_format = lc($img_format);
$img_format = 'jpeg' if ($img_format eq 'jpg');
$img_format = 'png' if ($img_format ne 'jpeg');
# Extract the output directory from outfile so that graphs will
# be created in the same directory
if ($current_out_file ne '-')
{
if (!$html_outdir && !$outdir)
{
my @infs = fileparse($current_out_file);
if ($infs[0] ne '')
{
$outdir = $infs[1];
}
else
{
# maybe a confusion between -O and -o
localdie("FATAL: output file $current_out_file is a directory, should be a file\nor maybe you want to use -O | --outdir option instead.\n");
}
}
elsif ($outdir && !-d "$outdir")
{
# An output directory has been passed as command line parameter
localdie("FATAL: $outdir is not a directory or doesn't exist.\n");
}
elsif ($html_outdir && !-d "$html_outdir")
{
# An HTML output directory has been passed as command line parameter
localdie("FATAL: $html_outdir is not a directory or doesn't exist.\n");
}
$current_out_file = basename($current_out_file);
$current_out_file = ($html_outdir || $outdir) . '/' . $current_out_file;
$current_out_file =~ s#//#/#g;
}
# Remove graph support if output is not html
$graph = 0 unless ($extens eq 'html' or $extens eq 'binary' or $extens eq 'json');
$graph = 0 if ($nograph);
# Set some default values
my $end_top = $top - 1;
$queue_size ||= 1;
$job_per_file ||= 1;
if ($^O =~ /MSWin32|dos/i)
{
if ( ($queue_size > 1) || ($job_per_file > 1) ) {
print STDERR "WARNING: parallel processing is not supported on this platform.\n";
}
$queue_size = 1;
$job_per_file = 1;
}
# 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) {
localdie("FATAL: please choose between no event report and reporting events only.\n");
}
# Set default search pattern for database, user name, application name and host in log_line_prefix
my $regex_prefix_dbname = qr/(?:db|database)=([^,]*)/;
my $regex_prefix_dbuser = qr/(?:user|usr)=([^,]*)/;
my $regex_prefix_dbclient = qr/(?:client|remote|ip|host|connection_source)=([^,\(]*)/;
my $regex_prefix_dbappname = qr/(?:app|application|application_name)=([^,]*)/;
my $regex_prefix_queryid = qr/(?:queryid)=([^,]*)/;
my $regex_prefix_sqlstate = qr/(?:error_code|state|state_code)=([^,]*)/;
my $regex_prefix_backendtype = qr/(?:backend_type|btype)=([^,]*)/;
# Set pattern to look for query type
my $action_regex = qr/^[\s\(]*(DELETE|INSERT|UPDATE|SELECT|COPY|WITH|CREATE|DROP|ALTER|TRUNCATE|BEGIN|COMMIT|ROLLBACK|START|END|SAVEPOINT|DECLARE|CLOSE|FETCH|MOVE)/is;
# Loading excluded query from file if any
if ($exclude_file) {
open(my $in, '<', $exclude_file) or localdie("FATAL: can't read file $exclude_file: $!\n");
my @exclq = <$in>;
close($in);
chomp(@exclq);
foreach my $r (@exclq) {
$r =~ s/\r//;
&check_regex($r, '--exclude-file');
}
push(@exclude_query, @exclq);
}
# Testing regex syntax
if ($#exclude_query >= 0) {
foreach my $r (@exclude_query) {
&check_regex($r, '--exclude-query');
}
}
# Testing regex syntax
if ($#exclude_time >= 0) {
foreach my $r (@exclude_time) {
&check_regex($r, '--exclude-time');
}
}
#
# Testing regex syntax
if ($#include_time >= 0) {
foreach my $r (@include_time) {
&check_regex($r, '--include-time');
}
}
# Loading included query from file if any
if ($include_file) {
open(my $in, '<', $include_file) or localdie("FATAL: can't read file $include_file: $!\n");
my @exclq = <$in>;
close($in);
chomp(@exclq);
foreach my $r (@exclq) {
$r =~ s/\r//;
&check_regex($r, '--include-file');
}
push(@include_query, @exclq);
}
# Testing regex syntax
if ($#include_query >= 0) {
foreach my $r (@include_query) {
&check_regex($r, '--include-query');
}
}
# Check start/end date time
if ($from)
{
if ( $from =~ /^(\d{2}):(\d{2}):(\d{2})([.]\d+([+-]\d+)?)?$/)
{
# only time, trick around the date part
my $fractional_seconds = $4 || "0";
$from_hour = "$1:$2:$3.$fractional_seconds";
&logmsg('DEBUG', "Setting begin time to [$from_hour]" );
}
elsif( $from =~ /^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})([.]\d+([+-]\d+)?)?$/ )
{
my $fractional_seconds = $7 || "0";
$from = "$1-$2-$3 $4:$5:$6.$fractional_seconds";
&logmsg('DEBUG', "Setting begin datetime to [$from]" );
}
else
{
localdie("FATAL: bad format for begin datetime/time, should be yyyy-mm-dd hh:mm:ss.l+tz or hh:mm:ss.l+tz\n");
}
}
if ($to)
{
if ( $to =~ /^(\d{2}):(\d{2}):(\d{2})([.]\d+([+-]\d+)?)?$/)
{
# only time, trick around the date part
my $fractional_seconds = $4 || "0";
$to_hour = "$1:$2:$3.$fractional_seconds";
&logmsg('DEBUG', "Setting end time to [$to_hour]" );
}
elsif( $to =~ /^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2}):(\d{2})([.]\d+([+-]\d+)?)?$/)
{
my $fractional_seconds = $7 || "0";
$to = "$1-$2-$3 $4:$5:$6.$fractional_seconds";
&logmsg('DEBUG', "Setting end time to [$to]" );
}
else
{
localdie("FATAL: bad format for ending datetime, should be yyyy-mm-dd hh:mm:ss.l+tz or hh:mm:ss.l+tz\n");
}
}
if ($from && $to && ($from gt $to)) {
localdie("FATAL: begin date is after end date!\n") ;
}
# Stores the last parsed line from log file to allow incremental parsing
my $LAST_LINE = '';
# Set the level of the data aggregator, can be minute, hour or day follow the
# size of the log file.
my $LEVEL = 'hour';
# Month names
my %month_abbr = (
'Jan' => '01', 'Feb' => '02', 'Mar' => '03', 'Apr' => '04', 'May' => '05', 'Jun' => '06',
'Jul' => '07', 'Aug' => '08', 'Sep' => '09', 'Oct' => '10', 'Nov' => '11', 'Dec' => '12'
);
my %abbr_month = (
'01' => 'Jan', '02' => 'Feb', '03' => 'Mar', '04' => 'Apr', '05' => 'May', '06' => 'Jun',
'07' => 'Jul', '08' => 'Aug', '09' => 'Sep', '10' => 'Oct', '11' => 'Nov', '12' => 'Dec'
);
# Inbounds of query times histogram
my @histogram_query_time = (0, 1, 5, 10, 25, 50, 100, 500, 1000, 10000);
# Inbounds of session times histogram
my @histogram_session_time = (0, 500, 1000, 30000, 60000, 600000, 1800000, 3600000, 28800000);
# Check histogram values user redefinition
if ($histogram_query)
{
if ($histogram_query =~ /[^0-9\s,]+/) {
die("Incorrect value for option --histogram_query\n");
}
@histogram_query_time = split(/\s*,\s*/, $histogram_query);
}
if ($histogram_session)
{
if ($histogram_session =~ /[^0-9\s,]+/) {
die("Incorrect value for option --histogram_session\n");
}
@histogram_session_time = split(/\s*,\s*/, $histogram_session);
}
# Where statistics are stored
my %overall_stat = ();
my %pgb_overall_stat = ();
my %overall_checkpoint = ();
my %top_slowest = ();
my %normalyzed_info = ();
my %error_info = ();
my %pgb_error_info = ();
my %pgb_pool_info = ();
my %logs_type = ();
my %errors_code = ();
my %per_minute_info = ();
my %pgb_per_minute_info = ();
my %lock_info = ();
my %tempfile_info = ();
my %cancelled_info = ();
my %connection_info = ();
my %pgb_connection_info = ();
my %database_info = ();
my %application_info = ();
my %user_info = ();
my %host_info = ();
my %session_info = ();
my %pgb_session_info = ();
my %conn_received = ();
my %checkpoint_info = ();
my %autovacuum_info = ();
my %autoanalyze_info = ();
my @graph_values = ();
my %cur_info = ();
my %cur_temp_info = ();
my %cur_plan_info = ();
my %cur_cancel_info = ();
my %cur_lock_info = ();
my $nlines = 0;
my %last_line = ();
my %pgb_last_line = ();
our %saved_last_line = ();
our %pgb_saved_last_line= ();
my %top_locked_info = ();
my %top_tempfile_info = ();
my %top_cancelled_info = ();
my %drawn_graphs = ();
my %cur_bind_info = ();
my %prepare_info = ();
my %bind_info = ();
# Global output filehandle
my $fh = undef;
my $t0 = Benchmark->new;
# Write resources files from __DATA__ section if they have not been already copied
# and return the HTML links to that files. If --extra-file is not used returns the
# CSS and JS code to be embeded in HTML files
my @jscode = &write_resources();
# Automatically set parameters with incremental mode
if ($incremental)
{
# In incremental mode an output directory must be set
if (!$html_outdir && !$outdir)
{
localdie("FATAL: you must specify an output directory with incremental mode, see -O or --outdir.\n")
}
# Ensure this is not a relative path
if ($outdir && dirname($outdir) eq '.')
{
localdie("FATAL3: output directory ($outdir) is not an absolute path.\n");
}
if ($html_outdir && dirname($html_outdir) eq '.')
{
localdie("FATAL: output HTML directory ($html_outdir) is not an absolute path.\n");
}
# Ensure that the directory already exists
if ($outdir && !-d $outdir)
{
localdie("FATAL: output directory $outdir does not exists.\n");
}
# Verify that the HTML outdir exixts when specified
if ($html_outdir && !-d $html_outdir)
{
localdie("FATAL: output HTML directory $html_outdir does not exists.\n");
}
# Set default last parsed file in incremental mode
if (!$last_parsed && $incremental)
{
$last_parsed = $outdir . '/LAST_PARSED';
}
$current_out_file = 'index.html';
# Set default output format
$extens = 'binary';
if ($rebuild && !$month_report && !$day_report)
{
# Look for directory where report must be generated again
my @build_directories = ();
# Find directories that shoud be rebuilt
unless(opendir(DIR, "$outdir"))
{
localdie("FATAL: can't opendir $outdir: $!\n");
}
my @dyears = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
foreach my $y (sort { $a <=> $b } @dyears)
{
unless(opendir(DIR, "$outdir/$y"))
{
localdie("FATAL: can't opendir $outdir/$y: $!\n");
}
my @dmonths = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
foreach my $m (sort { $a <=> $b } @dmonths)
{
unless(opendir(DIR, "$outdir/$y/$m"))
{
localdie("FATAL: can't opendir $outdir/$y/$m: $!\n");
}
my @ddays = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
foreach my $d (sort { $a <=> $b } @ddays)
{
unless(opendir(DIR, "$outdir/$y/$m/$d"))
{
localdie("FATAL: can't opendir $outdir/$y/$m/$d: $!\n");
}
my @binfiles = grep { $_ =~ /\.bin$/ } readdir(DIR);
closedir DIR;
push(@build_directories, "$y-$m-$d") if ($#binfiles >= 0);
}
}
}
&build_incremental_reports(@build_directories);
my $t2 = Benchmark->new;
my $td = timediff($t2, $t0);
&logmsg('DEBUG', "rebuilding reports took: " . timestr($td));
# Remove pidfile
unlink("$PID_FILE");
exit 0;
}
elsif ($month_report)
{
# Look for directory where cumulative report must be generated
my @build_directories = ();
# Get year+month as a path
$month_report =~ s#/#-#g;
my $month_path = $month_report;
$month_path =~ s#-#/#g;
if ($month_path !~ m#^\d{4}/\d{2}$#)
{
localdie("FATAL: invalid format YYYY-MM for --month-report option: $month_report");
}
&logmsg('DEBUG', "building month report into $outdir/$month_path");
# Find days directories that shoud be used to build the monthly report
unless(opendir(DIR, "$outdir/$month_path"))
{
localdie("FATAL: can't opendir $outdir/$month_path: $!\n");
}
my @ddays = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
foreach my $d (sort { $a <=> $b } @ddays)
{
unless(opendir(DIR, "$outdir/$month_path/$d"))
{
localdie("FATAL: can't opendir $outdir/$month_path/$d: $!\n");
}
my @binfiles = grep { $_ =~ /\.bin$/ } readdir(DIR);
closedir DIR;
push(@build_directories, "$month_report-$d") if ($#binfiles >= 0);
}
&build_month_reports($month_path, @build_directories);
my $t2 = Benchmark->new;
my $td = timediff($t2, $t0);
&logmsg('DEBUG', "building month report took: " . timestr($td));
# Remove pidfile
unlink("$PID_FILE");
exit 0;
}
elsif ($day_report)
{
# Look for directory where cumulative report must be generated
my @build_directories = ();
# Get year+month as a path
$day_report =~ s#/#-#g;
my $day_path = $day_report;
$day_path =~ s#-#/#g;
if ($day_path !~ m#^\d{4}/\d{2}\/\d{2}$#)
{
localdie("FATAL: invalid format YYYY-MM-DD for --day-report option: $day_report");
}
&logmsg('DEBUG', "building day report into $outdir/$day_path");
# Find days directories that shoud be used to build the monthly report
unless(opendir(DIR, "$outdir/$day_path"))
{
localdie("FATAL: can't opendir $outdir/$day_path: $!\n");
}
my @binfiles = grep { $_ =~ /\.bin$/ } readdir(DIR);
closedir DIR;
push(@build_directories, "$day_report") if ($#binfiles >= 0);
&build_day_reports($day_path, @build_directories);
my $t2 = Benchmark->new;
my $td = timediff($t2, $t0);
&logmsg('DEBUG', "building day report took: " . timestr($td));
# Remove pidfile
unlink("$PID_FILE");
exit 0;
}
}
else
{
# Extra files for resources are not allowed without incremental mode
$extra_files = 0;
}
# Reading last line parsed
if ($last_parsed && -e $last_parsed)
{
if (open(my $in, '<', $last_parsed))
{
my @content = <$in>;
close($in);
foreach my $line (@content)
{
chomp($line);
next if (!$line);
my ($datetime, $current_pos, $orig, @others) = split(/\t/, $line);
# Last parsed line with pgbouncer log starts with this keyword
if ($datetime eq 'pgbouncer')
{
$pgb_saved_last_line{datetime} = $current_pos;
$pgb_saved_last_line{current_pos} = $orig;
$pgb_saved_last_line{orig} = join("\t", @others);
}
else
{
$saved_last_line{datetime} = $datetime;
$saved_last_line{current_pos} = $current_pos;
$saved_last_line{orig} = $orig;
}
&logmsg('DEBUG', "Found log offset " . ($saved_last_line{current_pos} || $pgb_saved_last_line{current_pos}) . " in file $last_parsed");
}
# Those two log format must be read from start of the file
if ( ($format eq 'binary') || ($format eq 'csv') )
{
$saved_last_line{current_pos} = 0;
$pgb_saved_last_line{current_pos} = 0 if ($format eq 'binary');
}
}
else
{
localdie("FATAL: can't read last parsed line from $last_parsed, $!\n");
}
}
$tmp_last_parsed = 'tmp_' . basename($last_parsed) if ($last_parsed);
$tmp_last_parsed = "$TMP_DIR/$tmp_last_parsed";
$tmp_dblist = "$TMP_DIR/dblist.tmp";
###
### Explanation:
###
### Logic for the BINARY storage ($outdir) SHOULD be:
### If ('noclean')
### do nothing (keep everything)
### If (NO 'noclean') and (NO 'retention'):
### use an arbitrary retention duration of: 5 weeks
### remove BINARY files older than LAST_PARSED_MONTH-retention
### DO NOT CHECK for HTML file existence as OLD HTML files may be deleted by external tools
### which may lead to BINARY files NEVER deleted
### If (NO 'noclean') and ('retention'):
### remove BINARY files older than LAST_PARSED_MONTH-retention
### DO NOT CHECK for HTML file existence as OLD HTML files may be deleted by external tools
### which may lead to BINARY files NEVER deleted
###
### Logic for the HTML storage ($html_outdir || $outdir) SHOULD be:
### DO NOT check 'noclean' as this flag is dedicated to BINARY files
### If (NO 'retention'):
### do nothing (keep everything)
### If ('retention'):
### remove HTML folders/files older than LAST_PARSED_MONTH-retention (= y/m/d):
### days older than d in y/m/d
### months older than m in y/m/d
### years older than y in y/m/d
###
# Clear BIN/HTML storages in incremental mode
my @all_outdir = ();
push(@all_outdir, $outdir) if ($outdir);
push(@all_outdir, $html_outdir) if ($html_outdir);
### $retention_bin = 5 if (!$noclean && !$retention);
### $retention_bin = 0 if ($noclean && !$retention);
### $retention_bin = $retention if (!$noclean && $retention);
### $retention_bin = 0 if ($noclean && $retention);
### equivalent to:
my $retention_bin = $retention;
$retention_bin = 0 if ($noclean && $retention);
$retention_bin = 5 if (!$noclean && !$retention);
### $retention_html = 0 if (!$noclean && !$retention);
### $retention_html = 0 if ($noclean && !$retention);
### $retention_html = $retention if (!$noclean && $retention);
### $retention_html = $retention if ($noclean && $retention);
### equivalent to:
my $retention_html = $retention;
### We will handle noclean/!noclean and retention_xxx/!retention_xxx below
### Note: !retention_xxx equivalent to retention_xxx = 0
&logmsg('DEBUG', "BIN/HTML Retention cleanup: Initial cleanup flags - noclean=[$noclean] - retention_bin=[$retention_bin] - retention_html=[$retention_html] - saved_last_line{datetime}=[$saved_last_line{datetime}] - pgb_saved_last_line{datetime}=[$pgb_saved_last_line{datetime}] - all_outdir=[@all_outdir]");
if ( scalar(@all_outdir) && ($saved_last_line{datetime} || $pgb_saved_last_line{datetime}) )
{
foreach my $ret_dir (@all_outdir)
{
if (($saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /) ||
($pgb_saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /))
{
# Search the current week following the last parse date
my $limit_yw_bin = $1;
my $limit_yw_html = $1;
my $wn = &get_week_number($1, $2, $3);
# BIN: Case of year overlap
if (($wn - $retention_bin) < 1)
{
# Rewind to previous year
$limit_yw_bin--;
# Get number of last week of previous year, can be 52 or 53
my $prevwn = &get_week_number($limit_yw_bin, 12, 31);
# Add week number including retention_bin to the previous year
$limit_yw_bin .= sprintf("%02d", $prevwn - abs($wn - $retention_bin));
} else {
$limit_yw_bin .= sprintf("%02d", $wn - $retention_bin);
}
&logmsg('DEBUG', "BIN Retention cleanup: YearWeek Limit computation - YearWeek=<$limit_yw_bin> - This will help later removal");
# HTML: Case of year overlap
if (($wn - $retention_html) < 1)
{
# Rewind to previous year
$limit_yw_html--;
# Get number of last week of previous year, can be 52 or 53
my $prevwn = &get_week_number($limit_yw_html, 12, 31);
# Add week number including retention_html to the previous year
$limit_yw_html .= sprintf("%02d", $prevwn - abs($wn - $retention_html));
} else {
$limit_yw_html .= sprintf("%02d", $wn - $retention_html);
}
&logmsg('DEBUG', "HTML Retention cleanup: YearWeek Limit computation - YearWeek=<$limit_yw_html> - This will help later removal");
my @dyears = ();
if ( opendir(DIR, "$ret_dir") ) {
@dyears = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
} else {
&logmsg('ERROR', "BIN/HTML Retention cleanup: can't opendir $ret_dir: $!");
}
# Find obsolete weeks dir that shoud be cleaned
foreach my $y (sort { $a <=> $b } @dyears) {
my @weeks = ();
if ( opendir(DIR, "$ret_dir/$y") ) {
@weeks = grep { $_ =~ /^week-\d+$/ } readdir(DIR);
closedir DIR;
} else {
&logmsg('ERROR', "BIN/HTML Retention cleanup: can't opendir $ret_dir/$y: $!");
}
foreach my $w (sort { $a <=> $b } @weeks) {
$w =~ /^week-(\d+)$/;
if ( (!$noclean) && $retention_bin ) {
if ("$y$1" lt $limit_yw_bin) {
&logmsg('DEBUG', "BIN Retention cleanup: Removing obsolete week directory $ret_dir/$y/week-$1");
&cleanup_directory_bin("$ret_dir/$y/week-$1", 1);
}
}
if ( $retention_html ) {
if ("$y$1" lt $limit_yw_html) {
&logmsg('DEBUG', "HTML Retention cleanup: Removing obsolete week directory $ret_dir/$y/week-$1");
&cleanup_directory_html("$ret_dir/$y/week-$1", 1);
}
}
}
}
# Find obsolete months and days
foreach my $y (sort { $a <=> $b } @dyears) {
my @dmonths = ();
if ( opendir(DIR, "$ret_dir/$y") ) {
@dmonths = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
} else {
&logmsg('ERROR', "BIN/HTML Retention cleanup: can't opendir $ret_dir/$y: $!");
}
# Now remove the HTML monthly reports
if ( $retention_html ) {
foreach my $m (sort { $a <=> $b } @dmonths) {
my $diff_day = $retention_html * 7 * 86400;
my $lastday = 0;
if (($saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /) ||
($pgb_saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /)) {
$lastday = timelocal(1,1,1,$3,$2-1,$1-1900);
}
if ( $lastday ) {
my $lastday_minus_retention = $lastday - $diff_day;
my $lastday_minus_retention_Y = strftime('%Y', localtime($lastday_minus_retention));
my $lastday_minus_retention_M = strftime('%m', localtime($lastday_minus_retention));
my $lastday_minus_retention_prev_month_Y = $lastday_minus_retention_Y;
my $lastday_minus_retention_prev_month_M = $lastday_minus_retention_M - 1;
if ( $lastday_minus_retention_prev_month_M < 1 ) {
$lastday_minus_retention_prev_month_Y -= 1;
$lastday_minus_retention_prev_month_M = 12;
}
$lastday_minus_retention_prev_month_Y = sprintf("%04d", $lastday_minus_retention_prev_month_Y);
$lastday_minus_retention_prev_month_M = sprintf("%02d", $lastday_minus_retention_prev_month_M);
if ("$y$m" lt "$lastday_minus_retention_Y$lastday_minus_retention_M") {
&logmsg('DEBUG', "HTML Retention cleanup: Removing obsolete month directory $ret_dir/$y/$m");
&cleanup_directory_html("$ret_dir/$y/$m", 1);
}
}
}
}
# Now remove the corresponding days
foreach my $m (sort { $a <=> $b } @dmonths) {
my @ddays = ();
if ( opendir(DIR, "$ret_dir/$y/$m") ) {
@ddays = grep { $_ =~ /^\d+$/ } readdir(DIR);
closedir DIR;
} else {
&logmsg('ERROR', "BIN/HTML Retention cleanup: can't opendir $ret_dir/$y/$m: $!");
}
foreach my $d (sort { $a <=> $b } @ddays)
{
if ( (!$noclean) && $retention_bin ) {
# Remove obsolete days when we are in binary mode
# with noreport - there's no week-N directory
my $diff_day = $retention_bin * 7 * 86400;
my $oldday = timelocal(1,1,1,$d,$m-1,$y-1900);
my $lastday = $oldday;
if (($saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /) ||
($pgb_saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /)) {
$lastday = timelocal(1,1,1,$3,$2-1,$1-1900);
}
if (($lastday - $oldday) > $diff_day) {
&logmsg('DEBUG', "BIN Retention cleanup: Removing obsolete day directory $ret_dir/$y/$m/$d");
&cleanup_directory_bin("$ret_dir/$y/$m/$d", 1);
}
}
if ( $retention_html ) {
# Remove obsolete days when we are in binary mode
# with noreport - there's no week-N directory
my $diff_day = $retention_html * 7 * 86400;
my $oldday = timelocal(1,1,1,$d,$m-1,$y-1900);
my $lastday = $oldday;
if (($saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /) ||
($pgb_saved_last_line{datetime} =~ /^(\d+)\-(\d+)\-(\d+) /)) {
$lastday = timelocal(1,1,1,$3,$2-1,$1-1900);
}
if (($lastday - $oldday) > $diff_day) {
&logmsg('DEBUG', "HTML Retention cleanup: Removing obsolete day directory $ret_dir/$y/$m/$d");
&cleanup_directory_html("$ret_dir/$y/$m/$d", 1);
}
}
}
if ( rmdir("$ret_dir/$y/$m") ) {
&logmsg('DEBUG', "BIN/HTML Retention cleanup: Removing obsolete empty directory $ret_dir/$y/$m");
}
}
if ( rmdir("$ret_dir/$y") ) {
&logmsg('DEBUG', "BIN/HTML Retention cleanup: Removing obsolete empty directory $ret_dir/$y");
}
}
}
}
}
# Main loop reading log files
my $global_totalsize = 0;
my @given_log_files = ( @log_files );
chomp(@given_log_files);
# Store globaly total size for each log files
my %file_size = ();
foreach my $logfile ( @given_log_files )
{
$file_size{$logfile} = &get_file_size($logfile);
$global_totalsize += $file_size{$logfile} if ($file_size{$logfile} > 0);
}
# Verify that the file has not changed for incremental move
if (($incremental || $last_parsed) && !$remote_host)
{
my @tmpfilelist = ();
# Removed files that have already been parsed during previous runs
foreach my $f (@given_log_files)
{
if ($f eq '-')
{
&logmsg('DEBUG', "waiting for log entries from stdin.");
$saved_last_line{current_pos} = 0;
push(@tmpfilelist, $f);
}
elsif ($f =~ /\.bin$/)
{
&logmsg('DEBUG', "binary file \"$f\" as input, there is no log to parse.");
$saved_last_line{current_pos} = 0;
push(@tmpfilelist, $f);
}
elsif ( $journalctl_cmd && ($f eq $journalctl_cmd) )
{
my $since = '';
if ( ($journalctl_cmd !~ /--since|-S/) &&
($saved_last_line{datetime} =~ /^(\d+)-(\d+)-(\d+).(\d+):(\d+):(\d+)/) )
{
$since = " --since='$1-$2-$3 $4:$5:$6'";
}
&logmsg('DEBUG', "journalctl call will start since: $saved_last_line{datetime}");
my $new_journalctl_cmd = "$f$since";
push(@tmpfilelist, $new_journalctl_cmd);
$file_size{$new_journalctl_cmd} = $file_size{$f};
}
elsif ( $log_command && ($f eq $log_command) )
{
&logmsg('DEBUG', "custom command waiting for log entries from stdin.");
$saved_last_line{current_pos} = 0;
push(@tmpfilelist, $f);
}
else
{
# Auto detect log format for proper parsing
my $fmt = $format || 'stderr';
$fmt = autodetect_format($f, $file_size{$f});
$fmt ||= $format;
# Set regex to parse the log file
$fmt = set_parser_regex($fmt);
if (($fmt ne 'pgbouncer') && ($saved_last_line{current_pos} > 0))
{
my ($retcode, $msg) = &check_file_changed($f, $file_size{$f}, $fmt, $saved_last_line{datetime}, $saved_last_line{current_pos});
if (!$retcode)
{
&logmsg('DEBUG', "this file has already been parsed: $f, $msg");
}
else
{
push(@tmpfilelist, $f);
}
}
elsif (($fmt eq 'pgbouncer') && ($pgb_saved_last_line{current_pos} > 0))
{
my ($retcode, $msg) = &check_file_changed($f, $file_size{$f}, $fmt, $pgb_saved_last_line{datetime}, $pgb_saved_last_line{current_pos});
if (!$retcode)
{
&logmsg('DEBUG', "this file has already been parsed: $f, $msg");
}
else
{
push(@tmpfilelist, $f);
}
}
else
{
push(@tmpfilelist, $f);
}
}
}
@given_log_files = ();
push(@given_log_files, @tmpfilelist);
}
# Pipe used for progress bar in multiprocess
my $pipe = undef;
# Seeking to an old log position is not possible outside incremental mode
if (!$last_parsed || !exists $saved_last_line{current_pos}) {
$saved_last_line{current_pos} = 0;
$pgb_saved_last_line{current_pos} = 0;
}
if ($dump_all_queries)
{
$fh = new IO::File;
$fh->open($outfiles[0], '>') or localdie("FATAL: can't open output file $outfiles[0], $!\n");
}
####
# Start parsing all log files
####
# Number of running process
my $child_count = 0;
# Set max number of parallel process
my $parallel_process = 0;
# Open a pipe for interprocess communication
my $reader = new IO::Handle;
my $writer = new IO::Handle;
# Fork the logger process
if (!$nofork && $progress)
{
$pipe = IO::Pipe->new($reader, $writer);
$writer->autoflush(1);
spawn sub
{
&multiprocess_progressbar($global_totalsize);
};
}
# Initialise the list of reports to produce with the default report
# if $report_per_database is enabled there will be a report for each
# database. Information not related to a database (checkpoint, pgbouncer
# statistics, etc.) will be included in the default report which should
# be the postgres database to be read by the DBA of the PostgreSQL cluster.
$DBLIST{$DBALL} = 1;
# Prevent parallelism perl file to be higher than the number of files
$job_per_file = ($#given_log_files+1) if ( ($job_per_file > 1) && ($job_per_file > ($#given_log_files+1)) );
# Parse each log file following the multiprocess mode chosen (-j or -J)
my $current_log_file = '';
foreach my $logfile ( @given_log_files )
{
$current_log_file = $logfile if ($#given_log_files > 0);
# If we just want to build incremental reports from binary files
# just build the list of input directories with binary files
if ($incremental && $html_outdir && !$outdir)
{
my $incr_date = '';
my $binpath = '';
if ($logfile =~ /^(.*\/)(\d+)\/(\d+)\/(\d+)\/[^\/]+\.bin$/)
{
$binpath = $1;
$incr_date = "$2-$3-$4";
}
# Mark the directory as needing index update
if (open(my $out, '>>', "$last_parsed.tmp")) {
print $out "$binpath$incr_date\n";
close($out);
} else {
&logmsg('ERROR', "can't save last parsed line into $last_parsed.tmp, $!");
}
next;
}
# Confirm if we can use multiprocess for this file
my $pstatus = confirm_multiprocess($logfile);
if ($pstatus >= 0)
{
if ($pstatus = 1 && $job_per_file > 1)
{
$parallel_process = $job_per_file;
}
else
{
$parallel_process = $queue_size;
}
}
else
{
&logmsg('DEBUG', "parallel processing will not be used.");
$parallel_process = 1;
}
# Wait until a child dies if max parallel processes is reach
while ($child_count >= $parallel_process)
{
my $kid = waitpid(-1, WNOHANG);
if ($kid > 0)
{
$child_count--;
delete $RUNNING_PIDS{$kid};
}
sleep(1);
}
# Get log format of the current file
my $fmt = $format || 'stderr';
my $logfile_orig = $logfile;
if ($logfile ne '-' && !$journalctl_cmd && !$log_command)
{
$fmt = &autodetect_format($logfile, $file_size{$logfile});
$fmt ||= $format;
# Remove log format from filename if any
$logfile =~ s/:(stderr|csv|syslog|pgbouncer|jsonlog|logplex|rds|redshift)\d*$//i;
&logmsg('DEBUG', "pgBadger will use log format $fmt to parse $logfile.");
}
else
{
&logmsg('DEBUG', "Can not autodetect log format, assuming $fmt.");
}
# Set the timezone to use
&set_timezone();
# Set the regex to parse the log file following the format
$fmt = set_parser_regex($fmt);
# Do not use split method with remote and compressed files, stdin, custom or journalctl command
if ( ($parallel_process > 1) && ($queue_size > 1) &&
($logfile !~ $compress_extensions) &&
($logfile !~ /\.bin$/i) && ($logfile ne '-') &&
($logfile !~ /^(http[s]*|ftp[s]*|ssh):/i) &&
(!$journalctl_cmd || ($logfile !~ /\Q$journalctl_cmd\E/)) &&
(!$log_command || ($logfile !~ /\Q$log_command\E/))
)
{
# Create multiple processes to parse one log file by chunks of data
my @chunks = split_logfile($logfile, $file_size{$logfile_orig}, ($fmt eq 'pgbouncer') ? $pgb_saved_last_line{current_pos} : $saved_last_line{current_pos});
&logmsg('DEBUG', "The following boundaries will be used to parse file $logfile, " . join('|', @chunks));
for (my $i = 0; $i < $#chunks; $i++)
{
while ($child_count >= $parallel_process)
{
my $kid = waitpid(-1, WNOHANG);
if ($kid > 0)
{
$child_count--;
delete $RUNNING_PIDS{$kid};
}
sleep(1);
}
localdie("FATAL: Abort signal received when processing to next chunk\n") if ($interrupt == 2);
last if ($interrupt);
push(@tempfiles, [ tempfile('tmp_pgbadgerXXXX', SUFFIX => '.bin', DIR => $TMP_DIR, O_TEMPORARY => 1, UNLINK => 1 ) ]);
spawn sub
{
&process_file($logfile, $file_size{$logfile_orig}, $fmt, $tempfiles[-1]->[0], $chunks[$i], $chunks[$i+1], $i);
};
$child_count++;
}
}
else
{
# Start parsing one file per parallel process
if (!$nofork)
{
push(@tempfiles, [ tempfile('tmp_pgbadgerXXXX', SUFFIX => '.bin', DIR => $TMP_DIR, UNLINK => 1 ) ]);
spawn sub
{
&process_file($logfile, $file_size{$logfile_orig}, $fmt, $tempfiles[-1]->[0], ($fmt eq 'pgbouncer') ? $pgb_saved_last_line{current_pos} : $saved_last_line{current_pos});
};
$child_count++;
}
else
{
&process_file($logfile, $file_size{$logfile_orig}, $fmt, undef, ($fmt eq 'pgbouncer') ? $pgb_saved_last_line{current_pos} : $saved_last_line{current_pos});
}
}
localdie("FATAL: Abort signal received when processing next file\n") if ($interrupt == 2);
last if ($interrupt);
}
# Wait for all child processes to localdie except for the logger
# On Windows OS $progress is disabled so we don't go here
while (scalar keys %RUNNING_PIDS > $progress)
{
my $kid = waitpid(-1, WNOHANG);
if ($kid > 0) {
delete $RUNNING_PIDS{$kid};
}
sleep(1);
}
# Terminate the process logger
if (!$nofork)
{
foreach my $k (keys %RUNNING_PIDS)
{
kill('USR1', $k);
%RUNNING_PIDS = ();
}
# Clear previous statistics
&init_stats_vars();
}
# Load all data gathered by all the different processes
if (!$nofork)
{
foreach my $f (@tempfiles)
{
next if (!-e "$f->[1]" || -z "$f->[1]");
my $fht = new IO::File;
$fht->open("< $f->[1]") or localdie("FATAL: can't open temp file $f->[1], $!\n");
load_stats($fht);
$fht->close();
}
}
# Get last line parsed from all process
if ($last_parsed)
{
&logmsg('DEBUG', "Reading temporary last parsed line from $tmp_last_parsed");
if (open(my $in, '<', $tmp_last_parsed) )
{
while (my $line = <$in>)
{
chomp($line);
$line =~ s/\r//;
my ($d, $p, $l, @o) = split(/\t/, $line);
if ($d ne 'pgbouncer')
{
if (!$last_line{datetime} || ($d gt $last_line{datetime}))
{
$last_line{datetime} = $d;
$last_line{orig} = $l;
$last_line{current_pos} = $p;
}
}
else
{
$d = $p;
$p = $l;
$l = join("\t", @o);
if (!$pgb_last_line{datetime} || ($d gt $pgb_last_line{datetime}))
{
$pgb_last_line{datetime} = $d;
$pgb_last_line{orig} = $l;
$pgb_last_line{current_pos} = $p;
}
}
}
close($in);
}
unlink("$tmp_last_parsed");
}
# Save last line parsed
if ($last_parsed && ($last_line{datetime} || $pgb_last_line{datetime})
&& ($last_line{orig} || $pgb_last_line{orig}) )
{
&logmsg('DEBUG', "Saving last parsed line into $last_parsed");
if (open(my $out, '>', $last_parsed))
{
if ($last_line{datetime})
{
$last_line{current_pos} ||= 0;
print $out "$last_line{datetime}\t$last_line{current_pos}\t$last_line{orig}\n";
}
elsif ($saved_last_line{datetime})
{
$saved_last_line{current_pos} ||= 0;
print $out "$saved_last_line{datetime}\t$saved_last_line{current_pos}\t$saved_last_line{orig}\n";
}
if ($pgb_last_line{datetime})
{
$pgb_last_line{current_pos} ||= 0;
print $out "pgbouncer\t$pgb_last_line{datetime}\t$pgb_last_line{current_pos}\t$pgb_last_line{orig}\n";
}
elsif ($pgb_saved_last_line{datetime})
{
$pgb_saved_last_line{current_pos} ||= 0;
print $out "pgbouncer\t$pgb_saved_last_line{datetime}\t$pgb_saved_last_line{current_pos}\t$pgb_saved_last_line{orig}\n";
}
close($out);
}
else
{
&logmsg('ERROR', "can't save last parsed line into $last_parsed, $!");
}
}
if ($terminate)
{
unlink("$PID_FILE");
exit 2;
}
####
# Generates statistics output
####
my $t1 = Benchmark->new;
my $td = timediff($t1, $t0);
&logmsg('DEBUG', "the log statistics gathering took:" . timestr($td));
if ($dump_all_queries)
{
$fh->close();
# Remove pidfile and temporary file
unlink($tmp_dblist) if ($tmp_dblist);
unlink("$PID_FILE");
unlink("$last_parsed.tmp") if (-e "$last_parsed.tmp");
unlink($TMP_DIR . "/pgbadger_tmp_$$.bin") if ($#outfiles >= 1);
exit 0;
}
# Read the list of database we have proceeded in all child process
if ($report_per_database)
{
%DBLIST = ();
if (open(my $out, '<', "$tmp_dblist"))
{
my @data = <$out>;
foreach my $tmp (@data)
{
chomp($tmp);
my %dblist = split(/;/, $tmp);
foreach my $d (keys %dblist)
{
next if ($#dbname >= 0 and !grep(/^$d$/i, @dbname));
$DBLIST{$d} = 1;
$overall_stat{nlines}{$d} += $dblist{$d};
}
}
close($out);
&logmsg('DEBUG', "looking for list of database retrieved from log: " . join(',', keys %DBLIST));
}
else
{
&logmsg('ERROR', "can't read list of database from file $tmp_dblist, $!");
}
}
if ( !$incremental && ($#given_log_files >= 0) )
{
# If we have a temporary binary file (multiple output formats), load it before generating reports
if ($#outfiles >= 1 && -e $TMP_DIR . "/pgbadger_tmp_$$.bin")
{
&init_stats_vars();
if (open(my $bin_fh, '<', $TMP_DIR . "/pgbadger_tmp_$$.bin"))
{
my %stats = %{ fd_retrieve($bin_fh) };
close($bin_fh);
# Restore all statistics from binary file
%overall_stat = %{ $stats{overall_stat} } if (exists $stats{overall_stat});
%pgb_overall_stat = %{ $stats{pgb_overall_stat} } if (exists $stats{pgb_overall_stat});
%overall_checkpoint = %{ $stats{overall_checkpoint} } if (exists $stats{overall_checkpoint});
%normalyzed_info = %{ $stats{normalyzed_info} } if (exists $stats{normalyzed_info});
%error_info = %{ $stats{error_info} } if (exists $stats{error_info});
%pgb_error_info = %{ $stats{pgb_error_info} } if (exists $stats{pgb_error_info});
%pgb_pool_info = %{ $stats{pgb_pool_info} } if (exists $stats{pgb_pool_info});
%connection_info = %{ $stats{connection_info} } if (exists $stats{connection_info});
%pgb_connection_info = %{ $stats{pgb_connection_info} } if (exists $stats{pgb_connection_info});
%database_info = %{ $stats{database_info} } if (exists $stats{database_info});
%application_info = %{ $stats{application_info} } if (exists $stats{application_info});
%user_info = %{ $stats{user_info} } if (exists $stats{user_info});
%host_info = %{ $stats{host_info} } if (exists $stats{host_info});
%checkpoint_info = %{ $stats{checkpoint_info} } if (exists $stats{checkpoint_info});
%session_info = %{ $stats{session_info} } if (exists $stats{session_info});
%pgb_session_info = %{ $stats{pgb_session_info} } if (exists $stats{pgb_session_info});
%tempfile_info = %{ $stats{tempfile_info} } if (exists $stats{tempfile_info});
%logs_type = %{ $stats{logs_type} } if (exists $stats{logs_type});
%lock_info = %{ $stats{lock_info} } if (exists $stats{lock_info});
%per_minute_info = %{ $stats{per_minute_info} } if (exists $stats{per_minute_info});
%pgb_per_minute_info = %{ $stats{pgb_per_minute_info} } if (exists $stats{pgb_per_minute_info});
%top_slowest = %{ $stats{top_slowest} } if (exists $stats{top_slowest});
@log_files = @{ $stats{log_files} } if (exists $stats{log_files});
%autovacuum_info = %{ $stats{autovacuum_info} } if (exists $stats{autovacuum_info});
%autoanalyze_info = %{ $stats{autoanalyze_info} } if (exists $stats{autoanalyze_info});
%top_tempfile_info = %{ $stats{top_tempfile_info} } if (exists $stats{top_tempfile_info});
%top_locked_info = %{ $stats{top_locked_info} } if (exists $stats{top_locked_info});
%prepare_info = %{ $stats{prepare_info} } if (exists $stats{prepare_info});
%bind_info = %{ $stats{bind_info} } if (exists $stats{bind_info});
$nlines = $stats{nlines} if (exists $stats{nlines});
&logmsg('DEBUG', "binary statistics loaded from temporary file.");
}
else
{
&logmsg('WARNING', "could not load temporary binary file for multiple output generation.");
}
}
my $chld_running = 0;
foreach my $db (sort keys %DBLIST)
{
next if (!$db);
if ($nofork || $parallel_process <= 1) {
&gen_database_report($db);
}
else
{
while ($chld_running >= $parallel_process)
{
my $kid = waitpid(-1, WNOHANG);
if ($kid > 0)
{
$chld_running--;
delete $RUNNING_PIDS{$kid};
}
sleep(1);
}
spawn sub
{
&gen_database_report($db);
};
$chld_running++;
}
}
if (!$nofork && $parallel_process > 1)
{
while (scalar keys %RUNNING_PIDS > $progress)
{
my $kid = waitpid(-1, WNOHANG);
if ($kid > 0) {
delete $RUNNING_PIDS{$kid};
}
sleep(1);
}
}
}
elsif (!$incremental || !$noreport)
{
# Look for directory where report must be generated
my @build_directories = ();
if (-e "$last_parsed.tmp")
{
if (open(my $in, '<', "$last_parsed.tmp")) {
while (my $l = <$in>) {
chomp($l);
$l =~ s/\r//;
push(@build_directories, $l) if (!grep(/^$l$/, @build_directories));
}
close($in);
unlink("$last_parsed.tmp");
} else {
&logmsg('ERROR', "can't read file $last_parsed.tmp, $!");
}
&build_incremental_reports(@build_directories);
} else {
&logmsg('DEBUG', "no new entries in your log(s) since last run.");
}
}
my $t2 = Benchmark->new;
$td = timediff($t2, $t1);
&logmsg('DEBUG', "building reports took: " . timestr($td));
$td = timediff($t2, $t0);
&logmsg('DEBUG', "the total execution time took: " . timestr($td));
# Remove pidfile and temporary file
unlink($tmp_dblist);
unlink("$PID_FILE");
unlink("$last_parsed.tmp") if (-e "$last_parsed.tmp");
unlink($TMP_DIR . "/pgbadger_tmp_$$.bin") if ($#outfiles >= 1);
exit 0;
#-------------------------------------------------------------------------------
# Show pgBadger command line usage
sub usage
{
print qq{
Usage: pgbadger [options] logfile [...]
PostgreSQL log analyzer with fully detailed reports and graphs.
Arguments:
logfile can be a single log file, a list of files, or a shell command
returning a list of files. If you want to pass log content from stdin
use - as filename. Note that input from stdin will not work with csvlog.
Options:
-a | --average minutes : number of minutes to build the average graphs of
queries and connections. Default 5 minutes.
-A | --histo-average min: number of minutes to build the histogram graphs
of queries. Default 60 minutes.
-b | --begin datetime : start date/time for the data to be parsed in log
(either a timestamp or a time)
-c | --dbclient host : only report on entries for the given client host.
-C | --nocomment : remove comments like /* ... */ from queries.
-d | --dbname database : only report on entries for the given database.
-D | --dns-resolv : client ip addresses are replaced by their DNS name.
Be warned that this can really slow down pgBadger.
-e | --end datetime : end date/time for the data to be parsed in log
(either a timestamp or a time)
-E | --explode : explode the main report by generating one report
per database. Global information not related to a
database is added to the postgres database report.
-f | --format logtype : possible values: syslog, syslog2, stderr, jsonlog,
csv, pgbouncer, logplex, rds and redshift. Use this
option when pgBadger is not able to detect the log
format.
-G | --nograph : disable graphs on HTML output. Enabled by default.
-h | --help : show this message and exit.
-H | --html-outdir path: path to directory where HTML report must be written
in incremental mode, binary files stay on directory
defined with -O, --outdir option.
-i | --ident name : programname used as syslog ident. Default: postgres
-I | --incremental : use incremental mode, reports will be generated by
days in a separate directory, --outdir must be set.
-j | --jobs number : number of jobs to run at same time for a single log
file. Run as single by default or when working with
csvlog format.
-J | --Jobs number : number of log files to parse in parallel. Process
one file at a time by default.
-l | --last-parsed file: allow incremental log parsing by registering the
last datetime and line parsed. Useful if you want
to watch errors since last run or if you want one
report per day with a log rotated each week.
-L | --logfile-list file:file containing a list of log files to parse.
-m | --maxlength size : maximum length of a query, it will be restricted to
the given size. Default truncate size is $maxlength.
-M | --no-multiline : do not collect multiline statements to avoid garbage
especially on errors that generate a huge report.
-N | --appname name : only report on entries for given application name
-o | --outfile filename: define the filename for the output. Default depends
on the output format: out.html, out.txt, out.bin,
or out.json. This option can be used multiple times
to output several formats. To use json output, the
Perl module JSON::XS must be installed, to dump
output to stdout, use - as filename.
-O | --outdir path : directory where out files must be saved.
-p | --prefix string : the value of your custom log_line_prefix as
defined in your postgresql.conf. Only use it if you
aren't using one of the standard prefixes specified
in the pgBadger documentation, such as if your
prefix includes additional variables like client IP
or application name. MUST contain escape sequences
for time (%t, %m or %n) and processes (%p or %c).
See examples below.
-P | --no-prettify : disable SQL queries prettify formatter.
-q | --quiet : don't print anything to stdout, not even a progress
bar.
-Q | --query-numbering : add numbering of queries to the output when using
options --dump-all-queries or --normalized-only.
-r | --remote-host ip : set the host where to execute the cat command on
remote log file to parse the file locally.
-R | --retention N : number of weeks to keep in incremental mode. Defaults
to 0, disabled. Used to set the number of weeks to
keep in output directory. Older weeks and days
directories are automatically removed.
-s | --sample number : number of query samples to store. Default: 3.
-S | --select-only : only report SELECT queries.
-t | --top number : number of queries to store/display. Default: 20.
-T | --title string : change title of the HTML page report.
-u | --dbuser username : only report on entries for the given user.
-U | --exclude-user username : exclude entries for the specified user from
report. Can be used multiple time.
-v | --verbose : enable verbose or debug mode. Disabled by default.
-V | --version : show pgBadger version and exit.
-w | --watch-mode : only report errors just like logwatch could do.
-W | --wide-char : encode html output of queries into UTF8 to avoid
Perl message "Wide character in print".
-x | --extension : output format. Values: text, html, bin or json.
Default: html
-X | --extra-files : in incremental mode allow pgBadger to write CSS and
JS files in the output directory as separate files.
-z | --zcat exec_path : set the full path to the zcat program. Use it if
zcat, bzcat or unzip is not in your path.
-Z | --timezone +/-XX : Set the number of hours from GMT of the timezone.
Use this to adjust date/time in JavaScript graphs.
The value can be an integer, ex.: 2, or a float,
ex.: 2.5.
--anonymize : obscure all literals in queries, useful to hide
--charset : used to set the HTML charset to be used.
Default: utf-8.
--command CMD : command to execute to retrieve log entries on
stdin. pgBadger will open a pipe to the command
and parse log entries generated by the command.
--csv-separator : used to set the CSV field separator, default: ,
--day-report YYYY-MM-DD: create an HTML report over the specified day.
Requires incremental output directories and the
presence of all necessary binary data files
--disable-autovacuum : do not generate autovacuum report.
confidential data.
--disable-checkpoint : do not generate checkpoint/restartpoint report.
--disable-connection : do not generate connection report.
--disable-error : do not generate error report.
--disable-hourly : do not generate hourly report.
--disable-lock : do not generate lock report.
--disable-query : do not generate query reports (slowest, most
frequent, queries by users, by database, ...).
--disable-session : do not generate session report.
--disable-temporary : do not generate temporary report.
--disable-type : do not generate report of queries by type, database
or user.
--dump-all-queries : dump all queries found in the log file replacing
bind parameters included in the queries at their
respective placeholders positions.
--dump-raw-csv : parse the log and dump the information into CSV
format. No further processing is done, no report.
--enable-checksum : used to add an md5 sum under each query report.
--exclude-appname name : exclude entries for the specified application name
from report. Example: "pg_dump". Can be used
multiple times.
--exclude-client name : exclude log entries for the specified client ip.
Can be used multiple times.
--exclude-db name : exclude entries for the specified database from
report. Example: "postgres". Can be used multiple
times.
--exclude-file filename: path of the file that contains each regex to use
to exclude queries from the report. One regex per
line.
--exclude-line regex : exclude any log entry that will match the given
regex. Can be used multiple times.
--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-time regex : any timestamp matching the given regex will be
excluded from the report. Example: "2013-04-12 .*"
You can use this option multiple times.
--explain-url URL : use it to override the url of the graphical explain
tool. Default: $EXPLAIN_URL
--histogram-query VAL : use custom inbound for query times histogram.
Default inbound in milliseconds:
0,1,5,10,25,50,100,500,1000,10000
--histogram-session VAL: use custom inbound for session times histogram.
Default inbound in milliseconds:
0,500,1000,30000,60000,600000,1800000,3600000,28800000
--include-file filename: path of the file that contains each regex to the
queries to include 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-pid PID : only report events related to the session pid (\%p).
Can be used multiple time.
--include-session ID : only report events related to the session id (\%c).
Can be used multiple time.
--include-time regex : only timestamps matching the given regex will be
included in the report. Example: "2013-04-12 .*"
You can use this option multiple times.
--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.
--keep-comments : do not remove comments from normalized queries. It
can be useful if you want to distinguish between
same normalized queries.
--journalctl command : command to use to replace PostgreSQL logfile by
a call to journalctl. Basically it might be:
journalctl -u postgresql-9.5
--log-duration : force pgBadger to associate log entries generated
by both log_duration = on and log_statement = 'all'
--log-timezone +/-XX : Set the number of hours from GMT of the timezone
that must be used to adjust date/time read from
log file before beeing parsed. Using this option
makes log search with a date/time more difficult.
The value can be an integer, ex.: 2, or a float,
ex.: 2.5.
--month-report YYYY-MM : create a cumulative HTML report over the specified
month. Requires incremental output directories and
the presence of all necessary binary data files
--noexplain : do not process lines generated by auto_explain.
--no-fork : do not fork any process, for debugging purpose.
--no-process-info : disable changing process title to help identify
pgbadger process, some system do not support it.
--no-progressbar : disable progressbar.
--noreport : no reports will be created in incremental mode.
--no-week : inform pgbadger to not build weekly reports in
incremental mode. Useful if it takes too much time.
--normalized-only : only dump all normalized queries to out.txt
--pgbouncer-only : only show PgBouncer-related menus in the header.
--pid-dir path : set the path where the pid file must be stored.
Default /tmp
--pid-file file : set the name of the pid file to manage concurrent
execution of pgBadger. Default: pgbadger.pid
--pie-limit num : pie data lower than num% will show a sum instead.
--prettify-json : use it if you want json output to be prettified.
--rebuild : used to rebuild all html reports in incremental
output directories where there's binary data files.
--start-monday : in incremental mode, calendar weeks start on
Sunday. Use this option to start on a Monday.
--tempdir DIR : set directory where temporary files will be written
Default: File::Spec->tmpdir() || '/tmp'
pgBadger is able to parse a remote log file using a passwordless ssh connection.
Use -r or --remote-host to set the host IP address or hostname. There are also
some additional options to fully control the ssh connection.
--ssh-identity file path to the identity file to use.
--ssh-option options list of -o options to use for the ssh connection.
Options always used:
-o ConnectTimeout=\$ssh_timeout
-o PreferredAuthentications=hostbased,publickey
--ssh-port port ssh port to use for the connection. Default: 22.
--ssh-program ssh path to the ssh program to use. Default: ssh.
--ssh-timeout second timeout to ssh connection failure. Default: 10 sec.
--ssh-user username connection login name. Defaults to running user.
Log file to parse can also be specified using an URI, supported protocols are
http[s] and [s]ftp. The curl command will be used to download the file, and the
file will be parsed during download. The ssh protocol is also supported and will
use the ssh command like with the remote host use. See examples bellow.
Return codes:
0: on success
1: die on error
2: if it has been interrupted using ctr+c for example
3: the pid file already exists or can not be created
4: no log file was given at command line
Examples:
pgbadger /var/log/postgresql.log
pgbadger /var/log/postgres.log.2.gz /var/log/postgres.log.1.gz /var/log/postgres.log
pgbadger /var/log/postgresql/postgresql-2012-05-*
pgbadger --exclude-query="^(COPY|COMMIT)" /var/log/postgresql.log
pgbadger -b "2012-06-25 10:56:11" -e "2012-06-25 10:59:11" /var/log/postgresql.log
cat /var/log/postgres.log | pgbadger -
# Log line prefix with stderr log output
pgbadger --prefix '%t [%p]: user=%u,db=%d,client=%h' /pglog/postgresql-2012-08-21*
pgbadger --prefix '%m %u@%d %p %r %a : ' /pglog/postgresql.log
# Log line prefix with syslog log output
pgbadger --prefix 'user=%u,db=%d,client=%h,appname=%a' /pglog/postgresql-2012-08-21*
# Use my 8 CPUs to parse my 10GB file faster, much faster
pgbadger -j 8 /pglog/postgresql-10.1-main.log
Use URI notation for remote log file:
pgbadger http://172.12.110.1//var/log/postgresql/postgresql-10.1-main.log
pgbadger ftp://username\@172.12.110.14/postgresql-10.1-main.log
pgbadger ssh://username\@172.12.110.14:2222//var/log/postgresql/postgresql-10.1-main.log*
You can use together a local PostgreSQL log and a remote pgbouncer log file to parse:
pgbadger /var/log/postgresql/postgresql-10.1-main.log ssh://username\@172.12.110.14/pgbouncer.log
Reporting errors every week by cron job:
30 23 * * 1 /usr/bin/pgbadger -q -w /var/log/postgresql.log -o /var/reports/pg_errors.html
Generate report every week using incremental behavior:
0 4 * * 1 /usr/bin/pgbadger -q `find /var/log/ -mtime -7 -name "postgresql.log*"` -o /var/reports/pg_errors-`date +\\%F`.html -l /var/reports/pgbadger_incremental_file.dat
This supposes that your log file and HTML report are also rotated every week.
Or better, use the auto-generated incremental reports:
0 4 * * * /usr/bin/pgbadger -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/
will generate a report per day and per week.
In incremental mode, you can also specify the number of weeks to keep in the
reports:
/usr/bin/pgbadger --retention 2 -I -q /var/log/postgresql/postgresql.log.1 -O /var/www/pg_reports/
If you have a pg_dump at 23:00 and 13:00 each day during half an hour, you can
use pgBadger as follow to exclude these periods from the report:
pgbadger --exclude-time "2013-09-.* (23|13):.*" postgresql.log
This will help avoid having COPY statements, as generated by pg_dump, on top of
the list of slowest queries. You can also use --exclude-appname "pg_dump" to
solve this problem in a simpler way.
You can also parse journalctl output just as if it was a log file:
pgbadger --journalctl 'journalctl -u postgresql-9.5'
or worst, call it from a remote host:
pgbadger -r 192.168.1.159 --journalctl 'journalctl -u postgresql-9.5'
you don't need to specify any log file at command line, but if you have other
PostgreSQL log files to parse, you can add them as usual.
To rebuild all incremental html reports after, proceed as follow:
rm /path/to/reports/*.js
rm /path/to/reports/*.css
pgbadger -X -I -O /path/to/reports/ --rebuild
it will also update all resource files (JS and CSS). Use -E or --explode
if the reports were built using this option.
pgBadger also supports Heroku PostgreSQL logs using logplex format:
heroku logs -p postgres | pgbadger -f logplex -o heroku.html -
this will stream Heroku PostgreSQL log to pgbadger through stdin.
pgBadger can auto detect RDS and cloudwatch PostgreSQL logs using
rds format:
pgbadger -f rds -o rds_out.html rds.log
Each CloudSQL Postgresql log is a fairly normal PostgreSQL log, but encapsulated
in JSON format. It is autodetected by pgBadger but in case you need to force
the log format use `jsonlog`:
pgbadger -f jsonlog -o cloudsql_out.html cloudsql.log
This is the same as with the jsonlog extension, the json format is different
but pgBadger can parse both formats.
pgBadger also supports logs produced by CloudNativePG Postgres operator for Kubernetes:
pgbadger -f jsonlog -o cnpg_out.html cnpg.log
To create a cumulative report over a month use command:
pgbadger --month-report 2919-05 /path/to/incremental/reports/
this will add a link to the month name into the calendar view in
incremental reports to look at report for month 2019 May.
Use -E or --explode if the reports were built using this option.
};
# Note that usage must be terminated by an extra newline
# to not break POD documentation at make time.
exit 0;
}
sub gen_database_report
{
my $db = shift;
foreach $outfile (@outfiles)
{
($current_out_file, $extens) = &set_output_extension($outdir, $outfile, $extension, $db);
$extens = $dft_extens if ($current_out_file eq '-' && $dft_extens);
if ($report_per_database)
{
&logmsg('LOG', "Ok, generating $extens report for database $db...");
}
else
{
&logmsg('LOG', "Ok, generating $extens report...");
}
$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);
}
else
{
&dump_as_html('.', $db);
}
$fh->close;
}
}
####
# Function used to validate the possibility to use process on the given
# file. Returns 1 when all multiprocess can be used, 0 when we can not
# use multiprocess on a single file (remore file) and -1 when parallel
# process can not be used too (binary mode).
####
sub confirm_multiprocess
{
my $file = shift;
if (!$nofork && $progress)
{
# Not supported on Windows
if ($queue_size > 1) {
&logmsg('DEBUG', "parallel processing is not supported on this plateform.");
}
return 0;
}
if ($remote_host || $file =~ /^(http[s]*|ftp[s]*|ssh):/) {
# Disable multi process when using ssh to parse remote log
if ($queue_size > 1) {
&logmsg('DEBUG', "parallel processing is not supported with remote files.");
}
return 0;
}
# Disable parallel processing in binary mode
if ($format eq 'binary') {
if (($queue_size > 1) || ($job_per_file > 1)) {
&logmsg('DEBUG', "parallel processing is not supported with binary format.") if (!$quiet);
}
return -1;
}
return 1;
}
sub set_ssh_command
{
my ($ssh_cmd, $rhost) = @_;
#http://www.domain.com:8080/file.log:format
#ftp://www.domain.com/file.log:format
#ssh:root@domain.com:file.log:format
# Extract format part
my $fmt = '';
if ($rhost =~ s/\|([a-z2]+)$//) {
$fmt = $1;
}
$ssh_cmd = $ssh_bin || 'ssh';
$ssh_cmd .= " -p $ssh_port" if ($ssh_port);
$ssh_cmd .= " -i $ssh_identity" if ($ssh_identity);
$ssh_cmd .= " $ssh_options" if ($ssh_options);
if ($ssh_user && $rhost !~ /\@/) {
$ssh_cmd .= " $ssh_user\@$rhost";
} else {
$ssh_cmd .= " $rhost";
}
if (wantarray()) {
return ($ssh_cmd, $fmt);
} else {
return $ssh_cmd;
}
}
sub set_file_list
{
my $file = shift;
my @lfiles = ();
my $file_orig = $file;
my $fmt = '';
# Remove log format from log file if any
if ($file =~ s/(:(?:stderr|csv|syslog|pgbouncer|jsonlog|logplex|rds|redshift)\d*)$//i)
{
$fmt = $1;
}
# Store the journalctl command as is we will create a pipe from this command
if ( $journalctl_cmd && ($file =~ m/\Q$journalctl_cmd\E/) )
{
push(@lfiles, $file_orig);
$empty_files = 0;
}
# Store the journalctl command as is we will create a pipe from this command
elsif ( $log_command && ($file =~ m/\Q$log_command\E/) )
{
push(@lfiles, $file_orig);
$empty_files = 0;
}
# Input from stdin
elsif ($file eq '-')
{
if ($logfile_list)
{
localdie("FATAL: stdin input - can not be used with logfile list (-L).\n");
}
push(@lfiles, $file_orig);
$empty_files = 0;
}
# For input from other sources than stdin
else
{
# if it is not a remote file store the file if it is not an empty file
if (!$remote_host && $file !~ /^(http[s]*|[s]*ftp|ssh):/i)
{
localdie("FATAL: logfile \"$file\" must exist!\n") if (not -f $file);
if (-z $file)
{
print "WARNING: file $file is empty\n" if (!$quiet);
next;
}
push(@lfiles, $file_orig);
$empty_files = 0;
}
# if this is a remote file extract the list of files using a ssh command
elsif ($file !~ /^(http[s]*|[s]*ftp):/i)
{
# Get files from remote host
if ($file !~ /^ssh:/)
{
my($filename, $dirs, $suffix) = fileparse($file);
my $ssh_command_ls = "\"";
$ssh_command_ls .= "sudo " if ($ssh_sudo);
$ssh_command_ls .= "ls '$dirs'$filename\"";
&logmsg('DEBUG', "Looking for remote filename using command: $remote_command $ssh_command_ls");
my @rfiles = `$remote_command "ls '$dirs'$filename"`;
foreach my $f (@rfiles)
{
push(@lfiles, "$f$fmt");
}
}
elsif ($file =~ m#^ssh://([^\/]+)/(.*)#)
{
my $host_info = $1;
my $file = $2;
my $ssh = $ssh_command || 'ssh';
if ($host_info =~ s/:(\d+)$//) {
$host_info = "-p $1 $host_info";
}
$ssh .= " -i $ssh_identity" if ($ssh_identity);
$ssh .= " $ssh_options" if ($ssh_options);
my($filename, $dirs, $suffix) = fileparse($file);
my $ssh_command_ls = "\"";
$ssh_command_ls .= "sudo " if ($ssh_sudo);
$ssh_command_ls .= "ls '$dirs'$filename\"";
&logmsg('DEBUG', "Looking for remote filename using command: $ssh $host_info $ssh_command_ls");
my @rfiles = `$ssh $host_info $ssh_command_ls`;
$dirs = '' if ( $filename ne '' ); #ls returns relative paths for an directory but absolute ones for a file or filename pattern
foreach my $f (@rfiles)
{
$host_info =~ s/-p (\d+) (.*)/$2:$1/;
push(@lfiles, "ssh://$host_info/$dirs$f$fmt");
}
}
$empty_files = 0;
}
# this is remote file extracted using http/ftp protocol, store the uri
else
{
push(@lfiles, $file_orig);
$empty_files = 0;
}
}
return @lfiles;
}
# Get inbounds of query times histogram
sub get_hist_inbound
{
my ($duration, @histogram) = @_;
for (my $i = 0; $i <= $#histogram; $i++) {
return $histogram[$i-1] if ($histogram[$i] > $duration);
}
return -1;
}
# Compile custom log line prefix prefix
sub set_parser_regex
{
my $fmt = shift;
@prefix_params = ();
@prefix_q_params = ();
if ($fmt eq 'pgbouncer')
{
$pgbouncer_log_format = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? (\d+) ([^\s]+) (.\-0x[0-9a-f\.]*): ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)(?:\(\d+\))??[:\d]* (.*)/;
@pgb_prefix_params = ('t_timestamp', 't_pid', 't_loglevel', 't_session_id', 't_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse1 = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? (\d+) ([^\s]+) (.\-0x[0-9a-f\.]+|[Ss]tats): (.*)/;
@pgb_prefix_parse1 = ('t_timestamp', 't_pid', 't_loglevel', 't_session_id', 't_query');
$pgbouncer_log_parse2 = qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+(?: [A-Z\+\-\d]{3,6})? \d+ [^\s]+ .\-0x[0-9a-f\.]*: ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_parse2 = ('t_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse3 = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? (\d+) ([^\s]+) ([^:]+: .*)/;
@pgb_prefix_parse3 = ('t_timestamp', 't_pid', 't_loglevel', 't_query');
}
elsif ($fmt eq 'pgbouncer1')
{
$pgbouncer_log_format = qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]: (.\-0x[0-9a-f\.]*): ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_params = ('t_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_id', 't_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse1 = qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]: (.\-0x[0-9a-f\.]+|[Ss]tats): (.*)/;
@pgb_prefix_parse1 = ('t_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_id', 't_query');
$pgbouncer_log_parse2 = qr/^...\s+\d+\s\d+:\d+:\d+(?:\s[^\s]+)?\s[^\s]+\s[^\s\[]+\[\d+\]: .\-0x[0-9a-f\.]*: ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_parse2 = ('t_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse3 = qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]: ([^:]+: .*)/;
@pgb_prefix_parse3 = ('t_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_query');
}
elsif ($fmt eq 'pgbouncer2')
{
$pgbouncer_log_format = qr/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]: (.\-0x[0-9a-f\.]*): ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_params = ('t_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_id', 't_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse1 = qr/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]: (.\-0x[0-9a-f\.]+|[Ss]tats): (.*)/;
@pgb_prefix_parse1 = ('t_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_id', 't_query');
$pgbouncer_log_parse2 = qr/^\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:.[^\s]+)?\s[^\s]+\s(?:[^\s]+\s)?(?:[^\s]+\s)?[^\s\[]+\[\d+\]: .\-0x[0-9a-f\.]*: ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_parse2 = ('t_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse3 = qr/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]: ([^:]+: .*)/;
@pgb_prefix_parse3 = ('t_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_query');
}
elsif ($fmt eq 'pgbouncer3')
{
$pgbouncer_log_format = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? \[(\d+)\] ([^\s]+) (.\-0x[0-9a-f\.]*): ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)(?:\(\d+\))??[:\d]* (.*)/;
@pgb_prefix_params = ('t_timestamp', 't_pid', 't_loglevel', 't_session_id', 't_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse1 = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? \[(\d+)\] ([^\s]+) (.\-0x[0-9a-f\.]+|[Ss]tats): (.*)/;
@pgb_prefix_parse1 = ('t_timestamp', 't_pid', 't_loglevel', 't_session_id', 't_query');
$pgbouncer_log_parse2 = qr/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d+(?: [A-Z\+\-\d]{3,6})? \[\d+\] [^\s]+ .\-0x[0-9a-f\.]*: ([0-9a-zA-Z\_\[\]\-\.]*)\/([0-9a-zA-Z\_\[\]\-\.]*)\@([a-zA-Z0-9\-\.]+|\[local\]|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}|[0-9a-fA-F:]+)?(?:\(\d+\))?(?:\(\d+\))?[:\d]* (.*)/;
@pgb_prefix_parse2 = ('t_dbname', 't_dbuser', 't_client', 't_query');
$pgbouncer_log_parse3 = qr/^(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2})\.\d+(?: [A-Z\+\-\d]{3,6})? \[(\d+)\] ([^\s]+) ([^:]+: .*)/;
@pgb_prefix_parse3 = ('t_timestamp', 't_pid', 't_loglevel', 't_query');
}
elsif ($log_line_prefix)
{
# Build parameters name that will be extracted from the prefix regexp
my %res = &build_log_line_prefix_regex($log_line_prefix);
my $llp = $res{'llp'};
@prefix_params = @{ $res{'param_list'} };
$q_prefix = $res{'q_prefix'};
@prefix_q_params = @{ $res{'q_param_list'} };
if ($fmt eq 'syslog')
{
$llp =
'^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(\.\d+)?(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?\s\[(\d+)(?:\-\d+)?\]\s*'
. $llp
. '\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)';
$compiled_prefix = qr/$llp/;
unshift(@prefix_params, 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_ms', 't_host', 't_ident', 't_pid', 't_session_line');
push(@prefix_params, 't_loglevel', 't_query');
$other_syslog_line = qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?\s\[(\d+)(?:\-\d+)?\]\s*(.*)/;
}
elsif ($fmt eq 'syslog2')
{
$fmt = 'syslog';
$llp =
'^(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?(?:\s\[(\d+)(?:\-\d+)?\])?\s*'
. $llp
. '\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)';
$compiled_prefix = qr/$llp/;
unshift(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_line');
push(@prefix_params, 't_loglevel', 't_query');
$other_syslog_line = qr/^(\d+-\d+)-(\d+)T(\d+):(\d+):(\d+)(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?(?:\s\[(\d+)(?:\-\d+)?\])?\s*(.*)/;
}
elsif ($fmt eq 'logplex')
{
# The output format of the heroku pg logs is as follows: timestamp app[dyno]: message
$llp =
'^(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)(\.\d+)?[+\-]\d{2}:\d{2}\s+(?:[^\s]+)?\s*app\[postgres\.(\d+)\][:]?\s+\[([^\]]+)\]\s+\[\d+\-\d+\]\s+'
. $llp
. '\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)';
$compiled_prefix = qr/$llp/;
unshift(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_ms', 't_pid', 't_dbname');
push(@prefix_params, 't_loglevel', 't_query');
$other_syslog_line = qr/^(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)(?:\.\d+)?[+\-]\d{2}:\d{2}\s+(?:[^\s]+)?\s*app\[postgres\.\d+\][:]?\s+\[([^\]]+)\]\s+\[(\d+)\-(\d+)\]\s*(.*)/;
}
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 another timestamp
$llp = '^' . $llp
. '(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)';
$compiled_prefix = qr/$llp/;
@prefix_params = ('t_timestamp', 't_client', 't_dbuser', 't_dbname', 't_pid', 't_loglevel', 't_query');
}
elsif ($fmt =~ /^redshift$/)
{
# Look at format of the AWS redshift pg logs, for example:
# '2020-03-07T16:09:43Z UTC [ db=dev user=rdsdb pid=16929 userid=1 xid=7382 ]'
$llp = '^' . $llp
. '(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)';
$compiled_prefix = qr/$llp/;
@prefix_params = ('t_timestamp', 't_dbname', 't_dbuser', 't_pid', 't_loglevel', 't_query');
}
elsif ($fmt eq 'stderr' || $fmt eq 'default' || $fmt eq 'jsonlog')
{
$fmt = 'stderr' if ($fmt ne 'jsonlog');
$llp = '^' . $llp . '\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)';
$compiled_prefix = qr/$llp/;
push(@prefix_params, 't_loglevel', 't_query');
}
}
elsif ($fmt eq 'syslog')
{
$compiled_prefix =
qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(\.\d+)?(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?\s\[(\d+)(?:\-\d+)?\]\s*(.*?)\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)/;
push(@prefix_params, 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_ms', 't_host', 't_ident', 't_pid', 't_session_line',
't_logprefix', 't_loglevel', 't_query');
$other_syslog_line = qr/^(...)\s+(\d+)\s(\d+):(\d+):(\d+)(?:\.\d+)?(?:\s[^\s]+)?\s([^\s]+)\s([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?\s\[(\d+)(?:\-\d+)?\]\s*(.*)/;
}
elsif ($fmt eq 'syslog2')
{
$fmt = 'syslog';
$compiled_prefix =
qr/^(\d{4})-(\d{2})-(\d{2})T(\d{2}):(\d{2}):(\d{2})(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?(?:\s\[(\d+)(?:\-\d+)?\])?\s*(.*?)\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)/;
push(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_host', 't_ident', 't_pid', 't_session_line',
't_logprefix', 't_loglevel', 't_query');
$other_syslog_line = qr/^(\d+-\d+)-(\d+)T(\d+):(\d+):(\d+)(?:.[^\s]+)?\s([^\s]+)\s(?:[^\s]+\s)?(?:[^\s]+\s)?([^\s\[]+)\[(\d+)\]:(?:\s\[[^\]]+\])?(?:\s\[(\d+)(?:\-\d+)?\])?\s*(.*)/;
}
elsif ($fmt eq 'logplex')
{
# The output format of the heroku pg logs is as follows: timestamp app[dyno]: message
$compiled_prefix =
qr/^(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)(\.\d+)?[+\-]\d{2}:\d{2}\s+(?:[^\s]+)?\s*app\[postgres\.(\d+)\][:]?\s+\[([^\]]+)\]\s+\[\d+\-\d+\]\s+(.*?)\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)/;
unshift(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_ms', 't_pid', 't_dbname');
push(@prefix_params, 't_logprefix', 't_loglevel', 't_query');
$other_syslog_line = qr/^(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)(?:\.\d+)?[+\-]\d{2}:\d{2}\s+(?:[^\s]+)?\s*app\[(postgres)\.(\d+)\][:]?\s+\[([^\]]+)\]\s+\[\d+\-\d+\]\s*(.*)/;
}
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 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+(.*)/;
unshift(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_client', 't_dbuser', 't_dbname', 't_pid', 't_loglevel', 't_query');
}
elsif ($fmt eq 'redshift')
{
# Look at format of the AWS redshift pg logs, for example:
# '2020-03-07T16:09:43Z UTC [ db=dev user=rdsdb pid=16929 userid=1 xid=7382 ]'
$compiled_prefix =
qr/^'(\d+)-(\d+)-(\d+)T(\d+):(\d+):(\d+)Z [^\s]+ \[ db=(.*?) user=(.*?) pid=(\d+) userid=\d+ xid=(?:.*?) \]' (LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(.*)/;
unshift(@prefix_params, 't_year', 't_month', 't_day', 't_hour', 't_min', 't_sec', 't_dbname', 't_dbuser', 't_pid', 't_loglevel', 't_query');
}
elsif ($fmt eq 'stderr')
{
$compiled_prefix =
qr/^(\d{10}\.\d{3}|\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})(\.\d+)?(?: [A-Z\+\-\d]{3,6})?\s\[([0-9a-f\.]+)\][:]*\s(?:\[\d+\-\d+\])?\s*(.*?)\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)/;
push(@prefix_params, 't_timestamp', 't_ms', 't_pid', 't_logprefix', 't_loglevel', 't_query');
}
elsif ($fmt eq 'default')
{
$fmt = 'stderr';
$compiled_prefix =
qr/^(\d{10}\.\d{3}|\d{4}-\d{2}-\d{2}\s\d{2}:\d{2}:\d{2})(\.\d+)?(?: [A-Z\+\-\d]{3,6})?\s\[([0-9a-f\.]+)\][:]*\s(.*?)\s*(LOG|WARNING|ERROR|FATAL|PANIC|DETAIL|STATEMENT|HINT|CONTEXT|LOCATION):\s+(?:[0-9A-Z]{5}:\s+)?(.*)/;
push(@prefix_params, 't_timestamp', 't_ms', 't_pid', 't_logprefix', 't_loglevel', 't_query');
}
return $fmt;
}
sub check_regex
{
my ($pattern, $varname) = @_;
eval {m/$pattern/i;};
if ($@) {
localdie("FATAL: '$varname' invalid regex '$pattern', $!\n");
}
}
sub build_incremental_reports
{
my @build_directories = @_;
my $destdir = $html_outdir || $outdir;
my %weeks_directories = ();
foreach my $bpath (sort @build_directories)
{
my $binpath = '';
$binpath = $1 if ($bpath =~ s/^(.*\/)(\d+\-\d+\-\d+)$/$2/);
&logmsg('DEBUG', "Building incremental report for " . $bpath);
$incr_date = $bpath;
$last_incr_date = $bpath;
# Set the path to binary files
$bpath =~ s/\-/\//g;
# Get the week number following the date
$incr_date =~ /^(\d+)-(\d+)\-(\d+)$/;
my $wn = &get_week_number($1, $2, $3);
if (!$noweekreport)
{
if ($rebuild || !exists $weeks_directories{$wn})
{
$weeks_directories{$wn}{dir} = "$1-$2";
$weeks_directories{$wn}{prefix} = $binpath if ($binpath);
}
}
# First clear previous stored statistics
&init_stats_vars();
# Load all data gathered by all the different processes
$destdir = $binpath || $outdir;
if (opendir(DIR, "$destdir/$bpath"))
{
my @mfiles = grep { !/^\./ && ($_ =~ /\.bin$/) } readdir(DIR);
closedir DIR;
foreach my $f (@mfiles)
{
my $fht = new IO::File;
$fht->open("< $destdir/$bpath/$f") or localdie("FATAL: can't open file $destdir/$bpath/$f, $!\n");
load_stats($fht);
$fht->close();
}
}
$destdir = $html_outdir || $outdir;
foreach my $db (sort keys %DBLIST)
{
#next if ($#dbname >= 0 and !grep(/^$db$/i, @dbname));
my $tmp_dir = "$destdir/$db";
$tmp_dir = $destdir if (!$report_per_database);
# Generate reports for each specified output format
foreach $outfile (@outfiles)
{
my ($out_filename, $out_ext) = &set_output_extension('', $outfile, '', $db);
$extens = $out_ext;
$current_out_file = $out_filename;
&logmsg('LOG', "Ok, generating $extens daily report into $tmp_dir/$bpath/...");
# set path and create subdirectories
mkdir("$tmp_dir") if (!-d "$tmp_dir");
if ($bpath =~ m#^(\d+)/(\d+)/(\d+)#)
{
mkdir("$tmp_dir/$1") if (!-d "$tmp_dir/$1");
mkdir("$tmp_dir/$1/$2") if (!-d "$tmp_dir/$1/$2");
mkdir("$tmp_dir/$1/$2/$3") if (!-d "$tmp_dir/$1/$2/$3");
}
else
{
&logmsg('ERROR', "invalid path: $bpath, can not create subdirectories.");
}
$fh = new IO::File ">$tmp_dir/$bpath/$current_out_file";
if (not defined $fh) {
localdie("FATAL: can't write to $tmp_dir/$bpath/$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);
}
}
else
{
&dump_as_html('../../..', $db);
}
$fh->close;
}
}
}
# Build a report per week
foreach my $wn (sort { $a <=> $b } keys %weeks_directories)
{
&init_stats_vars();
# Get all days of the current week
my $getwnb = $wn;
$getwnb-- if (!$iso_week_number);
my @wdays = &get_wdays_per_month($getwnb, $weeks_directories{$wn}{dir});
my $binpath = '';
$binpath = $weeks_directories{$wn}{prefix} if (defined $weeks_directories{$wn}{prefix});
my $wdir = '';
# Load data per day
foreach my $bpath (@wdays)
{
$incr_date = $bpath;
$bpath =~ s/\-/\//g;
$incr_date =~ /^(\d+)\-(\d+)\-(\d+)$/;
$wdir = "$1/week-$wn";
$destdir = $binpath || $outdir;
# Load all data gathered by all the differents processes
if (-e "$destdir/$bpath")
{
unless(opendir(DIR, "$destdir/$bpath")) {
localdie("FATAL: can't opendir $destdir/$bpath: $!\n");
}
my @mfiles = grep { !/^\./ && ($_ =~ /\.bin$/) } readdir(DIR);
closedir DIR;
foreach my $f (@mfiles)
{
my $fht = new IO::File;
$fht->open("< $destdir/$bpath/$f") or localdie("FATAL: can't open file $destdir/$bpath/$f, $!\n");
load_stats($fht);
$fht->close();
}
}
}
$destdir = $html_outdir || $outdir;
foreach my $db (sort keys %DBLIST)
{
#next if ($#dbname >= 0 and !grep(/^$db$/i, @dbname));
my $tmp_dir = "$destdir/$db";
$tmp_dir = $destdir if (!$report_per_database);
# Generate reports for each specified output format
foreach $outfile (@outfiles)
{
my ($out_filename, $out_ext) = &set_output_extension('', $outfile, '', $db);
$extens = $out_ext;
$current_out_file = $out_filename;
&logmsg('LOG', "Ok, generating $extens weekly report into $tmp_dir/$wdir/...");
mkdir("$tmp_dir") if (!-d "$tmp_dir");
my $path = $tmp_dir;
foreach my $d (split('/', $wdir))
{
mkdir("$path/$d") if (!-d "$path/$d");
$path .= "/$d";
}
$fh = new IO::File ">$tmp_dir/$wdir/$current_out_file";
if (not defined $fh) {
localdie("FATAL: can't write to $tmp_dir/$wdir/$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);
}
}
else
{
&dump_as_html('../..', $db);
}
$fh->close;
}
}
}
# Generate global index to access incremental reports
&build_global_index();
}
sub build_month_reports
{
my ($month_path, @build_directories) = @_;
# First clear previous stored statistics
&init_stats_vars();
foreach my $bpath (sort @build_directories) {
$incr_date = $bpath;
$last_incr_date = $bpath;
# Set the path to binary files
$bpath =~ s/\-/\//g;
# Get the week number following the date
$incr_date =~ /^(\d+)-(\d+)\-(\d+)$/;
&logmsg('DEBUG', "reading month statistics from $outdir/$bpath");
# Load all data gathered by all the different processes
unless(opendir(DIR, "$outdir/$bpath")) {
localdie("FATAL: can't opendir $outdir/$bpath: $!\n");
}
my @mfiles = grep { !/^\./ && ($_ =~ /\.bin$/) } readdir(DIR);
closedir DIR;
foreach my $f (@mfiles) {
my $fht = new IO::File;
$fht->open("< $outdir/$bpath/$f") or localdie("FATAL: can't open file $outdir/$bpath/$f, $!\n");
load_stats($fht);
$fht->close();
}
}
my $dest_dir = $html_outdir || $outdir;
foreach my $db (sort keys %DBLIST)
{
my $tmp_dir = "$dest_dir/$db";
$tmp_dir = $dest_dir if (!$report_per_database);
# Generate reports for each specified output format
foreach $outfile (@outfiles)
{
my ($out_filename, $out_ext) = &set_output_extension('', $outfile, '', $db);
$extens = $out_ext;
$current_out_file = $out_filename;
&logmsg('LOG', "Ok, generating $extens monthly report into $tmp_dir/$month_path/$current_out_file");
mkdir("$tmp_dir") if (!-d "$tmp_dir");
my $path = $tmp_dir;
foreach my $d (split('/', $month_path)) {
mkdir("$path/$d") if (!-d "$path/$d");
$path .= "/$d";
}
$fh = new IO::File ">$tmp_dir/$month_path/$current_out_file";
if (not defined $fh) {
localdie("FATAL: can't write to $tmp_dir/$month_path/$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);
}
}
else
{
&dump_as_html('../..', $db);
}
$fh->close;
}
}
# Generate global index to access incremental reports
&build_global_index();
}
sub build_day_reports
{
my ($day_path, @build_directories) = @_;
# First clear previous stored statistics
&init_stats_vars();
foreach my $bpath (sort @build_directories) {
$incr_date = $bpath;
$last_incr_date = $bpath;
# Set the path to binary files
$bpath =~ s/\-/\//g;
# Get the week number following the date
$incr_date =~ /^(\d+)-(\d+)\-(\d+)$/;
&logmsg('DEBUG', "reading month statistics from $outdir/$bpath");
# Load all data gathered by all the different processes
unless(opendir(DIR, "$outdir/$bpath")) {
localdie("FATAL: can't opendir $outdir/$bpath: $!\n");
}
my @mfiles = grep { !/^\./ && ($_ =~ /\.bin$/) } readdir(DIR);
closedir DIR;
foreach my $f (@mfiles) {
my $fht = new IO::File;
$fht->open("< $outdir/$bpath/$f") or localdie("FATAL: can't open file $outdir/$bpath/$f, $!\n");
load_stats($fht);
$fht->close();
}
}
my $dest_dir = $html_outdir || $outdir;
foreach my $db (sort keys %DBLIST)
{
my $tmp_dir = "$dest_dir/$db";
$tmp_dir = $dest_dir if (!$report_per_database);
# Generate reports for each specified output format
foreach $outfile (@outfiles)
{
my ($out_filename, $out_ext) = &set_output_extension('', $outfile, '', $db);
$extens = $out_ext;
$current_out_file = $out_filename;
&logmsg('LOG', "Ok, generating $extens daily report into $tmp_dir/$day_path/$current_out_file");
mkdir("$tmp_dir") if (!-d "$tmp_dir");
my $path = $tmp_dir;
foreach my $d (split('/', $day_path))
{
mkdir("$path/$d") if (!-d "$path/$d");
$path .= "/$d";
}
$fh = new IO::File ">$tmp_dir/$day_path/$current_out_file";
if (not defined $fh) {
localdie("FATAL: can't write to $tmp_dir/$day_path/$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);
}
}
else
{
&dump_as_html('../..', $db);
}
$fh->close;
}
}
# Generate global index to access incremental reports
&build_global_index();
}
sub build_global_index
{
&logmsg('LOG', "Ok, generating global index to access incremental reports...");
my $dest_dir = $html_outdir || $outdir;
# Get database directories
unless(opendir(DIR, "$dest_dir")) {
localdie("FATAL: can't opendir $dest_dir: $!\n");
}
my @dbs = grep { !/^\./ && !/^\d{4}$/ && -d "$dest_dir/$_" } readdir(DIR);
closedir DIR;
@dbs = ($DBALL) if (!$report_per_database);
foreach my $db (@dbs)
{
#next if ($#dbname >= 0 and !grep(/^$db$/i, @dbname));
my $tmp_dir = "$dest_dir/$db";
$tmp_dir = $dest_dir if (!$report_per_database);
&logmsg('DEBUG', "writing global index into $tmp_dir/index.html");
$fh = new IO::File ">$tmp_dir/index.html";
if (not defined $fh) {
localdie("FATAL: can't write to $tmp_dir/index.html, $!\n");
}
my $date = localtime(time);
my @tmpjscode = @jscode;
my $path_prefix = '.';
$path_prefix = '..' if ($report_per_database);
for (my $i = 0; $i <= $#tmpjscode; $i++) {
$tmpjscode[$i] =~ s/EDIT_URI/$path_prefix/;
}
my $local_title = 'Global Index on incremental reports';
if ($report_title) {
$local_title = 'Global Index - ' . $report_title;
}
print $fh qq{
| ", &get_calendar($db, $y, $m), " | \n"; print $fh "
| Day | Hour | Count | Min duration | Max duration | Avg duration | Latency Percentile(90) | Latency Percentile(95) | Latency Percentile(99) |
|---|
| Day | Hour | SELECT | COPY TO | Average Duration | Latency Percentile(90) | Latency Percentile(95) | Latency Percentile(99) |
|---|
| Day | Hour | INSERT | UPDATE | DELETE | COPY FROM | Average Duration | Latency Percentile(90) | Latency Percentile(95) | Latency Percentile(99) |
|---|
| Day | Hour | Prepare | Bind | Bind/Prepare | Percentage of prepare |
|---|
| Day | Hour | Count | Average / Second |
|---|
| Day | Hour | Count | Average Duration | Average idle time |
|---|
| User | Count |
|---|
| User | Count |
|---|
| Host | Count |
|---|
| Host | Count |
|---|
| Database | User | Count |
|---|
| Database | User | Count |
|---|
| Range | Count | Percentage |
|---|
| Range | Count | Percentage |
|---|
| User | Count | Total Duration | Average Duration |
|---|
| User | Count | Total Duration | Average Duration |
|---|
| Host | Count | Total Duration | Average Duration |
|---|
| Host | Count | Total Duration | Average Duration |
|---|
| Application | Count | Total Duration | Average Duration |
|---|
| Database | Count | Total Duration | Average Duration |
|---|
| Database | User | Count | Total Duration | Average Duration |
|---|
| Day | Hour | Written buffers | Write time | Sync time | Total time |
|---|
| Day | Hour | Added | Removed | Recycled | Synced files | Longest sync | Average sync |
|---|
| Day | Hour | Count | Avg time (sec) |
|---|
| Day | Hour | Mean distance | Mean estimate |
|---|
| Cause | Number of checkpoints |
|---|
| Day | Hour | Count | Total size | Average size |
|---|
| Table | Number of analyzes |
|---|
| Day | Hour | VACUUMs | ANALYZEs |
|---|
| Index | Buffer usage | Skipped | WAL usage | Frozen | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Table | Vacuums | scans | hits | misses | dirtied | pins | frozen | records | full page | bytes | pages | tuples |
| I/O timing (ms) | CPU (s) | ||
|---|---|---|---|
| Table | read | write | elapsed |
| Index | Tuples | Pages | |||||
|---|---|---|---|---|---|---|---|
| Table | Vacuums | scans | removed | remain | not yet removable | removed | remain |
| Table | Number of vacuums | Index scans | Tuples removed | Pages removed |
|---|
| Type | Object | Count | Total Duration | Average Duration (s) |
|---|
| Type | Count | Percentage |
|---|
| Database | Request type | Count | Duration |
|---|
| Application | Request type | Count | Duration |
|---|
| User | Request type | Count | Duration |
|---|
| User | Request type | Count | Duration |
|---|
| Host | Request type | Count | Duration |
|---|
" . $plan . "\n
" . $plan . "\n
| Rank | Count | Total time | Min time | Max time | Avg duration | Query |
|---|---|---|---|---|---|---|
| $rank | $count | $total_time | $min_time | $max_time | $avg_time |
$query $md5
$example
|
| $NODATA | ||||||
| Rank | Wait time | Query |
|---|---|---|
| $rank | $time |
$query $md5
$details |
| $NODATA | ||
| Rank | Count | Total size | Min size | Max size | Avg size | Query |
|---|---|---|---|---|---|---|
| $rank | $count | $total_size | $min_size | $max_size | $avg_size |
$query $md5
$example
|
| Rank | Size | Query |
|---|---|---|
| $rank | $size |
$query $md5
$details |
| Rank | Count | Query |
|---|---|---|
| $rank | $count |
$query $md5
$example
"; $details .= "Database: $normalyzed_info{$curdb}{$k}{samples}{$d}{db} " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{db}); $details .= "User: $normalyzed_info{$curdb}{$k}{samples}{$d}{user} " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{user}); $details .= "Remote: $normalyzed_info{$curdb}{$k}{samples}{$d}{remote} " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{remote}); $details .= "Application: $normalyzed_info{$curdb}{$k}{samples}{$d}{app} " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{app}); $details .= "Queryid: $normalyzed_info{$curdb}{$k}{samples}{$d}{queryid} " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{queryid}); $details .= "Bind query: yes " if ($normalyzed_info{$curdb}{$k}{samples}{$d}{bind}); $details .= "log file: " . $normalyzed_info{$curdb}{$k}{samples}{$d}{logfile} if ($normalyzed_info{$curdb}{$k}{samples}{$d}{logfile}); print $fh qq{ |
| Rank | Number | Query |
|---|---|---|
| $rank | $count |
$query $md5
$details |
| Range | Count | Percentage |
|---|
| Rank | Duration | Query |
|---|---|---|
| $rank | $duration |
$query $md5
$details$explain |
| $NODATA | ||
| Rank | Total duration | Times executed | Min duration | Max duration | Avg duration | Query | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| $zday | $ht | " . &comma_numbers($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{count}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{duration}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{average}) . " | |||||||
| $zday | $ht:$rd | " . &comma_numbers($hourly_count{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}/($hourly_count{"$ht:$rd"}||1)) . " | |||||||
| $rank | $duration | $count | $min | $max | $avg |
$query $md5
Times Reported Time consuming queries #$rank$query_histo
$users_involved $apps_involved }; if (scalar keys %{$normalyzed_info{$curdb}{$k}{users}} > 0) { print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{users}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{users}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{users}})
{
if ($normalyzed_info{$curdb}{$k}{users}{$u}{duration} > 0) {
my $details = "[ User: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{users}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{users}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
if (scalar keys %{$normalyzed_info{$curdb}{$k}{apps}} > 0)
{
print $fh qq{
$details}; $idx++; } } print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{apps}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{apps}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{apps}})
{
if ($normalyzed_info{$curdb}{$k}{apps}{$u}{duration} > 0)
{
my $details = "[ Application: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{apps}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{apps}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
print $fh qq{
$details}; $idx++; } } print $fh qq{
|
|||||
| $NODATA | |||||||||||
| Rank | Times executed | Total duration | Min duration | Max duration | Avg duration | Query | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| $zday | $ht | " . &comma_numbers($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{count}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{duration}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{average}) . " | |||||||
| $zday | $ht:$rd | " . &comma_numbers($hourly_count{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}/($hourly_count{"$ht:$rd"}||1)) . " | |||||||
| $rank | $count | $duration | $min | $max | $avg |
$query $md5
Times Reported Time consuming queries #$rank$query_histo
$users_involved $apps_involved }; if (scalar keys %{$normalyzed_info{$curdb}{$k}{users}} > 0) { print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{users}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{users}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{users}})
{
if ($normalyzed_info{$curdb}{$k}{users}{$u}{duration} > 0)
{
my $details = "[ User: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{users}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{users}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
if (scalar keys %{$normalyzed_info{$curdb}{$k}{apps}} > 0)
{
print $fh qq{
$details}; $idx++; } } print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{apps}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{apps}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{apps}})
{
if ($normalyzed_info{$curdb}{$k}{apps}{$u}{duration} > 0)
{
my $details = "[ Application: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{apps}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{apps}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
print $fh qq{
$details}; $idx++; } } print $fh qq{
|
|||||
| $NODATA | |||||||||||
| Rank | Min duration | Max duration | Avg duration | Times executed | Total duration | Query | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| $zday | $ht | " . &comma_numbers($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{count}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{duration}) . " | " . &convert_time($normalyzed_info{$curdb}{$k}{chronos}{$d}{$h}{average}) . " | |||||||
| $zday | $ht:$rd | " . &comma_numbers($hourly_count{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}/($hourly_count{"$ht:$rd"}||1)) . " | |||||||
| $rank | $min | $max | $avg | $count | $duration |
$query $md5
Times Reported Time consuming queries #$rank$query_histo
$users_involved }; if (scalar keys %{$normalyzed_info{$curdb}{$k}{users}} > 0) { print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{users}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{users}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{users}})
{
if ($normalyzed_info{$curdb}{$k}{users}{$u}{duration} > 0)
{
my $details = "[ User: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{users}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{users}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
if (scalar keys %{$normalyzed_info{$curdb}{$k}{apps}} > 0)
{
print $fh qq{
$details}; $idx++; } } print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$normalyzed_info{$curdb}{$k}{apps}{$b}{duration} <=> $normalyzed_info{$curdb}{$k}{apps}{$a}{duration}} keys %{$normalyzed_info{$curdb}{$k}{apps}})
{
if ($normalyzed_info{$curdb}{$k}{apps}{$u}{duration} > 0)
{
my $details = "[ Application: $u";
$details .= " - Total duration: ".&convert_time($normalyzed_info{$curdb}{$k}{apps}{$u}{duration});
$details .= " - Times executed: $normalyzed_info{$curdb}{$k}{apps}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
print $fh qq{
$details}; $idx++; } } print $fh qq{
|
|||||
| $NODATA | |||||||||||
| Rank | Total duration | Times executed | Min duration | Max duration | Avg duration | Query | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| $zday | $ht | " . &comma_numbers($prepare_info{$curdb}{$k}{chronos}{$d}{$h}{count}) . " | " . &convert_time($prepare_info{$curdb}{$k}{chronos}{$d}{$h}{duration}) . " | " . &convert_time($prepare_info{$curdb}{$k}{chronos}{$d}{$h}{average}) . " | |||||||
| $zday | $ht:$rd | " . &comma_numbers($hourly_count{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}/($hourly_count{"$ht:$rd"}||1)) . " | |||||||
| $rank | $duration | $count | $min | $max | $avg |
$query $md5
Times Reported Time consuming prepare #$rank$query_histo
$users_involved $apps_involved }; if (scalar keys %{$prepare_info{$curdb}{$k}{users}} > 0) { print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$prepare_info{$curdb}{$k}{users}{$b}{duration} <=> $prepare_info{$curdb}{$k}{users}{$a}{duration}} keys %{$prepare_info{$curdb}{$k}{users}})
{
if ($prepare_info{$curdb}{$k}{users}{$u}{duration} > 0)
{
my $details = "[ User: $u";
$details .= " - Total duration: ".&convert_time($prepare_info{$curdb}{$k}{users}{$u}{duration});
$details .= " - Times executed: $prepare_info{$curdb}{$k}{users}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
if (scalar keys %{$prepare_info{$curdb}{$k}{apps}} > 0)
{
print $fh qq{
$details}; $idx++; } } print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$prepare_info{$curdb}{$k}{apps}{$b}{duration} <=> $prepare_info{$curdb}{$k}{apps}{$a}{duration}} keys %{$prepare_info{$curdb}{$k}{apps}})
{
if ($prepare_info{$curdb}{$k}{apps}{$u}{duration} > 0)
{
my $details = "[ Application: $u";
$details .= " - Total duration: ".&convert_time($prepare_info{$curdb}{$k}{apps}{$u}{duration});
$details .= " - Times executed: $prepare_info{$curdb}{$k}{apps}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
print $fh qq{
$details}; $idx++; } } print $fh qq{
|
|||||
| $NODATA | |||||||||||
| Rank | Total duration | Times executed | Min duration | Max duration | Avg duration | Query | |||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| $zday | $ht | " . &comma_numbers($bind_info{$curdb}{$k}{chronos}{$d}{$h}{count}) . " | " . &convert_time($bind_info{$curdb}{$k}{chronos}{$d}{$h}{duration}) . " | " . &convert_time($bind_info{$curdb}{$k}{chronos}{$d}{$h}{average}) . " | |||||||
| $zday | $ht:$rd | " . &comma_numbers($hourly_count{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}) . " | " . &convert_time($hourly_duration{"$ht:$rd"}/($hourly_count{"$ht:$rd"}||1)) . " | |||||||
| $rank | $duration | $count | $min | $max | $avg |
$query $md5
Times Reported Time consuming bind #$rank$query_histo
$users_involved $apps_involved }; if (scalar keys %{$bind_info{$curdb}{$k}{users}} > 0) { print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$bind_info{$curdb}{$k}{users}{$b}{duration} <=> $bind_info{$curdb}{$k}{users}{$a}{duration}} keys %{$bind_info{$curdb}{$k}{users}})
{
if ($bind_info{$curdb}{$k}{users}{$u}{duration} > 0)
{
my $details = "[ User: $u";
$details .= " - Total duration: ".&convert_time($bind_info{$curdb}{$k}{users}{$u}{duration});
$details .= " - Times executed: $bind_info{$curdb}{$k}{users}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
if (scalar keys %{$bind_info{$curdb}{$k}{apps}} > 0)
{
print $fh qq{
$details}; $idx++; } } print $fh qq{
};
my $idx = 1;
foreach my $u (sort {$bind_info{$curdb}{$k}{apps}{$b}{duration} <=> $bind_info{$curdb}{$k}{apps}{$a}{duration}} keys %{$bind_info{$curdb}{$k}{apps}})
{
if ($bind_info{$curdb}{$k}{apps}{$u}{duration} > 0)
{
my $details = "[ Application: $u";
$details .= " - Total duration: ".&convert_time($bind_info{$curdb}{$k}{apps}{$u}{duration});
$details .= " - Times executed: $bind_info{$curdb}{$k}{apps}{$u}{count}";
$details .= " ]\n";
print $fh qq{
};
}
print $fh qq{
$details}; $idx++; } } print $fh qq{
|
|||||
| $NODATA | |||||||||||