Skip site navigation (1) Skip section navigation (2)

Re: backup large Pg databases with large objects

From: Camm Maguire <camm(at)enhanced(dot)com>
To: "pgsql-admin" <pgsql-admin(at)fc(dot)emc(dot)com(dot)ph>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: backup large Pg databases with large objects
Date: 2000-09-18 14:42:40
Message-ID: 54pum1sqvz.fsf@intech19.enhanced.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(at)fc(dot)emc(dot)com(dot)ph> writes:

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

-- 
Camm Maguire			     			camm(at)enhanced(dot)com
==========================================================================
"The earth is but one country, and mankind its citizens."  --  Baha'u'llah

In response to

pgsql-admin by date

Next:From: M CarlingDate: 2000-09-18 16:46:49
Subject: [JOB] Postgres DBA needed
Previous:From: Loïc TREGOUËTDate: 2000-09-18 10:30:14
Subject: speed Postgresql 7.0 vs PostgreSQL 6.5.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group