wiki.unix7.org

#!/usr/bin/env perl
 
package PGu;
 
use Data::Dumper;
use DBI;
use strict;
 
sub new {
    my ($class, $pghost, $username, $password) = @_;
    my $self = { 
        pghost => $pghost,
        dsn => "dbi:Pg:dbname=postgres;host=$pghost",
        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 dbsize {
 
    my ($self, $dbname) = @_;
    my $db = DBI->connect($self->dsn, $self->username, $self->password) or return -1;
 
    my $query = "select pg_database_size('$dbname');";
    my $sth = $db->prepare($query);
    my $rows = $sth->execute or return -1;
    my $row = $sth->fetchrow_array;
    $sth->finish;
    $db->disconnect;
    my $dbsize = int($row/1024/1024);
    return $dbsize;
}
 
sub dblist {
    my $self = shift;
    my $db = DBI->connect($self->dsn, $self->username, $self->password) or return -1;
 
    my $query = "select datname from pg_stat_database;";
    my $sth = $db->prepare($query);
    my $rows = $sth->execute;
 
    my @dblist;
    while (my $row = $sth->fetchrow_hashref) {
        my $dbname = $row->{'datname'};
        push @dblist, $dbname;
    }
    $sth->finish;
    $db->disconnect;
    return \@dblist;
}
1;
 
use Data::Dumper;
use DBI;
use strict;
 
 
sub timestamp {
    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);
    my $timestamp = sprintf ( "%04d-%02d-%02d %02d:%02d:%02d",
                                   $year+1900,$mon+1,$mday,$hour,$min,$sec);
    return $timestamp;
}
 
sub alog {
    my $message = shift;
    $message = timestamp . " " . $message . "\n";
    open(my $logf, '>>', 'pg-dump-all.log');
    print $message;
    print $logf $message;
    close $logf;
}
 
my @pghosts = ( 'pghost1.domain.org', 'pghost1.domain.org' );
 
foreach my $pghost (@pghosts) {
 
    my $username = 'postuser';
    my $password = 'superpass';
 
    my $pgu = PGu->new($pghost, $username, $password);
 
    my $dblist = $pgu->dblist;
 
    do { 
        alog("error list databases from $pghost , exit");
        next;
    } if $dblist < 0;
 
    foreach my $dbname (sort @{$dblist}) {
 
        next if $dbname =~ m/template/;
        next if $dbname =~ m/postgres/;
 
        my $dbsize = $pgu->dbsize($dbname);
        my $dbdump = $dbname . '.sqlz';
 
        do { 
            alog("dump of $pghost:$dbname as $dbdump already exist");
            next; 
        } if -f $dbdump;
 
        alog("pg_dump $pghost:$dbname begin dump to $dbdump with dbsize=$dbsize");
        alog("pg_dump command PGPASSWORD=$password pg_dump -h $pghost -U $username -Fc -f $dbdump $dbname");
 
        my $out = qx/PGPASSWORD=$password pg_dump -h $pghost -U $username -Fc -f $dbdump $dbname 2>&1/;
        my $retcode = $?;
        do { 
            alog("pg_dump $pghost $dbname was done with error with err code=$retcode and message: $out"); 
            unlink $dbdump;
            next;
        } if $retcode > 0;
        alog("pg_dump $pghost:$dbname done successfully"); 
 
    }
}
#EOF