Обсуждение: Postgres 7.3, pg_dump, pg_restore and "lo" type
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.
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