Re: BLOBs, pg_dump & pg_restore

Howard Lowndes <lannet@lannet.com.au> writes:

> OK, I'm convinced, except for one small, but not insignificant hiccup.
> When you dump a database with the BLOBs, even with the -c option, and then
> restore that database again with the -c option, you get double the BLOBs.
> The original BLOBs are there as are the new copies, and the cross
> referenced oids are updated.  It looks as if there should be some way of
> removing the old BLOB at restore time once the new BLOB is in place.  I
> don't know the detail of how pg_restore works but it does create a table
> solely for the purpose of cross referencing the oids.
>
> This of course means that each dump and subsequent restore doubles up on
> the BLOBs and since BLOBs are by nature Large there could be disk space
> problems.

If you blow away the database (DROP DATABASE) and recreate it before
doing the restore, those LOs will be gone.  If not, something is very
wrong.  pg_restore basically assumes a virgin database.

If you just clear out the tables before the restore, you should also
clear out the pg_largeobject table.  It's not hard to keep garbage LOs
from hanging around by putting an ON DELETE trigger on the referencing
table.

-Doug