=head1 NAME

    PgAnalyzer v2.0 - PostgreSQL Log Analyzer


=head1 SYNOPSIS

      #!/usr/bin/perl
      use strict;
      use PgAnalyzer;

      # Instanciate PgAnalyzer with configuration file as argument
      my $pa = new PgAnalyzer($ARGV[0]);

      # Run log file parsing
      $pa->parseFile();

      # Generate graphics and html
      $pa->buildHTML();


=head1 DESCRIPTION

    PgAnalyzer is a perl script and library used to analyze serverlog file
    of the excellent PostgreSQL RDBMS. It report practical statistics
    information for database connection, database user and host access.
    It also provide SQL query and error statistics.

    Reports are generated for years, months and days stats. It also generate
    png graphs for connection and SQL queries.

    Only PostgreSQL internal log format is supported (no support for syslog).

    WARNING: This log analyzer only works with PostgreSQL version up to
    version 7.3.4.

=head1 ABSTRACT

    Statistic reports are oriented for database connection and SQL query
    statistics. Report for SQL queries try to remove all dynamic parameters
    and replace them by a '?'. This groups same queries independantly of the
    dynamics parameters. You can remove query parsing to gain parser speed
    but you will loose some usefull information like total time taken by each
    SQL queries.

    This analyzer is incremental so it can be run in a daily cron. Take care
    if you have rotate log enable to run it before rotation is done.

    The HTML output need a some disk space for large number of PostgreSQL
    users and connections. Here for 500000 connections and 5000000 of SQL
    queries in 3 month for 18 databases, it takes only 15 Mb. On the
    5000000 SQL query there's only 2840 different queries after removing
    dynamics parameters.

    The speed of the parser and the HTML output generation depend on the
    size of the log file and the number of SQL queries if you enable detail.
    SQL query. But it should be really acceptable.

    In my dual 1.4 GHz cpu with 300Mb of free memory, a one day serverlog file
    of 40 Mb (around 20,000 connection and 160,000 SQL queries) it takes five
    minutes 30 secondes to do all the job. If I disable SQL query detail it only
    takes 30 secondes.

    Notes that it only works on Unix system, port to other system will not
    be done by me, so don't ask. You can send me patches if you do so.

    You may found a special database report called 'unknow', this is due to the
    fact that the only way to associate a query to a database is to have the
    login pid traced into the serverlog. During rotate log and incremental
    parsing, some SQL queries can not be attached to a login pid so they are
    stored and associated to the 'unknow' database.


=head1 REQUIREMENT

    You need a modern perl distribution, 5.6.x and more. PgAnalyzer also
    use lib GD 1.8.4 or more to draw graph and the following perl modules:

	GD
	GD::Graph
	GD::Graph::bars3d

    Graph images are in PNG format so you also need libpng and lib gd must
    be compliled with png support.

    PgAnalyzer also need to have something to parse, so here are the log
    options that must be enable into the postgresql.conf file:

	silent_mode = false

	log_connections = true
	log_pid = true
	log_statement = true
	log_duration = true
	log_timestamp = true

	log_min_error_statement = error


    Of course, you need a http server to be able to watch HTML output.

    WARNING: This log analyzer only works with PostgreSQL version up to
    version 7.3.4.


=head1 PUBLIC METHODS

=head2 new

    Creates a new PgAnalyzer object instance. You should give the path
    to the configuration file as argument if this file is in an other
    place than /etc/pganalyzer.conf. See next section to know what option
    can be used in this file. This method perform all initialisation
    and configuration of the application.

=head2 parseFile

    Method call to run the PostgreSQL serverlog parsing and the output
    of data files.

=head2 buildHTML

    This method is called to generate HTML and graph output.


=head1 CONFIGURATION

    PgAnalyzer used a configuration file to match your needs and your
    custom installation. Format of this file is as follow:

	variableName	value

    where field separation can be space or tabulation. Here are the
    recognized options:

=over 4

=item Output html_output_dir

Directory where PgAnalyzer should dump all HTML and graphs.
Choose a path that can be read by a Web browser. Default is:
/usr/local/apache/htdocs/PgAnalyzer

=item LogFile serverlog_file

Path to the postgresql log file. Default:
/usr/local/pgsql/logs/serverlog

=item QuietMode 0|1

Run in quiet mode or print debug information. Default is
verbose output (0).

=item TopIP integer

Top number of host access to show. Default: 100. If set to 0
all host ip are reported.

=item TopSQL integer

Top number of SQL queries to show. Default: 100. If set to 0
all SQL queries are reported.

=item OrderQuery count|duration

Set the ordering type of SQL queries. Must be 'count' or 'duration'.
Default is count

=item OrderError count|date

Set the ordering type of SQL error. Must be 'count' or 'date'.
Default is date. The date correspond to the last datetime
the error was parsed.

=item LogoUrl path_to_pg_logo

Set the Uri of the elephant logo. Default:
/icons/pg_diamond_70x60_blue.gif

=item ExcludeDB db1 db2 ...

List of database name separated by a space character that must
be exclude from statistics report. Typically you can set it to
"template0 template1".

=item ExcludeUser username1 username2 ...

List of connection username  separated by a space character that
must be exclude from statistics report. Typically you can set i
to "postgres" if only used for administration.

=item DetailSQL 0|1

Disable or enable detailed SQL queries statistics. Default is enable.
You can disable this to speed up log reporting, but you will loose
a good way to see what SQL queries take the more time.

=back


=head1 AUTHOR

    Gilles Darold <gilles@darold.net>


=head1 COPYRIGHT

    Copyright (c) 2001-2003 Gilles Darold - All rights reserved. This program
    is free software; you can redistribute it and/or modify it under the same
    terms as Perl itself.


=head1 BUGS

    I will do my best to give you official support for PgAnalyzer. Your
    volontee to help construct it and your contribution are welcome. If
    you find any bug please mail me at gilles@darold.net

