Greetings! Here is my poor man's replication/backup script for a DB
with large objects:
=============================================================================
pgbackup
=============================================================================
#!/bin/bash
H=db
D=maguire
BDIR=/home/camm/pgbackup
set -e
rm -fr $BDIR/*
psql -h $H -d $D -t -q -c "select lo_export(image,textcat('$BDIR/',image::text)) from check_images" >/dev/null
j=$(ls -l $BDIR/* | wc -l)
pg_dump -c -o -h $H $D | gzip -9 >$BDIR/$D.dump.gz
destroydb -h db1 $D
createdb -h db1 $D
zcat $BDIR/$D.dump.gz | psql -h db1 $D >/dev/null 2>&1 || true
k=$(psql -t -q -h db1 $D -c "select image_import('$BDIR')")
#echo $j $k
if [ $j -ne $k ] ; then
echo Image copy error: $j $k
exit 1
fi
exit 0
=============================================================================
pgplsql function image_import
=============================================================================
drop function image_import(text);
create function image_import(text) returns int4 as '
declare
tci check_images%rowtype;
ttext alias for $1;
tint int4;
begin
update check_images set image = 0 from check_images t1 where t1.number = 0 and check_images.number != 0 and
t1.image= check_images.image;
tint := 0;
for tci in select * from check_images where image != 0 loop
update check_images set image = lo_import(textcat(textcat(ttext,''/''),image::text)) where image = tci.image;
raise notice ''% %'', tint, tci.image;
tint := tint + 1;
end loop;
update check_images set image = t1.image from check_images t1 where t1.number = 0 and check_images.image = 0;
return tint;
end;
' language 'plpgsql';
=============================================================================
"pgsql-admin" <pgsql-admin@fc.emc.com.ph> writes:
> Has anyone tried to backup all Pg databases with large objects?
> Pls share. Thanks.
>
> Sherwin
>
>
>
--
Camm Maguire camm@enhanced.com
==========================================================================
"The earth is but one country, and mankind its citizens." -- Baha'u'llah