After many tests to dump

Поиск
Список
Период
Сортировка
Искать

After many tests to dump

От:
Gustavo Pérez <e7640227@est.fib.upc.es>
Дата:

  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

FAQ