Thread: pg_restore problem

pg_restore problem

From
"Jules Alberts"
Date:
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.

Re: pg_restore problem

From
"Jules Alberts"
Date:
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.

Re: pg_restore problem

From
Tom Lane
Date:
"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

Re: pg_restore problem

From
"Jules Alberts"
Date:
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.

Re: pg_restore problem

From
Tom Lane
Date:
"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

Re: pg_restore problem

From
"Jules Alberts"
Date:
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.

Re: pg_restore problem

From
Tom Lane
Date:
"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

Re: pg_restore problem

From
"Jules Alberts"
Date:
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!