1 |
#!/usr/bin/perl |
#!/usr/bin/perl -w |
2 |
# |
# |
3 |
# $Id$ |
# Monitor multiple postgresql databases on different hosts |
4 |
# $Revision$ |
# |
5 |
# $Author$ |
# Based on postgresql.monitor 1.3 |
6 |
# |
# by Severin Luftensteiner <severin.luftensteiner@cubit.at> |
7 |
#Usage: postresql.monitor [options] |
# |
8 |
# |
#Usage: postresql.monitor username[:password]\@host/database ... |
|
# --database=<Databasename> indicates the database to which is connected |
|
|
# --username=<Username> DB-User which is used to connect to the DB |
|
|
# --password=<Password> DB-Password which is used to connect to the DB |
|
|
# --host=<Databasehost> Host of the Database (optional,default=localhost) |
|
|
# --port=<Portnumber> Port on which you want to connect (optional,default=5432) |
|
9 |
# |
# |
10 |
# a monitor to determine if a PostgreSQL database server is operational |
# a monitor to determine if a PostgreSQL database server is operational |
11 |
# |
# |
12 |
# Rather than use tcp.monitor to ensure that your SQL server is responding |
# Rather than use tcp.monitor to ensure that your SQL server is responding |
13 |
# on the proper port, this attempts to connect to and list the databases |
# on the proper port, this attempts to connect to and count all tables |
14 |
# on a given database server. |
# in given database on given server. |
15 |
|
# |
16 |
|
# You can use this monitor along with fping+args which also knows how to |
17 |
|
# ping hosts in that user@host/dabase format. |
18 |
# |
# |
19 |
# This monitor requires the perl5 DBI, DBD::mSQL and DBD::mysql modules, |
# This monitor requires the perl5 DBI, DBD::mSQL and DBD::mysql modules, |
20 |
# available from CPAN (http://www.cpan.org) |
# available from CPAN (http://www.cpan.org) |
21 |
# |
# |
22 |
# Copyright (C) 2001, CubIT IT Solutions |
# Copyright (C) 2001, CubIT IT Solutions |
23 |
# Written by Severin Luftensteiner <severin.luftensteiner@cubit.at> |
# Written by Severin Luftensteiner <severin.luftensteiner@cubit.at> |
24 |
|
# Copyright (C) 2002, Dobrica Pavlinusic <dpavlin@rot13.org> |
25 |
# |
# |
26 |
# This program is free software; you can redistribute it and/or modify |
# This program is free software; you can redistribute it and/or modify |
27 |
# it under the terms of the GNU General Public License as published by |
# it under the terms of the GNU General Public License as published by |
39 |
# |
# |
40 |
|
|
41 |
use DBI; |
use DBI; |
42 |
use Getopt::Long; |
use strict; |
|
|
|
|
GetOptions( \%options,"port=s", "username=s", "password=s", "database=s", "host=s" ); |
|
43 |
|
|
44 |
# uncomment these two lines and provide suitable information if you don't |
if (! @ARGV) { |
|
# want to pass sensitive information on the command line |
|
|
$options{username} ||= "postgres"; |
|
|
$options{password} ||= ""; |
|
|
|
|
|
$mode="Pg"; |
|
|
if ($options{host} eq ""){ |
|
|
$options{"host"=>"localhost"}; |
|
|
} |
|
|
if ($options{port} eq ""){ |
|
|
$options{"port"=>"5432"}; |
|
|
} |
|
|
|
|
|
if (($options{username} eq "") || (! defined $options{password}) || ($options{database} eq "")){ |
|
45 |
print <<EOP1; |
print <<EOP1; |
46 |
Usage: postresql.monitor [options] |
Usage: postresql.monitor username[:password]\@host/database ... |
|
|
|
|
--database=<Databasename> indicates the database to which is connected |
|
|
--username=<Username> DB-User which is used to connect to the DB |
|
|
--password=<Password> DB-Password which is used to connect to the DB |
|
|
--host=<Databasehost> Host of the Database (optional,default=localhost) |
|
|
--port=<Portnumber> Port on which you want to connect (optional,default=5432) |
|
47 |
EOP1 |
EOP1 |
48 |
die(); |
exit 1; |
49 |
} |
} |
50 |
|
|
51 |
my @test_db; |
my @test_db; |
52 |
|
my @failures; |
53 |
|
|
54 |
foreach (@ARGV) { |
foreach (@ARGV) { |
55 |
if (m/^([^:]+):([^\@]+)\@(.+)/) { |
if (m/^([^:]+):?([^\@]*)\@([^\/]+)\/?(.*)$/) { |
56 |
push |
push @test_db, { user => $1, passwd => $2, host => $3, database => $4 }; |
|
%test[host] = $1; |
|
|
%test[user] = $2; |
|
|
%test |
|
|
my ($host,$user,$database) = ($1,$2,$3); |
|
57 |
} else { |
} else { |
58 |
push @failures, "Can't parse configuration: host '$_' not in host:user\@database format!"; |
push @failures, "Can't parse configuration: host '$_' not in username:password\@host/database format!"; |
59 |
} |
} |
60 |
} |
} |
61 |
|
|
62 |
my( $dbh ) = DBI->connect( "DBI:$mode:dbname=$options{database};$options{host};$options{port}", $options{username}, $options{password} ); |
foreach (@test_db) { |
63 |
if( ! $dbh ) { |
my $dbh = DBI->connect( "DBI:Pg:dbname=$_->{database};host=$_->{host};", $_->{user}, $_->{passwd} ); |
64 |
push( @failures, "Could not connect to $mode server $host: " . $DBI::errstr ); |
if( ! $dbh ) { |
65 |
} else { |
push @failures,"Could not connect server $_->{host}, database $_->{database}: " . $DBI::errstr; |
66 |
my $sth = $dbh -> prepare("select count(*) from pg_tables"); |
} else { |
67 |
|
my $sth = $dbh->prepare("select count(*) from pg_tables where tablename not like 'pg_%'"); |
68 |
|
if (! $sth->execute() ) { |
69 |
|
push @failures, "Can't find out number of tables on $_->{host}, database $_->{database} " . $DBI::errstr; |
70 |
|
} else { |
71 |
|
my ($nr) = $sth->fetchrow_array; |
72 |
|
if ($nr == 0) { |
73 |
|
push @failures, "No tables on $_->{host}, database $_->{database} (turn off monitoring for this database?)"; |
74 |
|
} |
75 |
|
} |
76 |
|
$sth->finish(); |
77 |
|
} |
78 |
|
if ($dbh) { |
79 |
|
$dbh->disconnect(); |
80 |
|
} |
81 |
} |
} |
82 |
|
|
|
|
|
|
|
|
83 |
if (@failures) { |
if (@failures) { |
84 |
print join (", ", @failures), "\n"; |
print join (", ", @failures), "\n"; |
85 |
exit 1; |
exit 1; |
86 |
}; |
}; |
|
if ($dbh){ |
|
|
$dbh->disconnect(); |
|
|
} |
|
|
|
|
87 |
exit 0; |
exit 0; |