Thread: Large object and pg_restore problem

Dear All,

We are using the postgreSQL version 8.0.7 and we are facing a problem
regarding the large object and pg_dump.

We are storing image data as OID type. We have developed a PHP
application on it and we can see those images properly.

The problem is when we dump the DB using the following command:
pg_dump -U user -h host -i -o -b -v -F c db_name  -f  file_name

We are then getting an error when we try to load it on the same host
using pg_restore as follow:

pg_restore -i -h host -p 5432 -U user -d db_name -v file_name

Here is the error we got:

pg_restore: [archiver] could not create large object 488676329
pg_restore: *** aborted because of error


So, we try to get rid of this particular image by first deleting the
picture row with this corresponding oid and then making a vaccumdb +
reindex to delete the reference in the pg_largeoject but still the
largeobject is there.

Do you have any advices how can I delete this record (can I do a simple
delete from pg_catalog.pg_largeobject or this will trigger other
problems?) or if there are some workarounds?

I thank you for your attention and I look forward to hearing from you.

Best regards,

Gregory


--

--
 ------------------------------------------------------------------
 Gregory Gimenez.
 Bioinformatics Software Engineer
 Marcelle group
 Institut de Biologie du Developpement de Marseille
 Campus de Luminy Case 907
 13288 Marseille Cedex 9
 FRANCE
 Tel. +33 (0)491 829 240
 Fax +33  (0)491 820 682

 Web: http://www.ibdm.univ-mrs.fr
 ------------------------------------------------------------------

Gregory Gimenez <gimenez@ibdm.univ-mrs.fr> writes:
> So, we try to get rid of this particular image by first deleting the
> picture row with this corresponding oid and then making a vaccumdb +
> reindex to delete the reference in the pg_largeoject but still the
> largeobject is there.

Well, yeah, you didn't delete the large object.  Since you're restoring
the whole DB, why don't you just drop and recreate the database?

If you really don't want to do that, contrib/vacuumlo might help you, or
in the longer term consider using the contrib/lo datatype instead of
bare OIDs for referencing large objects.

BTW, using -i with pg_dump or pg_restore on a routine basis is
extremely bad practice.  It will bite you eventually.  If pg_dump
doesn't want to play with your server version, there is probably
a good reason, and you should not override it without knowing
exactly what you are doing and why it's safe for the particular
combination of versions.

            regards, tom lane