NAME
    pgCluu - PostgreSQL Cluster utilization

DESCRIPTION
    pgCluu is a PostgreSQL performance monitoring and auditing tool.

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

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

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

    If you don't need system utilization reports or don't want to install
    the sysstat package, you can disable it at command line. You will only
    have reports about your PostgreSQL Cluster. If you are running pgCluu
    from a central server using option -h to monitor remotely a PostgreSQL
    Cluster, the call to sar is automatically disabled.

    If you just want to have system utilization reports or generate graphs
    from a sar data file, it's also possible.

SYNOPSIS
    PostgreSQL and System metrics collector.

            pgcluu_collectd [options] output_dir

    Report generator.

            pgcluu [options] -o report_dir input_dir

REQUIREMENT
    pgCluu comes with two Perl scripts. You need a modern Perl distribution,
    the psql client and the sar command line utility (sysstat). The sysstat
    package is optional, you can still use pgCluu to generate reports about
    your PostgreSQL Cluster without it.

    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 SourceForge and unpack the archive:

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

    This will copy the Perl scripts pgcluu_collectd and pgcluu into /usr/bin
    or /usr/local/bin directory following your distribution and the man page
    to /usr/share/man/man1/pgcluu.1 or /usr/local/share/man/man1/pgcluu.1.
    Those are the default installation directories for 'site' install on
    some well-known distribution but the path may change.

    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
   Manually
    See next two chapters for a complete description of the command line
    options. For the impatient, 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.

   Using systemd unit files
    pgcluu comes with systemd service files:

        - pgcluu_collectd.service: execute pgcluu_collectd as a daemon to collect statistics.
        - pgcluu.service: execute pgcluu to generate reports.
        - pgcluu.timer: run periodically pgcluu.service

    This is sample files that you may want to adapt to your Linux
    distribution or your needs. To install them after editing use commands
    below:

            cp pgcluu_collectd.service /usr/lib/systemd/system/
            cp pgcluu.service /usr/lib/systemd/system/
            cp pgcluu.timer /usr/lib/systemd/system/

            systemctl daemon-reload
            systemctl enable pgcluu_collectd.service
            systemctl enable pgcluu.service
            systemctl enable pgcluu.timer
            systemctl start pgcluu_collectd.service
            systemctl start pgcluu.timer

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 daemon mode (option -D) or in interactive mode for debugging
    purpose. All you need is to provide a directory where data will be
    stored. Statistics will be pooled at a default interval of 60 seconds,
    using option -i you can customize it. See below for a complete list of
    command line options.

  pgcluu_collectd usage
    usage: pgcluu_collectd [options] output_dir

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

    options:

      -B, --enable-buffercache enable buffercache statistics if pg_buffercache
                               extension is installed.
      -c, --capture            create a snapshot of the PostgreSQL installation
                               into tmp/pgcluu_capture.tar.gz.
      -C, --end-counter=NUM    terminate program after NUM reports.
      -d, --dbname=DATABASE    database name to connect to. Default to current user.
      -D, --daemonize          detach from console and enter in daemon mode.
      -E, --end-after=SECOND   self terminate program after a given number of seconds.
                               Can be written: 7200 or 120M or 2H, for days use 7D for
                               example to stop collecting data after seven days. 
      -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.
      -M, --max-size=SIZE      self terminate program when the size of the output dir
                               exceed a given size. Can be written: 2GB or 2000MB. 
      -p, --port=PORT          database port(s) to connect to. Defaults to 5432.
      -P, --psql=BIN           path to the psql command. Default: psql.
      -Q, --no-statement       do not collect queries statistics from pg_stat_statements.
      -r, --rotate-daily       force the daily rotation of data files.
      -R, --rotate-hourly      force the hourly rotation of data files.
      -s, --sar=BIN            path to sar sysstat command. Default: sar.
      -S, --disable-sar        disable collect of system statistics with sar.
      -T, --notablespace       disable lookup at tablespace when the connect user
                               is not superuser to avoid printing an error message.
      -U, --dbuser=USERNAME    database user to connect as. Default to current user.
      -v, --verbose            Print out debug informations.
      -V, --version            Show pgcluu_collectd version and exit.
      -W, --password=pass      database password.
      -z, --compress           force compression of rotated data files.
      --included-db=DATABASE   collect statistics only for those databases present
                               in a comma separated list of database names.
      --list-metric            list available metrics actions that can be performed.
      --sysinfo                get operating system information and exit (sysinfo.txt).
      --no-sysinfo             do not collect operating system information at all. 
      --no-database            do not collect database statistics at all. 
      --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.
      --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.
      --exclude-time RANGE     exclude a laps of time by giving the start and end
                               hours.
      --help                   print usage

    Use those options to execute sar on the remote host defined by the -h
    option, otherwise it will be executed locally:

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

    For example, as postgres user to monitor locally a full PostgreSQL
    cluster:

            mkdir /tmp/stat_db1/
            pgcluu_collectd -D -i 60 /tmp/stat_db1/

    to collect statistics from pgbouncer too, and limit database statistics
    to a single database:

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

    to disable statistics collect between 22:30 and 06:30 the next day:

            pgcluu_collectd -D -i 60 /tmp/stat_db1/ --exclude-time "22:30-06:30"

    to collect statistics from a remote server:

            pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.0.0.1 -U postgres --disable-sar

    the same but collecting system statistics using remote sar calls:

            pgcluu_collectd -D -i 60 /tmp/stat_db1/ -h 10.0.0.1 -U postgres --enable-ssh 
                    --ssh-user postgres --ssh-identity /var/lib/postgresql/.ssh/id_rsa.pub

    You may need a .pgpass and be able to establish passwordless ssh
    connections to be able to collect statistics from remote hosts.

    Then after some time and activities on the database, stop the daemon as
    follow:

            pgcluu_collectd -k

    or by sending sigterm to the pgcluu_collectd's pid.

  Statistics files
    The output directory with all statistics collected should look likes:

            /tmp/stat_db1/
            |--- pgbouncer.ini
            |--- pgbouncer_req_stats.csv
            |--- pgbouncer_stats.csv
            |--- pg_class_size.csv
            |--- pg_database_buffercache.csv
            |--- pg_database_isdirty.csv
            |--- pg_database_size.csv
            |--- pg_database_usagecount.csv
            |--- pg_hba.conf
            |--- pg_ident.conf
            |--- pg_relation_buffercache.csv
            |--- pg_settings.csv
            |--- pg_db_role_setting.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_locks.csv
            |--- pg_stat_missing_fkindexes.csv
            |--- pg_stat_redundant_indexes.csv
            |--- pg_stat_replication.csv
            |--- pg_stat_statements.csv
            |--- pg_stat_unused_indexes.csv
            |--- pg_stat_user_functions.csv
            |--- pg_stat_user_indexes.csv
            |--- pg_stat_user_tables.csv
            |--- pg_tablespace_size.csv
            |--- pg_xlog_stat.csv
            |--- postgresql.conf
            |--- sar_stats.dat
            |--- sysinfo.txt

    Then now you can proceed with pgcluu to generate reports.

  Rotation and compression
    When used the --rotate-daily or --rotate-hourly commands line option
    will force pgcluu_collectd to rotate daily or hourly all statistic's
    files. In this case, statistics files will be created in a subdirectory
    based on rotation frequency, output_dir/year/month/day[/hour].

    To save filesystem space it is possible to enable compression of all
    rotated files during the rotation process. Just activate the -z or
    --compress command line option.

  Capture mode
    The goal of this mode is to be able to obtain a simple report about the
    PostgreSQL installation without collected metrics others than database
    and tablespace size. This report can be use by ITs to better understand
    the configuration and things that need to be tuned.

    To enable this mode, just run pgcluu_collectd with the single option -c
    or --capture. Other command line options will not be taken in account.
    pgcluu_collectd will create e temporary directory /tmp/pgcluu_capture to
    store temporary data and will removed if after building a compressed tar
    archive: /tmp/pgcluu_capture.tar.gz. This is this archive that can be
    used with pgcluu to build a snapshot report of the instance. pgcluu will
    automatically detect this mode.

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 utilization. If you just want to make
    a report from a sar file use the -i or -I options.

    usage: pgcluu [options] [-i sar_file [--from-sa-file] | -I sadc_file]
    [input_dir]

            input_dir: directory where pgcluu_collectd or pgstats and sar data
                       files are stored.

    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 or give a comma separated
    list of database name. -D, --device-only dev only report I/O stats for a
    particular device You can use it multiple time or give a comma separated
    list of device name, ex: sda,sdc. -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.
    -n, --top-number Top number of tables or indexes I/O stats to show.
    Default is set to top 10. Set it to 0 to show all. -N, --network-only
    iface only report stats for a particular network interface. You can use
    it multiple time or give a comma separated list of network ingterface,
    ex: eth0,eth1. -o, --output=DIR output directory -r, --reverse-date By
    default pgcluu look at mm/dd/yy format in sar file. When enabled pgcluu
    will look at dd/mm/yy format. -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,
    --with-table table Only report for the whole tables and the given table
    name. You can use it multiple time or give a comma separated list of
    database name. -T, --no-table Do not report statistics related to
    tables. -v, --verbose Print out debug informations. -V, --version Show
    pgcluu_collectd version and exit. -z, --timezone +/-XX Set the number of
    hour(s) from GMT of the timezone. Use this to adjust date/time from the
    sar output, pgcluu use GMT time to draw charts. --from-sa-file instruct
    pgcluu that file specified by the -i option uses the standard system
    activity daily data file. --help print usage

    For example, you can generate all HTML reports from data files stored
    into /tmp/stat_db1/ with the following commands:

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

    If you just want reports of some databases, use the following:

            pgcluu -o /tmp/report_db1/ /tmp/stat_db1/ --db-only "db1,db2,db3"

    If you just want to create a report from a sar output file:

            sar -p -A 10 60 > /root/my_sar_file.txt
            pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt

    or from a daily sa file:

            sar -p -A -f /var/log/sa/sa18 > /root/my_sar_file.txt
            pgcluu -o /tmp/report_sar/ -i /root/my_sar_file.txt --from-sa-file

    and directly from a sa binary file:

            pgcluu -o /tmp/report_sar/ -i /var/log/sysstat/sa22

    or the same sa text file if you don't have the same version of sysstat:

            pgcluu -o /tmp/report_sar/ -i /var/log/sysstat/sar22 --from-sa-file

LICENSE
    Copyright (c) 2012-2018, 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/