Thread: Fw: Postgres 7.3, pg_dump, pg_restore and "lo" type

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
>
>

> 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.

First, thanks to everybody who tried to help me in this trouble.

After thinking a long time, I have found one solution. This solution has steps
that are individuals for each database, but are very simple. I remember that
I use the version 7.3.0, I think that in 7.3.2, some steps could be jumped.

Here is, how to restore a database dump, in Postgres 7.3, that uses the "lo"
type of the contrib dir. I explain the steps of this "algorithm" for
everybody who have the same problem as me.

MAKING THE BACKUP
-----------------------------------
1. We have the dump in a tar file, made with something similar to:
    pg_dump -Ft -o -b -U myuser mydb > backup.tar

THE RESTORING PROCESS
--------------------------------------------
2. We drop de database, if it exists.
    dropdb -U myuser mydb

3. We restore the structure of the database and create it. Use something
similiar to:
    pg_restore -Ft -v -s -C -U myuser -d test backup.tar

4. We access with "psql" to the database and create a implicit cast of the
types "lo" and "oid" on both directions.

5. We delete the triggers in the tables with "lo" columns, that calls the
function "lo_manage".

6.- We restore the data of the database.
    pg_restore -Ft -v -a  -U myuser -d mydb backup.tar

7.- We create the triggers (again) deleted, in the step 5.

I said that this "algorithm" depends on the particular database that we used,
because the steps (5 and 7).

I think that these steps (5 and 7) could be converted to an database
sistematic database independient step. Therefore if someone can improve this
solution, write to me and we'll speak.

Bye.

Juan Miguel Moreno Escar
     juanmime@ono.com