1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
|
#!@@PERL@@
# -*- cperl -*-
# vim: ft=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]);
}
|
