[check_postgres] [commit] Add autovac_freeze action,
bump to version 2.1.0
check_postgres at bucardo.org
check_postgres at bucardo.org
Fri Jul 18 22:06:40 UTC 2008
Committed by Greg Sabino Mullane <greg at endpoint.com>
Add autovac_freeze action, bump to version 2.1.0
---
check_postgres.pl | 119 ++++++++++++++++++++++++++++++++++++++++++++++--
check_postgres.pl.asc | 6 +-
check_postgres.pl.html | 37 ++++++++++++++-
index.html | 6 +-
t/99_spellcheck.t | 1 +
5 files changed, 156 insertions(+), 13 deletions(-)
diff --git a/check_postgres.pl b/check_postgres.pl
index c9c5f5b..d25c8cc 100755
--- a/check_postgres.pl
+++ b/check_postgres.pl
@@ -28,7 +28,7 @@ $Data::Dumper::Varname = 'POSTGRES';
$Data::Dumper::Indent = 2;
$Data::Dumper::Useqq = 1;
-our $VERSION = '2.0.1';
+our $VERSION = '2.1.0';
use vars qw/ %opt $PSQL $res $COM $SQL $db /;
@@ -168,6 +168,7 @@ if ($opt{version}) {
## Quick hash to put normal action information in one place:
our $action_info = {
# Name # clusterwide? # helpstring
+ autovac_freeze => [1, 'Checks how close databases are to autovacuum_freeze_max_age.'],
backends => [1, 'Number of connections, compared to max_connections.'],
bloat => [0, 'Check for table and index bloat.'],
connection => [0, 'Simple connection check.'],
@@ -462,6 +463,7 @@ our $checksumre = qr{^[a-f0-9]{32}$};
## If in test mode, verify that we can run each requested action
our %testaction = (
+ autovac_freeze => 'VERSION: 8.2',
last_vacuum => 'ON: stats_row_level(<8.3) VERSION: 8.2',
last_analyze => 'ON: stats_row_level(<8.3) VERSION: 8.2',
last_autovacuum => 'ON: stats_row_level(<8.3) VERSION: 8.2',
@@ -653,6 +655,9 @@ check_custom_query() if $action eq 'custom_query';
## Test of replication
check_replicate_row() if $action eq 'replicate_row';
+## See how close we are to autovacuum_freeze_max_age
+check_autovac_freeze() if $action eq 'autovac_freeze';
+
finishup();
exit 0;
@@ -1184,6 +1189,18 @@ sub validate_range {
my $regex = ($string =~ s/^~//) ? '~' : '=';
$string =~ /^\w+$/ or die qq{Invalid option\n};
}
+ elsif ('percent' eq $type) {
+ if (length $critical) {
+ if ($critical !~ /^\d+\%$/) {
+ ndie qq{Invalid 'critical' option: must be percentage\n};
+ }
+ }
+ if (length $warning) {
+ if ($warning !~ /^\d+\%$/) {
+ ndie qq{Invalid 'warning' option: must be percentage\n};
+ }
+ }
+ }
elsif ('size or percent' eq $type) {
if (length $critical) {
if ($critical =~ $sizere) {
@@ -1267,6 +1284,79 @@ sub validate_range {
} ## end of validate_range
+sub check_autovac_freeze {
+
+ ## Check how close all databases are to autovacuum_freeze_max_age
+ ## Supports: Nagios, MRTG
+ ## It makes no sense to run this more than once on the same cluster
+ ## Warning and criticals are percentages
+ ## Can also ignore databases with exclude, and limit with include
+
+ my ($warning, $critical) = validate_range
+ ({
+ type => 'percent',
+ default_warning => '90%',
+ default_critical => '95%',
+ forcemrtg => 1,
+ });
+
+ (my $w = $warning) =~ s/\D//;
+ (my $c = $critical) =~ s/\D//;
+ my $SQL = q{SELECT freez, txns, ROUND(100*(txns/freez::float)) AS perc, datname}.
+ q{ FROM (SELECT foo.freez::int, age(datfrozenxid) AS txns, datname}.
+ q{ FROM pg_database d JOIN (SELECT setting AS freez FROM pg_settings WHERE name = 'autovacuum_freeze_max_age') AS foo}.
+ q{ ON (true)) AS foo2 ORDER BY 3 DESC, 4 ASC};
+ my $info = run_command($SQL, {regex => qr[\w+] } );
+
+ for $db (@{$info->{db}}) {
+ my (@crit, at warn, at ok);
+ my ($maxp,$maxt,$maxdb) = (0,0,''); ## used by MRTG only
+ SLURP: while ($db->{slurp} =~ /\s*(\d+) \|\s+(\d+) \|\s+(\d+) \| (.+?)$/gsm) {
+ my ($freeze,$age,$percent,$dbname) = ($1,$2,$3,$4);
+ next SLURP if skip_item($dbname);
+
+ if ($MRTG) {
+ if ($percent > $maxp) {
+ $maxdb = $dbname;
+ }
+ elsif ($percent == $maxp) {
+ $maxdb .= sprintf "%s$dbname", length $maxdb ? ' | ' : '';
+ }
+ $maxt = $age if $age > $maxt;
+ next;
+ }
+
+ my $msg = "$dbname=$percent\% ($age)";
+ $db->{perf} .= " $msg";
+ if (length $critical and $percent >= $c) {
+ push @crit => $msg;
+ }
+ elsif (length $warning and $percent >= $w) {
+ push @warn => $msg;
+ }
+ else {
+ push @ok => $msg;
+ }
+ }
+ if ($MRTG) {
+ do_mrtg({one => $maxp, two => $maxt, msg => $maxdb});
+ }
+ if (@crit) {
+ add_critical join ' ' => @crit;
+ }
+ elsif (@warn) {
+ add_warning join ' ' => @warn;
+ }
+ else {
+ add_ok join ' ' => @ok;
+ }
+ }
+
+ return;
+
+} ## end of check_autovac_freeze
+
+
sub check_backends {
## Check the number of connections
@@ -1277,8 +1367,8 @@ sub check_backends {
## critical = 12 -- complain if there are 12 or more connections
## critical = 95% -- complain if >= 95% of available connections are used
## critical = -5 -- complain if there are only 5 or fewer connection slots left
- ## Can also ignore databases with exclude, and limit with include
## The former two options only work with simple numbers - no percentage or negative
+ ## Can also ignore databases with exclude, and limit with include
my $warning = $opt{warning} || '90%';
my $critical = $opt{critical} || '95%';
@@ -1383,7 +1473,6 @@ sub check_backends {
} ## end of check_backends
-
sub check_bloat {
## Check how bloated the tables and indexes are
@@ -3172,7 +3261,7 @@ check_postgres.pl - Postgres monitoring script for Nagios, MRTG, and others
=head1 VERSION
-This documents describes B<check_postgres.pl> version 2.0.1
+This documents describes B<check_postgres.pl> version 2.1.0
=head1 SYNOPSIS
@@ -3433,6 +3522,24 @@ The current supported actions are:
=over 4
+=item B<autovac_freeze> (symlink: C<check_postgres_autovac_freeze>)
+
+Checks how close each database is to the Postgres B<autovacuum_freeze_max_age> setting. This
+action will only work for databases version 8.2 or higher. The I<--warning> and
+I<--critical> options should be expressed as percentages. The 'age' of the transactions
+in each database is compared to the autovacuum_freeze_max_age setting (200 million by default)
+to generate a rounded percentage. The default values are B<90%> for the warning and B<95%> for
+the critical. Databases can be filtered by use of the I<--include> and I<--exclude> options. See
+the L</"BASIC FILTERING"> section for more details.
+
+Example 1: Give a warning when any databases on port 5432 are above 80%
+
+ check_postgres_autovac_freeze --port=5432 --warning="80%"
+
+For MRTG output, the highest overall percentage is reported on the first line, and the highest age is
+reported on the second line. All databases which have the percentage from the first line are reported
+on the fourth line, separated by a pipe symbol.
+
=item B<backends> (symlink: C<check_postgres_backends>)
Checks the current number of connections for one or more databases, and optionally
@@ -4220,6 +4327,10 @@ Items not specifically attributed are by Greg Sabino Mullane.
=over 4
+=item B<Version 2.1.0> (July 18, 2008)
+
+Add the "autovac_freeze" action, thanks to Robert Treat for the idea and design.
+
=item B<Version 2.0.1> (July 16, 2008)
Optimizations to speed up the "bloat" action quite a bit.
diff --git a/check_postgres.pl.asc b/check_postgres.pl.asc
index b8178a8..b65d222 100644
--- a/check_postgres.pl.asc
+++ b/check_postgres.pl.asc
@@ -1,6 +1,6 @@
-----BEGIN PGP SIGNATURE-----
-iEYEABEDAAYFAkh+dREACgkQvJuQZxSWSsgZsACg4VDg4TBfOnb93R5xs/M+0OaI
-Ai4AoICb2on+HJJVHhiltRcyZqvQ5PpZ
-=jv+D
+iEYEABEDAAYFAkiBDsEACgkQvJuQZxSWSsjMrwCgow2P033dfXcilqQsg6aPZJ/N
+9fYAn0CkMZfaFsxuenwLohnhFEZl8vef
+=8qMi
-----END PGP SIGNATURE-----
diff --git a/check_postgres.pl.html b/check_postgres.pl.html
index f80ae1b..0ba153e 100644
--- a/check_postgres.pl.html
+++ b/check_postgres.pl.html
@@ -63,7 +63,7 @@
</p>
<hr />
<h1><a name="version">VERSION</a></h1>
-<p>This documents describes <strong>check_postgres.pl</strong> version 2.0.1</p>
+<p>This documents describes <strong>check_postgres.pl</strong> version 2.1.0</p>
<p>
</p>
<hr />
@@ -351,6 +351,30 @@ because criticals are always checked first, setting the warning equal to the
critical is an effective way to turn warnings off and always give a critical.</p>
<p>The current supported actions are:</p>
<dl>
+<dt><strong><a name="autovac_freeze" class="item"><strong>autovac_freeze</strong> (symlink: <code>check_postgres_autovac_freeze</code>)</a></strong>
+
+<dd>
+<p>Checks how close each database is to the Postgres <strong>autovacuum_freeze_max_age</strong> setting. This
+action will only work for databases version 8.2 or higher. The <em>--warning</em> and
+<em>--critical</em> options should be expressed as percentages. The 'age' of the transactions
+in each database is compared to the autovacuum_freeze_max_age setting (200 million by default)
+to generate a rounded percentage. The default values are <strong>90%</strong> for the warning and <strong>95%</strong> for
+the critical. Databases can be filtered by use of the <em>--include</em> and <em>--exclude</em> options. See
+the <a href="#basic_filtering">BASIC FILTERING</a> section for more details.</p>
+</dd>
+<dd>
+<p>Example 1: Give a warning when any databases on port 5432 are above 80%</p>
+</dd>
+<dd>
+<pre>
+ check_postgres_autovac_freeze --port=5432 --warning="80%"</pre>
+</dd>
+<dd>
+<p>For MRTG output, the highest overall percentage is reported on the first line, and the highest age is
+reported on the second line. All databases which have the percentage from the first line are reported
+on the fourth line, separated by a pipe symbol.</p>
+</dd>
+</li>
<dt><strong><a name="backends" class="item"><strong>backends</strong> (symlink: <code>check_postgres_backends</code>)</a></strong>
<dd>
@@ -1343,13 +1367,20 @@ feature requests, and commit notices, send email to <a href="mailto:check_postgr
<h1><a name="history">HISTORY</a></h1>
<p>Items not specifically attributed are by Greg Sabino Mullane.</p>
<dl>
+<dt><strong><a name="0" class="item"><strong>Version 2.1.0</strong> (July 18, 2008)</a></strong>
+
+<dd>
+<p>Add the "autovac_freeze" action, thanks to Robert Treat for the idea and design.</p>
+</dd>
+</li>
<dt><strong><a name="1" class="item"><strong>Version 2.0.1</strong> (July 16, 2008)</a></strong>
<dd>
-<p>Optimizations to speed up the "bloat" action quite a bit.</p>
+<p>Optimizations to speed up the "bloat" action quite a bit.
+Fix "version" action to not always output in mrtg mode.</p>
</dd>
</li>
-<dt><strong><a name="0" class="item"><strong>Version 2.0.0</strong> (July 15, 2008)</a></strong>
+<dt><strong><strong>Version 2.0.0</strong> (July 15, 2008)</strong>
<dd>
<p>Add support for MRTG and "simple" output options.
diff --git a/index.html b/index.html
index 2520748..2f6825d 100644
--- a/index.html
+++ b/index.html
@@ -21,13 +21,13 @@ h1 {
<h1>check_postgres.pl</h1>
-<p><b>check_postgres.pl</b> is a script for checking the state of one or more Postgres databases and reporting back in a Nagios-friendly manner. It was developed by Greg Sabino Mullane of <a href="http://www.endpoint.com/">End Point Corporation</a> and is BSD-licensed. The latest version is <b>2.0.1</b>, and was released on July 16, 2008.</p>
+<p><b>check_postgres.pl</b> is a script for checking the state of one or more Postgres databases and reporting back in a Nagios-friendly manner. It was developed by Greg Sabino Mullane of <a href="http://www.endpoint.com/">End Point Corporation</a> and is BSD-licensed. The latest version is <b>2.1.0</b>, and was released on July 18, 2008.</p>
<ul>
- <li><a href="/check_postgres/check_postgres.pl.html">Documentation for check_postgres 2.0.1</a></li>
+ <li><a href="/check_postgres/check_postgres.pl.html">Documentation for check_postgres 2.1.0</a></li>
</ul>
<ul>
- <li><a href="/check_postgres/check_postgres.pl">Download check_postgres.pl 2.0.1</a></li>
+ <li><a href="/check_postgres/check_postgres.pl">Download check_postgres.pl 2.1.0</a></li>
<li><a href="/check_postgres/check_postgres.pl.asc">PGP signature for check_postgres.pl</a></li>
</ul>
diff --git a/t/99_spellcheck.t b/t/99_spellcheck.t
index b5fce26..6c2e2d5 100644
--- a/t/99_spellcheck.t
+++ b/t/99_spellcheck.t
@@ -177,6 +177,7 @@ xmlns
artemus
Astill
AUTOanalyze
+autovac
autovacuum
AUTOvacuum
backends
--
1.5.5.4
More information about the Check_postgres
mailing list