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.

Search Discussions

  • Jules Alberts at Aug 14, 2002 at 10:04 am

    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.
  • Tom Lane at Aug 14, 2002 at 3:13 pm

    "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
  • Jules Alberts at Aug 15, 2002 at 9:14 am

    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.
  • Tom Lane at Aug 15, 2002 at 1:22 pm

    "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
  • Jules Alberts at Aug 15, 2002 at 2:44 pm

    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.
  • Tom Lane at Aug 15, 2002 at 3:45 pm

    "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
  • Jules Alberts at Aug 16, 2002 at 8:04 am

    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!

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedAug 14, '02 at 9:54a
activeAug 16, '02 at 8:04a
posts8
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Jules Alberts: 5 posts Tom Lane: 3 posts

People

Translate

site design / logo © 2022 Grokbase