Re: [ADMIN] Backup of db with large objects.

Try the following to restore the large objects

------- restoreLO.sh -------
#!/bin/sh

# 3 args on the command line
# $1 is the dir with the blobs extracted from tar file
# $2 is the table name
# $3 is the db name

# the $1 dir is created with pg_lodump and then tarred up and moved to another
machine where this script is run

for f in $(ls $1); do
echo "foid is $f"
echo $1/$f
# ignore output as it is not an error
x=`psql -d $3 -t -c "update $2 set foid=(lo_import('$1/$f')) where
foid=$f"`
done

----- end -----------
John

Jeroen Elassaiss-Schaap wrote:

> >
> > Date: Mon, 21 Feb 2000 11:10:44 +0000 (GMT)
> > From: M(dot)Mazurek(at)poznan(dot)multinet(dot)pl
> > Subject: Re: [ADMIN] Backup of db with large objects.
> >
> > On Mon, 21 Feb 2000, Karel Zak - Zakkr wrote:
> > > > > pg_dump doesn't support large objects, I haven't found any advise how
> > > > > to do it in doc neither in FAQ. How an I to backup a db with large
> > > > > objects without losing my data?
> > > > You need to write a C program using libpq's lo_import and lo_export
> > > > functions.
> > >
> > > ftp://ftp2.zf.jcu.cz/users/zakkr/pg/pg_dumplo-0.0.3.tar.gz
> > >
> > > (..still available for PG's contrib, Wanted?)
> > Frankly speaking I was surprised theres no such a thing at least in
> > contrib... unlesss there are some strong agruments for not doing it.
>
> Maybe this is helpful also, I've made a perlscript to accomplish the
> same. It stores lo's in a tarball, and restores them using the oid's,
> probably much alike the C program. I use it for my everyday backups, but
> my databases are not that large:
>
> -----------getlo------------
>
> #! /usr/bin/perl
>
> # GetLO
> # Jeroen Elassaiss Schaap 20-10-99
> #
> # Perl script to get all large objects from table
> #
> # I will adept the 'new style'. See man Pg
>
> use Pg; # use postgres extension
>
> if (! $ARGV[1]) {
> syntax();
> die "Inproper arguments\n";
> }
>
> #Some 'constants'.
> $tempdir = "/tmp/postgres";
> $tarfile ="$tempdir/$ARGV[0].tar";
> $dbname = $ARGV[0];
> $tablename_input = $ARGV[1];
>
> $conn = Pg::connectdb("dbname=$dbname"); #Connects to $dbname
> check_result(PGRES_CONNECTION_OK,$conn->status,"Connected to $dbname");
>
> # Get those values from the table
> $excstr="SELECT data". " FROM $tablename_input";
> $result = $conn->exec($excstr);
> check_result(PGRES_TUPLES_OK, $result->resultStatus,
> "Fetched record from $tablename_input.");
>
> $ntuples=$result->ntuples;
> for ($index=0;$index<$ntuples;$index++){
> $oid = get_record($result->getvalue($index,0));
> }
>
> system('gzip',"$tarfile");
>
> #Subroutine for checking status of postgres-command/connection
> sub check_result {
>
> my $cmp = shift;
> my $ret = shift;
> my $msg = shift; #import the passed variables
> my $errmsg;
>
> if ("$cmp" eq "$ret") { #compare
> } else {
> $errmsg = $conn->errorMessage;
> print "Error trying\n\t$msg\nError\n\t$errmsg\n";
> exit;
> }
> }
>
> #Subroutine for getting the values from the database.
> sub get_record{
> my $oid = shift;
>
> # Get those values from the table
> $excstr="SELECT lo_export(data,'$tempdir/$oid' )".
> "from $tablename_input where data=$oid";
>
> $result2 = $conn->exec($excstr);
> check_result(PGRES_TUPLES_OK, $result->resultStatus,
> "Fetched record from $tablename_input.");
> system("tar","-C","$tempdir","-rf","$tarfile","$oid");
> unlink($tempdir.'/'.$oid);
> return $oid;
> }
>
>
> sub syntax {
> print <<'EOS';
> getlo J. Elassaiss-Schaap 20/10/99
> usage: getlo <name of database> <name of table>
> name of table has to have its oid's in column 'data'
> EOS
> }
> --------end-of-getlo
>
> I checked and saw that the other component, the script to restore the
> large objects, still is an ugly hack only suitable for my own database.
> If asked, I will clean it up and post it also.
>
> Cheers,
>
> Jeroen
>
> --
> J. Elassaiss-Schaap
> Dept. Physiology
> Leiden University Medical Centre
> Postbus 9604
> 2300 RC Leiden
> The Netherlands
>
> tel: 071-5276811
> fax: 071-5276782
> E-mail: j(dot)schaap(at)physiology(dot)medfac(dot)leidenuniv(dot)nl
>
> ************

--
--------------------------------------------------------------------
Come to the first official Apache Software Foundation Conference!
------------------------- http://ApacheCon.Com ---------------------