postgres_streaming_

#!/usr/bin/perl
#
# Copyright (C) 2010 Magnus Hagander, Redpill Linpro AB
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; version 2 dated June,
# 1991.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 USA.

=head1 NAME

postgres_streaming_ - Plugin to monitor PostgreSQL streaming replication lag.

=head1 CONFIGURATION

The configuration for the database connections need to be made in
pg_service.conf (see the PostgreSQL documentation). To specify which
cluster to monitor, link this plugin to
postgres_streaming_<master>:<slave>, where <master> and <slave> are
the names of the services specified in pg_service.conf.

=head1 SEE ALSO

=head1 MAGIC MARKERS

 #%# family=manual

=head1 AUTHOR

Magnus Hagander <magnus@hagander.net>, Redpill Linpro AB

=head1 COPYRIGHT/License.

Copyright (c) 2010 Magnus Hagander, Redpill Linpro AB

All rights reserved. This program is free software; you can
redistribute it and/or modify it under the terms of the GNU General
Public License as published by the Free Software Foundation; version 2
dated June, 1991.

=cut

use strict;
use warnings;

use bigint;

use DBI;
use DBD::Pg;

if (defined $ARGV[0] && $ARGV[0] ne '') {
    if ($ARGV[0] eq 'autoconf') {
        print "no (autoconf not supported)\n";
        exit(1);
    }
    elsif ($ARGV[0] eq 'config') {
        print "graph_title PostgreSQL replication lag\n";
        print "graph_vlabel Lag (kb xlog)\n";
        print "graph_category db\n";
        print "graph_info PostgreSQL streaming replication lag\n";
        print "graph_args --base 1024\n";
        print "receive.label Receive delay\n";
        print "receive.type GAUGE\n";
        print "receive.draw LINE1\n";
        print "receive.min 0\n";
        print "apply.label Apply delay\n";
        print "apply.type GAUGE\n";
        print "apply.draw LINE1\n";
        print "apply.min 0\n";
        exit(0);
    }
    else {
        print "Unknown command: '$ARGV[0]'\n";
        exit(1);
    }
}

# Process!
unless ($0 =~ /postgres_streaming_([^:]+):([^:]+)$/) {
    print "Invalid link: $0. Must be postgres_streaming_<master>:<slave>\n";
    exit(2);
}
my $master = $1;
my $slave  = $2;

my $dbmaster = DBI->connect("DBI:Pg:service=$master") or die "Could not connect to master at $master\n";
my $dbslave  = DBI->connect("DBI:Pg:service=$slave")  or die "Could not connect to slave at $slave\n";

my $masterdata;
my $slavedata;

# PostgreSQL 10 renamed the WAL status functions as follows:
#
#   - pg_current_xlog_location()      -> pg_current_wal_lsn()
#   - pg_last_xlog_receive_location() -> pg_last_wal_receive_lsn()
#   - pg_last_xlog_replay_location()  -> pg_last_wal_replay_lsn()
#
(my $pg_maj_ver) = $dbmaster->{pg_server_version} =~ /(\d+)(\d){2,2}(\d){2,2}$/;
if ($pg_maj_ver < 10) {
    $masterdata = $dbmaster->selectall_arrayref("SELECT pg_current_xlog_location()") or die "Could not query for xlog location on master\n";
    $slavedata = $dbslave->selectall_arrayref("SELECT pg_last_xlog_receive_location(), pg_last_xlog_replay_location()") or die "Could not query for xlog locations on slave\n";
} else {
    $masterdata = $dbmaster->selectall_arrayref("SELECT pg_current_wal_lsn()") or die "Could not query for WAL location on master\n";
    $slavedata = $dbslave->selectall_arrayref("SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn()") or die "Could not query for WAL locations on slave\n";
}

$dbmaster->disconnect();
$dbslave->disconnect();

my $master_num    = CalculateNumericalOffset($masterdata->[0]->[0]);
my $receive_delay = ($master_num - CalculateNumericalOffset($slavedata->[0]->[0]));
my $replay_delay  = ($master_num - CalculateNumericalOffset($slavedata->[0]->[1]));

print "receive.value $receive_delay\n";
print "apply.value $replay_delay\n";

exit(0);


sub CalculateNumericalOffset
{
    my $stringofs = shift;

    my @pieces = split /\//, $stringofs;
    die "Invalid offset: $stringofs" unless ($#pieces == 1);

    # First part is logid, second part is record offset
    return (hex("ff000000") * hex($pieces[0])) + hex($pieces[1]);
}