NAME
    pgCluu - PostgreSQL Cluster utilization

DESCRIPTION
    pgCluu is a PostgreSQL performances monitoring and auditing tool.

    It is a Perl program used to perform a full audit of a PostgreSQL
    Cluster and System performances. It is divided in two parts:

    - A collector used to grab statistics on the PostgreSQL cluster using
    the psql command line utility and sar.

    - A pur Perl grapher that will generate all HTML and charts output
    without any requirements.

    If you don't want system utilization reports, you can disable it at
    command line. If you just want to graph a sar data file, it's also
    possible and finally, if you just want to generate report from the
    pgstats utility, that's possible. Note that pgcluu_collectd is mostly a
    Perl replacement of pgstats.

SYNOPSIS
    PostgreSQL and System metrics collector.

            pgcluu_collectd [options] output_dir

    Reports generator.

            pgcluu [options] -o report_dir input_dir

REQUIREMENT
    pgCluu comes with two Perl scripts. You need a modern Perl distribution,
    the psql PostgreSQL client and the sar command line utility (from
    sysstat).

    Charts are rendered using a Javascript library so you don't need
    anything else. Your browser will do all the work.

INSTALLATION
    Download the tarball from Source Forge and unpack the archive:

            tar xzf pgcluu-1.x.tar.gz
            cd pgcluu-1.x/
            perl Makefile.PL
            make && sudo make install

    This will copy the Perl scripts pgcluu_collectd and pgcluu into the
    default /usr/local/bin directory and the man page to
    /usr/local/share/man/man1/pgcluu.1. Those are the default installation
    directories for 'site' install.

    If you want to install all under /usr location, use INSTALLDIRS='perl'
    as an argument of Makefile.PL. The script will be installed into
    /usr/bin/pgcluu and the manpage into /usr/share/man/man1/pgcluu.1.

    For example, to install everything just like Debian does, proceed as
    follows:

            perl Makefile.PL INSTALLDIRS=vendor

    By default INSTALLDIRS is set to site.

USAGE
    See next two chapters for a complete description of the command line
    options. For the impatients, here some simple commands that could be run
    as postgres user:

            mkdir /tmp/stat_db1/
            pgcluu_collectd -D -i 60 /tmp/stat_db1/
            LOG: Detach from terminal with pid: 11323

    or with more options

            pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb
            LOG: Detach from terminal with pid: 14671

    wait some time and activity on your PostgreSQL Cluster... Then stop the
    pgcluu_collectd daemon and generate the report:

            pgcluu_collectd -k
            LOG: Received terminating signal.
            mkdir /tmp/report_db1/
            pgcluu -o /tmp/report_db1/ /tmp/stat_db1/

    You should obtain something like example at
    http://pgcluu.darold.net/example/

    By default all javascript, css and the webfont fontawesome are
    automatically generated into the output directory if those files does
    not already exits.

COLLECTING STATISTICS
    To generate reports about your PostgreSQL Cluster Utilization you must
    collect statistics before. pgcluu_collectd is here for that. It can be
    run in a daemom mode (option -D) or in interactive mode for debuging
    purpose. All you need is to provide a directory where data will be
    stored. Statistics will be pooled at a default interval of 60 secondes,
    using option -i you can customize it. See below for a complete list of
    command line options.

    usage: pgcluu_collectd [options] output_dir

            output_dir: full path to directory where pgcluu_collectd will
                        store statistics.

    options:

      -d, --dbname=DATABASE    database name to connect to. Default to current user.
      -D, --daemonize          detach from console and enter in daemon mode.
      -f, --pid-file=FILE      path to pid file. Default: /tmp/pgcluu_collectd.pid.
      -h, --host=HOSTNAME      database server host or socket directory
      -i, --interval=NUM       time to wait between runs
      -k, --kill               stop current pgcluu_collectd running daemon.
      -m, --metric=METRIC      set a coma separated list of metrics to perform.
      -p, --port=PORT          database port(s) to connect to. Defaults to 5432.
      -P, --psql=BIN           path to the psql command. Default: /usr/bin/psql.
      -s, --sar=BIN            path to sar sysstat command. Default: /usr/bin/sar.
      -S, --disable-sar        disable collect of system statistics with sar.
      -U, --dbuser=USERNAME    database user to connect as. Default to current user.
      --list-metric            list available metrics actions that can be performed.
      --pgbouncer-args=OPTIONS Option to used to connect to the pgbouncer system
                               database. Ex: -p 6432 -U postgres -h 192.168.1.100
                               You must at least give one parameter to enable
                               pgbouncer monitoring, for example: '-p 5432'.
      --sar-file=FILE          path to sar output data file for sysstat stats
                               Default to output_dir/sar_stats.dat.
      --stat-type all|user     Set stats tables to read. Values: 'all' or 'user' to
                               look at pg_stat_(all|user) tables. Default: user.
      --pgversion X.Y          force the PostgreSQL version to the given value.
      --pgservice NAME         Name of service inside of the pg_service.conf file.
      --help                   print usage

    For example, as postgres user:

            mkdir /tmp/stat_db1/
            pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb

    to collect statistics from pgbouncer too:

            pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.10.1.1 -U postgres -d mydb \
                    --pgbouncer-args='-p 5342'

    then after some long time and activities on the database, stop the
    daemon:

            pgcluu_collectd -k

    The output directory should look likes:

            /tmp/stat_db1/
            ├── pg_class_size.csv
            ├── pg_database_size.csv
            ├── pg_stat_bgwriter.csv
            ├── pg_stat_connections.csv
            ├── pg_stat_database_conflicts.csv
            ├── pg_stat_database.csv
            ├── pg_statio_user_indexes.csv
            ├── pg_statio_user_sequences.csv
            ├── pg_statio_user_tables.csv
            ├── pg_stat_replication.csv
            ├── pg_stat_user_functions.csv
            ├── pg_stat_user_indexes.csv
            ├── pg_stat_user_tables.csv
            ├── pg_stat_xact_user_functions.csv
            ├── pg_stat_xact_user_tables.csv
            ├── pg_xlog_stat.csv
            └── sar_stats.dat

    Then now you can proceed with pgcluu to generate reports.

GENERATING REPORTS
    To generate a pgCluu report about a PostgreSQL Cluster you must, at
    least, have a directory that contains all data files generated by
    pgcluu_collectd or pgstats. In this directory, if you have a file named
    sar_stats.dat or sadc_stats.dat for binary sadc data file, it will be
    taken to build report about system utilisation. If you just want to make
    a report from a sar file use the -i or -I options.

    usage: ./pgcluu [options] [-i sar_file | -I sadc_file] [input_dir]

            input_dir: directory where pgcluu_collectd or pgstats and sar data

    options:

      -b, --begin  datetime    start date/time for the data to be parsed.
      -d, --db-only dbname     Only report for the whole cluster and the given
                               database name. You can use it multiple time.
      -e, --end    datetime    end date/time for the data to be parsed.
      -i, --sar-file=FILE      path to the sar text data file to read to generate
                               system reports. Default to input_dir/sar_stats.dat.
      -I, --sadc-file=FILE     sadc binary data file to read to generate system
                               reports. Default to input_dir/sadc_stats.dat.
      -o, --output=DIR         output directory
      -p, --dev-only device    Only report I/O stats for a particular device
                               You can use it multiple time.
      -s, --sadf=BIN           path to the sadf sysstat command used to read the
                               sadc binary data file. Default: /usr/bin/sadf.
      -S, --disable-sar        disable collect of system statistics with sar.
      -t, --all-table          report per table statistics, default is report
                               for the whole database.
      -T, --with-table table   Only report for the whole tables and the given
                               table name. You can use it multiple time.
      -z, --timezone +/-XX     Set the number of hour(s) from GMT of the timezone.
                               Use this to adjust date/time from the sar output as
                               pgcluu use GMT time to draw charts.

      --help                   print usage

    Using the example above, you can generate all reports with the following
    command:

            mkdir /tmp/report_db1/
            pgcluu -o /tmp/report_db1/ /tmp/stat_db1/

LICENSE
    Copyright (c) 2012-2014, Gilles Darold

    pgCluu is licenced under the PostgreSQL Licence a liberal Open Source
    license, similar to the BSD or MIT licenses. That mean that all parts of
    the program are open source and free of charge.

            Permission to use, copy, modify, and distribute this software and its
            documentation for any purpose, without fee, and without a written agreement
            is hereby granted, provided that the above copyright notice and this
            paragraph and the following two paragraphs appear in all copies.

            IN NO EVENT SHALL Dalibo BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
            SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS,
            ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
            Dalibo HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

            Gilles DArold SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT
            LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
            PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS,
            AND Gilles Darold HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
            UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

    This is the case for both, pgcluu_collectd and the grapher pgcluu
    programs.

AUTHORS
    pgCluu is an original development of Gilles Darold.

    Some parts of the collector are taken from pgstats a C program writen by
    Guillaume Lelarge and especially the SQL queries including the
    compatibility with all PostgreSQL versions. See
    https://github.com/gleu/pgstats

    Btw pgCluu grapher is compatible with files generated by pgstats, sar
    and sadc so you can use it independantly to graph those data. Some part
    of the sar output parser are taken from SysUsage. See
    http://sysusage.darold.net/