| From: | Doug McNaught <doug(at)wireboard(dot)com> | 
|---|---|
| To: | <pgsql-general(at)postgresql(dot)org> | 
| Subject: | Backing up databases with large objects DOESN'T WORK? | 
| Date: | 2002-02-07 22:20:20 | 
| Message-ID: | m3eljx53yz.fsf@varsoon.denali.to | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-general | 
Does anyone actually do this and have it work?
I have an int4 field in one of my tables that refers to
'pg_largeobject.loid' so I can retrieve the associated LOB.  There is
no RI constraint on this field (due to the way my 3rd-party DB library
works; I can probably add one with ALTER TABLE if necessary). 
Under 7.1.3 (as distributed with Red Hat 7.2) I use:
$ pg_dump -b -Fc mydb > outfile
I then do:
$ dropdb mydb
$ createdb mydb
$ pg_restore -d mydb <outfile
This runs to completion with no problems, however:
*The 'loid' fields in the pg_largeobject table are different after the
restore!* 
This of course breaks the references to pg_largeobject in my other
tables.
"No problem", I say, "I'll just use the -o option to pg_dump."
Now, running pg_restore as above gives me:
Archiver(db): Could not execute query. No result from backend.
Not too helpful.
OK, download and compile 7.2, and create and load my database.
$ pg_dump -b -Fc mydb > outfile
$ dropdb mydb
$ createdb mydb
$ pg_restore -d mydb < outfile
pg_restore: [archiver (db)] could not execute query: ERROR:  function plpgsql_call_handler already exists with same argument types
WTF?  I can't restore from backups because I did 
'createlang plpgsql template1'?
Am I doing something wrong or does this stuff just plain not work?
No flames intended but it's been a really frustrating afternoon. ;)
-Doug
-- 
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863
| From | Date | Subject | |
|---|---|---|---|
| Next Message | David Siebert | 2002-02-07 22:56:51 | what is the best way to backup? | 
| Previous Message | Medi Montaseri | 2002-02-07 21:55:08 | Re: Dream Server? |