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" <[EMAIL PROTECTED]> writes:

> Has anyone tried to backup all Pg databases with large objects?
> Pls share. Thanks.
> 
> Sherwin
> 
> 
> 

-- 
Camm Maguire                                            [EMAIL PROTECTED]
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

Reply via email to