Thread: Database backup and restore

> Am I missing something?   There seems to be no way to dump all databases
> and then restore them (as you'd have to do on a version change) if the
> databases contain blobs.
>
> pg_dump supports dumping of blobs with the -b -Ft switch, but using
> pg_dumpall with those switches doesn't seem to work.  And in any case,
> pg_restore seems to be missing its equivalent pg_restoreall.  Other than
> (painfully) manually pg_dumping and pg_restoring each database, how else
> could it be done?
>
> I would search the mailing lists but the site's search engine is down.
i wrote a script in perl which dumps all the DBs but template*
i can mail it to you if you wish

My client would like to test using a ram drive to hold some of our
postgre files.  Does anybody have any suggestions for how to go about
doing this?  Details are below:

http://www.3dretreat.com/reviews/rocketdrive/

This is a review that explains the benefits of a ram drive: it's a pci
card that holds several sticks of SDRAM, but reports it as another
filesystem to linux.  So the idea is that we could put several postgre
tables on this card, and we will hopefully see a huge performance
increase by being able to access these files from the ram drive instead
of a hard drive.

Our /usr/local/pgsql/data dir is nearly 4GB in size, but our current
ramdrive only holds 2GB of ram, so we can't put the whole data dir on
the ramdrive.

Which files would be ideal for loading onto the ram drive?  Are there
specific files that contain the table indices?  This ram drive is
nascent ground for us, so all ideas are welcome.

Thanks,
Tim


--
Dyrect Media Group
P.O. Box 486
6000 Goodrich Rd
Clarence Center, NY  14032-0486

OFFICE: 716-504-1141 ext 208
CELL: 716-510-2451
AIM: somecallmetim100

On 22 Nov 2002 at 19:07, dima wrote:

> i wrote a script in perl which dumps all the DBs but template*
> i can mail it to you if you wish

Why not post it to the list?  That way it's available for everyone.
I'm guessing it'll be less than 2K or so...
--
Dan Langille : http://www.langille.org/

In bash, I use:

========== script fragment ==========
~!/bin/bash
$BackupDir=<whereever>
$LogFile=<whichever>
for db_name in $(/usr/local/pgsql/bin/psql -U postgres -d template1 -n -t -c
"select datname from pg_database where datistemplate='f';");
    do
        /usr/local/pgsql/bin/pg_dump -U postgres -Fc -Z1 -b
"$db_name" -f "$BackupDir/$db_name.pgdump" 2>> $LogFile.err && \
            echo "$(date +%c): Successfully dumped database
$db_name" >> $LogFile
    done
========== script fragment ==========

Cheers,
    Murthy


-----Original Message-----
From: dima [mailto:_pppp@mail.ru]
Sent: Friday, November 22, 2002 11:07
To: Colin Stearman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Database backup and restore


> Am I missing something?   There seems to be no way to dump all databases
> and then restore them (as you'd have to do on a version change) if the
> databases contain blobs.
>
> pg_dump supports dumping of blobs with the -b -Ft switch, but using
> pg_dumpall with those switches doesn't seem to work.  And in any case,
> pg_restore seems to be missing its equivalent pg_restoreall.  Other than
> (painfully) manually pg_dumping and pg_restoring each database, how else
> could it be done?
>
> I would search the mailing lists but the site's search engine is down.
i wrote a script in perl which dumps all the DBs but template*
i can mail it to you if you wish


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Dan,

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.

I'll be working on it some more and will share what I come up with.

It seems a glaring omission that it is impossible to upgrade PostgreSQL
across major versions if your database(s) contain blobs (at least, not
without a lot of work).

Colin

----- Original Message -----
From: "Dan Langille" <dan@langille.org>
To: "dima" <_pppp@mail.ru>
Cc: <pgsql-admin@postgresql.org>
Sent: Friday, November 22, 2002 2:05 PM
Subject: Re: [ADMIN] Database backup and restore


On 22 Nov 2002 at 19:07, dima wrote:

> i wrote a script in perl which dumps all the DBs but template*
> i can mail it to you if you wish

Why not post it to the list?  That way it's available for everyone.
I'm guessing it'll be less than 2K or so...
--
Dan Langille : http://www.langille.org/


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

On 22 Nov 2002 at 14:41, Colin Stearman wrote:

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

I think that would be a very good addition to the PostgreSQL toolkit.
 FWIW, I do not use blobs and consequently do not think of them when
backups are required.

> I'll be working on it some more and will share what I come up with.

I'm sure it will be added to the contrib directory.
--
Dan Langille : http://www.langille.org/

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

>>On first glance at dima's script it does not seem to deal with blobs,
>>although could easily me made to do so.
> I think that would be a very good addition to the PostgreSQL toolkit.
>  FWIW, I do not use blobs and consequently do not think of them when
> backups are required.
>
>>I'll be working on it some more and will share what I come up with.
> I'm sure it will be added to the contrib directory.
Heya, Dan!

I posted the script to the list as you asked. Well, restore_all stuff
Colin asked for needs some more work since we need to keep owner names
somewhere outside the dumps ( I'm not about to parse those dump files ;)
) to restore the DBs with the same owners. I don't think making backups
of template? DBs makes sense since they would be unportable between the
versions of PostgreSQL. Anyway, restore_all script will be work in
progress (I hope I'll have enough free time to contribute this week).

Porting dumps between versions of PostgreSQL seems to be a problem. Say,
  I faced 'serial' type incompatibility moving my dumps from 7.1.3 to
7.2.2 (since they became 64-bit integers).

I have completed the development of a BASH script to dump and load DBs with
(or without) blobs.

It will dump the globals also, like user names, but I don't know about
triggers, etc.  As it uses pg_dump
and pg_restore to deal with the DBs and pg_dumpall to deal with the globals,
it should be all there.

You can get the scripts from www.infofind.com/postgreSQL.

Although I tested them, NO GUARANTEES OF ANY KIND ARE GIVEN.  So test them
on something you don't care about first (especially the fullrestore script)!

Colin

PS. Mail list admin is welcome to add these scripts to the contribution
directory, if desired.

----- Original Message -----
From: "Dan Langille" <dan@langille.org>
To: "Colin Stearman" <cstearman@infofind.com>
Cc: <pgsql-admin@postgresql.org>
Sent: Friday, November 22, 2002 2:46 PM
Subject: Re: [ADMIN] Database backup and restore


On 22 Nov 2002 at 14:41, Colin Stearman wrote:

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

I think that would be a very good addition to the PostgreSQL toolkit.
 FWIW, I do not use blobs and consequently do not think of them when
backups are required.

> I'll be working on it some more and will share what I come up with.

I'm sure it will be added to the contrib directory.
--
Dan Langille : http://www.langille.org/