#!/usr/local/bin/perl
#
# $Id: db-list.pl,v 1.1 2017/06/26 12:45:18 root Exp $
#
package DBH;
 
use utf8;
use DBI;
use Data::Dumper; 
 
sub new {
    my ($class, $dbhost, $username, $password) = @_;
    my $self = { 
        dsn => "dbi:Pg:dbname=postgres;host=$dbhost",
        username => $username,
        password => $password
    };
    bless $self, $class;
    return $self;
}
 
sub dsn { return shift->{dsn}; }
sub username { return shift->{username}; }
sub password { return shift->{password}; }
 
sub listDBs {
    my ($self) = @_;
    my $db = DBI->connect($self->dsn, $self->username, $self->password);
 
    my $query = "SELECT datname FROM pg_database WHERE datistemplate = false;";
    my $sth = $db->prepare($query);
    my $rows = $sth->execute;
    my @a;
    while (my $row = $sth->fetchrow_array) {
        push @a, $row;
    }
    $sth->finish;
    $db->disconnect;
    return undef unless $rows*1;;
    return \@a;
}
 
 
sub dbSize {
    my ($self, $dbname ) = @_;
    my $db = DBI->connect($self->dsn, $self->username, $self->password);
 
    my $query = "select pg_database_size('$dbname');";
    my $sth = $db->prepare($query);
    my $rows = $sth->execute;
    my @a;
    $row = $sth->fetchrow_array;
    $sth->finish;
    $db->disconnect;
    return undef unless $rows*1;
    return $row;
}
 
1;
 
my $dbname = "postgres";
my $username = "xxxxxxx";
my $password = "xxxxxxx";
 
@dbs = (
    "pgdb1.some.org", 
    "pgdb2.some.org", 
    "pgdb3.some.org", 
    "pgdb4.some.org", 
    "pgdb5.some.org", 
); 
 
foreach my $dbhost (@dbs) {
    my $mu = DBH->new("$dbhost","$username","$password");
    my $dblist = $mu->listDBs;
    print "\n";
    foreach my $db (@{$dblist}) {
        my $dbsize = $mu->dbSize($db);
        $dbsize = int($dbsize/(1024*1024));
        print $dbhost," -- \t",$db." -- \t".$dbsize."\n";
    }
}
 
#EOF