Fw: Postgres 7.3, pg_dump, pg_restore and "lo" type - Mailing list pgsql-admin

Juan - if you look back in the posts to 4/21, you'll see a thread titled
"[ADMIN] trouble migrating large objects from 7.1.3 to 7.3.2".  In there,
you'll see I was having a similar problem.  I discovered that using the -o
option of pg_dump was causing the tar archiver error.  As it turns out, most
people don't need the -o option.

If you can, I'd try re-running pg_dump & pg_restore without the -o option.
If you can't rerun pg_dump or you really need the -o option for pg_dump,
then I'm not sure how to proceed.  I couldn't get it to work either, but
luckily I didn't need it.


 ---------------------------------------------------------------------------
---

>
> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>