http://www.perlmonks.org?node_id=1027175

Shivaramakrishnan has asked for the wisdom of the Perl Monks concerning the following question:

Hi , I am a newbie to perl with some bash experience.I am trying to get a monitoring script for postgres database.I was able to get the script working with no issues.But I am having problems with output form.Here is the current output that I get.

./check_postgres.pl --buffers_alloc --exclusivelock --activeconn 17212root@host:

which is to be read as 172 for buffers_alloc 1 for exclusivelock and 2 for activeconn.

I would like to have my output as

./check_postgres.pl --buffers_alloc --exclusivelock --activeconn ------

Buffers_Alloc:172 Exclusive_lock:1 Active_Conn:2

root@host:

That is Description:Value Description:Value followed by next line at the end. I am trying to fix this from past three days but not able to succeed.Any help is greatly appreciated.I need to get this working as soon as possible.
#!/usr/bin/perl use strict; use DBI; use Getopt::Long; use Sys::Hostname; my $database = 'atloffice'; my $user = 'postgres'; my $hostname = '10.28.128.143'; my %querys = ( # State "activeconn" => qq{SELECT SUM(numbackends) FROM pg_sta +t_database}, # Locks "exclusivelock" => qq{SELECT COUNT(*) FROM pg_loc +ks WHERE mode='ExclusiveLock'}, # Checkpoints "buffers_alloc" => qq{SELECT buffers_alloc FROM p +g_stat_bgwriter}, ); GetOptions( 'help!' => \&usage, 'user=s' => \$user, 'database=s' => \$database, 'activeconn' => sub { print query_database($querys{activeconn}) }, 'exclusivelock' => sub { print query_database($querys{exclusiveloc +k}) }, 'buffers_alloc' => sub { print query_database($querys{buffers_allo +c}) } ) or die "$0: try --help for more information\n"; sub query_database { my $query = shift(@_); my $dbh = DBI->connect("dbi:Pg:dbname=$database;host=$hostname +",$user); my $sth = $dbh->prepare("$query") or die $|; $sth->execute; while (my @array = $sth->fetchrow_array) { return @array[0]; } } sub usage { print "[-] $0 script to monitor PostgreSQL databases,\n"; print "Usage: $0 [--OPTION]\n"; while ( my($key, undef) = each %querys ) { print "\t--".$key."\n"; } exit 0 } if (!$ARGV) { usage }

Replies are listed 'Best First'.
Re: Print subroutine problem
by NetWallah (Canon) on Apr 05, 2013 at 17:58 UTC
    You need to fix your "print" statements inside your GetOptions processing to format the output. Eg:
    'activeconn' => sub { print "Active_Conn:" . query_database($querys{activeconn}) . " "},
    Add printing of a "\n" after you finish "GetOptions".
    Also, the final test should be for "@ARGV", not "$ARGV".

    Update: Noticed that CountOrlok beat me to it, with almost identical advice.

                 "I'm fairly sure if they took porn off the Internet, there'd only be one website left, and it'd be called 'Bring Back the Porn!'"
            -- Dr. Cox, Scrubs

      Thanks a ton!! That worked

      I wanted to know is there a way to modify my current script to accept ip address of the database server (in script used as hostname) as an arguement since I am implementing this over multiple database servers and don't want to statically define an IP.

        Just the way you have the line 'database=s' => \$database, you can add a line for $hostname.

        Also, look into making your usage statement better, i.e. tell users that they can also pass --user, --database, etc. in the command line.

        Just a side note, it hurts my eyes seeing the word "querys". Someone is going to get an error if they type the correct pluralization in the script.
Re: Print subroutine problem
by CountOrlok (Friar) on Apr 05, 2013 at 17:56 UTC
    The simple solution is to change the anonymous subs to look like this:
    sub { print "Active_Conn: ".query_database($querys{activeconn})." " }
    And add a print "\n" at the end before exiting. I suggest refactoring it once you get it working.