User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

perl:db-list-maker [2020-02-15 00:57] (current)
Line 1: Line 1:
 +====== PgSQL DB list maker======
  
 +<code perl  db-list.pl>​
 +#​!/​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
 +</​code>​
 +
 +----
 +[<>]