Postgres 7.3, pg_dump, pg_restore and "lo" type

Поиск
Список
Период
Сортировка
От Juan Miguel
Тема Postgres 7.3, pg_dump, pg_restore and "lo" type
Дата
Msg-id 200304290009.23374.juanmime@ono.com
обсуждение исходный текст
Список pgsql-admin
Hello,

First, sorry about my english.

I have compiled and installed Postgres 7.3 Database Server, on Linux. I made
an DB where same tables need BLOB columns, and I thought to use the "lo"
type, present in the contrib dir.

This type is perfet for me, because I access remotely to the DB by ODBC, and
manage the orphan "oids" erasing these objects when we drop the afected rows.

Well. Some day ago, I did a database dump, for replicate the information in
other server (same PostgreSql Version, Linux, ...).

I dumped this database with largeobjects, something similar to : pg_dump -o -b
-C -Ft -U myuser mydb > backup.tar

After, when I try to recover the database using pg_restore in the new server.
These are the steps that I did:

1. Create the database structure.
    pg_restore -Ft -v -s -o -U -C myuser -d test backup_20030425.tar

OPTION A.
========
2.- Restore the datas.
    pg_restore -Ft -v -a -o -U myuser -d mydb backup_20030425.tar

    This is the result:
        pg_restore: connecting to database for restore
        pg_restore: executing <Init> Max OID
        pg_restore: restoring data for table BLOBS
        pg_restore: restoring large object OID 74763
        pg_restore: connecting to database inmobayo as user inmouser
        pg_restore: creating table for large object cross-references
        pg_restore: restoring large object OID 74765
        pg_restore: restoring large object OID 74767
        pg_restore: restoring large object OID 74769
        pg_restore: restoring large object OID 74771
        pg_restore: restoring large object OID 74773
        pg_restore: restoring large object OID 74775
        pg_restore: restoring large object OID 74777
        pg_restore: restoring large object OID 74779
        pg_restore: restoring large object OID 74781
        pg_restore: restoring large object OID 74783
        pg_restore: restoring large object OID 74785
        pg_restore: restoring large object OID 74787
        pg_restore: restoring large object OID 74789
        pg_restore: restoring large object OID 74791
        pg_restore: restoring large object OID 74793
        pg_restore: restoring large object OID 74795
        pg_restore: restoring large object OID 74797
        pg_restore: restoring large object OID 74799
        pg_restore: restoring large object OID 74801
        pg_restore: restoring large object OID 74803
        pg_restore: restored 21 large objects
        pg_restore: restoring data for table reportinfo
        pg_restore: [tar archiver] could not find header for file 127.dat in tar
archive
        pg_restore: *** aborted because of error

    ¿ Why ? If I untar the file "backup_20030425.tar", the file 127.dat exists.

OPTION B.
========
2.- If  I try to recover the data without the "-o" option (pg_restore -Ft -v
-a  -U myuser -d mydb backup_20030425.tar) , now the error is:

        pg_restore: [archiver (db)] error while updating column "photo" of table
                 "photos": ERROR:  Unable to identify an operator '=' for types 'oid' and
'lo'
                You will have to retype this query using an explicit cast.

OPTION C.
========
2.- Doing some little tricks, I can cast "oid as lo" and viceversa, then If I
try again, now the error is:
        pg_restore: fixing up large object cross-reference for photos
        pg_restore: fixing large object cross-references for photos.photo
        pg_restore: [archiver (db)] error while updating column "photo" of table
                 "photos": ERROR:  LargeObjectDrop: large object 74763 not found

        The origin of this error is the TRIGGER BEFORE DELETE OR UPDATE, of the
table photos. I think that pg_restore now, recover the BLOBS with diferents
oids, and try to UPDATE the records of the table, but then, the TRIGGER try
to drop the old OID ====> Errrorrr !!!!

        Is there any way of recover the data using pg_dunp and pg_restore ?  I need
a systematic method for doing backups and restore in the new or old server.
It's urgent and I need those datas on the new server.

Thanks very much.


В списке pgsql-admin по дате отправления:

Предыдущее
От: "dlangschied"
Дата:
Сообщение: Logical Fields in postgres.
Следующее
От: "John M. Layman"
Дата:
Сообщение: Fw: Postgres 7.3, pg_dump, pg_restore and "lo" type