Re: backup large Pg databases with large objects

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