#!/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