Postgres 7.3, pg_dump, pg_restore and "lo" type

From: Juan Miguel <juanmime(at)ono(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Postgres 7.3, pg_dump, pg_restore and "lo" type
Date: 2003-04-28 22:09:23
Message-ID: 200304290009.23374.juanmime@ono.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Browse pgsql-admin by date

  From Date Subject
Next Message jml 2003-04-28 22:49:41 Re: Postgres 7.3, pg_dump, pg_restore and "lo" type
Previous Message dlangschied 2003-04-28 20:35:48 Logical Fields in postgres.