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