1 |
dpavlin |
1.1 |
#!/usr/bin/perl -w |
2 |
dpavlin |
1.3 |
# |
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 |
dpavlin |
1.1 |
|
20 |
|
|
use strict; |
21 |
|
|
use DBI; |
22 |
|
|
|
23 |
dpavlin |
1.3 |
my $info = ""; # optional parametars to database |
24 |
dpavlin |
1.1 |
my $host = shift @ARGV || ''; |
25 |
dpavlin |
1.3 |
$info .= ";host=$host" if ($host); |
26 |
dpavlin |
1.2 |
my $user = shift @ARGV || "dpavlin"; |
27 |
|
|
my $pass = shift @ARGV || ""; |
28 |
dpavlin |
1.3 |
my $port = shift @ARGV; |
29 |
|
|
my $tunnel = join(" ",@ARGV); |
30 |
|
|
$info .= ";port=$port" if ($port); |
31 |
dpavlin |
1.1 |
|
32 |
|
|
my $sql = "select sum(numbackends),sum(xact_commit),sum(xact_rollback),sum(blks_read),sum(blks_hit) from pg_stat_database"; |
33 |
|
|
|
34 |
dpavlin |
1.3 |
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 |
dpavlin |
1.1 |
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 |
dpavlin |
1.3 |
|
58 |
|
|
if ($tunnel) { |
59 |
|
|
print STDERR "kill tunnel\n"; |
60 |
|
|
kill 9,-$$; |
61 |
|
|
close($t_fd); |
62 |
|
|
} |