Re: Postgres 7.3, pg_dump, pg_restore and "lo" type

From: jml(at)attbi(dot)com
To: juanmime(at)ono(dot)com
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Postgres 7.3, pg_dump, pg_restore and "lo" type
Date: 2003-04-28 22:49:41
Message-ID: 20030428224944.86BB0475ADE@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Browse pgsql-admin by date

  From Date Subject
Next Message John M. Layman 2003-04-28 23:43:47 Fw: Postgres 7.3, pg_dump, pg_restore and "lo" type
Previous Message Juan Miguel 2003-04-28 22:09:23 Postgres 7.3, pg_dump, pg_restore and "lo" type