Thread: pg_restore problem
Hello world,
(PostgreSQL 7.2.1-5, fully patched RedHat 7.3)
I have a db with some relations on OIDs and some BLOBs. I create a
backup with this command:
pg_dump -Fc -o -b mydb > mydb.dump
But a restore with:
pg_restore -dmydb -Fc mydb.dump
Gives this error:
CREATE DATABASE
pg_restore: connecting to database for restore
pg_restore: executing <Init> Max OID
pg_restore: creating FUNCTION "plpgsql_call_handler" ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error
Am I doing something wrong? TIA!
--
Jules Alberts.
On 14 Aug 2002 at 11:49, Jules Alberts wrote: > Hello world, > > (PostgreSQL 7.2.1-5, fully patched RedHat 7.3) > > I have a db with some relations on OIDs and some BLOBs. I create a > backup with this command: > > pg_dump -Fc -o -b mydb > mydb.dump > > But a restore with: BTW in between I do a dropdb mydb > pg_restore -dmydb -Fc mydb.dump > > Gives this error: > > CREATE DATABASE > pg_restore: connecting to database for restore > pg_restore: executing <Init> Max OID > pg_restore: creating FUNCTION "plpgsql_call_handler" () > pg_restore: [archiver (db)] could not execute query: ERROR: function > plpgsql_call_handler already exists with same argument types > pg_restore: *** aborted because of error > > Am I doing something wrong? TIA! -- Jules Alberts.
"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> BTW in between I do a dropdb mydb
You probably defined plpgsql in template1, so that when you do "createdb
mydb" there's already a plpgsql definition in mydb. This confuses
pg_restore, which is expecting to restore into a virgin database.
Try "createdb -T template0 mydb" to make a database with no local
additions, and then restore into that.
regards, tom lane
On 14 Aug 2002 at 11:13, Tom Lane wrote:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> > BTW in between I do a dropdb mydb
>
> You probably defined plpgsql in template1, so that when you do "createdb
> mydb" there's already a plpgsql definition in mydb. This confuses
> pg_restore, which is expecting to restore into a virgin database. Try
> "createdb -T template0 mydb" to make a database with no local additions,
> and then restore into that.
>
> regards, tom lane
Thanks, that was it! I did a complete reinstall of 7.2.1 to make sure
everything is default again (it's a test environment).
Now I have another pg_restore problem. When I lo_import() an image I
get a OID, say 241803. An lo_export() works OK. Then I do a backup with
pg_dump --oids --blobs --format=c --compress=9 \
--verbose --file=mydb.dump mydb &> mydbBackup.log
then a dropdb mydb, then a restore with
pg_restore --dbname=mydb --verbose --format=c \
mydb.dump &> mydbRestore.log
This restores the OIDs of all my tables (as I expected), except the
BLOBs, lo_export(241803, '/tmp/foobar') says "ERROR inv_open large
object 241803 not found". This is a problem because I save the BLOBs
OID as a reference in other tables. Am I doing something wrong or is
this a known issue?
TIA!
--
Jules Alberts.
"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> This restores the OIDs of all my tables (as I expected), except the
> BLOBs, lo_export(241803, '/tmp/foobar') says "ERROR inv_open large
> object 241803 not found". This is a problem because I save the BLOBs
> OID as a reference in other tables. Am I doing something wrong or is
> this a known issue?
pg_restore should fix up OID references to BLOBs ... if they are in
columns of type OID (or type lo, if you've installed contrib/lo).
I suspect you stored all your OID references in integer columns?
regards, tom lane
On 15 Aug 2002 at 9:22, Tom Lane wrote:
> "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> > This restores the OIDs of all my tables (as I expected), except the
> > BLOBs, lo_export(241803, '/tmp/foobar') says "ERROR inv_open large
> > object 241803 not found". This is a problem because I save the BLOBs
> > OID as a reference in other tables. Am I doing something wrong or is
> > this a known issue?
>
> pg_restore should fix up OID references to BLOBs ... if they are in
> columns of type OID (or type lo, if you've installed contrib/lo). I
> suspect you stored all your OID references in integer columns?
>
> regards, tom lane
(thanks for reacting)
Sorry, I wasn't quite clear. The problem isn't that the references get
lost, but that the actual OIDs of the blobs change. Here's an example:
#############################################################
-- bash
createdb test
psql test
-- psql
select lo_import('/usr/share/pixmaps/gimp.png'); -- echoes 243596
-- bash
pg_dump --oids --blobs --format=c --file=test.dump test
dropdb test
createdb test
pg_restore --dbname=test --format=c test.dump
-- psql
select lo_export(243596, '/tmp/gimp.png');
-- ERROR: inv_open: large object 243596 not found
#############################################################
In my database I want to store things like PDF files, images etc. for,
say, a customer called CUST. Also there's a table to link the customers
to any BLOBs related to him called CUST_BLOBS. In this table I create
one row for each BLOB for a customer. A CUST_BLOBS row contains the
customers OID + the BLOBs OID, so I can find all BLOBs of a customer by
selecting blob_oid from CUST_BLOBS where CUST_BLOBS.customer_oid =
customer.oid. This results in 0 or more OIDs of BLOBS. That's the
reason why I need presistent OIDs for BLOBs.
Hope I was a little bit more clear this time, TIA for any tips!
--
Jules Alberts.
"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> Sorry, I wasn't quite clear. The problem isn't that the references get
> lost, but that the actual OIDs of the blobs change.
Yes, they will, and there's not anything you can do to prevent it.
What is supposed to happen is that pg_restore should update your
CUST_BLOBS table to contain new blob OIDs instead of old ones.
It builds a map from the old OIDs, which it can see in the dump file,
to the new ones that get assigned on-the-fly as the blobs are loaded.
Then it looks through the database for OID columns, and substitutes
new blob OIDs wherever it can find a match to the list of old OIDs.
One hole in this approach is that the lookup table CUST_BLOBS had better
be present when the blob loading is done. Perhaps you tried to load it
separately after loading the blobs?
regards, tom lane
On 15 Aug 2002 at 11:45, Tom Lane wrote: > "Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes: > > Sorry, I wasn't quite clear. The problem isn't that the references get > > lost, but that the actual OIDs of the blobs change. > > Yes, they will, and there's not anything you can do to prevent it. > What is supposed to happen is that pg_restore should update your > CUST_BLOBS table to contain new blob OIDs instead of old ones. > It builds a map from the old OIDs, which it can see in the dump file, to > the new ones that get assigned on-the-fly as the blobs are loaded. Then > it looks through the database for OID columns, and substitutes new blob > OIDs wherever it can find a match to the list of old OIDs. Again something learned :-) I tested it, it works just like you said. Thanks a lot!