Howdy:
I have a script where I would like to connect to
my database (PostgreSQL) and do a dump
depending on what day it is. I'm having
problems trying to figure out why I either can't
open a file to write to it inside the loop, or, if
I create a file outside of the for loop, nothing
is written in the file.
What am I doing wrong?
When I run the script as-is, I do not get an error,
but I don't get the results that I expect, either.
If I change the script so that I open the file before
the for loop, I get and error (if the $file does not
exist previously) saying:
[snip error]
sh: /usr/local/home/shaunn/tmp/backup_list.txt: No such file or directory
[/snip error]
Which is true - I do *not* have a file just yet ... but why should it
care at this point?
The sql inside the script works, as I've test that by hand. Even
so, the script should give *some* kind of error. It does not.
Perhaps another pair of eyes can tell me what I'm doing
wrong ...
[snip script]
#!/usr/bin/perl
use strict;
use diagnostics;
use DBI;
# creating an alternative backup strategy for
# the database - gonna be ugly ...
# create a few variables
my $addr='[EMAIL PROTECTED]';
#my $outfile=`date +%d%b%Y`;
my $outfile=`date | cut -f 1 -d ' '`;
my $datetype=`date`;
my $file='/usr/local/home/joe/tmp/backup_list.txt';
my $matchday=`date +%a`;
#chop $outfile;
# define the connection to the database and user or give up
my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'postgres')
or die "Can not connect: $!";
# create an sql to get the initial list of tables
# backup tables that go from A - C
my $onesql = qq| select
relname
from pg_class
where relname ~ '^[A-Ca-c]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# backup tables that starts with D
my $twosql = qq| select
relname
from pg_class
where relname ~ '^[Dd]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# backup tables that go from E - O
my $threesql = qq| select
relname
from pg_class
where relname ~ '^[E-Oe-o]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# backup tables that starts with P
my $foursql = qq| select
relname
from pg_class
where relname ~ '^[Pp]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# backup tables that go from Q - R
my $fivesql = qq| select
relname
from pg_class
where relname ~ '^[Q-Rq-r]'
and relkind = 'r'
and relname not like 'pg_%'
and relname not like 'ref_%'
order by 1;
|;
# backup tables that start with S
my $sixsql = qq| select
relname
from pg_class
where relname ~ '^[Ss]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# backup tables that start with T-Z
my $sevensql = qq| select
relname
from pg_class
where relname ~ '^[T-Zt-z]'
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
|;
# test the database handler and prep the sql statements for
# execution
# this opens a file, but nothing is written to it
#
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
if ($matchday eq 'Sun') { #do for SUNDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($onesql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$onesql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
#print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end SUNDAY
elsif ( $matchday eq 'Mon' ) { # do for MONDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($twosql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$twosql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
#print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end MONDAY
elsif ($matchday eq 'Tue') { # do for TUESDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($threesql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$threesql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
# print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end TUESDAY
#close FILE;
elsif ($matchday eq 'Wed') { # do for WEDNESDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($foursql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$foursql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
#print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end WEDNESDAY
#close FILE;
elsif ($matchday eq 'Thu') { # do for THURSDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($fivesql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$fivesql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
# print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end THURSDAY
#close FILE;
elsif ($matchday eq 'Fri') { # do for FRIDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($sixsql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$sixsql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end FRIDAY
#close FILE;
elsif ($matchday eq 'Sat') { # do for SATURDAY
open (FILE, ">$file") || die "Can nae open $file: $!\n";
my $sth=$dbh->prepare($sevensql) or die "Error =", DBI::errstr;
unless ($sth->execute) { #check execution of sql
print"\n\tExecute failed for stmt:\n\t$sevensql\nError = ",
DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
} #end check
# the work - this is the loop
#open (FILE, ">$file") || die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
while ( my($table)=$sth->fetchrow ) {
print "Backing up $table...\n";
# print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
#print FILE "Please review\n\n";
close FILE;
} #end SATURDAY
#close FILE;
# maybe i should mail something to me anyway
my $sendmailtable = "/usr/sbin/sendmail $addr < $file";
print `$sendmailtable`;
$dbh->disconnect;
__END__
[/snip end script]
Thanks!
-X
