| 1 |
dpavlin |
1.4 |
#!/usr/bin/perl -w |
| 2 |
dpavlin |
1.1 |
# |
| 3 |
dpavlin |
1.5 |
# Monitor multiple postgresql databases on different hosts |
| 4 |
|
|
# |
| 5 |
dpavlin |
1.7 |
# Dobrica Pavlinusic <dpavlin@rot13.org> |
| 6 |
|
|
# http://www.rot13.org/~dpavlin/sysadm.html |
| 7 |
|
|
# |
| 8 |
dpavlin |
1.5 |
# Based on postgresql.monitor 1.3 |
| 9 |
|
|
# by Severin Luftensteiner <severin.luftensteiner@cubit.at> |
| 10 |
dpavlin |
1.7 |
# it's not limited to singe host and/or singe database. |
| 11 |
dpavlin |
1.5 |
# |
| 12 |
dpavlin |
1.7 |
# Usage: pgsql.monitor username[:password]@host/database ... |
| 13 |
dpavlin |
1.1 |
# |
| 14 |
|
|
# a monitor to determine if a PostgreSQL database server is operational |
| 15 |
|
|
# |
| 16 |
|
|
# Rather than use tcp.monitor to ensure that your SQL server is responding |
| 17 |
dpavlin |
1.5 |
# on the proper port, this attempts to connect to and count all tables |
| 18 |
|
|
# in given database on given server. |
| 19 |
|
|
# |
| 20 |
|
|
# You can use this monitor along with fping+args which also knows how to |
| 21 |
|
|
# ping hosts in that user@host/dabase format. |
| 22 |
dpavlin |
1.1 |
# |
| 23 |
dpavlin |
1.7 |
# This monitor requires the perl5 DBI, DBD::Pg modules, |
| 24 |
dpavlin |
1.1 |
# available from CPAN (http://www.cpan.org) |
| 25 |
|
|
# |
| 26 |
|
|
# Copyright (C) 2001, CubIT IT Solutions |
| 27 |
|
|
# Written by Severin Luftensteiner <severin.luftensteiner@cubit.at> |
| 28 |
dpavlin |
1.5 |
# Copyright (C) 2002, Dobrica Pavlinusic <dpavlin@rot13.org> |
| 29 |
dpavlin |
1.1 |
# |
| 30 |
|
|
# This program is free software; you can redistribute it and/or modify |
| 31 |
|
|
# it under the terms of the GNU General Public License as published by |
| 32 |
|
|
# the Free Software Foundation; either version 2 of the License, or |
| 33 |
|
|
# (at your option) any later version. |
| 34 |
|
|
# |
| 35 |
|
|
# This program is distributed in the hope that it will be useful, |
| 36 |
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 37 |
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 38 |
|
|
# GNU General Public License for more details. |
| 39 |
|
|
# |
| 40 |
|
|
# You should have received a copy of the GNU General Public License |
| 41 |
|
|
# along with this program; if not, write to the Free Software |
| 42 |
|
|
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA |
| 43 |
|
|
# |
| 44 |
|
|
|
| 45 |
|
|
use DBI; |
| 46 |
dpavlin |
1.4 |
use strict; |
| 47 |
dpavlin |
1.1 |
|
| 48 |
dpavlin |
1.4 |
if (! @ARGV) { |
| 49 |
dpavlin |
1.1 |
print <<EOP1; |
| 50 |
dpavlin |
1.4 |
Usage: postresql.monitor username[:password]\@host/database ... |
| 51 |
dpavlin |
1.1 |
EOP1 |
| 52 |
dpavlin |
1.4 |
exit 1; |
| 53 |
dpavlin |
1.1 |
} |
| 54 |
|
|
|
| 55 |
dpavlin |
1.3 |
my @test_db; |
| 56 |
dpavlin |
1.4 |
my @failures; |
| 57 |
dpavlin |
1.3 |
|
| 58 |
|
|
foreach (@ARGV) { |
| 59 |
dpavlin |
1.4 |
if (m/^([^:]+):?([^\@]*)\@([^\/]+)\/?(.*)$/) { |
| 60 |
|
|
push @test_db, { user => $1, passwd => $2, host => $3, database => $4 }; |
| 61 |
dpavlin |
1.3 |
} else { |
| 62 |
dpavlin |
1.4 |
push @failures, "Can't parse configuration: host '$_' not in username:password\@host/database format!"; |
| 63 |
dpavlin |
1.3 |
} |
| 64 |
|
|
} |
| 65 |
dpavlin |
1.1 |
|
| 66 |
dpavlin |
1.4 |
foreach (@test_db) { |
| 67 |
|
|
my $dbh = DBI->connect( "DBI:Pg:dbname=$_->{database};host=$_->{host};", $_->{user}, $_->{passwd} ); |
| 68 |
|
|
if( ! $dbh ) { |
| 69 |
|
|
push @failures,"Could not connect server $_->{host}, database $_->{database}: " . $DBI::errstr; |
| 70 |
|
|
} else { |
| 71 |
|
|
my $sth = $dbh->prepare("select count(*) from pg_tables where tablename not like 'pg_%'"); |
| 72 |
|
|
if (! $sth->execute() ) { |
| 73 |
|
|
push @failures, "Can't find out number of tables on $_->{host}, database $_->{database} " . $DBI::errstr; |
| 74 |
|
|
} else { |
| 75 |
|
|
my ($nr) = $sth->fetchrow_array; |
| 76 |
|
|
if ($nr == 0) { |
| 77 |
|
|
push @failures, "No tables on $_->{host}, database $_->{database} (turn off monitoring for this database?)"; |
| 78 |
|
|
} |
| 79 |
|
|
} |
| 80 |
|
|
$sth->finish(); |
| 81 |
|
|
} |
| 82 |
|
|
if ($dbh) { |
| 83 |
|
|
$dbh->disconnect(); |
| 84 |
|
|
} |
| 85 |
dpavlin |
1.1 |
} |
| 86 |
dpavlin |
1.3 |
|
| 87 |
dpavlin |
1.1 |
if (@failures) { |
| 88 |
|
|
print join (", ", @failures), "\n"; |
| 89 |
|
|
exit 1; |
| 90 |
|
|
}; |
| 91 |
|
|
exit 0; |
Parent Directory
| 
Revision Log