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

Поиск
Список
Период
Сортировка
От John Reynolds
Тема Re: [ADMIN] Backup of db with large objects.
Дата
Msg-id 38B282E9.8601892D@asitatech.ie
обсуждение исходный текст
Ответ на Re: [ADMIN] Backup of db with large objects.  (Jeroen Elassaiss-Schaap <j.schaap@lumc.nl>)
Список pgsql-admin
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.Mazurek@poznan.multinet.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.schaap@physiology.medfac.leidenuniv.nl
>
> ************

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




В списке pgsql-admin по дате отправления:

Предыдущее
От: Jeroen Elassaiss-Schaap
Дата:
Сообщение: Re: [ADMIN] Backup of db with large objects.
Следующее
От: "Anthony Goubard"
Дата:
Сообщение: FATAL 1: SetUserId: user 'postgresql' is not in 'pg_shadow'