We've been getting errors similar to the following (the specific large
object that is "missing" is different every time) during our nightly
pg_dump:
pg_dump: dumpBlobs(): could not open large object: ERROR: inv_open: large
object 48217896 not found
I found a suggestion
(http://archives.postgresql.org/pgsql-admin/2003-02/msg00326.php ) from Tom
L that this might indicate the index on pg_largeobject needed to be
reindexed, so we first put a test server into single user mode and did the
reindex. When we did the reindex on our test box and then immediately ran a
pg_dump everything worked great and it resolved our problem, so I do believe
that our index was messed up. We then tried it on our production server,
but the regularly scheduled pg_dump failed with the error above.
Possibilities that I can think of:
1) pg_dump is failing because large objects are being added and removed
while the pg_dump is running
(I think I've proved to myself that this is possible, by starting a
transaction in session1, deleting a blob in session2, and then trying to
\lo_export the blob from session1.)
2) something is so screwed up that causes the pg_largeobject index to get
corrupted (in a matter of 6 hours?)
Obviously we can't upgrade to a newer version of PostgreSQL until we can get
good backups, so somehow I need to get this resolved using this version.
I've considered patching pg_dump to not exit upon failure to retrieve a
blob, so that I can at least have a backup (albeit missing some of my
blobs), but can't help but think I'm not the first person to have come
across this problem (of having lots of blob activity occurring at the same
time as a pg_dump). Are there better avenues for me to pursue?
Thanks,
-ron
