Re: BLOBs, pg_dump & pg_restore

On 2 Oct 2003, Doug McNaught wrote:

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

Tks

--
Howard.
LANNet Computing Associates - Your Linux people <http://www.lannetlinux.com>
------------------------------------------
Flatter government, not fatter government - Get rid of the Australian states.
------------------------------------------
If all economists were laid end to end, they would not reach a conclusion
- George Bernard Shaw