Re: pg_clog (?) problem with VACUMM

"Gerardo Herzig" <gherzig(at)fmed(dot)uba(dot)ar> writes:

> Hi all. Well, im having this problem for the first time.
> When executing a VACUUM, i have this msg:
> gse_new_version=# VACUUM ;
> ERROR: could not access status of transaction 118
> DETAIL: could not open file "pg_clog/0000": No existe el fichero o el
> directorio

This is the typical error you see if the data is corrupted by bad memory or
disk. It's trying to check if the record was committed or aborted and finding
a bogus transactionid.

(Except in this case I'm not sure that the transaction id is necessarily
bogus. It's awfully early though. Was the table in the template database
created soon after running initdb? And then you're creating a copy of the
database much later?)

It's also possible it's one of the various bugs fixed in the bug-fix releases
8.1.3 through 8.1.10. I don't see any related to clog but there are some
related to vacuum which could cause data corruption.

But aside from upgrading immediately to 8.1.10 I would suggest you run
memtest86 (assuming it's a x86 machine) or some other memory checker. Bad
memory is quite common and could easily cause this. Also run some SMART tool
to check the disks.

> Ok, wtf, i will create the file. So touch pg_clog/0000 and VACUUM again....
> ERROR: could not access status of transaction 118
> DETAIL: could not read from file "pg_clog/0000" at offset 0: Conseguido

That's almost the right idea for a last ditch attempt to extract what data you
can from a corrupted table. You have to fill the file with nul bytes though.
Something like dd if=/dev/zero of=0000 bs=1k count=nnn where nnn is, uh, I'm
not sure how large, it won't take much to cover transactionid 118 though.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning