Postgres 7.3, pg_dump, pg_restore and "lo" type

Поиск
Список
Период
Сортировка
Искать

Postgres 7.3, pg_dump, pg_restore and "lo" type

От:
Juan Miguel <juanmime@ono.com>
Дата:
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  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.

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

От:
jml@attbi.com
Дата:
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  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

FAQ