1 |
#!/usr/bin/perl -w |
2 |
# |
3 |
# cricket module which draws statistics of PostgreSQL commits, rollbacks and |
4 |
# number of backends |
5 |
# |
6 |
# http://www.rot13.org/~dpavlin/sysadm.html |
7 |
# |
8 |
# Usage: |
9 |
# |
10 |
# parse_pg_stat.pl host [user [password [port tunnel_command]]] |
11 |
# |
12 |
# tunnel command is used to invoke tunnel which will bring up tunnel port |
13 |
# (for example ssh :-) It's extremly important that tunnel return something |
14 |
# (using yes in this example) otherwise, this command will block and die |
15 |
# after 10 seconds e.g: |
16 |
# |
17 |
# parse_pg_stat.pl localhost dpavlin "" 15432 "ssh -L 15432:localhost:5432 -N -i ~cricket/.ssh/hostname hostname" |
18 |
# parse_pg_stat.pl localhost dpavlin "" 15432 "ssh -L 15432:localhost:5432 -i ~cricket/.ssh/izuh izuh yes" |
19 |
|
20 |
use strict; |
21 |
use DBI; |
22 |
|
23 |
my $info = ""; # optional parametars to database |
24 |
my $host = shift @ARGV || ''; |
25 |
$info .= ";host=$host" if ($host); |
26 |
my $user = shift @ARGV || "dpavlin"; |
27 |
my $pass = shift @ARGV || ""; |
28 |
my $port = shift @ARGV; |
29 |
my $tunnel = join(" ",@ARGV); |
30 |
$info .= ";port=$port" if ($port); |
31 |
|
32 |
my $sql = "select sum(numbackends),sum(xact_commit),sum(xact_rollback),sum(blks_read),sum(blks_hit) from pg_stat_database"; |
33 |
|
34 |
my $t_fd; |
35 |
|
36 |
if ($tunnel) { |
37 |
print STDERR "using tunnel '$tunnel'\n"; |
38 |
# eval { |
39 |
local $SIG{ALRM} = sub { kill 9,-$$; }; |
40 |
alarm 10; # wait for ssh to connect and return first line |
41 |
my $pid; |
42 |
open($t_fd,"$tunnel |") || die "$tunnel: $!"; |
43 |
my $foo=<$t_fd>; |
44 |
print STDERR "tunnel returned: $foo\n"; |
45 |
# }; |
46 |
} |
47 |
|
48 |
my $dbh = DBI->connect("DBI:Pg:dbname=template1$info",$user,$pass) || die $DBI::errstr; |
49 |
my $sth=$dbh->prepare($sql); |
50 |
|
51 |
if ($sth->execute()) { |
52 |
print join("\n",$sth->fetchrow_array()),"\n"; |
53 |
} |
54 |
|
55 |
undef $sth; |
56 |
$dbh->disconnect(); |
57 |
|
58 |
if ($tunnel) { |
59 |
print STDERR "kill tunnel\n"; |
60 |
kill 9,-$$; |
61 |
close($t_fd); |
62 |
} |