Re: [ADMIN] Database backup and restore

[dima]

On first glance at dima's script it does not seem to deal with blobs,
although could easily me made to do so.

Also it does not address automated reloading.  In light of the fact that
blobs must be output by -Ft or -Fc in pg_dump, which are tar and custom
respectively, the result of an entire db set dump would be one file of this
type per database.  Maybe the name could be used in a similar
reverse-direction script to send each to pg_restore to reload things.

You can easily add -F? option to the script. The result of pg_dump may be piped with gzip for large DBs as well instead of calling the 2nd system(). I don't think blobs are really the problem. The problem i faced before writing the script was to pass the DBA password to pg_dump since i call it from cron.

Thanks to Colin's suggestions I improved the script a bit. One can provide the pg_dump options in the beginning of the script as the $pg_dump_options variable (should I move the user name/password & options to a config file?). I added piping as well. I'll add a workaround for huge databases this week probably.

#!/usr/bin/perl -w

use DBI;

my ( $user ) = "user";
my ( $password ) = "password";
my ( $pg_dump_options ) = "-d -O -R";

my ( $path ) = $ARGV[0];
if( !$path || $path eq '' ) { $path = '.'; }
chdir( $path ) or die "Can't cd $path: " . $!;

my $dbh = DBI->connect( "DBI:Pg:dbname=template1", $user, $password ) ||
  die "Can't connect to the database: " . DBI->errstr;
my $sth = $dbh->prepare( "SELECT datname FROM pg_database" ) ||
  die "Can't prepare the query" . $dbh->errstr;
$sth->execute ||
  die "Can't execute the query" . $sth->errstr;

my ( @data, @databases );
my $count = 0;
while( @data = $sth->fetchrow_array() ) {
  if( !( $data[0] =~ m/template[0,1]/ ) ) {
    $databases[$count++] = $data[0];
  }
}

$sth->finish;
$dbh->disconnect;

foreach( @databases ) {
  my $db = $_;
  for( 1 .. 6 ) {
    if( -e "$db.backup." . (7-$_) ) {
      rename( "$db.backup." . (7-$_), "$db.backup." . (7-$_+1) );
    }
  }
  if( -e "$db.backup" ) { rename( "$db.backup", "$db.backup.1" ); }
  system( "export PGUSER=\"$user\"; export PGPASSWORD=\"$password\";
           pg_dump $pg_dump_options $_ | gzip > $_.backup" );
}

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly