After many tests to dump - Mailing list pgsql-admin

From Gustavo Pérez
Subject After many tests to dump
Date
Msg-id 3F678D3A.7090304@est.fib.upc.es
Whole thread Raw
List pgsql-admin
  Hi,

  I think It has been answered before (talking about a post in 4/21).
Checking the forums for many times didn't help, so I have no other
choice but to ask you :

  I'm trying to dump a DB with BLOB's on it. I'm using a Slackware 9.0
with postgreSQL 7.3.3. I have a single relation called 'files' like this :

     CREATE TABLE FILES (I INTEGER PRIMARY KEY,
                                               BIN OID);

   Inserting a tuple :

       INSERT INTO FILES VALUES (1,lo_import('/tmp/test'));

   worked fine, returning the oid. I retreived the file succesfully :

       SELECT lo_export (bin,'/tmp/value')
           FROM FILES
         WHERE I=1;

    as you should expect. So after comiting everything, I left the psql
client, and being in the bash I typed :

         ./pg_dump -Fc -obvf pgsql.dump -U pgsql pgsql

     which should dump the database with blob's and oid's to a file
called pgsql.dump in custom format (say that I triied
in tar format too). I seemed to work fine so I typed :

          ./creatdb -U pgsql -E SQL_ASCII -T template0 test

      Which should create a new database using template0 with enconding
in SQL_ASCII (which is the default, isn't it ?). After that :

         bin/pg_restore -Fc -v -d prova -o -U pgsql pgsql.dump

       Which should recreate the database. I checked for the BLOB's in
the new database by using the comand :

          \dl (or \lo_list)

       showed me just one BLOB with a DIFFERENT OID. I know this is what
should be. But the problem is that
the OID in the new relation in the new database is still the old value
!!!! (reading I found that pg_restore should
look for the oid's in the relations, resolving the old values and
updating with new oid values, seems not to work).

       Like this (keep in mind there's only one tuple in the relation
FILE) :

                               VALUE in OLD DATABASE
VALUE in NEW DATABASE

      \dl                                 58841
                                     58945
      select bin
        from FILES                58841
                             58841


       So the OID had not been rewritten. Is there any explanation to
this ? May you help me please with this very problem ? In the meanwhile
I'll be searching more information again.

        Thanks in advance for your help,

        Gustavo Pérez


pgsql-admin by date:

Previous
From: "Dennis Oudijk"
Date:
Subject: Statistical Reporting
Next
From: "chakkara rangarajan"
Date:
Subject: change of table name - any help