Re: Database backup and restore

> 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" );
}