Thread: Backing up large objects

    I've got a database which includes large objects.  In that past,
for the few times I've used lobjs, I've written a quick script to
handle backups, but this time we've got a slightly more complex
system.

    Does anyone have a general-purpose system for backing up dbs
with lobjs, or should I write my own dang script?

--
Adam Haberlach            |A cat spends her life conflicted between a
adam@newsnipple.com       |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500                 |profound desire to avoid getting wet.

Adam Haberlach <adam@newsnipple.com> writes:
>     Does anyone have a general-purpose system for backing up dbs
> with lobjs, or should I write my own dang script?

Try the attached ... it's a copy of something that is in 7.1 contrib,
back-ported to 7.0.  I think it'll work for 6.5 as well, but have not
tried it.

            regards, tom lane

I've created several blobs using the OID type.  I note that the database
stores the "oid" number that represents two files on disk, such as xinv21281
and xinx21281 with the oid in the database showing up as 21281 via a SELECT.

But, I deleted a row that contained this oid, and the two 'x' files are
still on disk.  I then ran a VACUUM, saw that 1 record was reaped, but the
two 'x' files remain.  Are these 'x' files going to be reused in some
manner, or is this a bug that keeps them around despite the fact that the
database row for it has been removed?

David

Large objects aren't automatically freed when the last reference to them
goes away.  Whether this is a bug or feature depends on your point of
view, perhaps.  See contrib/vacuumlo/ for help ...

            regards, tom lane