From: | Jeroen Elassaiss-Schaap <j(dot)schaap(at)lumc(dot)nl> |
---|---|
To: | pgsql-admin(at)hub(dot)org |
Subject: | Re: [ADMIN] Backup of db with large objects. |
Date: | 2000-02-22 08:37:01 |
Message-ID: | 38B24AAD.4DFC3DFD@lumc.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeroen Elassaiss-Schaap | 2000-02-22 09:00:35 | Re: [ADMIN] Backup of db with large objects. |
Previous Message | Chris Jester | 2000-02-22 07:34:14 | remove |