Backing up databases with large objects DOESN'T WORK?

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: Raw Message | Whole Thread | 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

Responses

Browse pgsql-general by date

  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?