#!/usr/bin/perl
# This program is open source, licensed under the PostgreSQL License.
# For license terms, see the LICENSE file.
#
# Copyright (C) 2012-2014: Open PostgreSQL Monitoring Development Group

=head1 check_pgactivity

check_pgactivity - PostgreSQL plugins for Nagios

=head2 SYNOPSIS

  check_pgactivity {-w|--warning THRESHOLD} {-c|--critical THRESHOLD} [-s|--service SERVICE ] [-h|--host HOST] [-U|--username ROLE] [-p|--port PORT] [-d|--dbname DATABASE] [-S|--dbservice SERVICE_NAME] [-P|--psql PATH] [--debug] [--status-file FILE] [--path PATH] [-t|--timemout TIMEOUT]
  check_pgactivity [--list]
  check_pgactivity [--help]

=head2 DESCRIPTION

check_pgactivity is dedicated to monitoring PostgreSQL cluster from Nagios. It
offers many different services and returns various usefull perfdata for
metrology.

=cut

use vars qw($VERSION $PROGRAM);

use strict;
use warnings;

use POSIX;
use Data::Dumper;
use File::Basename;
use File::Spec;
use File::Temp;
use Getopt::Long qw(:config bundling no_ignore_case_always);
use List::Util qw(max);
use Pod::Usage;
use Scalar::Util qw(looks_like_number);
use Storable qw(store retrieve);

setlocale( LC_ALL, 'C' );

$| = 1;

$VERSION = '1.13';
$PROGRAM = 'check_pgactivity';

my $PG_VERSION_MIN = 70400;
my $PG_VERSION_74  = 70400;
my $PG_VERSION_80  = 80000;
my $PG_VERSION_81  = 80100;
my $PG_VERSION_82  = 80200;
my $PG_VERSION_83  = 80300;
my $PG_VERSION_84  = 80400;
my $PG_VERSION_90  = 90000;
my $PG_VERSION_91  = 90100;
my $PG_VERSION_92  = 90200;
my $PG_VERSION_93  = 90300;
my $PG_VERSION_MAX = 9999999;

# Available services and descriptions.
#
# The referenced sub called to exec each service takes one parameters: a
# reference to the arguments hash (%args)
#
# Note that we can not use perl prototype for these subroutine as they are
# called indirectly (thus the args given by references).

my %services = (
    # 'service_name' => {
    #    'sub'     => sub reference to call to run this service
    #    'desc'    => 'a desctiption of the service'
    # }

    'autovacuum' => {
        'sub'  => \&check_autovacuum,
        'desc' => 'Check the autovacuum activity.'
    },
    'backends' => {
        'sub'  => \&check_backends,
        'desc' => 'Number of connections, compared to max_connections.'
    },
    'backends_status' => {
        'sub'  => \&check_backends_status,
        'desc' => 'Number of connections in relation to their status.'
    },
    'database_size' => {
        'sub'  => \&check_database_size,
        'desc' => 'Variation of database sizes.',
    },
    'wal_files' => {
        'sub'  => \&check_wal_files,
        'desc' => 'Total number of WAL files.',
    },
    'ready_archives' => {
        'sub'  => \&check_ready_archives,
        'desc' => 'Check the number of wal files ready to archive.',
    },
    'last_vacuum' => {
        'sub'  => \&check_last_vacuum,
        'desc' =>
            'Check the oldest vacuum (from autovacuum or not) on the database.',
    },
    'last_analyze' => {
        'sub'  => \&check_last_analyze,
        'desc' =>
            'Check the oldest analyze (from autovacuum or not) on the database.',
    },
    'locks' => {
        'sub'  => \&check_locks,
        'desc' => 'Check the number of locks on the hosts.'
    },
    'oldest_2pc' => {
        'sub'  => \&check_oldest_2pc,
        'desc' => 'Check the oldest two phase commit transaction.'
    },
    'oldest_idlexact' => {
        'sub'  => \&check_oldest_idlexact,
        'desc' => 'Check the oldest idle transaction.'
    },
    'longest_query' => {
        'sub'  => \&check_longest_query,
        'desc' => 'Check the longest running query.'
    },
    'bgwriter' => {
        'sub'  => \&check_bgwriter,
        'desc' => 'Check the bgwriter activity.',
    },
    'archive_folder' => {
        'sub'  => \&check_archive_folder,
        'desc' => 'Check archives in given folder.',
    },
    'minor_version' => {
        'sub'  => \&check_minor_version,
        'desc' => 'Check if the PostgreSQL minor version is the latest one.',
    },
    'hot_standby_delta' => {
        'sub'  => \&check_hot_standby_delta,
        'desc' => 'Check delta in bytes between a master and its Hot standbys.',
    },
    'streaming_delta' => {
        'sub'  => \&check_streaming_delta,
        'desc' => 'Check delta in bytes between a master and its standbys in streaming replication.',
    },
    'hit_ratio' => {
        'sub'  => \&check_hit_ratio,
        'desc' => 'Check hit ratio on databases.'
    },
    'backup_label_age' => {
        'sub'  => \&check_backup_label_age,
        'desc' => 'Check age of backup_label file.',
    },
    'connection' => {
        'sub'  => \&check_connection,
        'desc' => 'Perform a simple connection test.'
    },
    'custom_query' => {
        'sub'  => \&check_custom_query,
        'desc' => 'Perform the given user query.'
    },
    'configuration' => {
        'sub'  => \&check_configuration,
        'desc' => 'Check the most important settings.',
    },
    'btree_bloat' => {
        'sub'  => \&check_btree_bloat,
        'desc' => 'Check B-tree index bloat.'
    },
    'max_freeze_age' => {
        'sub'  => \&check_max_freeze_age,
        'desc' => 'Check oldest database in transaction age.'
    },
    'is_master' => {
        'sub'  => \&check_is_master,
        'desc' => 'Check if cluster is in production.'
    },
    'is_hot_standby' => {
        'sub'  => \&check_is_hot_standby,
        'desc' => 'Check if cluster is a hot standby.'
    },
    'pga_version' => {
        'sub'  => \&check_pga_version,
        'desc' => 'Check the version of this check_pgactivity script.'
    },
    'is_replay_paused' => {
        'sub'  => \&check_is_replay_paused,
        'desc' => 'Check if the replication is paused.'
    },
    'table_bloat' => {
        'sub'  => \&check_table_bloat,
        'desc' => 'Check tables bloat.'
    },
);


=over

=item B<-s>, B<--service> SERVICE

The nagios service to run. See section SERVICES for a description of available
services or option C<--list> for a short service and description list.

=item B<-h>, B<--host> HOST

Database server host or socket directory (default: "localhost").

=item B<-U>, B<--username> ROLE

Database user name (default: "postgres").

=item B<-p>, B<--port> PORT

Database server port (default: "5432").

=item B<-d>, B<--dbname> DATABASE

Database name to connect to (default: "postgres").

=item B<-S>, B<--dbservice> SERVICE_NAME

The service name to use from pg_service.conf to connect.

=item B<-w>, B<--warning> THRESHOLD

The warning threshold.

=item B<-c>, B<--critical> THRESHOLD

The critical threshold.

=item B<--tmpdir> DIRECTORY

Path to a directory where the script can create temporary files. The
script rely on system default temporary directory if possible.

=item B<-P>, B<--psql> FILE

Path to the C<psql> executable (default: "psql").

=item B<--status-file> PATH

PATH to the file where service status information will be kept between two
call. Default to check_pgactivity.data in the same directory of the script.

=item B<-t>, B<--timeout> TIMEOUT

Timeout to use (default: "30s"). It can be specified as raw (in second) or as
an interval. This timeout will be used as statement_timeout for psql and URL
timeout for minor_version service.

=item B<--list>

List available services.

=item B<-V>, B<--version>

Print version and exit.

=item B<--debug>

Print some debug messages.

=item B<-?>, B<--help>

Show this help page.

=back

=cut

my %args = (
    'service'               => undef,
    'host'                  => undef,
    'username'              => undef,
    'port'                  => undef,
    'dbname'                => undef,
    'dbservice'             => undef,
    'warning'               => undef,
    'critical'              => undef,
    'exclude'               => [],
    'tmpdir'                => File::Spec->tmpdir(),
    'psql'                  => undef,
    'path'                  => undef,
    'status-file'           => dirname(__FILE__) . '/check_pgactivity.data',
    'query'                 => undef,
    'type'                  => undef,
    'reverse'               => 0,
    'work_mem'              => undef,
    'maintenance_work_mem'  => undef,
    'shared_buffers'        => undef,
    'wal_buffers'           => undef,
    'checkpoint_segments'   => undef,
    'effective_cache_size'  => undef,
    'no_check_autovacuum'   => 0,
    'no_check_fsync'        => 0,
    'no_check_enable'       => 0,
    'no_check_track_counts' => 0,
    'ignore-wal-size'       => 0,
    'suffix'                => '',
    'slave'                 => [],
    'list'                  => 0,
    'help'                  => 0,
    'debug'                 => 0,
    'timeout'               => '30s',
);

# Set name of the program without path*
my $orig_name = $0;
$0 = $PROGRAM;

# Die on kill -1, -2, -3 or -15
$SIG{'HUP'} = $SIG{'INT'} = $SIG{'QUIT'} = $SIG{'TERM'} = \&terminate;

# handle SIG
sub terminate() {
    my ($signal) = @_;
    die ("SIG $signal caught");
}

# print the version and exit
sub version() {
    print "check_pgactivity version $VERSION\n";

    exit 0;
}

# List services that can be performed
sub list_services() {

    print "List of available services:\n\n";

    foreach my $service ( sort keys %services ) {
        printf "\t%-17s\t%s\n", $service, $services{$service}{'desc'};
    }

    exit 0;
}

# Record the given ref content for the given host in a file on disk.
# The file is defined by argument "--status-file" on command line. By default:
#
#  dirname(__FILE__) . '/check_pgactivity.data'
#
# Format of data in this file is:
#   {
#     "${host}${port}" => {
#       "$name" => ref
#     }
#   }
# data can be retrieved later using the "load" sub.
#
# Parameters are :
#  * the host structure ref that holds the "host" and "port" parameters
#  * the name of the structure to save
#  * the ref of the structure to save
#  * the path to the file storage
sub save($$$$) {
    my $host    = shift;
    my $name    = shift;
    my $ref     = shift;
    my $storage = shift;
    my $all     = {};
    my $hostkey;

    if (defined $host->{'dbservice'}) {
        $hostkey = "$host->{'dbservice'}";
    }
    else {
        $hostkey = "$host->{'host'}$host->{'port'}";
    }

    $all = retrieve($storage) if -f $storage;

    $all->{$hostkey}{$name} = $ref;

    store( $all, $storage )
        or die "Can't store data in '$storage'!\n";
}

# Load the given ref content for the given host from the file on disk.
#
# See "save" sub comments for more info.
# Parameters are :
#  * the host structure ref that holds the "host" and "port" parameters
#  * the name of the structure to load
#  * the path to the file storage
sub load($$$) {
    my $host    = shift;
    my $name    = shift;
    my $storage = shift;
    my $hostkey;
    my $all;

    if (defined $host->{'dbservice'}) {
        $hostkey = "$host->{'dbservice'}";
    }
    else {
        $hostkey = "$host->{'host'}$host->{'port'}";
    }

    return undef unless -f $storage;

    $all = retrieve($storage);

    return $all->{$hostkey}{$name};
}

# Returns formated size string with units.
# Takes a size in bytes as parameter.
sub to_size($) {
    my $val  = shift;
    my @units = qw{B kB MB GB TB PB EB};
    my $size = '';
    my $mod = 0;
    my $i;

    return $val if $val =~ /^(-?inf)|(NaN$)/i;

    $val = int($val);

    for ( $i=0; $i < 6 and $val > 1024; $i++ ) {
        $mod = $val%1024;
        $val = int( $val/1024 );
    }

    $val = "$val.$mod" unless $mod == 0;

    return "${val}$units[$i]";
}

# Returns formated time string with units.
# Takes a duration in seconds as parameter.
sub to_interval($) {
    my $val      = shift;
    my $interval = '';

    return $val if $val =~ /^-?inf/i;

    $val = int($val);

    if ( $val > 604800 ) {
        $interval = int( $val / 604800 ) . "w ";
        $val %= 604800;
    }

    if ( $val > 86400 ) {
        $interval .= int( $val / 86400 ) . "d ";
        $val %= 86400;
    }

    if ( $val > 3600 ) {
        $interval .= int( $val / 3600 ) . "h";
        $val %= 3600;
    }

    if ( $val > 60 ) {
        $interval .= int( $val / 60 ) . "m";
        $val %= 60;
    }

    $interval .= "${val}s" if $val > 0;

    return $interval;
}

=head2 THRESHOLDS

THRESHOLD given as warning and critical values can either be a raw number, a
percentage, an interval or a size. Each available service supports one or more
form (eg. a size and a percentage).

=over

=item B<Percentage>

If threshold is a percentage, the value should finish with a '%' without space
with the actual value. Eg.: 95%.

=item B<Interval>

If THRESHOLD is an interval, the following units are accepted (not case
sensitive): s (second), m (minute), h (hour), d (day). You can use more than
one unit per give value. If not set, the last unit is in seconds. Eg.: "1h 55m
6" = "1h55m6s".

=cut


sub is_size($){
    my $str_size = lc( shift() );
    return 1 if $str_size =~ /^\s*[0-9]+([kmgtpez][bo]?)?\s*$/ ;
    return 0;
}


sub is_time($){
    my $str_time = lc( shift() );
    return 1 if ( $str_time
        =~ /^(\s*([0-9]\s*[smhd]?\s*))+$/
    );
    return 0;
}

# Takes an interval (with units) as parameter and returns a duration in second.

sub get_time($) {
    my $str_time = lc( shift() );
    my $ts       = 0;
    my @date;

    die(      "Malformed interval: «$str_time»!\n"
            . "Authorized unit are: dD, hH, mM, sS\n" )
        unless is_time($str_time);

    # no bad units should exists after this line!

    @date = split( /([smhd])/, $str_time );

LOOP_TS: while ( my $val = shift @date ) {

        $val = int($val);
        die("Wrong value for an interval: «$val»!") unless defined $val;

        my $unit = shift(@date) || '';

        if ( $unit eq 'm' ) {
            $ts += $val * 60;
            next LOOP_TS;
        }

        if ( $unit eq 'h' ) {
            $ts += $val * 3600;
            next LOOP_TS;
        }

        if ( $unit eq 'd' ) {
            $ts += $val * 86400;
            next LOOP_TS;
        }

        $ts += $val;
    }

    return $ts;
}

=pod

=item B<Size>
If THRESHOLD is a size, the following units are accepted (not case sensitive):
b (Byte), k (KB), m (MB), g (GB), t (TB), p (PB), e (EB) or Z (ZB). Only
integers are accepted. Eg. C<1.5MB> will be refused, use C<1500kB>.

The factor between units is 1024 Bytes. Eg. C<1g = 1G = 1024*1024*1024.>

=back

=cut

# Takes a size with unit as parameter and returns it in bytes.
# If unit is '%', use the second parameter to compute the size in byte.
sub get_size($;$) {
    my $str_size = shift;
    my $size     = 0;
    my $unit     = '';

    die "Only integers are accepted as size. Ajust the unit for your need."
        if $str_size =~ /[.,]/;

    $str_size =~ /^([0-9]+)(.*)$/;

    $size = int($1);
    $unit = lc($2);

    return $size unless $unit ne '';

    if ( $unit eq '%' ) {
        my $ratio = shift;

        die("Can not compute a ratio without the factor!")
            unless defined $unit;

        return int( $size * $ratio / 100 );
    }

    return $size           if $unit eq 'b';
    return $size * 1024    if $unit =~ '^k[bo]?$';
    return $size * 1024**2 if $unit =~ '^m[bo]?$';
    return $size * 1024**3 if $unit =~ '^g[bo]?$';
    return $size * 1024**4 if $unit =~ '^t[bo]?$';
    return $size * 1024**5 if $unit =~ '^p[bo]?$';
    return $size * 1024**6 if $unit =~ '^e[bo]?$';
    return $size * 1024**7 if $unit =~ '^z[bo]?$';

    die("Unknown size unit: $unit");
}


=head2 CONNEXIONS

check_pgactivity allows two different of connexion specification: by service or
by specifying values for host, user, port and database. Moreover, some services
can run on multiple host or needs to connect to multiple ones.

You must specify one of the parameters bellow if the service need to connect
to your PostgreSQL instance. In other words, check_pgactivity will NOT look for
the libpq environment variables.

The rules with connexions parameters are:

=over

=item * Parameter C<--dbservice SERVICE_NAME>

Define a new host using the given service. Multiple hosts can be defined by
giving multiple services seperated by a comma. Eg.

  --dbservice service1,service2

=item * Parameters C<--host HOST>, C<--port PORT>, C<--user ROLE> or C<--dbname DATABASE>

One of these parameters is enough to defines a new host. If some other
parameters are missing, default values are used.

If multiple values are given, define as many host as maximum given values.

Values are associated by position. Eg.:

  --host h1,h2 --port 5432,5433

Means "host=h1 port=5432" and "host=h2 port=5433".

If the number of values is different between parameters, any host that miss a
parameter will use the first given value for this parameter. Eg.:

  --host h1,h2 --port 5433

Means: "host=h1 port=5433" and "host=h2 port=5433".

=item * Services are define first

As instance, giving:

  --dbservice s1 --host h1 --port 5433

Means "service=s1" and "host=h1 port=5433" in this order. If the service
supports only one host, the second is ignored

=item * Mutual exclusion between both methods

You can not overwrite services connexions variables with parameters C<--host HOST>, C<--port PORT>, C<--user ROLE> or C<--dbname DATABASE>

=back

=cut

sub parse_hosts(\%) {
    my %args = %{ shift() };
    my @hosts = ();

    if (defined $args{'dbservice'}) {
        push
            @hosts,
            {   'dbservice' => $_,
                'name'      => "service:$_",
                'pgversion' => undef
            }
        foreach split /,/, $args{'dbservice'};
    }


    # Add as many hosts than necessary depending on given parameters
    # host/port/db/user.
    # Any missing parameters will be set to its default value.
    if (defined $args{'host'}
        or defined $args{'username'}
        or defined $args{'port'}
        or defined $args{'dbname'}
    ) {
        $args{'host'} = $ENV{'PGHOST'} || 'localhost'
            unless defined $args{'host'};
        $args{'username'} = $ENV{'PGUSER'} || 'postgres'
            unless defined $args{'username'};
        $args{'port'} = $ENV{'PGPORT'} || '5432'
            unless defined $args{'port'};
        $args{'dbname'} = $ENV{'PGDATABASE'} || 'template1'
            unless defined $args{'dbname'};

        my @dbhosts = split( /,/, $args{'host'} );
        my @dbnames = split( /,/, $args{'dbname'} );
        my @dbusers = split( /,/, $args{'username'} );
        my @dbports = split( /,/, $args{'port'} );
        my $nbhosts = max $#dbhosts, $#dbnames, $#dbusers, $#dbports;

        # Take the first value for each connection properties as default.
        # eg. "-h localhost -p 5432,5433" gives two hosts:
        #    * localhost:5432
        #    * localhost:5433
        for ( my $i = 0; $i <= $nbhosts; $i++ ) {
            push(
                @hosts,
                {   'host'      => $dbhosts[$i] || $dbhosts[0],
                    'port'      => $dbports[$i] || $dbports[0],
                    'db'        => $dbnames[$i] || $dbnames[0],
                    'user'      => $dbusers[$i] || $dbusers[0],
                    'pgversion' => undef
                }
            );

            $hosts[-1]{'name'} = sprintf('host:%s port:%d db:%s',
                $hosts[-1]{'host'}, $hosts[-1]{'port'}, $hosts[-1]{'db'}
            );
        }
    }

    dprint ('Hosts: '. Dumper(\@hosts));

    return \@hosts;
}



# Execute a query on a host.
# Params:
#   * host
#   * query
#   * (optionnal) database
# The result is an array of array:
#   [
#     [column1, ...] # line1
#     ...
#   ]
sub query($$;$) {
    my $host  = shift;
    my $query = shift;
    my $db    = shift;
    my @res   = ();
    my $res   = '';
    my $RS    = chr(30); # ASCII RS  (record separator)
    my $FS    = chr(3);  # ASCII ETX (end of text)
    my $tmpfile;
    my $psqlcmd;
    my $rc;

    local $/ = undef;

    delete $ENV{PGSERVICE};
    delete $ENV{PGDATABASE};
    delete $ENV{PGHOST};
    delete $ENV{PGPORT};
    delete $ENV{PGUSER};
    delete $ENV{PGOPTIONS};

    $ENV{PGDATABASE} = $host->{'db'}        if defined $host->{'db'};
    $ENV{PGSERVICE}  = $host->{'dbservice'} if defined $host->{'dbservice'};
    $ENV{PGHOST}     = $host->{'host'}      if defined $host->{'host'};
    $ENV{PGPORT}     = $host->{'port'}      if defined $host->{'port'};
    $ENV{PGUSER}     = $host->{'user'}      if defined $host->{'user'};
    $ENV{PGOPTIONS}  = '-c client_min_messages=error -c statement_timeout=' . get_time($args{'timeout'}) * 1000;

    dprint ("Query: $query\n");
    dprint ("Env. service: $ENV{PGSERVICE} \n") if defined $host->{'dbservice'};
    dprint ("Env. host   : $ENV{PGHOST}    \n") if defined $host->{'host'};
    dprint ("Env. port   : $ENV{PGPORT}    \n") if defined $host->{'port'};
    dprint ("Env. user   : $ENV{PGUSER}    \n") if defined $host->{'user'};
    dprint ("Env. db     : $ENV{PGDATABASE}\n") if defined $host->{'db'};

    $tmpfile = File::Temp->new(
        TEMPLATE => 'check_pga-XXXXXXXX',
        DIR      => $args{'tmpdir'}
    );
    # Always add a comma to make sure psql doesn't wait for the rest of
    # the query string.
    print $tmpfile "$query;";

    $psqlcmd  = qq{ $args{'psql'} --set "ON_ERROR_STOP=1" }
              . qq{ -qXAtf $tmpfile -R $RS -F $FS };
    $psqlcmd .= qq{ --dbname='$db' } if defined $db;
    $res      = qx{ $psqlcmd 2>&1 };
    $rc       = $?;

    dprint("Query rc: $rc\n");
    dprint( sprintf( "  stderr (%u): «%s»\n", length $res, $res ) )
        if $rc;

    exit unknown('CHECK_PGACTIVITY',
        [ "Query fail !\n" . $res ]
    ) unless $rc == 0;

    if (defined $res) {
        chop $res;

        push @res, [ split(chr(3) => $_, -1) ]
            foreach split (chr(30) => $res, -1);
    }

    dprint( "Query result: ". Dumper( \@res ) );

    return \@res;
}

# Select the query appropriate query amongs an hash of query according to the
# backend version and execute it. Same argument order than in "query" sub.
# Hash of query must be of this form:
#   {
#     pg_version_num => $query1,
#     ...
#   }
#
# Where pg_version_num is the minimum PostgreSQL version which can run the
# query. The given versions are in numric version. See "set_pgversion" about
# how to compute a PostgreSQL num version, or globals $PG_VERSION_*.
sub query_ver($\%;$) {
    my $host    = shift;
    my %queries = %{ shift() };

    # shift returns undef if he db is not given. The value is then set in
    # "query" sub
    my $db = shift;

    set_pgversion($host);

    foreach my $ver ( sort { $b cmp $a } keys %queries ) {
        return query( $host, $queries{$ver}, $db )
            if ( $ver <= $host->{'version_num'} );
    }

    return undef;
}

# Returns an array (not sorted) with all databases existing in given host but
# templates and "postgres" one.
sub get_all_dbname($) {
    my @dbs;

    push @dbs => $_->[0] foreach (
        @{  query( shift, q{
                SELECT datname
                FROM pg_database
                WHERE NOT datistemplate
                    AND datallowconn
                    AND datname <> 'postgres'
                ORDER BY 1
            })
        }
    );

    return \@dbs;
}

# Query and set the version for the given host
sub set_pgversion($) {
    my $host = shift;

    unless ( $host->{'version'} ) {

        my $rs = query( $host, q{SELECT current_setting('server_version')} );

        if ( $? != 0 ) {
            dprint("FATAL: psql error, $!\n");
            exit 1;
        }

        $host->{'version'} = $rs->[0][0];

        chomp( $host->{'version'} );
    }

    if ( $host->{'version'} =~ /^(\d+)\.(\d+)(.(\d+))?/ ) {
        $host->{'version_num'} = int($1) * 10000 + int($2) * 100;

        # alpha/beta version have no minor version number
        $host->{'version_num'} += int($4) if defined $4;

        dprint(sprintf ("host %s is version %s/%s\n",
            $host->{'name'},
            $host->{'version'},
            $host->{'version_num'})
        );

        return;
    }

    return 1;
}

# Check host compatibility
sub is_compat($$$;$) {
    my $host    = shift;
    my $service = shift;
    my $min     = shift;
    my $max     = shift() || $PG_VERSION_MAX;
    my $ver;

    set_pgversion($host);
    $ver = 100*int($host->{'version_num'}/100);

    unless (
        $ver >= $min
        and $ver <= $max
    ) {
        warn sprintf "Service %s is not compatible with host '%s' (v%s).\n",
            $service, $host->{'name'}, $host->{'version'};
        return 0;
    }

    return 1;
}

sub dprint {
    return unless $args{'debug'};
    foreach (@_) {
        print "DEBUG: $_";
    }
}

sub unknown($;$$$) {
    return output( 3, $_[0], $_[1], $_[2], $_[3] );
}

sub critical($;$$$) {
    return output( 2, $_[0], $_[1], $_[2], $_[3] );
}

sub warning($;$$$) {
    return output( 1, $_[0], $_[1], $_[2], $_[3] );
}

sub ok($;$$$) {
    return output( 0, $_[0], $_[1], $_[2], $_[3] );
}

sub output ($$;$$$) {
    my $rc  = shift;
    my $ret = shift;
    my $state;
    my @msg;
    my @perfdata;
    my @longmsg;

    $ret .= " OK"       if $rc == 0;
    $ret .= " WARNING"  if $rc == 1;
    $ret .= " CRITICAL" if $rc == 2;
    $ret .= " UNKNOWN"  if $rc == 3;

    @msg      = @{ $_[0] } if defined $_[0];
    @perfdata = @{ $_[1] } if defined $_[1];
    @longmsg  = @{ $_[2] } if defined $_[2];

    $ret .= ": ".  join( ', ', @msg )     if @msg;
    $ret .= " | ". join( ' ', @perfdata ) if @perfdata;
    $ret .= "\n". join( ' ', @longmsg ) if @longmsg;

    print $ret;

    return $rc;
}

=head2 SERVICES

Here is the list, descriptions and parameters of available services.

=over

=item B<autovacuum> (8.1+)

Check the autovacuum activity on the cluster.

Perfdata contains the age of oldest autovacuum and the number of worker by kind
(VACUUM, VACUUM ANALYZE, ANALYZE, VACUUM FREEZE).

Thresholds, if any, are ignored.

=cut

sub check_autovacuum {

    my @rs;
    my @perfdata;
    my @msg;
    my @longmsg;
    my @hosts;
    my %args         = %{ $_[0] };
    my $me           = 'POSTGRES_AUTOVACUUM';
    my $oldest       = undef;
    my $numautovac  = 0;
    my $max_workers = "NaN";
    my %activity     = (
        'VACUUM'            => 0,
        'VACUUM_ANALYZE'    => 0,
        'ANALYZE'           => 0,
        'VACUUM_FREEZE'     => 0
    );

    my %queries      = (
        # field current_query, not autovacuum_max_workers
        $PG_VERSION_81 => q{
            SELECT current_query,
                extract(EPOCH FROM now()-query_start)::bigint,
                'NaN'
            FROM pg_stat_activity
            WHERE current_query LIKE 'autovacuum: %'
            ORDER BY query_start ASC
        },
        # field current_query, autovacuum_max_workers
        $PG_VERSION_83 => q{
            SELECT a.current_query,
                extract(EPOCH FROM now()-a.query_start)::bigint,
                s.setting
            FROM
                (SELECT current_setting('autovacuum_max_workers') AS setting) AS s
            LEFT JOIN (
                SELECT * FROM pg_stat_activity
                WHERE current_query LIKE 'autovacuum: %'
                ) AS a ON true
            ORDER BY query_start ASC
        },
        # field query, still autovacuum_max_workers
        $PG_VERSION_92 => q{
            SELECT a.query,
                extract(EPOCH FROM now()-a.query_start)::bigint,
                s.setting
            FROM
                (SELECT current_setting('autovacuum_max_workers') AS setting) AS s
            LEFT JOIN (
                SELECT * FROM pg_stat_activity
                WHERE query LIKE 'autovacuum: %'
                ) AS a ON true
            ORDER BY a.query_start ASC
        }
    );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "autovacuum".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'autovacuum', $PG_VERSION_81 or exit 1;

    @rs = @{ query_ver( $hosts[0], %queries ) };

    REC_LOOP: foreach my $r (@rs) {
        if ( not defined $oldest ){
            $max_workers = $r->[2];
            next REC_LOOP if ( $r->[1] eq "" );
            $oldest = $r->[1];
        }
        $numautovac++;
        if ( $r->[0] =~ '(to prevent wraparound)$' ) {
            $activity{'VACUUM_FREEZE'}++;
        } else {
            if ( $r->[0] =~ '^autovacuum: VACUUM ANALYZE' ) {
                $activity{'VACUUM_ANALYZE'}++;
            } elsif ( $r->[0] =~ 'autovacuum: VACUUM' ) {
                $activity{'VACUUM'}++;
            } else {
                $activity{'ANALYZE'}++;
            };
        }
        $r->[0] =~ s/autovacuum: //;
        push @longmsg, $r->[0];
    }

    $oldest = 'NaN' if not defined ( $oldest );

    @perfdata = map { "$_=$activity{$_}" } keys %activity;
    push @perfdata, "oldest_autovacuum=$oldest" . "s";
    push @perfdata, "max_workers=$max_workers";
    push @msg, "Number of autovacuum: $numautovac";
    push @msg, "Oldest autovacuum: " . to_interval($oldest) if $oldest ne "NaN";

    return ok( $me, \@msg , \@perfdata, \@longmsg );

}

=item B<backends> (all)

Check the total number of connexions on the cluster.

Perfdata contains the number of connexions per database.

Critical and Warning thresholds accept either a raw number or a percentage (eg.
80%). When a threshold is in percent, it is compared to the cluster parameter
C<max_connections>.

=cut

sub check_backends {

    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my %args         = %{ $_[0] };
    my $me           = 'POSTGRES_BACKENDS';
    my $num_backends = 0;
    my $sql          = q{
        SELECT s.datname, s.numbackends,
            current_setting('max_connections')
        FROM pg_stat_database AS s
            JOIN pg_database d ON d.oid=s.datid
        WHERE d.datallowconn
    };

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    # warning and critical must be raw or %.
    pod2usage(
        -message => "FATAL: critical and warning thresholds only accept raw numbers or %.",
        -exitval => 127
    ) unless $args{'warning'}  =~ m/^([0-9.]+)%?$/
        and  $args{'critical'} =~ m/^([0-9.]+)%?$/;

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "backends".',
        -exitval => 127
    ) if @hosts != 1;


    @rs = @{ query( $hosts[0], $sql ) };

    $args{'critical'} = int( $rs[0][2] * $1 / 100 )
        if $args{'critical'} =~ /^([0-9.]+)%$/;

    $args{'warning'} = int( $rs[0][2] * $1 / 100 )
        if $args{'warning'} =~ /^([0-9.]+)%$/;

    foreach my $db (@rs) {
        $num_backends += $db->[1];
        push @perfdata,
            "$db->[0]=$db->[1];$args{'warning'};$args{'critical'};0;$db->[2]";
    }

    push @msg => "$num_backends connections on $rs[0][2]";

    return critical( $me, \@msg, \@perfdata )
        if $num_backends >= $args{'critical'};

    return warning( $me, \@msg, \@perfdata )
        if $num_backends >= $args{'warning'};

    return ok( $me, \@msg, \@perfdata );
}


=item B<backends_status> (8.2+)

Check and report the status of the backends. Depending to your PostgreSQL
version, status are: idle, idle in transaction, idle in transaction (aborted)
(>=9.0 only), fastpath function call, active, waiting for lock, undefined,
disabled and insufficient privilege. The last one appears when you are not
allowed to see the status of other connexions.

This service supports argument C<--exclude REGEX> to exclude queries
matching the given regexp from the check. You can give multiple
C<--exclude REGEX>.

Critical and Warning thresholds are optionnal. They accept a list of
'status_label=value' separated by comma. Available labels are idle, idle_xact,
aborted_xact, fastpath, active and waiting. Values are raw numbers and empty
list are forbidden. Here is an example:

    -w 'waiting=5,idle_xact=10' -c 'waiting=20,idle_xact=30'

Perfdata contains the number of backends for each status and the oldest one for
each of them for 8.2+.

Note that the number of backend reported as Nagios message B<includes>
excluded backend.

=cut

sub check_backends_status {
    my @rs;
    my @hosts;
    my @perfdata;
    my @msg_warn;
    my @msg_crit;
    my @exclude_re;
    my %warn;
    my %crit;
    my $max_connections;
    my $num_backends = 0;
    my $me           = 'POSTGRES_BACKENDS_STATUS';
    my %status       = (
        'idle'                          => [0, 0],
        'idle in transaction'           => [0, 0],
        'idle in transaction (aborted)' => [0, 0],
        'fastpath function call'        => [0, 0],
        'waiting for lock'              => [0, 0],
        'active'                        => [0, 0],
        'disabled'                      => [0, 0],
        'undefined'                     => [0, 0],
        'insufficient privilege'        => [0, 0]
    );
    my %queries      = (
        # doesn't support "idle in transaction (aborted)" and xact age
        $PG_VERSION_82 => q{
            SELECT CASE
                    WHEN s.current_query = '<IDLE>'
                        THEN 'idle'
                    WHEN s.current_query = '<IDLE> in transaction'
                        THEN 'idle in transaction'
                    WHEN s.current_query = '<FASTPATH> function call'
                        THEN 'fastpath function call'
                    WHEN s.current_query = '<command string not enabled>'
                        THEN 'disabled'
                    WHEN s.current_query = '<backend information not available>'
                        THEN 'undefined'
                    WHEN s.current_query = '<insufficient privilege>'
                        THEN 'insufficient privilege'
                    WHEN s.waiting = 't'
                        THEN 'waiting for lock'
                    ELSE 'active'
                END AS status,
                NULL, s.current_query, current_setting('max_connections')
            FROM pg_stat_activity AS s
                JOIN pg_database d ON d.oid=s.datid
            WHERE d.datallowconn
        },
        # doesn't support "idle in transaction (aborted)"
        $PG_VERSION_83 => q{
            SELECT CASE
                    WHEN s.current_query = '<IDLE>'
                        THEN 'idle'
                    WHEN s.current_query = '<IDLE> in transaction'
                        THEN 'idle in transaction'
                    WHEN s.current_query = '<FASTPATH> function call'
                        THEN 'fastpath function call'
                    WHEN s.current_query = '<command string not enabled>'
                        THEN 'disabled'
                    WHEN s.current_query = '<backend information not available>'
                        THEN 'undefined'
                    WHEN s.current_query = '<insufficient privilege>'
                        THEN 'insufficient privilege'
                    WHEN s.waiting = 't'
                        THEN 'waiting for lock'
                    ELSE 'active'
                END AS status,
                extract('epoch' FROM
                    date_trunc('milliseconds', current_timestamp-s.xact_start)
                ),
                s.current_query, current_setting('max_connections')
            FROM pg_stat_activity AS s
                JOIN pg_database d ON d.oid=s.datid
            WHERE d.datallowconn
        },
        # support everything
        $PG_VERSION_90 => q{
            SELECT CASE
                    WHEN s.current_query = '<IDLE>'
                        THEN 'idle'
                    WHEN s.current_query = '<IDLE> in transaction'
                        THEN 'idle in transaction'
                    WHEN s.current_query = '<IDLE> in transaction (aborted)'
                        THEN 'idle in transaction (aborted)'
                    WHEN s.current_query = '<FASTPATH> function call'
                        THEN 'fastpath function call'
                    WHEN s.current_query = '<command string not enabled>'
                        THEN 'disabled'
                    WHEN s.current_query = '<backend information not available>'
                        THEN 'undefined'
                    WHEN s.current_query = '<insufficient privilege>'
                        THEN 'insufficient privilege'
                    WHEN s.waiting = 't'
                        THEN 'waiting for lock'
                    ELSE 'active'
                END,
                extract('epoch' FROM
                    date_trunc('milliseconds', current_timestamp-s.xact_start)
                ),
                s.current_query, current_setting('max_connections')
            FROM pg_stat_activity AS s
                JOIN pg_database d ON d.oid=s.datid
            WHERE d.datallowconn
        },
        # pg_stat_activity schema change
        $PG_VERSION_92 => q{
            SELECT CASE
                WHEN s.waiting = 't' THEN 'waiting for lock'
                WHEN s.query = '<insufficient privilege>'
                    THEN 'insufficient privilege'
                WHEN s.state IS NULL THEN 'undefined'
                ELSE s.state
              END,
              extract('epoch' FROM
                date_trunc('milliseconds', current_timestamp-s.xact_start)
              ), s.query, current_setting('max_connections')
            FROM pg_stat_activity AS s
              JOIN pg_database d ON d.oid=s.datid
            WHERE d.datallowconn
        }
    );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "backends_status".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'backends_status', $PG_VERSION_82 or exit 1;


    if ( defined $args{'warning'} ) {
        my $threshods_re
            = qr/(idle|idle_xact|aborted_xact|fastpath|active|waiting)\s*=\s*(\d+)/i;

        # warning and critical must be raw
        pod2usage(
            -message => "FATAL: critical and warning thresholds only accept a list of 'label=value' separated by comma.\n"
                . "See documentation for more information.",
            -exitval => 127
        ) unless $args{'warning'} =~ m/^$threshods_re(\s*,\s*$threshods_re)*$/
            and $args{'critical'} =~ m/^$threshods_re(\s*,\s*$threshods_re)*$/ ;

        while ( $args{'warning'} =~ /$threshods_re/g ) {
            my ($threshold, $value) = ($1, $2);
            $warn{$threshold} = $value if $1 and defined $2;
        }

        while ( $args{'critical'} =~ /$threshods_re/g ) {
            my ($threshold, $value) = ($1, $2);
            $crit{$threshold} = $value if $1 and defined $2;
        }
    }

    push @exclude_re => qr/$_/ foreach @{ $args{'exclude'} };

    @rs = @{ query_ver( $hosts[0], %queries ) };

    delete $status{'idle in transaction (aborted)'}
        if $hosts[0] < $PG_VERSION_90;

    $max_connections = $rs[0][3] if scalar @rs;

    REC_LOOP: foreach my $r (@rs) {

        $num_backends++;

        foreach my $exclude_re (@exclude_re) {
            next REC_LOOP if $r->[2] =~ /$exclude_re/;
        }

        $status{$r->[0]}[0]++;

        $status{$r->[0]}[1] = $r->[1]
            if $r->[1] and $r->[1] > $status{$r->[0]}[1];
    }

    STATUS_LOOP: foreach my $s (sort keys %status) {
        my $perf = "'$s'=$status{$s}[0]";
        $perf .= ";$warn{$s};$crit{$s};0;$max_connections"
            if exists $warn{$s} and exists $crit{$s};

        push @perfdata => $perf;
        push @perfdata => "'oldest $s'=$status{$s}[1]s"
            if $hosts[0]->{'version_num'} >= $PG_VERSION_83
                and $s !~ '^(?:disabled|undefined|insufficient)';

        # Criticals
        if ( exists $crit{$s} and $status{$s}[0] >= $crit{$s} ) {
            push @msg_crit => "$status{$s}[0] $s";
            next STATUS_LOOP;
        }

        # Warning
        if ( exists $warn{$s} and $status{$s}[0] >= $warn{$s} ) {
            push @msg_warn => "$status{$s}[0] $s";
            next STATUS_LOOP;
        }
    }

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if scalar @msg_crit > 0;

    return warning( $me, \@msg_warn, \@perfdata ) if scalar @msg_warn > 0;

    return ok( $me, [ "$num_backends backend connected" ], \@perfdata );
}

=item B<database_size> (8.1+)

Check the variation of database sizes.

This service uses the status file (see C<--status-file> parameter).

Perfdata contains the size difference for each database since last call.

Critical and Warning thresholds accept either a raw number, a percentage or a
size (eg. 2.5G).

=cut

sub check_database_size {
    my @msg_crit;
    my @msg_warn;
    my @rs;
    my @perfdata;
    my %new_db_sizes;
    my @hosts;
    my %db_sizes;
    my %args = %{ $_[0] };
    my $me   = 'POSTGRES_DB_SIZE';
    my $sql  = q{SELECT datname, pg_database_size(datname)
        FROM pg_database};

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "database_size".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'database_size', $PG_VERSION_81 or exit 1;


    %db_sizes = %{ load( $hosts[0], 'db_size', $args{'status-file'} ) || {} };

    @rs = @{ query( $hosts[0], $sql ) };

DB_LOOP: foreach my $db (@rs) {
        my $delta;
        my $w_limit;
        my $c_limit;

        $new_db_sizes{ $db->[0] } = $db->[1];

        next DB_LOOP unless defined $db_sizes{ $db->[0] };

        $w_limit = get_size( $args{'warning'},  $db->[1] );
        $c_limit = get_size( $args{'critical'}, $db->[1] );
        $delta = $db->[1] - $db_sizes{ $db->[0] };

        push @perfdata => "$db->[0]=$db->[1]B;$w_limit;$c_limit";

        if ( abs($delta) >= $c_limit ) {
            push @msg_crit => "$db->[0] ($delta, now: $db->[1])";
            next DB_LOOP;
        }

        if ( abs($delta) >= $w_limit ) {
            push @msg_warn => "$db->[0] ($delta, now: $db->[1])";
            next DB_LOOP;
        }
    }

    save $hosts[0], 'db_size', \%new_db_sizes, $args{'status-file'};

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if scalar @msg_crit > 0;

    return warning( $me, \@msg_warn, \@perfdata ) if scalar @msg_warn > 0;

    return ok( $me, [ scalar(@rs) . " database(s) checked" ], \@perfdata );
}

=item B<wal_files> (8.1+)

Check the number of wal files.

Perfdata returns the total number of wal files, current number of written wal
and the current number of recycled wal.

Critical and Warning thresholds accept either a raw number of file or a
percentage. In case of percentage, the limit is computed based on:

  100% = 1 + checkpoint_segments * (2 + checkpoint_completion_target)

For PostgreSQL 8.1 and 8.2:

  100% = 1 + checkpoint_segments * 2

If C<wal_keep_segments> is set for 9.0 and above, the limit is the greatest
between the following formulas :

  100% = 1 + checkpoint_segments * (2 + checkpoint_completion_target)
  100% = 1 + wal_keep_segments + 2 * checkpoint_segments

=cut

sub check_wal_files {
    my $wal_num;
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_WAL_FILES';
    my %queries = (
      $PG_VERSION_90 => q{
         SELECT count(*) AS count, sum(is_recycled::int) AS is_recycled,
           sum((NOT is_recycled)::int) AS written,
           greatest(1 + current_setting('checkpoint_segments')::float4 *
              (2 + current_setting('checkpoint_completion_target')::float4),
                    1 + current_setting('wal_keep_segments')::float4 +
              2 * current_setting('checkpoint_segments')::float4
           ) AS max_wal,
           current_setting('wal_keep_segments')::integer
         FROM (
           SELECT file > first_value(file) OVER w AS is_recycled
           FROM pg_ls_dir('pg_xlog') as file
           WHERE file ~ '^[0-9A-F]{24}$'
           WINDOW w AS (
             ORDER BY (pg_stat_file('pg_xlog/'||file)).modification
             DESC
           )
         ) AS t
         GROUP BY 4;},
      $PG_VERSION_84 => q{
         SELECT count(*) AS count, sum(is_recycled::int) AS is_recycled,
           sum((NOT is_recycled)::int) AS written,
           1 + (
             current_setting('checkpoint_segments')::float4
             * ( 2 + current_setting('checkpoint_completion_target')::float4 )
           ) AS max_wal
         FROM (
           SELECT file > first_value(file) OVER w AS is_recycled
           FROM pg_ls_dir('pg_xlog') as file
           WHERE file ~ '^[0-9A-F]{24}$'
           WINDOW w AS (
             ORDER BY (pg_stat_file('pg_xlog/'||file)).modification
             DESC
           )
         ) AS t
         GROUP BY 4},
      $PG_VERSION_83 => q{
        SELECT count(*) AS num_file,
          sum(recycled::int) AS is_recycled,
          sum((NOT recycled)::int) AS written,
          1 + (
            current_setting('checkpoint_segments')::float4
            * ( 2 + current_setting('checkpoint_completion_target')::float4 )
          )
        FROM (
          SELECT file, file > (
            SELECT s.f
            FROM pg_ls_dir('pg_xlog') AS s(f)
            ORDER BY (pg_stat_file('pg_xlog/'||s.f)).modification DESC
            LIMIT 1
          ) AS recycled
          FROM pg_ls_dir('pg_xlog') AS file
          WHERE file ~ '^[0-9A-F]{24}$'
        ) AS t},
      $PG_VERSION_81 => q{
         SELECT count(*) AS num_file,
           sum(recycled::int) AS is_recycled,
           sum((NOT recycled)::int) AS written,
           1 + (current_setting('checkpoint_segments')::integer * 2)
         FROM (
            SELECT file, file > (
              SELECT s.f
              FROM pg_ls_dir('pg_xlog') AS s(f)
              ORDER BY (pg_stat_file('pg_xlog/'||s.f)).modification DESC
              LIMIT 1
            ) AS recycled
            FROM pg_ls_dir('pg_xlog') AS file
            WHERE file ~ '^[0-9A-F]{24}$'
         ) AS t}
    );

    if ( defined $args{'warning'} ) {
        # warning and critical are mandatory.
        pod2usage(
            -message => "FATAL: you must specify critical and warning thresholds.",
            -exitval => 127
        ) unless defined $args{'warning'} and defined $args{'critical'} ;

        # warning and critical must be raw or %.
        pod2usage(
            -message => "FATAL: critical and warning thresholds only accept raw numbers or %.",
            -exitval => 127
        ) unless $args{'warning'}  =~ m/^([0-9.]+)%?$/
            and  $args{'critical'} =~ m/^([0-9.]+)%?$/;
    }

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "wal_files".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'wal_files', $PG_VERSION_81 or exit 1;

    @rs = @{ query_ver( $hosts[0], %queries )->[0] };

    push @perfdata => "total_wal=$rs[0]";
    push @perfdata => "recycled_wal=$rs[1]";

    # pay attention to the wal_keep_segment in perfdatas
    if ( $hosts[0]{'version_num'} >= $PG_VERSION_90) {
        if ($rs[4] > 0) {
            # cheet with numbers if the keep_segment was just set and the
            # number of wal doesn't match it yet.
            if ($rs[4] > $rs[2]) {
                push @perfdata => "written_wal=1";
                push @perfdata => "kept_wal=". ($rs[2] - 1);
            }
            else {
                push @perfdata => "written_wal=". ($rs[2] - $rs[4]);
                push @perfdata => "kept_wal=$rs[4]";
            }
        }
        else {
            push @perfdata => "written_wal=$rs[2]";
            push @perfdata => "kept_wal=0";
        }
    }
    else {
        push @perfdata => "written_wal=$rs[2]";
    }

    push @msg => "$rs[0] WAL files";

    if ( defined $args{'warning'} ) {
        my $w_limit = get_size( $args{'warning'},  $rs[3] );
        my $c_limit = get_size( $args{'critical'}, $rs[3] );

        $perfdata[0] .= ";$w_limit;$c_limit;1;$rs[3]";

        return critical( $me, \@msg, \@perfdata ) if $rs[0] >= $c_limit;
        return warning( $me, \@msg, \@perfdata )  if $rs[0] >= $w_limit;
    }

    return ok( $me, \@msg, \@perfdata );
}

=item B<ready_archives> (8.1+)

Check the number of wal files ready to archive.

Perfdata returns the number of wal files waiting to be archived.

Critical and Warning thresholds only accept a raw number of file

=cut

sub check_ready_archives {
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my %args  = %{ $_[0] };
    my $me    = 'POSTGRES_READY_ARCHIVES';
    my $query = q{
         SELECT count(*) AS count
         FROM pg_ls_dir('pg_xlog/archive_status') as file
         WHERE file ~ '^[0-9A-F]{24}.ready$' };

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    # warning and critical must be raw
    pod2usage(
        -message => "FATAL: critical and warning thresholds only accept raw numbers.",
        -exitval => 127
    ) unless $args{'warning'}  =~ m/^([0-9]+)$/
        and  $args{'critical'} =~ m/^([0-9]+)$/;

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "ready_archives".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'ready_archives', $PG_VERSION_81 or exit 1;

    @rs = @{ query( $hosts[0], $query )->[0] };

    push @perfdata => "ready_archives=$rs[0];$args{'warning'};$args{'critical'}";

    push @msg => "$rs[0] WAL files ready to archive";

    return critical( $me, \@msg, \@perfdata ) if $rs[0] >= $args{'critical'};
    return warning( $me, \@msg, \@perfdata ) if $rs[0] >= $args{'warning'};
    return ok( $me, \@msg, \@perfdata );
}

# Agnostic check vacuum or analyze sub
sub check_last_maintenance {
    my $rs;
    my $c_limit;
    my $w_limit;
    my @perfdata;
    my @msg_crit;
    my @msg_warn;
    my @msg;
    my @hosts;
    my @all_db;
    my %counts;
    my %new_counts;
    my $type    = $_[0];
    my %args    = %{ $_[1] };
    my $me      = 'POSTGRES_LAST_' . uc($type);
    my %queries = (
        $PG_VERSION_82 => qq{
            SELECT coalesce(min(
                extract(epoch FROM current_timestamp -
                    CASE last_auto${type} > last_${type}
                        WHEN 't' THEN last_auto${type}
                        ELSE last_${type}
                    END
                )::float
            ), 'NaN'::float)
            FROM pg_stat_user_tables
        },
        $PG_VERSION_91 => qq{
            SELECT coalesce(min(
                    extract(epoch FROM current_timestamp -
                        CASE last_auto${type} > last_${type}
                            WHEN 't' THEN last_auto${type}
                            ELSE last_${type}
                        END
                    )::float
                ), 'NaN'::float),
                coalesce(sum(${type}_count), 0) AS ${type}_count,
                coalesce(sum(auto${type}_count), 0) AS auto${type}_count
            FROM pg_stat_user_tables
        }
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );

    $c_limit = get_time $args{'critical'};
    $w_limit = get_time $args{'warning'};


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => "FATAL: you must give only one host with service \"last_$type\".",
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], "last_$type", $PG_VERSION_82 or exit 1;


    @all_db = @{ get_all_dbname( $hosts[0] ) };

    %counts = %{ load( $hosts[0], "${type}_counts", $args{'status-file'} ) || {} }
        if $hosts[0]->{'version_num'} >= $PG_VERSION_91;

LOOP_DB: foreach my $db (@all_db) {
        my $perfdata;
        my $rs = query_ver( $hosts[0], %queries, $db )->[0];
        $db =~ s/=//g;

        $perfdata = "$db=$rs->[0]s;$w_limit;$c_limit";

        if ($hosts[0]->{'version_num'} >= $PG_VERSION_91 ) {
            $new_counts{$db} = [$rs->[1], $rs->[2]];

            if ( defined $counts{$db} ) {
                my $delta      = $rs->[1] - $counts{$db}[0];
                my $delta_auto = $rs->[2] - $counts{$db}[1];

                $perfdata .= " '$db $type'=$delta"
                    ." '$db auto$type'=$delta_auto";
            }
        }

        push @perfdata => $perfdata;

        if ( $rs->[0] =~ /^-inf/i or $rs->[0] >= $c_limit ) {
            push @msg_crit => "$db: " . to_interval($rs->[0]);
            next LOOP_DB;
        }

        if ( $rs->[0] >= $w_limit ) {
            push @msg_warn => "$db: " . to_interval($rs->[0]);
            next LOOP_DB;
        }
    }

    save $hosts[0], "${type}_counts", \%new_counts, $args{'status-file'}
        if $hosts[0]->{'version_num'} >= $PG_VERSION_91;

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if scalar @msg_crit > 0;

    return warning( $me, \@msg_warn, \@perfdata ) if scalar @msg_warn > 0;

    return ok( $me, [ scalar(@all_db) . " database(s) checked" ], \@perfdata );
}

=item B<last_analyze> (8.2+)

Check on each databases that the oldest analyze (from autovacuum or not) is not
older than the given threshold.

This service uses the status file (see C<--status-file> parameter) with
PostgreSQL 9.1+.

Perfdata returns oldest analyze per database in seconds. With PostgreSQL
9.1+, it returns the number of [auto]analyses per database since last
call as well.

Critical and Warning thresholds only accept an interval (eg. 1h30m25s)
and apply on the oldest analyse maintenance.

=cut

sub check_last_analyze {
    return check_last_maintenance( 'analyze', @_ );
}

=item B<last_vacuum> (8.2+)

Check on each databases that the oldest vacuum (from autovacuum or not) is not
older than the given threshold.

This service uses the status file (see C<--status-file> parameter) with
PostgreSQL 9.1+.

Perfdata returns oldest vacuum per database in seconds. With PostgreSQL
9.1+, it returns the number of [auto]vacuums per database since last
call as well.

Critical and Warning thresholds only accept an interval (eg. 1h30m25s)
and apply on the oldest vacuum.

=cut

sub check_last_vacuum {
    return check_last_maintenance( 'vacuum', @_ );
}

=item B<locks> (all)

Check the number of locks on the hosts.

Perfdata returns the number of lock for kind of lock.

Critical and Warning thresholds accept either a raw number of lock or a
percentage. In case of percentage, it is computed against the following limits
for 7.4 to 8.1:

  max_locks_per_transaction * max_connections

for 8.2+:

  max_locks_per_transaction * (max_connections + max_prepared_transactions)

=cut

sub check_locks {
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my %args          = %{ $_[0] };
    my $total_locks   = 0;
    my $waiting_locks = 0;
    my $me            = 'POSTGRES_LOCKS';
    my %queries       = (
        $PG_VERSION_74 => q{
            SELECT count(l.*), ref.mode,
                current_setting('max_locks_per_transaction')::integer
                * current_setting('max_connections')::integer, ref.granted
            FROM (
                SELECT 'AccessShareLock',                't'::boolean
                UNION SELECT 'RowShareLock',             't'
                UNION SELECT 'RowExclusiveLock',         't'
                UNION SELECT 'ShareUpdateExclusiveLock', 't'
                UNION SELECT 'ShareLock',                't'
                UNION SELECT 'ShareRowExclusiveLock',    't'
                UNION SELECT 'ExclusiveLock',            't'
                UNION SELECT 'AccessExclusiveLock',      't'
                UNION SELECT 'AccessShareLock',          'f'
                UNION SELECT 'RowShareLock',             'f'
                UNION SELECT 'RowExclusiveLock',         'f'
                UNION SELECT 'ShareUpdateExclusiveLock', 'f'
                UNION SELECT 'ShareLock',                'f'
                UNION SELECT 'ShareRowExclusiveLock',    'f'
                UNION SELECT 'ExclusiveLock',            'f'
                UNION SELECT 'AccessExclusiveLock',      'f'
            ) ref (mode, granted)
            LEFT JOIN pg_locks l
                ON (ref.mode, ref.granted) = (l.mode, l.granted)
            GROUP BY 2,3,4
            ORDER BY ref.granted, ref.mode
        },
        $PG_VERSION_82 => q{
            SELECT count(l.*), ref.mode,
                current_setting('max_locks_per_transaction')::integer * (
                    current_setting('max_prepared_transactions')::integer
                    + current_setting('max_connections')::integer), ref.granted
            FROM (SELECT * FROM ( VALUES
                ('AccessShareLock',          't'::boolean),
                ('RowShareLock',             't'),
                ('RowExclusiveLock',         't'),
                ('ShareUpdateExclusiveLock', 't'),
                ('ShareLock',                't'),
                ('ShareRowExclusiveLock',    't'),
                ('ExclusiveLock',            't'),
                ('AccessExclusiveLock',      't'),
                ('AccessShareLock',          'f'),
                ('RowShareLock',             'f'),
                ('RowExclusiveLock',         'f'),
                ('ShareUpdateExclusiveLock', 'f'),
                ('ShareLock',                'f'),
                ('ShareRowExclusiveLock',    'f'),
                ('ExclusiveLock',            'f'),
                ('AccessExclusiveLock',      'f')
                ) lockmode (mode, granted)
            ) ref
            LEFT JOIN pg_locks l
                ON (ref.mode, ref.granted) = (l.mode, l.granted)
            GROUP BY 2,3,4
            ORDER BY ref.granted, ref.mode
        }
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    # warning and critical must be raw or %.
    pod2usage(
        -message => "FATAL: critical and warning thresholds only accept raw numbers or %.",
        -exitval => 127
    ) unless $args{'warning'}  =~ m/^([0-9.]+)%?$/
        and  $args{'critical'} =~ m/^([0-9.]+)%?$/;


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "locks".',
        -exitval => 127
    ) if @hosts != 1;


    @rs = @{ query_ver $hosts[0], %queries };

    $args{'critical'} = int($1 * $rs[0][2]/100) if $args{'critical'} =~ /^([0-9.]+)%$/;
    $args{'warning'}  = int($1 * $rs[0][2]/100) if $args{'warning'}  =~ /^([0-9.]+)%$/;

    map {
        $total_locks += $_->[0];
        if ($_->[3] eq 't') {
            push @perfdata =>
                ( "$_->[1]=$_->[0];$args{'warning'};$args{'critical'}" );
        }
        else {
            $waiting_locks += $_->[0];
            push @perfdata =>
                ( "'Waiting $_->[1]'=$_->[0];$args{'warning'};$args{'critical'}" );
        }
    } @rs;

    push @msg => "$total_locks locks, $waiting_locks waiting locks";

    return critical( $me, \@msg, \@perfdata )
        if $total_locks >= $args{'critical'};

    return warning( $me, \@msg, \@perfdata )
        if $total_locks >= $args{'warning'};

    return ok( $me, \@msg, \@perfdata );
}

=item B<bgwriter> (8.3+)

Check the percentage of pages written by backends since last check.

This service uses the status file (see C<--status-file> parameter).

Perfdata contains differentials of pg_stat_bgwriter counters since last
call.

Critical and Warning thresholds are optionnal. If set, they only accept a
percentage.

=cut

sub check_bgwriter {
    my @msg;
    my @msg_crit;
    my @msg_warn;
    my @rs;
    my @perfdata;
    my $delta_buff_total;
    my $delta_buff_backend;
    my $delta_buff_bgwriter;
    my $delta_buff_checkpointer;
    my $delta_buff_alloc;
    my $delta_checkpoint_timed;
    my $delta_checkpoint_req;
    my $delta_maxwritten_clean;
    my $delta_backend_fsync;
    my %new_bgw;
    my %bgw;
    my @hosts;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_BGWRITER';
    my %queries = (
        $PG_VERSION_83 => q{SELECT checkpoints_timed, checkpoints_req,
              buffers_checkpoint * current_setting('block_size')::numeric,
              buffers_clean * current_setting('block_size')::numeric,
              maxwritten_clean,
              buffers_backend * current_setting('block_size')::numeric,
              buffers_alloc * current_setting('block_size')::numeric,
              0,
              0
            FROM pg_stat_bgwriter;
        },
        $PG_VERSION_91 => q{SELECT checkpoints_timed, checkpoints_req,
              buffers_checkpoint * current_setting('block_size')::numeric,
              buffers_clean * current_setting('block_size')::numeric,
              maxwritten_clean,
              buffers_backend * current_setting('block_size')::numeric,
              buffers_alloc * current_setting('block_size')::numeric,
              buffers_backend_fsync,
              extract ('epoch' from stats_reset)
            FROM pg_stat_bgwriter;
        }
    );

    # warning and critical must be %.
    pod2usage(
        -message => "FATAL: critical and warning thresholds only accept percentages.",
        -exitval => 127
    ) unless not (defined $args{'warning'} and defined $args{'critical'} )
        or (
            $args{'warning'}  =~ m/^([0-9.]+)%$/
            and $args{'critical'} =~ m/^([0-9.]+)%$/
        );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "bgwriter".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'bgwriter', $PG_VERSION_83 or exit 1;


    %bgw = %{ load( $hosts[0], 'bgwriter', $args{'status-file'} ) || {} };

    @rs = @{ query_ver( $hosts[0], %queries )->[0] };

    $new_bgw{'checkpoint_timed'} = $rs[0];
    $new_bgw{'checkpoint_req'}   = $rs[1];
    $new_bgw{'buff_checkpoint'}  = $rs[2];
    $new_bgw{'buff_clean'}       = $rs[3];
    $new_bgw{'maxwritten_clean'} = $rs[4];
    $new_bgw{'buff_backend'}     = $rs[5];
    $new_bgw{'buff_alloc'}       = $rs[6];
    $new_bgw{'backend_fsync'}    = $rs[7];
    $new_bgw{'stat_reset'}       = $rs[8];

    save $hosts[0], 'bgwriter', \%new_bgw, $args{'status-file'};

    return ok( $me, ['First call'] ) unless keys %bgw;

    return ok( $me, ['Stats reseted since last call'] )
        if $new_bgw{'stat_reset'}       > $bgw{'stat_reset'}
        or $new_bgw{'checkpoint_timed'} < $bgw{'checkpoint_timed'}
        or $new_bgw{'checkpoint_req'}   < $bgw{'checkpoint_req'}
        or $new_bgw{'buff_checkpoint'}  < $bgw{'buff_checkpoint'}
        or $new_bgw{'buff_clean'}       < $bgw{'buff_clean'}
        or $new_bgw{'maxwritten_clean'} < $bgw{'maxwritten_clean'}
        or $new_bgw{'buff_backend'}     < $bgw{'buff_backend'}
        or $new_bgw{'buff_alloc'}       < $bgw{'buff_alloc'}
        or $new_bgw{'backend_fsync'}    < $bgw{'backend_fsync'};

    $delta_buff_total = $rs[2] - $bgw{'buff_checkpoint'}
        + $rs[3] - $bgw{'buff_clean'}
        + $rs[5] - $bgw{'buff_backend'};

    $delta_buff_backend      = $rs[5] - $bgw{'buff_backend'};
    $delta_buff_bgwriter     = $rs[3] - $bgw{'buff_clean'};
    $delta_buff_checkpointer = $rs[2] - $bgw{'buff_checkpoint'};
    $delta_buff_alloc        = $rs[6] - $bgw{'buff_alloc'};
    $delta_checkpoint_timed  = $rs[0] - $bgw{'checkpoint_timed'};
    $delta_checkpoint_req    = $rs[1] - $bgw{'checkpoint_req'};
    $delta_maxwritten_clean  = $rs[4] - $bgw{'maxwritten_clean'};
    $delta_backend_fsync     = $rs[7] - $bgw{'backend_fsync'};

    push @perfdata, "buffers_backend=${delta_buff_backend}B".
                    " checkpoint_timed=${delta_checkpoint_timed}".
                    " checkpoint_req=${delta_checkpoint_req}".
                    " buffers_checkpoint=${delta_buff_checkpointer}B".
                    " buffers_clean=${delta_buff_bgwriter}B".
                    " maxwritten_clean=${delta_maxwritten_clean}".
                    " buffers_backend_fsync=${delta_backend_fsync}".
                    " buffers_alloc=${delta_buff_alloc}B";

    if ($delta_buff_total) {

        push @msg => sprintf(
            "%.2f%% from backends, %.2f%% from bgwriter, %.2f%% from checkpointer",
            100 * $delta_buff_backend      / $delta_buff_total,
            100 * $delta_buff_bgwriter     / $delta_buff_total,
            100 * $delta_buff_checkpointer / $delta_buff_total
        );
    }
    else {
        push @msg => "No writes";
    }

    # Alarm if asked.
    # FIXME: threshold should accept a % and a minimal writen size
    if ( defined $args{'warning'}
        and defined $args{'critical'}
        and $delta_buff_total
    ) {
        my $w_limit = get_size( $args{'warning'},  $delta_buff_total );
        my $c_limit = get_size( $args{'critical'}, $delta_buff_total );

        return critical( $me, \@msg, \@perfdata )
            if $delta_buff_backend >= $c_limit;
        return warning( $me, \@msg, \@perfdata )
            if $delta_buff_backend >= $w_limit;
    }

    return ok( $me, \@msg, \@perfdata );
}


=item B<archive_folder>

Check if all archived WAL exist between the oldest and the latest WAL in the
archive folder and make sure they are 16MB. The given folder must have archived
files from ONE cluster. The version of PostgreSQL that created the archives is
only checked on the last one for speed consideration.

This service requires the argument C<--path> on the command line to specify the
archive folder path to check.

Optionnal argument C<--ignore-wal-size> allows to NOT check the WAL size. Usefull if your
archived WALs are compressed. Default behaviour is to check the WALs size.

Optionnal argument C<--suffix> allows you define the prefix of your archived
WALs. Usefull if they are compressed with an extension (eg. .gz, .bz2, ...).
Default is no suffix.

Perfdata contains the number of WAL archived and the age of the latest one.

Critical and Warning define the max age of the latest archived WAL as an
interval (eg. 5m or 300s ).

=cut

sub check_archive_folder {
    my @msg;
    my @msg_crit;
    my @msg_warn;
    my @perfdata;
    my @filelist;
    my @filelist_sorted;
    my $w_limit;
    my $c_limit;
    my $timeline;
    my $wal;
    my $seg;
    my $latest_wal_age;
    my $dh;
    my $wal_version;
    my $filename_re;
    my $suffix         = $args{'suffix'};
    my $check_size     = not $args{'ignore-wal-size'};
    my $me             = 'POSTGRES_ARCHIVES';
    my $seg_per_wal    = 255; # increased later for pg > 9.2
    my %args           = %{ $_[0] };
    my %wal_versions   = (
        '80' => 53340,
        '81' => 53341,
        '82' => 53342,
        '83' => 53346,
        '84' => 53347,
        '90' => 53348,
        '91' => 53350,
        '92' => 53361,
        '93' => 53365
    );

    # "path" argument must be given
    pod2usage(
        -message => 'FATAL: you must specify the archive folder using "--path <dir>".',
        -exitval => 127
    ) unless defined $args{'path'};

    # invalid "path" argument
    pod2usage(
        -message => "FATAL: \"$args{'path'}\" is not a valid folder.",
        -exitval => 127
    ) unless -d $args{'path'};

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );


    opendir( $dh, $args{'path'} )
        or die "Cannot opendir $args{'path'} : $!\n";

    $filename_re = qr/^[0-9A-F]{24}$suffix$/;
    @filelist = map { [ $_ => (stat("$args{'path'}/$_"))[9,7] ] }
        grep( /$filename_re/, readdir($dh) );

    closedir($dh);

    return unknown $me, ['No archived WAL found.'] unless @filelist;


    $w_limit = get_time($args{'warning'});
    $c_limit = get_time($args{'critical'});

    # sort by mtime
    @filelist_sorted = sort { ($a->[1] <=> $b->[1]) || ($a->[0] cmp $b->[0]) } @filelist;

    $latest_wal_age = time() - $filelist_sorted[-1][1];

    # We need to read the XLOG_PAGE_MAGIC to be able to guess $seg_per_wal
    open FIRST_WAL, "<$args{'path'}/$filelist_sorted[-1][0]";
    read(FIRST_WAL, $wal_version, 2);
    close FIRST_WAL;
    $wal_version = unpack('S', $wal_version);

    dprint ("wal version: $wal_version\n");

    # FIXME: As there is no consensus about XLOG_PAGE_MAGIC algo accros postrges
    # versions this piece of code should checked for compatibility for each new
    # PostgreSQL version to confirm the new XLOG_PAGE_MAGIC is still greater
    # than the previous one (or at least the 9.2 one).
    $seg_per_wal++ if $wal_version >= $wal_versions{'93'};

    push @perfdata,
        "latest_archive_age=${latest_wal_age}s;$w_limit;$c_limit ".
        "num_archives=". scalar(@filelist_sorted);

    $timeline = hex(substr($filelist_sorted[-1][0], 0, 8));
    $wal = hex(substr($filelist_sorted[0][0], 8, 8));
    $seg = hex(substr($filelist_sorted[0][0], 16, 8));

    # check ALL archives are here.
    for ( my $i = 0; $i <= $#filelist_sorted ; $i++ ) {
        dprint ("Checking WAL $filelist_sorted[$i][0]\n");
        my $curr = sprintf('%08X%08X%08X%s',
            $timeline,
            $wal + int(($seg + $i)/$seg_per_wal),
            ($seg + $i)%$seg_per_wal,
            $suffix
        );

        if ( $curr ne $filelist_sorted[$i][0] ) {
            push @msg => "Wrong sequence or file missing @ '$curr'";
            last;
        }

        if ( $check_size and $filelist_sorted[$i][2] != 16777216 ) {
            push @msg => "'$curr' is not 16MB";
            last;
        }
    }

    return critical( $me, \@msg, \@perfdata ) if @msg;

    push @msg => scalar(@filelist_sorted)." WAL archived in '$args{'path'}', "
        ."latest archived since ". to_interval($latest_wal_age);

    return critical( $me, \@msg, \@perfdata )
        if $latest_wal_age >= $c_limit;

    return warning( $me, \@msg, \@perfdata )
        if $latest_wal_age >= $w_limit;

    return ok( $me, \@msg, \@perfdata );
}


=item B<minor_version> (all)

Check if the cluster is running the latest minor version of PostgreSQL.

Latest version of PostgreSQL can be fetch from PostgreSQL official
website if check_pgactivity can access it or given as a parameter.

Without C<--critical> or C<--warning> parameters, this service attempt
to fetch the latest version online. You can optionnaly set the path to
your prefered program using the parameter C<--path> (eg.
C<--path '/usr/bin/wget'>). Supported programs are: GET, wget, curl,
fetch, lynx, links, links2.

The online version, rise a critical alert if the minor version is not
the latest.

If you do not want to (or can not) query the PostgreSQL website, you
must provide the expected version using either C<--warning> OR
C<--critical>. The given format must be one or more MINOR version
seperated by anything but a '.'. Eg. the following parameters are all
equivalent:

  --critical "9.3.2 9.2.6 9.1.11 9.0.15 8.4.19"
  --critical "9.3.2, 9.2.6, 9.1.11, 9.0.15, 8.4.19"
  --critical 9.3.2,9.2.6,9.1.11,9.0.15,8.4.19
  --critical 9.3.2/9.2.6/9.1.11/9.0.15/8.4.19

Anything other than a 3 numbered dot-separated version will be ignored.
if the running PostgreSQL major version is not found, the service rises an
unknown status.

Using the offline version rises either a critical or a warning depending
on which one has been set.

Perfdata returns the numerical version of PostgreSQL.

=cut

sub check_minor_version {
    my @perfdata;
    my @msg;
    my %latest_versions;
    my $rss;
    my @hosts;
    my $major_version;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_MINOR_VERSION';
    my $timeout = get_time($args{'timeout'});
    my $url     = 'http://www.postgresql.org/versions.rss';

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "minor_version".',
        -exitval => 127
    ) if @hosts != 1;

    set_pgversion($hosts[0]);

    if (not defined $args{'warning'}
        and not defined $args{'critical'}
    ) {
        # These methods comes from check_postgres,
        # by Greg Sabino Mullane <greg@endpoint.com>,
        # licenced under BSD
        our %get_methods = (
            'GET'    => "GET -t $timeout -H 'Pragma: no-cache' $url",
            'wget'   => "wget --quiet --timeout=$timeout --no-cache -O - $url",
            'curl'   => "curl --silent --max-time=$timeout -H 'Pragma: no-cache' $url",
            'fetch'  => "fetch -q -T $timeout -o - $url",
            'lynx'   => "lynx --connect-timeout=$timeout --dump $url",
            'links'  => 'links -dump $url',
            'links2' => 'links2 -dump $url'
        );

        # Force the fetching method
        if ($args{'path'}) {
            my $meth = basename $args{'path'};

            pod2usage(
                -message => "FATAL: \"$args{'path'}\" is not a valid program.",
                -exitval => 127
            ) unless -x $args{'path'};

            pod2usage(
                -message => "FATAL: \"$args{'path'}\" is not a supported program.",
                -exitval => 127
            ) unless $meth =~ 'GET|wget|curl|fetch|lynx|links|links2';
        }

        # fetch the latest versions
        foreach my $exe (values %get_methods) {
            $rss = qx{$exe 2>/dev/null};

            last if $rss =~ 'PostgreSQL latest versions';
        }

        return unknown($me, [ 'Could not fetch PostgreSQL latest versions' ])
            unless $rss;

        $latest_versions{"$1.$2"} = [$1 * 10000 + $2 * 100 + $3, "$1.$2.$3"]
            while $rss =~ m/<title>(\d+)\.(\d+)\.(\d+)/g;
    }
    else {
        pod2usage(
            -message => 'FATAL: you must provide a warning OR a critical threshold for service minor_version!',
            -exitval => 127
        ) if defined $args{'critical'} and defined $args{'warning'};

        my $given_version = defined $args{'critical'} ?
            $args{'critical'}
            : $args{'warning'};

        while ( $given_version =~ m/(\d+)\.(\d+)\.(\d+)/g ) {
            $latest_versions{"$1.$2"} = [$1 * 10000 + $2 * 100 + $3, "$1.$2.$3"];
        }
    }

    $hosts[0]{'version'} =~ '^(\d+\.\d+).*$';
    $major_version = $1;

    unless ( defined $latest_versions{$major_version} ) {
        push @msg => "Unknown major PostgreSQL version $major_version";
        return unknown( $me, \@msg );
    }

    push @perfdata => "version=". $hosts[0]{'version_num'} . "PGNUMVER";

    if ( $hosts[0]{'version_num'} != $latest_versions{$major_version}[0] ) {
        push @msg => "PostgreSQL version ". $hosts[0]{'version'}
            ." (should be $latest_versions{$major_version}[1])";

        return warning( $me, \@msg, \@perfdata )
            if defined $args{'warning'};
        return critical( $me, \@msg, \@perfdata );
    }

    push @msg => "PostgreSQL version ". $hosts[0]{'version'};

    return ok( $me, \@msg, \@perfdata );
}


=item B<hot_standby_delta> (9.0)

Check the data delta between a cluster and its Hot standbys.

You must give two or more hosts' connection parameters.

Perfdata returns the data delta in bytes between the master and all given Hot
standbys.

Critical and Warning thresholds can takes one or two values separated by a
comma. If only one value given, it applies on both received and replayed data.
If two values given, the first one applies on received data, the second one on
replayed ones. These threshold only accept a size (eg. 2.5G).

This service rise a critical if it doesn't find exactly ONE cluster production
(ie. critical when 0 or 2 and more masters).

=cut

sub check_hot_standby_delta {
    my @perfdata;
    my @msg;
    my @msg_crit;
    my @msg_warn;
    my $w_limit_received;
    my $c_limit_received;
    my $w_limit_replayed;
    my $c_limit_replayed;
    my @hosts;
    my %args  = %{ $_[0] };
    my $master_location = '';
    my $num_clusters = 0;
    my $me    = 'POSTGRES_HOT_STANDBY_DELTA';
    # we need to coalesce on pg_last_xlog_receive_location because it returns
    # NULL during WAL Shipping
    my $query = "
        SELECT (NOT pg_is_in_recovery())::int,
            CASE pg_is_in_recovery()
                WHEN 't' THEN coalesce(
                    pg_last_xlog_receive_location(),
                    pg_last_xlog_replay_location()
                )
                ELSE pg_current_xlog_location()
            END,
            CASE pg_is_in_recovery()
                WHEN 't' THEN pg_last_xlog_replay_location()
                ELSE NULL::text
            END
    ";

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give two or more hosts with service "hot_standby_delta".',
        -exitval => 127
    ) if @hosts < 2;

    is_compat $hosts[0], 'hot_standby_delta', $PG_VERSION_90 or exit 1;


    # fetch LSNs
    foreach my $host (@hosts) {
        $host->{'rs'} = \@{ query( $host, $query )->[0] };
        $num_clusters += $host->{'rs'}[0];
        $master_location = $host->{'rs'}[1] if $host->{'rs'}[0];
    }

    return critical($me, ['More than one cluster in production.'])
        if $num_clusters != 1;

    ($w_limit_received, $w_limit_replayed) = split /,/, $args{'warning'};
    ($c_limit_received, $c_limit_replayed) = split /,/, $args{'critical'};

    if (!defined($w_limit_replayed)) {
        $w_limit_replayed = $w_limit_received;
    }
    if (!defined($c_limit_replayed)) {
        $c_limit_replayed = $c_limit_received;
    }

    $w_limit_received = get_size( $w_limit_received );
    $c_limit_received = get_size( $c_limit_received );
    $w_limit_replayed = get_size( $w_limit_replayed );
    $c_limit_replayed = get_size( $c_limit_replayed );

    # we recycle this one to count the number of slave
    $num_clusters = 0;

    $master_location =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
    $master_location = (hex('ff000000') * hex($1)) + hex($2);

    # compute deltas
    foreach my $host (@hosts) {
        next if $host->{'rs'}[0];
        my ($a, $b) = split(/\//, $host->{'rs'}[1]);
        $host->{'receive_delta'} = $master_location - (hex('ff000000') * hex($a)) - hex($b);

        ($a, $b) = split(/\//, $host->{'rs'}[2]);
        $host->{'replay_delta'} = $master_location - (hex('ff000000') * hex($a)) - hex($b);

        $host->{'name'} =~ s/ db=.*$//;

        push @perfdata => "'receive delta $host->{'name'}'=".
            ($host->{'receive_delta'} > 0 ? $host->{'receive_delta'}:0).
            "B 'replay delta $host->{'name'}'=".
            ($host->{'replay_delta'} > 0 ? $host->{'replay_delta'}:0) .'B'
        ;

        if ($host->{'receive_delta'} > $c_limit_received) {
            push @msg_crit, "critical receive lag for $host->{'name'}";
            next;
        }

        if ($host->{'replay_delta'} > $c_limit_replayed) {
            push @msg_crit, "critical replay lag for $host->{'name'}";
            next;
        }

        if ($host->{'receive_delta'} > $w_limit_received) {
            push @msg_warn, "warning receive lag for $host->{'name'}";
            next;
        }

        if ($host->{'replay_delta'} > $w_limit_replayed) {
            push @msg_warn, "warning replay lag for $host->{'name'}";
            next;
        }

        $num_clusters++;
    }

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if @msg_crit > 0;

    return warning( $me, \@msg_warn, \@perfdata ) if @msg_warn > 0;

    return ok($me, [ "$num_clusters Hot standby checked" ], \@perfdata);
}


=item B<streaming_delta> (9.1+)

Check the data delta between a cluster and its standbys in streaming replication.

Optionnal argument C<--slave> allows to specify some slaves that MUST be
connected. This argument can be used as many time as you need to check multiple
slaves connections, or you can specify multiple slaves connections at one time,
using comma separated values. Both methods can be used in a single call. The
given value must be of the form "APPLICATION_NAME IP".
Any of those two following examples will check for the presence of two slaves:

  --slave 'slave1 192.168.1.11' --slave 'slave2 192.168.1.12'
  --slave 'slave1 192.168.1.11','slave2 192.168.1.12'

Perfdata returns the data delta in bytes between the master and all standbys
found and the number of slave connected.

Critical and Warning thresholds can takes one or two values separated by a
comma. If only one value given, it applies on both flushed and replayed data.
If two values given, the first one applies on flushed data, the second one on
replayed ones. These thresholds only accept a size (eg. 2.5G).

=cut

sub check_streaming_delta {
    my @perfdata;
    my @msg;
    my @msg_crit;
    my @msg_warn;
    my @rs;
    my $w_limit_flushed;
    my $c_limit_flushed;
    my $w_limit_replayed;
    my $c_limit_replayed;
    my @hosts;
    my %slaves;
    my %args            = %{ $_[0] };
    my $me              = 'POSTGRES_STREAMING_DELTA';
    my $master_location = '';
    my $num_clusters    = 0;
    my %queries         = (
        $PG_VERSION_92 => q{SELECT application_name, client_addr, pid,
            sent_location, write_location, flush_location, replay_location,
            pg_current_xlog_location()
            FROM pg_stat_replication},
        $PG_VERSION_91 => q{SELECT application_name, client_addr, procpid,
            sent_location, write_location, flush_location, replay_location,
            pg_current_xlog_location()
            FROM pg_stat_replication}
    );
    # FIXME this service should check for given slaves in opts!

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "streaming_delta".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'streaming_delta', $PG_VERSION_91 or exit 1;
    if ( scalar @{ $args{'slave'} } ) {
        $slaves{$_} = 0 foreach ( split ( /,/, join ( ',', @{ $args{'slave'} } ) ) );
    }

    @rs = @{ query_ver( $hosts[0], %queries ) };

    return unknown($me, ['No slaves connected'], ["'# of slaves'=0"])
        unless @rs;

    $rs[0][7] =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
    $master_location = (hex('ff000000') * hex($1)) + hex($2);

    ($w_limit_flushed, $w_limit_replayed) = split /,/, $args{'warning'};
    ($c_limit_flushed, $c_limit_replayed) = split /,/, $args{'critical'};

    if (!defined($w_limit_replayed)) {
        $w_limit_replayed = $w_limit_flushed;
    }
    if (!defined($c_limit_replayed)) {
        $c_limit_replayed = $c_limit_flushed;
    }

    $w_limit_flushed = get_size( $w_limit_flushed );
    $c_limit_flushed = get_size( $c_limit_flushed );
    $w_limit_replayed = get_size( $w_limit_replayed );
    $c_limit_replayed = get_size( $c_limit_replayed );


    push @perfdata => "'# of slaves'=". scalar @rs;

    # compute deltas
    foreach my $host (@rs) {
        my $send_delta;
        my $write_delta;
        my $flush_delta;
        my $replay_delta;
        my $name;

        $host->[3] =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
        $send_delta = $master_location - (hex('ff000000') * hex($1)) - hex($2);

        $host->[4] =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
        $write_delta = $master_location - (hex('ff000000') * hex($1)) - hex($2);

        $host->[5] =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
        $flush_delta = $master_location - (hex('ff000000') * hex($1)) - hex($2);

        $host->[6] =~ m{^([0-9A-F]+)/([0-9A-F]+)$};
        $replay_delta = $master_location - (hex('ff000000') * hex($1)) - hex($2);

        $name = "$host->[0]\@$host->[1]";

        push @perfdata => "'sent delta $name'=${send_delta}B ".
            "'wrote delta $name'=${write_delta}B ".
            "'flushed delta $name'=${flush_delta}B ".
            "'replay delta $name'=${replay_delta}B ".
            "'pid $name'=$host->[2]";

        $num_clusters++;

        $slaves{"$host->[0] $host->[1]"} = 1;

        if ($flush_delta > $c_limit_flushed) {
            push @msg_crit, "critical flush lag for $name";
            next;
        }

        if ($replay_delta > $c_limit_replayed) {
            push @msg_crit, "critical replay lag for $name";
            next;
        }

        if ($flush_delta > $w_limit_flushed) {
            push @msg_warn, "warning flush lag for $name";
            next;
        }

        if ($replay_delta > $w_limit_replayed) {
            push @msg_warn, "warning replay lag for $name";
            next;
        }
    }

    while ( my ( $host, $connected ) = each %slaves ) {
        unshift @msg_crit => "$host not connected" unless $connected;
    }

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if @msg_crit > 0;

    return warning( $me, \@msg_warn, \@perfdata ) if @msg_warn > 0;

    return ok($me, [ "$num_clusters slaves checked" ], \@perfdata);
}

=item B<hit_ratio> (all)

Check the cache hit ratio on the cluster.

Perfdata contains the hit ratio per database. Template databases and
databases that does not allow connections wont be checked, nor the
databases which has never been accessed.

Critical and Warning thresholds are optionnal. They only accept a percentage.

=cut

sub check_hit_ratio {
    my @rs;
    my @perfdata;
    my @msg_crit;
    my @msg_warn;
    my @hosts;
    my %args         = %{ $_[0] };
    my $me           = 'POSTGRES_HIT_RATIO';
    my $min_hit_ratio = 100000;
    my $sql          = q{SELECT d.datname,
        round((blks_hit::float/(blks_read+blks_hit+1)*100)::numeric, 2) as cachehitratio
        FROM pg_stat_database sd
        JOIN pg_database d ON d.oid = sd.datid
        WHERE d.datallowconn AND NOT d.datistemplate
        AND (blks_read+blks_hit) > 0
        ORDER BY datname, cachehitratio};

    # warning and critical must be %.
    if ( defined $args{'warning'} and defined $args{'critical'} ) {
        pod2usage(
            -message => "FATAL: critical and warning thresholds only accept percentages.",
            -exitval => 127
        ) unless $args{'warning'} =~ m/^([0-9.]+)%$/
            and $args{'critical'} =~ m/^([0-9.]+)%$/;

        $args{'warning'}  = substr $args{'warning'}, 0, -1;
        $args{'critical'} = substr $args{'critical'}, 0, -1;
    }


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "hit_ratio".',
        -exitval => 127
    ) if @hosts != 1;


    @rs = @{ query( $hosts[0], $sql ) };

    DB_LOOP: foreach my $db (@rs) {
        my $perfdata_value = "$db->[0]=$db->[1]%";

        $perfdata_value .= ";$args{'warning'};$args{'critical'}"
            if defined $args{'critical'};

        $min_hit_ratio = $db->[1] if $db->[1] < $min_hit_ratio ;

        push @perfdata, $perfdata_value;

        if ( defined $args{'critical'} ) {
            if ( $db->[1] < $args{'critical'} ) {
                push @msg_crit =>
                    "$db->[0]: $db->[1]";
                next DB_LOOP;
            }

            if ( defined $args{'warning'} and $db->[1] < $args{'warning'} ) {
                push @msg_warn =>
                    "$db->[0]: $db->[1]";
            }
        }
    }

    if ( defined $args{'critical'} ) {
        return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
            if $min_hit_ratio < $args{'critical'};

        return warning( $me, \@msg_warn, \@perfdata )
            if $min_hit_ratio < $args{'warning'};
    }

    return ok( $me, [ scalar(@rs) . " database(s) checked" ], \@perfdata );
}

=item B<backup_label_age> (8.1+)

Check the age of the backup label file.

Perfdata returns the age of the backup_label file, -1 if not present.

Critical and Warning thresholds only accept an interval (eg. 1h30m25s).

=cut

sub check_backup_label_age {
    my $rs;
    my $c_limit;
    my $w_limit;
    my @perfdata;
    my @hosts;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_BACKUP_LABEL_AGE';
    my $sql     = q{SELECT max(s.r) AS value FROM (
            SELECT CAST(extract(epoch FROM current_timestamp - (pg_stat_file(file)).modification) AS integer) AS r
            FROM pg_ls_dir('.') AS ls(file)
            WHERE file='backup_label' UNION SELECT 0
        ) AS s};

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );

    $c_limit = get_time( $args{'critical'} );
    $w_limit = get_time( $args{'warning'} );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "backup_label_age".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'backup_label_age', $PG_VERSION_81 or exit 1;

    $rs = @{ query( $hosts[0], $sql )->[0] }[0];

    push @perfdata,
         "age=${rs}s;$w_limit;$c_limit";

    return critical( $me, [ "age: ".to_interval($rs) ], \@perfdata )
        if $rs > $c_limit;

    return warning( $me, [ "age: ".to_interval($rs) ], \@perfdata )
        if $rs > $w_limit;

    return ok( $me, [ "backup_label file ".( $rs == 0 ? "absent":"present (age: ".to_interval($rs).")") ], \@perfdata );
}


=item B<oldest_2pc> (8.1+)

Check the oldest two phase commit transaction (aka. prepared transaction) in
the cluster.

Perfdata contains the max/avg age time and the number of prepared
transaction per databases.

Critical and Warning thresholds only accept an interval.

=cut

sub check_oldest_2pc {
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my $c_limit;
    my $w_limit;
    my $me          = 'POSTGRES_OLDEST_2PC';
    my $oldest_2pc = 0;
    my $nb_2pc      = 0;
    my %stats       = ();
    my $query         = q{SELECT transaction, gid,
            coalesce(extract('epoch' FROM
                    date_trunc('second', current_timestamp-prepared)
                ), -1),
            owner, d.datname
        FROM pg_database AS d
        LEFT JOIN pg_prepared_xacts AS x
            ON d.datname=x.database
        WHERE d.datallowconn
    };


    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );


    $c_limit = get_time $args{'critical'};
    $w_limit = get_time $args{'warning'};


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "oldest_2pc".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'postgres_oldest_2pc', $PG_VERSION_81 or exit 1;


    @rs = @{ query( $hosts[0], $query ) };

    REC_LOOP: foreach my $r (@rs) {

        $stats{$r->[4]} = {
            'num' => 0,
            'max' => -1,
            'avg' => 0,
        } unless exists $stats{$r->[4]};

        $oldest_2pc = $r->[2] if $r->[2] > $oldest_2pc;

        $stats{$r->[4]}{'num'}++ if $r->[0];
        $stats{$r->[4]}{'max'} = $r->[2] if $stats{$r->[4]}{'max'} < $r->[2];
        $stats{$r->[4]}{'avg'} = (
            $stats{$r->[4]}{'avg'} * ($stats{$r->[4]}{'num'} -1) + $r->[2])
            / $stats{$r->[4]}{'num'} if $stats{$r->[4]}{'num'};
    }

    DB_LOOP: foreach my $db (sort keys %stats) {

        $nb_2pc += $stats{$db}{'num'};

        unless($stats{$db}{'max'} > -1) {
            $stats{$db}{'max'} = 'NaN';
            $stats{$db}{'avg'} = 'NaN';
        }

        push @perfdata, (
            "'$db max'=$stats{$db}{'max'}s;$w_limit;$c_limit",
            "'$db avg'=$stats{$db}{'avg'}s;$w_limit;$c_limit",
            "'$db # prep. xact'=$stats{$db}{'num'}"
        );

        if ( $stats{$db}{'max'} > $c_limit ) {
            push @msg => "oldest 2pc on $db: ". to_interval($stats{$db}{'max'});
            next DB_LOOP;
        }

        if ( $stats{$db}{'max'} > $w_limit ) {
            push @msg => "oldest 2pc on $db: ". to_interval($stats{$db}{'max'});
        }
    }

    unshift @msg => "$nb_2pc prepared transaction(s)";

    return critical( $me, \@msg, \@perfdata )
        if $oldest_2pc > $c_limit;

    return warning( $me, \@msg, \@perfdata )
        if $oldest_2pc > $w_limit;

    return ok( $me, \@msg, \@perfdata );
}

=item B<oldest_xact> (8.3+)

Check the oldest idle transaction.

Perfdata contains the max/avg age time and the number of idle
transaction per databases.

Critical and Warning thresholds only accept an interval.

=cut

sub check_oldest_idlexact {
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my $c_limit;
    my $w_limit;
    my $me          = 'POSTGRES_OLDEST_IDLEXACT';
    my $oldest_idle = 0;
    my $nb_idle     = 0;
    my %stats       = ( );
    my %queries     = (
        $PG_VERSION_83 => q{SELECT datname,
            coalesce(extract('epoch' FROM
                    date_trunc('second', current_timestamp-xact_start)
                ), -1)
            FROM pg_stat_activity AS a
            WHERE current_query = '<IDLE> in transaction'},
        $PG_VERSION_92 => q{SELECT datname,
            coalesce(extract('epoch' FROM
                    date_trunc('second', current_timestamp-xact_start)
                ), -1)
            FROM pg_stat_activity AS a
            WHERE state='idle in transaction'}
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );


    $c_limit = get_time $args{'critical'};
    $w_limit = get_time $args{'warning'};


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "oldest_idlexact".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'oldest_idlexact', $PG_VERSION_83 or exit 1;


    @rs = @{ query_ver( $hosts[0], %queries ) };

    REC_LOOP: foreach my $r (@rs) {

        $stats{$r->[0]} = {
            'num' => 0,
            'max' => -1,
            'avg' => 0,
        } unless exists $stats{$r->[0]};

        $oldest_idle = $r->[1] if $r->[1] > $oldest_idle;

        $stats{$r->[0]}{'num'}++;
        $stats{$r->[0]}{'max'} = $r->[1] if $stats{$r->[0]}{'max'} < $r->[1];
        $stats{$r->[0]}{'avg'} = (
            $stats{$r->[0]}{'avg'} * ($stats{$r->[0]}{'num'} -1) + $r->[1])
            / $stats{$r->[0]}{'num'} if $stats{$r->[0]}{'num'};
    }

    DB_LOOP: foreach my $db (sort keys %stats) {

        $nb_idle += $stats{$db}{'num'};

        unless($stats{$db}{'max'} > -1) {
            $stats{$db}{'max'} = 'NaN';
            $stats{$db}{'avg'} = 'NaN';
        }

        push @perfdata, (
            "'$db max'=$stats{$db}{'max'}s;$w_limit;$c_limit",
            "'$db avg'=$stats{$db}{'avg'}s;$w_limit;$c_limit",
            "'$db # idle xact'=$stats{$db}{'num'}"
        );

        if ( $stats{$db}{'max'} > $c_limit ) {
            push @msg => "oldest idle xact on $db: ". to_interval($stats{$db}{'max'});
            next DB_LOOP;
        }

        if ( $stats{$db}{'max'} > $w_limit ) {
            push @msg => "oldest idle xact on $db: ". to_interval($stats{$db}{'max'});
        }
    }

    unshift @msg => "$nb_idle idle transaction(s)";

    return critical( $me, \@msg, \@perfdata )
        if $oldest_idle > $c_limit;

    return warning( $me, \@msg, \@perfdata )
        if $oldest_idle > $w_limit;

    return ok( $me, \@msg, \@perfdata );
}


=item B<longest_query> (all)

Check the longest running query in the cluster. This service supports argument
C<--exclude REGEX> to exclude queries matching the given regexp from the check.
You can give multiple C<--exclude REGEX>.

Perfdata contains the max/avg/min running time and the number of query per
databases.

Critical and Warning thresholds only accept an interval.

=cut

sub check_longest_query {
    my @rs;
    my @perfdata;
    my @msg;
    my @hosts;
    my @exclude_re;
    my $c_limit;
    my $w_limit;
    my %args          = %{ $_[0] };
    my $me            = 'POSTGRES_LONGEST_QUERY';
    my $longest_query = 0;
    my $nb_query      = 0;
    my %stats         = ();
    my %queries       = (
       $PG_VERSION_74 => q{SELECT d.datname,
                COALESCE(elapsed, -1),
                COALESCE(query, '')
            FROM pg_database AS d
            LEFT JOIN (
                SELECT datname, current_query AS query,
                    extract('epoch' FROM
                        date_trunc('second', current_timestamp-query_start)
                    ) AS elapsed
                FROM pg_stat_activity
                WHERE current_query NOT LIKE '<IDLE>%'
            ) AS s ON (d.datname=s.datname)
            WHERE d.datallowconn
        },
        $PG_VERSION_92 => q{SELECT d.datname,
                COALESCE(elapsed, 0),
                COALESCE(query, '')
            FROM pg_database AS d
            LEFT JOIN (
                SELECT datname, query,
                    extract('epoch' FROM
                        date_trunc('second', current_timestamp-state_change)
                    ) AS elapsed
                FROM pg_stat_activity
                WHERE state = 'active'
            ) AS s ON (d.datname=s.datname)
            WHERE d.datallowconn
        }
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    pod2usage(
        -message => "FATAL: critical and warning thresholds only acccepts interval.",
        -exitval => 127
    ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );


    $c_limit = get_time( $args{'critical'} );
    $w_limit = get_time( $args{'warning'} );


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "longest_query".',
        -exitval => 127
    ) if @hosts != 1;


    push @exclude_re => qr/$_/ foreach @{ $args{'exclude'} };

    @rs = @{ query_ver( $hosts[0], %queries ) };

    REC_LOOP: foreach my $r (@rs) {
        foreach my $exclude_re (@exclude_re) {
            next REC_LOOP if $r->[2] =~ /$exclude_re/;
        }

        $stats{$r->[0]} = {
            'num' => 0,
            'max' => -1,
            'avg' => 0,
        } unless exists $stats{$r->[0]};

        next REC_LOOP unless $r->[2] ne '';

        $longest_query = $r->[1] if $r->[1] > $longest_query;
        $nb_query++;

        $stats{$r->[0]}{'num'}++;
        $stats{$r->[0]}{'max'} = $r->[1] if $stats{$r->[0]}{'max'} < $r->[1];
        $stats{$r->[0]}{'avg'} = (
            $stats{$r->[0]}{'avg'} * ($stats{$r->[0]}{'num'} -1) + $r->[1])
            / $stats{$r->[0]}{'num'};
    }

    DB_LOOP: foreach my $db (keys %stats) {

        unless($stats{$db}{'max'} > -1) {
            $stats{$db}{'max'} = 'NaN';
            $stats{$db}{'avg'} = 'NaN';
        }

        push @perfdata, (
            "'$db max'=$stats{$db}{'max'}s;$w_limit;$c_limit",
            "'$db avg'=$stats{$db}{'avg'}s;$w_limit;$c_limit",
            "'$db #queries'=$stats{$db}{'num'}"
        );

        if ( $stats{$db}{'max'} > $c_limit ) {
            push @msg => "$db: ". to_interval($stats{$db}{'max'});
            next DB_LOOP;
        }

        if ( $stats{$db}{'max'} > $w_limit ) {
            push @msg => "$db: ". to_interval($stats{$db}{'max'});
        }
    }

    return critical( $me, \@msg, \@perfdata )
        if $longest_query > $c_limit;

    return warning( $me, \@msg, \@perfdata )
        if $longest_query > $w_limit;

    return ok( $me, [ "$nb_query running querie(s)" ], \@perfdata );
}

=item B<connection> (all)

Perform a simple connection test.

No perfdata is returned.

This service ignore critical and warning arguments.

=cut

sub check_connection {
    my @rs;
    my @hosts;
    my %args = %{ $_[0] };
    my $me   = 'POSTGRES_CONNECTION';
    my $sql  = q{SELECT now(), version()};

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "connection".',
        -exitval => 127
    ) if @hosts != 1;

    @rs = @{ query( $hosts[0], $sql ) };

    return ok( $me, [ "Connection successful at $rs[0][0], on $rs[0][1]" ] );
}

=item B<custom_query> (all)

Perform the given user query.

The query is specified with the C<--query parameter>. The first column will be
used to perform the test for the status, if warning and critical are provided.

The warning and critical arguments are optionnal. They can be treated as integer
(default), size or time depending to the C<--type> argument. Warning and critical
will be raised if they are greater than the first column, or less if the
C<--reverse> option is used.

All others columns will be used to generate the perfdata. The query has to
display them in the perfdata format, with unit if needed (eg. "size=35B").
If a field contains multiple values, they have to be space separated.

=cut

sub check_custom_query {
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_CUSTOM_QUERY';
    my $sql     = $args{'query'};
    my $type    = $args{'type'} || 'integer';
    my $reverse = $args{'reverse'};
    my $bounded = undef;
    my @rs;
    my @perfdata;
    my @hosts;
    my @msg_crit;
    my @msg_warn;
    my $c_limit;
    my $w_limit;
    my $perf;
    my $value;

    # FIXME: add warn/crit threshold in perfdata

    # query must be given
    pod2usage(
        -message => 'FATAL: you must set parameter "--query" with "custom_query" service.',
        -exitval => 127
    ) unless defined $args{'query'} ;

    # Critical and Warning must be given with --type argument
    pod2usage(
        -message => 'FATAL: you must specify critical and warning thresholds with "--type" parameter.',
        -exitval => 127
    ) unless ( not defined $args{'type'} ) or
        ( defined $args{'type'} and $args{'warning'} and $args{'critical'} );


    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "custom_query".',
        -exitval => 127
    ) if @hosts != 1;


    # Handle warning and critical type
    if ( $type eq 'size' ) {
        $w_limit = get_size( $args{'warning'} );
        $c_limit = get_size( $args{'critical'} );
    }
    elsif ( $type eq 'time' ) {
        pod2usage(
                -message => "FATAL: critical and warning thresholds only acccepts interval with --type time.",
                -exitval => 127
                ) unless ( is_time( $args{'warning'} ) and is_time( $args{'critical'} ) );

        $w_limit = get_time( $args{'warning'} );
        $c_limit = get_time( $args{'critical'} );
    }
    elsif (defined $args{'warning'} ) {
        pod2usage(
            -message => 'FATAL: given critical and/or warning are not numeric. Please, set "--type" parameter if needed.',
            -exitval => 127
        ) if $args{'warning'} !~ m/^[0-9.]+$/
            or $args{'critical'} !~ m/^[0-9.]+$/;
        $w_limit = $args{'warning'};
        $c_limit = $args{'critical'};
    }

    @rs = @{ query( $hosts[0], $sql ) };

    pod2usage(
        -message => 'FATAL: First column of your query is not numeric!',
        -exitval => 127
    ) unless looks_like_number($rs[0][0]);

    DB_LOOP: foreach my $rec ( @rs ) {
        $bounded = $rec->[0] unless $bounded;

        $bounded = $rec->[0] if ( !$reverse and $rec->[0] > $bounded )
            or ( $reverse and $rec->[0] < $bounded );

        $value = shift( @{$rec} );
        if ( @{$rec} > 0 ) {
            $perf = join(';',@{$rec});
        } else{
            $perf = undef;
        }

        push @perfdata, $perf if defined $perf;

        if ( ( defined $c_limit )
            and (
                ( !$reverse and ( $value > $c_limit ) )
                or ( $reverse and ( $value < $c_limit ) )
            )
        ) {
            push @msg_crit =>
                "value: $value ".( defined $perf ? "($perf)" : "" );
            next DB_LOOP;
        }

        if ( ( defined $w_limit )
            and (
                ( !$reverse and ( $value > $w_limit ) )
                or ( $reverse  and ( $value < $w_limit ) )
            )
        ) {
            push @msg_warn =>
                "value: $value ".( defined $perf ? "($perf)" : "" );
            next DB_LOOP;
        }
    }

    return critical( $me, [ @msg_crit, @msg_warn ], \@perfdata )
        if defined $c_limit and
            ( ( !$reverse and $bounded > $c_limit)
            or ( $reverse and $bounded < $c_limit) );

    return warning( $me, [ @msg_warn ], \@perfdata )
        if defined $w_limit and
            ( ( !$reverse and $bounded > $w_limit)
            or ( $reverse and $bounded < $w_limit) );

    return ok( $me, [ "Custom query ok" ], \@perfdata );
}

=item B<configuration> (8.0+)

Check the most important settings.

Warning and critical tresholds are ignored.

Specific parameters are :
C<--work_mem>, C<--maintenance_work_mem>, C<--shared_buffers>,C<-- wal_buffers>,
C<--checkpoint_segments>, C<--effective_cache_size>, C<--no_check_autovacuum>,
C<--no_check_fsync>, C<--no_check_enable>, C<--no_check_track_counts>.

=cut

sub check_configuration {
    my @perfdata;
    my @hosts;
    my @msg_crit;
    my %args = %{ $_[0] };
    my $me   = 'POSTGRES_CONFIGURATION';
    # This service is based on a probe by Marc Cousin (cousinmarc@gmail.com)
    # Limit parameters. Have defaut values
    my $work_mem             = $args{'work_mem'} || 4096; # At least 4MB
    my $maintenance_work_mem = $args{'maintenance_work_mem'} || 65536; # At least 64MB
    my $shared_buffers       = $args{'shared_buffers'} || 16384; # At least 128MB
    my $wal_buffers          = $args{'wal_buffers'} || 64; # At least 512k. Or -1 for 9.1
    my $checkpoint_segments  = $args{'checkpoint_segments'} || 10;
    my $effective_cache_size = $args{'effective_cache_size'} || 131072; # At least 1GB. No way a modern server has less than 2GB of ram
    # These will be checked to verify they are still the default values (no parameter, for now)
    # autovacuum, fsync, enable*,track_counts/stats_row_level
    my $no_check_autovacuum   = $args{'no_check_autovacuum'} || 0;
    my $no_check_fsync        = $args{'no_check_fsync'} || 0;
    my $no_check_enable       = $args{'no_check_enable'} || 0;
    my $no_check_track_counts = $args{'no_check_track_counts'} || 0;

    my $sql = "SELECT name,setting FROM pg_settings
        WHERE ( ( name='work_mem' and setting::bigint < $work_mem )
            or ( name='maintenance_work_mem' and setting::bigint < $maintenance_work_mem )
            or ( name='shared_buffers' and setting::bigint < $shared_buffers )
            or ( name='wal_buffers' and ( setting::bigint < $wal_buffers or setting = '-1') )
            or ( name='checkpoint_segments' and setting::bigint < $checkpoint_segments )
            or ( name='effective_cache_size' and setting::bigint < $effective_cache_size )
            or ( name='autovacuum' and setting='off' and $no_check_autovacuum = 0)
            or ( name='fsync' and setting='off' and $no_check_fsync=0  )
            or ( name~'^enable.*' and setting='off' and $no_check_enable=0)
            or (name='stats_row_level' and setting='off' and $no_check_track_counts=0)
            or (name='track_counts' and setting='off' and $no_check_track_counts=0)
        )";

    # FIXME make one parameter --ignore to rules 'em all.

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "configuration".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'configuration', $PG_VERSION_80 or exit 1;

    my @rc = @{ query( $hosts[0], $sql ) };

DB_LOOP:    foreach my $setting (@rc) {
        push @msg_crit => ( $setting->[0] . "=" . $setting->[1] );
    }

    # All the entries in $result are an error. If the array isn't empty, we return ERROR, and the list of errors
    return critical( $me, \@msg_crit )
        if ( @msg_crit > 0 );

    return ok( $me, [ "PostgreSQL configuration ok" ] );
}


=item B<max_freeze_age> (all)

Checks oldest database in transaction age.

Critical and Warning thresholds are optionnal. They accept either a raw
number or percentage for PostgreSQL 8.2 and more. If percentage is
given, the thresholds are computed based on the
"autovacumm_freeze_min_age" parameter.

Perfdatas return the age of each database.

=cut

sub check_max_freeze_age {
    my @rs;
    my @perfdata;
    my @msg;
    my @msg_crit;
    my @msg_warn;
    my @hosts;
    my @exclude_re;
    my $c_limit;
    my $w_limit;
    my $oldestdb;
    my $oldestage = -1;
    my %args          = %{ $_[0] };
    my $me            = 'POSTGRES_MAX_FREEZE_AGE';
    my %queries       = (
       $PG_VERSION_74 => q{SELECT datname, age(datfrozenxid)
            FROM pg_database
            WHERE datname <> 'template0'
       },
        $PG_VERSION_82 => q{SELECT datname, age(datfrozenxid),
                current_setting('autovacuum_freeze_max_age')
            FROM pg_database
            WHERE datname <> 'template0'
        }
    );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "max_freeze_age".',
        -exitval => 127
    ) if @hosts != 1;

    # warning and critical must be raw or %.
    if ( defined $args{'warning'} and defined $args{'critical'} ) {
        # warning and critical must be raw
        pod2usage(
            -message => "FATAL: critical and warning thresholds only accept raw numbers or % (for 8.2+).",
            -exitval => 127
        ) unless $args{'warning'}  =~ m/^([0-9]+)%?$/
            and  $args{'critical'} =~ m/^([0-9]+)%?$/;

        $w_limit = $args{'warning'};
        $c_limit = $args{'critical'};

        set_pgversion($hosts[0]);

        pod2usage(
            -message => "FATAL: only raw thresholds are compatible with PostgreSQL 8.1 and below.",
            -exitval => 127
        ) if $hosts[0]->{'version_num'} < $PG_VERSION_82
            and ($args{'warning'} =~ m/%\s*$/ or $args{'critical'} =~ m/%\s*$/);
    }

    push @exclude_re => qr/$_/ foreach @{ $args{'exclude'} };

    @rs = @{ query_ver( $hosts[0], %queries ) };

    if ( scalar @rs and defined $args{'critical'} ) {
        $c_limit = int($1 * $rs[0][2]/100) if $args{'critical'} =~ /^([0-9.]+)%$/;
        $w_limit  = int($1 * $rs[0][2]/100) if $args{'warning'}  =~ /^([0-9.]+)%$/;
    }

    REC_LOOP: foreach my $r (@rs) {
        my $perfdata_value;

        foreach my $exclude_re (@exclude_re) {
            next REC_LOOP if $r->[0] =~ /$exclude_re/;
        }

        if ($oldestage < $r->[1]) {
            $oldestdb = $r->[0];
            $oldestage = $r->[1];
        }

        $perfdata_value = "'$r->[0]'=$r->[1]";

        $perfdata_value .= ";$w_limit;$c_limit"
            if defined $c_limit;

        push @perfdata => $perfdata_value;

        if ( defined $c_limit ) {
            if ( $r->[1] > $c_limit ) {
                push @msg_crit => "$r->[0]";
                next REC_LOOP;
            }

            push @msg_warn => "$r->[0]"
                if defined $w_limit and $r->[1] > $w_limit;
        }
    }

    return critical( $me, [
        'Critical: '. join(',', @msg_crit)
        . (scalar @msg_warn? 'Warning: '. join(',', @msg_warn):'')
    ], \@perfdata ) if scalar @msg_crit;

    return warning( $me,
        [ 'Warning: '. join(',', @msg_warn) ], \@perfdata
    ) if scalar @msg_warn;

    return ok( $me, [ "oldest database is $oldestdb with age of $oldestage" ], \@perfdata );
}

=item B<is_master> (all)

Checks if the cluster accepts read and/or write queries. This state is reported
as "in production" by pg_controldata.

This service ignores critical and warning arguments.

No perfdata are returned.

=cut

sub check_is_master {
    my @rs;
    my @hosts;
    my %args          = %{ $_[0] };
    my $me            = 'POSTGRES_IS_MASTER';
    #For PostgreSQL 9.0+, the "pg_is_in_recovery()" function is used, for previous
    #versions the ability to connect is enough.
    my %queries       = (
       $PG_VERSION_74 => q{SELECT false
       },
        $PG_VERSION_90 => q{SELECT pg_is_in_recovery()
        }
    );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "is_master".',
        -exitval => 127
    ) if @hosts != 1;

    @rs = @{ query_ver( $hosts[0], %queries )->[0] };

    return critical( $me, [ "Cluster is not master" ] ) if ( $rs[0] eq "t" );
    return ok( $me, [ "Cluster is master" ] );
}

=item B<is_hot_standby> (9.0+)

Checks if the cluster is in recovery and accepts read only queries.

This service ignores critical and warning arguments.

No perfdata are returned.

=cut

sub check_is_hot_standby {
    my @rs;
    my @hosts;
    my %args          = %{ $_[0] };
    my $me            = 'POSTGRES_IS_HOT_STANDBY';
    my %queries       = (
        $PG_VERSION_90 => q{SELECT pg_is_in_recovery()}
    );

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "is_hot_standby".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], 'is_hot_standby', $PG_VERSION_90 or exit 1;
    @rs = @{ query_ver( $hosts[0], %queries )->[0] };

    return critical( $me, [ "Cluster is not hot standby" ] ) if ( $rs[0] eq "f" );
    return ok( $me, [ "Cluster is hot standby" ] );
}

=item B<pga_version>

Checks if this script is running the given version of check_pgactivity.
You must provide the expected version using either C<--warning> OR
C<--critical>.

No perfdata are returned.

=cut

sub check_pga_version {
    my @rs;
    my @hosts;
    my %args = %{ $_[0] };
    my $me   = 'PGACTIVITY_VERSION';

    pod2usage(
        -message => 'FATAL: you must provide a warning or a critical threshold for service pga_version!',
        -exitval => 127
    ) if (defined $args{'critical'} and defined $args{'warning'})
        or (not defined $args{'critical'} and not defined $args{'warning'});

    pod2usage(
        -message => "FATAL: given version does not look like a check_pgactivity version!",
        -exitval => 127
    ) if ( defined $args{'critical'} and $args{'critical'} !~ m/^\d\.\d+$/ )
        or (defined $args{'warning'} and $args{'warning'} !~ m/^\d\.\d+$/ );

    return critical( $me,
        [ "check_pgactivity version $VERSION (should be $args{'critical'}!)" ]
    ) if defined $args{'critical'} and $VERSION != $args{'critical'};

    return warning( $me,
        [ "check_pgactivity version $VERSION (should be $args{'warning'}!)" ]
    ) if defined $args{'warning'} and $VERSION != $args{'warning'};

    return ok( $me, [ "check_pgactivity version $VERSION" ] );
}

=item B<is_replay_paused> (9.1+)

Checks if the replication is paused. The service will return UNKNOWN if called
on a master server.

Thresholds are optionnals. They must be specified as interval. OK will always be returned if
the standby is not paused, even if replication delta time hits the thresholds.

Critical or warning are raised if last reported replayed timestamp is greater than given
threshold AND some data received from the master are not applied yet. OK will always be
returned if the standby is paused, already replayed everything from master and until some
writes activity happen on the master.

Perfdata returned are :
  * paused status (0 no, 1 yes, NaN if master)
  * lag time (in second)
  * data delta with master (0 no, 1 yes)

=cut

sub check_is_replay_paused {
    my @perfdata;
    my @rs;
    my @hosts;
    my $w_limit = -1;
    my $c_limit = -1;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_REPLICATION_PAUSED';
    my $query   = q{
        SELECT pg_is_in_recovery()::int AS is_in_recovery,
        CASE pg_is_in_recovery()
            WHEN 't' THEN pg_is_xlog_replay_paused()::int
            ELSE 0::int
        END AS is_paused,
        CASE pg_is_in_recovery()
            WHEN 't' THEN extract('epoch' FROM now()-pg_last_xact_replay_timestamp())::int
            ELSE NULL::int
        END AS lag,
        CASE
            WHEN pg_is_in_recovery() AND pg_last_xlog_replay_location() <> pg_last_xlog_receive_location()
                THEN 1::int
            WHEN pg_is_in_recovery() THEN 0::int
            ELSE NULL
        END AS delta
    };

    if ( defined $args{'warning'} and defined $args{'critical'} ) {
        # warning and critical must be interval if provided.
        pod2usage(
            -message => "FATAL: critical and warning thresholds only accept interval.",
            -exitval => 127
        ) unless ( is_time( $args{'warning'} ) and  is_time( $args{'critical'} ) );

        $c_limit = get_time $args{'critical'};
        $w_limit = get_time $args{'warning'};
    }

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "is_replay_paused".',
        -exitval => 127
    ) if @hosts != 1;

    is_compat $hosts[0], "is_replay_paused", $PG_VERSION_91 or exit 1;

    @rs = @{ query( $hosts[0], $query )->[0] };

    return unknown ( $me,
        [ "Server is not standby." ],
        [ "is_paused=NaN", "lag_time=NaNs", "has_data_delta=NaNs" ]
    ) if not $rs[0];

    push @perfdata, "is_paused=". $rs[1];
    push @perfdata, "lag_time=". $rs[2] . "s";
    push @perfdata, "has_data_delta=". $rs[3];

    # Always return ok if replay is not paused
    return ok( $me, [ ' replay is not paused' ], \@perfdata ) if not $rs[1];

    # Do we have thresholds ?
    if ( $c_limit != -1 ) {
        return critical( $me, [' replay lag time: ' . to_interval( $rs[2] ) ], \@perfdata )
            if $rs[3] and $rs[2] > $c_limit;
        return warning( $me, [' replay lag time: ' . to_interval( $rs[2] ) ], \@perfdata )
            if $rs[3] and $rs[2] > $w_limit;
    }

    return ok( $me, [ ' replay is paused.' ], \@perfdata );

}



=item B<btree_bloat>

Estimate bloat on B-tree indexes.

Warning and critical thresholds accept a comma separated list of either
raw number(for a size), size (eg. 125M) or percentage. The thresholds stand for
bloat size, not object size. If a percentage is given, the threshold will apply
to the bloat size compared to the total index size. If multiple threshold
values are passed, check_pgactivity will choose the one resulting in the biggest
bloat size allowed.

This service supports argument C<--exclude REGEX> to exclude relation matching
the given regexp from the check. The regexps apply on
"schema_name.relation_name". You can give multiple C<--exclude REGEX>.

Warning, with a non-superuser role, only index on table the role is granted to
access are checked!

Perfdata will give the number of indexes concerned by warning and critical
threshold per database.

=cut

sub check_btree_bloat {
    my @perfdata;
    my @longmsg;
    my @exclude_re;
    my @rs;
    my @hosts;
    my @all_db;
    my $total_index; # num of index checked, without excluded ones
    my $w_count = 0;
    my $c_count = 0;
    my %args    = %{ $_[0] };
    my $me      = 'POSTGRES_BTREE_BLOAT';
    my %queries = (
      # text types header is 4
      $PG_VERSION_74 =>  q{
      SELECT current_database(), nspname AS schemaname, tblname, idxname,
        bs*(sub.relpages)::bigint AS real_size,
        bs * otta::bigint AS estimated_size,
        bs * (sub.relpages-otta)::bigint AS bloat_size,
        100 * (sub.relpages-otta)::float / sub.relpages::float AS bloat_ratio
      FROM (
        SELECT bs, nspname, table_oid, tblname, idxname, relpages, coalesce(
          ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) + 1, 0
        ) AS otta, is_na
        FROM (
          SELECT bs, nspname, tblname, idxname, reltuples, relpages,
            ( tuple_hdr_bm + nulldatawidth + (2*ma)
             - CASE WHEN tuple_hdr_bm%ma = 0 THEN ma ELSE tuple_hdr_bm%ma END
             - CASE
                WHEN nulldatawidth = 0 THEN 0
                WHEN nulldatawidth::integer%ma = 0 THEN ma
                ELSE nulldatawidth::integer%ma
              END
            )::numeric AS nulldatahdrwidth, pagehdr, relam, table_oid, is_na
          FROM (
            SELECT i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.relam, a.attrelid AS table_oid,
              CASE cluster_version.v > 7
                WHEN true THEN current_setting('block_size')::numeric
                ELSE 8192::numeric
              END AS bs,
              CASE WHEN version()~'mingw32'
                OR version()~'64-bit|x86_64|ppc64|ia64|amd64'
                THEN 8 ELSE 4
              END AS ma,
              CASE WHEN cluster_version.v > 7 THEN 24 ELSE 20 END AS pagehdr,
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                THEN 2 ELSE 6
              END AS tuple_hdr_bm,
              sum( (1-coalesce(s.null_frac, 0))
                * coalesce(
                  CASE
                    WHEN t.typlen = -1 THEN s.avg_width + 4
                    WHEN t.typlen = -2 THEN s.avg_width + 1
                    ELSE t.typlen
                  END
                , 1024)) AS nulldatawidth,
              max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
            FROM pg_attribute AS a
            JOIN (
              SELECT nspname, tbl.relname AS tblname, idx.relam, indrelid,
                idx.relname AS idxname, idx.reltuples, idx.relpages,
                string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(indkey)), ' ')::smallint[] AS attnum
              FROM pg_index
                JOIN pg_class idx ON idx.oid = pg_index.indexrelid
                JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
                JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
              WHERE tbl.relkind = 'r'
            ) AS i ON i.indrelid = a.attrelid AND a.attnum = ANY (i.attnum)
            JOIN pg_type AS t ON a.atttypid = t.oid
            JOIN pg_stats AS s ON s.schemaname = i.nspname
              AND s.tablename = i.tblname AND s.attname = a.attname,
            ( SELECT substring(current_setting('server_version') FROM '#"[0-9]+#"%' FOR '#')::integer ) AS cluster_version(v)
            WHERE a.attnum > 0
            GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, cluster_version.v
          ) AS s1
        ) AS s2
        JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
      ) AS sub
      WHERE NOT is_na
      ORDER BY 2,3,4},
      # new column pg_index.indisvalid
      $PG_VERSION_82 =>  q{
      SELECT current_database(), nspname AS schemaname, tblname, idxname,
        bs*(sub.relpages)::bigint AS real_size,
        bs * otta::bigint AS estimated_size,
        bs * (sub.relpages-otta)::bigint AS bloat_size,
        100 * (sub.relpages-otta)::float / sub.relpages::float AS bloat_ratio
      FROM (
        SELECT bs, nspname, table_oid, tblname, idxname, relpages, coalesce(
          ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) + 1, 0
        ) AS otta, is_na
        FROM (
          SELECT bs, nspname, tblname, idxname, reltuples, relpages,
            ( tuple_hdr_bm + nulldatawidth + (2*ma)
             - CASE WHEN tuple_hdr_bm%ma = 0 THEN ma ELSE tuple_hdr_bm%ma END
             - CASE
                WHEN nulldatawidth = 0 THEN 0
                WHEN nulldatawidth::integer%ma = 0 THEN ma
                ELSE nulldatawidth::integer%ma
              END
            )::numeric AS nulldatahdrwidth, pagehdr, relam, table_oid, is_na
          FROM (
            SELECT i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.relam, a.attrelid AS table_oid,
              current_setting('block_size')::numeric AS bs,
              CASE WHEN version()~'mingw32'
                OR version()~'64-bit|x86_64|ppc64|ia64|amd64'
                THEN 8 ELSE 4
              END AS ma,
              24 AS pagehdr,
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                THEN 2 ELSE 6
              END AS tuple_hdr_bm,
              sum( (1-coalesce(s.null_frac, 0))
                * coalesce(
                  CASE
                    WHEN t.typlen = -1 THEN s.avg_width + 4
                    WHEN t.typlen = -2 THEN s.avg_width + 1
                    ELSE t.typlen
                  END
                , 1024)) AS nulldatawidth,
              max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
            FROM pg_attribute AS a
            JOIN (
              SELECT nspname, tbl.relname AS tblname, idx.relam, indrelid,
                idx.relname AS idxname, idx.reltuples, idx.relpages,
                string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(indkey)), ' ')::smallint[] AS attnum
              FROM pg_index
                JOIN pg_class idx ON idx.oid = pg_index.indexrelid
                JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
                JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
              WHERE pg_index.indisvalid AND tbl.relkind = 'r'
            ) AS i ON i.indrelid = a.attrelid AND a.attnum = ANY (i.attnum)
            JOIN pg_type AS t ON a.atttypid = t.oid
            JOIN pg_stats AS s ON s.schemaname = i.nspname
              AND s.tablename = i.tblname AND s.attname = a.attname
            WHERE a.attnum > 0
            GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
          ) AS s1
        ) AS s2
        JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
      ) AS sub
      WHERE NOT is_na
      ORDER BY 2,3,4},
      # text types header is 1 or 4
      $PG_VERSION_83 =>  q{
      SELECT current_database(), nspname AS schemaname, tblname, idxname,
        bs*(sub.relpages)::bigint AS real_size,
        bs * otta::bigint AS estimated_size,
        bs * (sub.relpages-otta)::bigint AS bloat_size,
        100 * (sub.relpages-otta)::float / sub.relpages::float AS bloat_ratio
      FROM (
        SELECT bs, nspname, table_oid, tblname, idxname, relpages, coalesce(
          ceil((reltuples*(4+nulldatahdrwidth))/(bs-pagehdr::float)) + 1, 0
        ) AS otta, is_na
        FROM (
          SELECT bs, nspname, tblname, idxname, reltuples, relpages,
            ( tuple_hdr_bm + nulldatawidth + (2*ma)
             - CASE WHEN tuple_hdr_bm%ma = 0 THEN ma ELSE tuple_hdr_bm%ma END
             - CASE
                WHEN nulldatawidth = 0 THEN 0
                WHEN nulldatawidth::integer%ma = 0 THEN ma
                ELSE nulldatawidth::integer%ma
              END
            )::numeric AS nulldatahdrwidth, pagehdr, relam, table_oid, is_na
          FROM (
            SELECT i.nspname, i.tblname, i.idxname, i.reltuples, i.relpages,
              i.relam, a.attrelid AS table_oid,
              current_setting('block_size')::numeric AS bs,
              CASE WHEN version()~'mingw32'
                OR version()~'64-bit|x86_64|ppc64|ia64|amd64'
                THEN 8 ELSE 4
              END AS ma,
              24 AS pagehdr,
              CASE WHEN max(coalesce(s.null_frac,0)) = 0
                THEN 2 ELSE 6
              END AS tuple_hdr_bm,
              sum( (1-coalesce(s.null_frac, 0))
                * coalesce(
                  CASE
                    WHEN t.typlen = -1 THEN
                      CASE WHEN s.avg_width < 127
                        THEN s.avg_width + 1 ELSE s.avg_width + 4
                      END
                    WHEN t.typlen = -2 THEN s.avg_width + 1
                    ELSE t.typlen
                  END
                , 1024)) AS nulldatawidth,
              max( CASE WHEN a.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
            FROM pg_attribute AS a
            JOIN (
              SELECT nspname, tbl.relname AS tblname, idx.relam, indrelid,
                idx.relname AS idxname, idx.reltuples, idx.relpages,
                string_to_array(pg_catalog.textin(pg_catalog.int2vectorout(indkey)), ' ')::smallint[] AS attnum
              FROM pg_index
                JOIN pg_class idx ON idx.oid = pg_index.indexrelid
                JOIN pg_class tbl ON tbl.oid = pg_index.indrelid
                JOIN pg_namespace ON pg_namespace.oid = idx.relnamespace
              WHERE pg_index.indisvalid AND tbl.relkind = 'r'
            ) AS i ON i.indrelid = a.attrelid AND a.attnum = ANY (i.attnum)
            JOIN pg_type AS t ON a.atttypid = t.oid
            JOIN pg_stats AS s ON s.schemaname = i.nspname
              AND s.tablename = i.tblname AND s.attname = a.attname
            WHERE a.attnum > 0
            GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
          ) AS s1
        ) AS s2
        JOIN pg_am am ON s2.relam = am.oid WHERE am.amname = 'btree'
      ) AS sub
      WHERE NOT is_na
      ORDER BY 2,3,4}
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "btree_bloat".',
        -exitval => 127
    ) if @hosts != 1;

    push @exclude_re => qr/$_/ foreach @{ $args{'exclude'} };

    @all_db = @{ get_all_dbname( $hosts[0] ) };

    # Iterate over all db
    ALLDB_LOOP: foreach my $db (sort @all_db) {
        my @rc = @{ query_ver( $hosts[0], %queries, $db ) };
        # Var to handle max,avg and count for size and percentage, per relkind
        my $nb_ind      = 0;
        my $idx_bloated = 0;
        BLOAT_LOOP: foreach my $bloat (@rc) {

            foreach my $exclude_re (@exclude_re) {
                next BLOAT_LOOP if "$bloat->[1].$bloat->[3]" =~ m/$exclude_re/;
            }

            if ( defined $args{'warning'} ) {
                my $w_limit = 0;
                my $c_limit = 0;
                # We need to compute effective thresholds on each object,
                # as the value can be given in percentage
                # The biggest calculated size will be used.
                foreach my $cur_warning (split /,/, $args{'warning'}) {
                    my $size = get_size( $cur_warning, $bloat->[4] );
                    $w_limit = $size if $size > $w_limit;
                }
                foreach my $cur_critical (split /,/, $args{'critical'}) {
                    my $size = get_size( $cur_critical, $bloat->[4] );
                    $c_limit = $size if $size > $c_limit;
                }

                if ( $bloat->[6] > $w_limit ) {
                    $idx_bloated++;
                    $w_count++;
                    $c_count++ if $bloat->[6] > $c_limit;

                    push @longmsg => sprintf "%s.%s.%s %s/%s (%.2f%%);",
                        $bloat->[0], $bloat->[1], $bloat->[3],
                        to_size($bloat->[6]), to_size($bloat->[4]), $bloat->[7];
                }
            }

            $nb_ind++;
        }

        $total_index += $nb_ind;

        # Construct perfdata
        my $perf = sprintf "'idx bloated in %s'=%s",
                $db, $idx_bloated;

        push @perfdata => $perf;
    }

    # we use the warning count for the **total** number of bloated index
    return critical $me,
        [ "$w_count/$total_index index(es) bloated" ],
        [ @perfdata ], [ @longmsg ]
            if $c_count > 0;
    return warning $me,
        [ "$w_count/$total_index index(es) bloated" ],
        [ @perfdata ], [ @longmsg ]
            if $w_count > 0;
    return ok $me, [ "Btree bloat ok" ], \@perfdata;
}



=item B<table_bloat>

Estimate bloat on tables.

Warning and critical thresholds accept a comma separated list of either
raw number(for a size), size (eg. 125M) or percentage. The thresholds stand for
bloat size, not object size. If a percentage is given, the threshold will apply
to the bloat size compared to the table+toast size. If multiple threshold
values are passed, check_pgactivity will choose the one resulting in the biggest
bloat size allowed.

This service supports argument C<--exclude REGEX> to exclude relation matching
the given regexp from the check. The regexps apply on
"schema_name.relation_name". You can give multiple C<--exclude REGEX>.

Warning, with a non-superuser role, only index on table the role is granted to
access are checked!

Perfdata will give the number of tables concerned by warning and critical
threshold per database.

=cut

sub check_table_bloat {
    my @perfdata;
    my @longmsg;
    my @exclude_re;
    my @rs;
    my @hosts;
    my @all_db;
    my $total_tbl = 0; # num of table checked, without excluded ones
    my $w_count   = 0;
    my $c_count   = 0;
    my %args      = %{ $_[0] };
    my $me        = 'POSTGRES_TABLE_BLOAT';
    my %queries   = (
        # text types header is 4, page header is 20 and block size 8192
        $PG_VERSION_74 =>  q{
          SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
            (tblpages-est_num_pages)*bs AS bloat_size,
            CASE WHEN tblpages - est_num_pages > 0
              THEN 100 * (tblpages - est_num_pages)/tblpages::float
              ELSE 0
            END AS bloat_ratio
          FROM (
            SELECT
              ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_num_pages, tblpages,
              bs, tblid, schemaname, tblname, heappages, toastpages, is_na
            FROM (
              SELECT
                ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
                  - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
                  - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
                ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
                toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, is_na
              FROM (
                SELECT
                  tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
                  tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                  coalesce(toast.reltuples, 0) AS toasttuples,
                  8192::numeric AS bs,
                  CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                  20 AS page_hdr,
                  23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
                      + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
                  sum( (1-coalesce(s.null_frac, 0))
                    * coalesce(
                      CASE
                        WHEN t.typlen = -1 THEN s.avg_width + 4
                        WHEN t.typlen = -2 THEN s.avg_width + 1
                        ELSE t.typlen
                      END
                    , 1024)) AS tpl_data_size,
                  max( CASE WHEN att.atttypid = 'pg_catalog.name'::regtype THEN 1 ELSE 0 END ) > 0 AS is_na
                FROM pg_attribute att
                  JOIN pg_type AS t ON att.atttypid = t.oid
                  JOIN pg_class tbl ON att.attrelid = tbl.oid
                  JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
                  JOIN pg_stats s ON s.schemaname=ns.nspname
                    AND s.tablename = tbl.relname
                    AND s.attname=att.attname
                  LEFT JOIN pg_class toast ON tbl.reltoastrelid = toast.oid
                WHERE att.attnum > 0 AND NOT att.attisdropped
                  AND tbl.relkind = 'r'
                GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
                ORDER BY 2,3
              ) as s
            ) as s2
          ) AS s3
          WHERE NOT is_na},
        # variable block size, page header is 24
        $PG_VERSION_80 =>  q{
          SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
            (tblpages-est_num_pages)*bs AS bloat_size,
            CASE WHEN tblpages - est_num_pages > 0
              THEN 100 * (tblpages - est_num_pages)/tblpages::float
              ELSE 0
            END AS bloat_ratio
          FROM (
            SELECT ceil(reltuples/((bs-page_hdr)/tpl_size)) + ceil(toasttuples/4) AS est_num_pages,
              tblpages, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
            FROM (
              SELECT
                ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
                  - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
                  - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
                ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
                toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, is_na
              FROM (
                SELECT
                  tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
                  tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                  coalesce(toast.reltuples, 0) AS toasttuples,
                  current_setting('block_size')::numeric AS bs,
                  CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                  24 AS page_hdr,
                  23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
                    + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
                  sum( (1-coalesce(s.null_frac, 0))
                    * coalesce(
                      CASE
                        WHEN t.typlen = -1 THEN s.avg_width + 4
                        WHEN t.typlen = -2 THEN s.avg_width + 1
                        ELSE t.typlen
                      END
                    , 1024)) AS tpl_data_size,
                  bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
                FROM pg_attribute AS att
                  JOIN pg_type AS t ON att.atttypid = t.oid
                  JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                  JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                  JOIN pg_stats AS s ON s.schemaname=ns.nspname
                    AND s.tablename = tbl.relname AND s.attname=att.attname
                  LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
                WHERE att.attnum > 0 AND NOT att.attisdropped
                  AND tbl.relkind = 'r'
                GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
                ORDER BY 2,3
              ) AS s
            ) AS s2
          ) AS s3
          WHERE NOT is_na},
        # text types header is 1 or 4
        $PG_VERSION_83 =>  q{
          SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
            (tblpages-est_num_pages)*bs AS bloat_size,
            CASE WHEN tblpages - est_num_pages > 0
              THEN 100 * (tblpages - est_num_pages)/tblpages::float
              ELSE 0
            END AS bloat_ratio
          FROM (
            SELECT ceil(reltuples/((bs-page_hdr)/tpl_size)) + ceil(toasttuples/4) AS est_num_pages,
              tblpages, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
            FROM (
              SELECT
                ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
                  - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
                  - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
                ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
                toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, is_na
              FROM (
                SELECT
                  tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
                  tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                  coalesce(toast.reltuples, 0) AS toasttuples,
                  current_setting('block_size')::numeric AS bs,
                  CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                  24 AS page_hdr,
                  23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
                    + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
                  sum( (1-coalesce(s.null_frac, 0))
                    * coalesce(
                      CASE
                        WHEN t.typlen = -1 THEN
                          CASE WHEN s.avg_width < 127
                            THEN s.avg_width + 1 ELSE s.avg_width + 4
                          END
                        WHEN t.typlen = -2 THEN s.avg_width + 1
                        ELSE t.typlen
                      END
                    , 1024)) AS tpl_data_size,
                  bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
                FROM pg_attribute AS att
                  JOIN pg_type AS t ON att.atttypid = t.oid
                  JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                  JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                  JOIN pg_stats AS s ON s.schemaname=ns.nspname
                    AND s.tablename = tbl.relname AND s.attname=att.attname
                  LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
                WHERE att.attnum > 0 AND NOT att.attisdropped
                  AND tbl.relkind = 'r'
                GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
                ORDER BY 2,3
              ) AS s
            ) AS s2
          ) AS s3
          WHERE NOT is_na},
        # exclude inherited stats
        $PG_VERSION_90 =>  q{
          SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
            (tblpages-est_num_pages)*bs AS bloat_size,
            CASE WHEN tblpages - est_num_pages > 0
              THEN 100 * (tblpages - est_num_pages)/tblpages::float
              ELSE 0
            END AS bloat_ratio
          FROM (
            SELECT ceil(reltuples/((bs-page_hdr)/tpl_size)) + ceil(toasttuples/4) AS est_num_pages,
              tblpages, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
            FROM (
              SELECT
                ( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
                  - CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
                  - CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
                ) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
                toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, is_na
              FROM (
                SELECT
                  tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
                  tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
                  coalesce(toast.reltuples, 0) AS toasttuples,
                  current_setting('block_size')::numeric AS bs,
                  CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
                  24 AS page_hdr,
                  23 + CASE WHEN MAX(coalesce(null_frac,0)) > 0 THEN ( 7 + count(*) ) / 8 ELSE 0::int END
                    + CASE WHEN tbl.relhasoids THEN 4 ELSE 0 END AS tpl_hdr_size,
                  sum( (1-coalesce(s.null_frac, 0))
                    * coalesce(
                      CASE
                        WHEN t.typlen = -1 THEN
                          CASE WHEN s.avg_width < 127
                            THEN s.avg_width + 1 ELSE s.avg_width + 4
                          END
                        WHEN t.typlen = -2 THEN s.avg_width + 1
                        ELSE t.typlen
                      END
                    , 1024)) AS tpl_data_size,
                  bool_or(att.atttypid = 'pg_catalog.name'::regtype) AS is_na
                FROM pg_attribute AS att
                  JOIN pg_type AS t ON att.atttypid = t.oid
                  JOIN pg_class AS tbl ON att.attrelid = tbl.oid
                  JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
                  JOIN pg_stats AS s ON s.schemaname=ns.nspname
                    AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
                  LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
                WHERE att.attnum > 0 AND NOT att.attisdropped
                  AND tbl.relkind = 'r'
                GROUP BY 1,2,3,4,5,6,7,8,9,10, tbl.relhasoids
                ORDER BY 2,3
              ) AS s
            ) AS s2
          ) AS s3
          WHERE NOT is_na}
    );

    # warning and critical are mandatory.
    pod2usage(
        -message => "FATAL: you must specify critical and warning thresholds.",
        -exitval => 127
    ) unless defined $args{'warning'} and defined $args{'critical'} ;

    @hosts = @{ parse_hosts %args };

    pod2usage(
        -message => 'FATAL: you must give only one host with service "table_bloat".',
        -exitval => 127
    ) if @hosts != 1;

    push @exclude_re => qr/$_/ foreach @{ $args{'exclude'} };

    @all_db = @{ get_all_dbname( $hosts[0] ) };

    # Iterate over all db
    ALLDB_LOOP: foreach my $db (sort @all_db) {
        my @rc = @{ query_ver( $hosts[0], %queries, $db ) };
        # Var to handle max,avg and count for size and percentage, per relkind
        my $nb_tbl      = 0;
        my $tbl_bloated = 0;
        BLOAT_LOOP: foreach my $bloat (@rc) {

            foreach my $exclude_re (@exclude_re) {
                next BLOAT_LOOP if "$bloat->[1].$bloat->[2]" =~ m/$exclude_re/;
            }

            if ( defined $args{'warning'} ) {
                my $w_limit = 0;
                my $c_limit = 0;
                # We need to compute effective thresholds on each object,
                # as the value can be given in percentage
                # The biggest calculated size will be used.
                foreach my $cur_warning (split /,/, $args{'warning'}) {
                    my $size = get_size( $cur_warning, $bloat->[3] );
                    $w_limit = $size if $size > $w_limit;
                }
                foreach my $cur_critical (split /,/, $args{'critical'}) {
                    my $size = get_size( $cur_critical, $bloat->[3] );
                    $c_limit = $size if $size > $c_limit;
                }

                if ( $bloat->[4] > $w_limit ) {
                    $tbl_bloated++;
                    $w_count++;
                    $c_count++ if $bloat->[4] > $c_limit;

                    push @longmsg => sprintf "%s.%s.%s %s/%s (%.2f%%);",
                        $bloat->[0], $bloat->[1], $bloat->[2],
                        to_size($bloat->[4]), to_size($bloat->[3]), $bloat->[5];
                }
            }

            $nb_tbl++;
        }

        $total_tbl += $nb_tbl;

        # Construct perfdata
        my $perf = sprintf "'table bloated in %s'=%s",
                $db, $tbl_bloated;

        push @perfdata => $perf;
    }

    # we use the warning count for the **total** number of bloated index
    return critical $me,
        [ "$w_count/$total_tbl table(s) bloated" ],
        [ @perfdata ], [ @longmsg ]
            if $c_count > 0;
    return warning $me,
        [ "$w_count/$total_tbl table(s) bloated" ],
        [ @perfdata ], [ @longmsg ]
            if $w_count > 0;
    return ok $me, [ "Table bloat ok" ], \@perfdata;
}

# End of SERVICE section in pod doc
=pod

=back

=cut

Getopt::Long::Configure('bundling');
GetOptions(
    \%args,
        'service|s=s',
        'host|h=s',
        'username|U=s',
        'port|p=s',
        'dbname|d=s',
        'dbservice|S=s',
        'warning|w=s',
        'critical|c=s',
        'exclude=s',
        'tmpdir=s',
        'psql|P=s',
        'path=s',
        'status-file=s',
        'query=s',
        'type=s',
        'reverse!',
        'work_mem=i',
        'maintenance_work_mem=i',
        'shared_buffers=i',
        'wal_buffers=i',
        'checkpoint_segments=i',
        'effective_cache_size=i',
        'no_check_autovacuum!',
        'no_check_fsync!',
        'no_check_enable!',
        'no_check_track_counts!',
        'ignore-wal-size!',
        'suffix=s',
        'slave=s',
        'list!',
        'version|V!',
        'help|?!',
        'debug!',
        'timeout|t=s'
) or pod2usage( -exitval => 127 );

list_services() if $args{'list'};
version()       if $args{'version'};

pod2usage( -verbose => 2 ) if $args{'help'};


# One service must be given
pod2usage(
    -message => "FATAL: you must specify one service.\n"
        . "    See -s or --service command line option.",
    -exitval => 127
) unless defined $args{'service'};


# Check that the given service exists.
pod2usage(
    -message => "FATAL: service $args{'service'} does not exist.\n"
        . "    Use --list to show the available services.",
    -exitval => 127
) unless exists $services{ $args{'service'} };


# Check we have write permission to the tempdir
pod2usage(
    -message => 'FATAL: temp directory given or found not writable.',
    -exitval => 127
) if not -d $args{'tmpdir'} or not -x $args{'tmpdir'};

# Both critical and warning must be given is optionnal,
# but for pga_version and minor_version which use only one of them.
pod2usage(
    -message => 'FATAL: you must provide both warning and critical thresholds.',
    -exitval => 127
) if $args{'service'} !~ m/^(pga_version|minor_version)$/ and (
    ( defined $args{'critical'} and not defined $args{'warning'} )
    or ( not defined $args{'critical'} and defined $args{'warning'} ));

# query, type and reverse are only allowed with "custom_query" service
pod2usage(
    -message => 'FATAL: query, type and reverse are only allowed with "custom_query" service.',
    -exitval => 127
) if ( ( defined $args{'query'} or defined $args{'type'} or $args{'reverse'} == 1 ) and ( $args{'service'} ne 'custom_query' ) );


# Check "configuration" specific arg
pod2usage(
    -message => 'FATAL: work_mem, maintenance_work_mem, shared_buffers, wal_buffers, checkpoint_segments, effective_cache_size, no_check_autovacuum, no_check_fsync, no_check_enable, no_check_track_counts are only allowed with "configuration" service.',
    -exitval => 127
) if ( (defined $args{'work_mem'} or defined $args{'maintenance_work_mem'} or defined $args{'shared_buffers'}
    or defined $args{'wal_buffers'} or defined $args{'checkpoint_segments'} or defined $args{'effective_cache_size'}
    or $args{'no_check_autovacuum'} == 1 or $args{'no_check_fsync'} == 1 or $args{'no_check_enable'} ==1
    or $args{'no_check_track_counts'} == 1) and ( $args{'service'} ne 'configuration' ) );


# Check "archive_folder" specific args --ignore-wal-size and --suffix
pod2usage(
    -message => 'FATAL: "ignore-wal-size" and "suffix" are only allowed with "archive_folder" service.',
    -exitval => 127
) if ( $args{'ignore-wal-size'} or $args{'suffix'} )
    and $args{'service'} ne 'archive_folder';


# Check "streaming_delta" specific args --slave
pod2usage(
    -message => 'FATAL: "slave" is only allowed with "streaming_delta" service.',
    -exitval => 127
) if scalar @{ $args{'slave'} }  and $args{'service'} ne 'streaming_delta';


# Set psql absolute path
unless ($args{'psql'}) {
    if ( $ENV{PGBINDIR} ) {
        $args{'psql'} = "$ENV{PGBINDIR}/psql";
    }
    else {
        $args{'psql'} = 'psql';
    }
}


exit $services{ $args{'service'} }{'sub'}->( \%args );

__END__

=head2 EXAMPLES

=over

=item C<check_pgactivity -h localhost -p 5492 -s last_vacuum -w 30m -c 1h30m>

Execute service "last_vacuum" on host "host=localhost port=5432".

=item C<check_pgactivity --debug --dbservice pg92,pg92s --service streaming_delta -w 60 -c 90>

Execute service "streaming_delta" between hosts "service=pg92" and "service=pg92s".

=item C<check_pgactivity --debug --dbservice pg92 -h slave -U supervisor --service streaming_delta -w 60 -c 90>

Execute service "streaming_delta" between hosts "service=pg92" and "host=slave user=supervisor".

=back

=head2 LICENSING

This program is open source, licensed under the PostgreSQL license.
For license terms, see the LICENSE provided with the sources.

=head2 AUTHORS

Author: Open PostgreSQL Monitoring Development Group
Copyright: (C) 2012-2014 Open PostgreSQL Development Group

=cut
