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
argumenttypes
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
