Re: BLOBs, pg_dump & pg_restore

Howard Lowndes <lannet(at)lannet(dot)com(dot)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