Re: data loss after vacuum

Allan Tong <actong(at)www(dot)quateams(dot)com> writes:
> I'm not sure if this is the right list to send this, but any help
> would be appreciated. We recently encountered a problem running
> postgres where, after a vacuum, all the data in one of our tables
> was gone. Now, I guess technically we don't know for sure if it
> was indeed vacuum that caused the data loss, but it seems likely.

The vacuum output shows that it thought it was removing only 27 out
of the nearly 700K rows. So I don't think vacuum is directly to
blame. However, it would very possibly have rewritten many of the
pages in your table, as a byproduct of moving rows, updating tuple
commit bits, etc.

> ... when I looked at the file contents, it was almost
> completely null'ed, so it looks like the data is really gone (though
> shouldn't a full vacuum reclaim the space?).

You mean the pages were all-zero? It sounds to me like a serious
hardware failure, or possibly kernel/filesystem misfeasance. Postgres
would certainly not have written zeroes, but apparently what got dropped
onto the disk platter was zeroes. Such failures are uncommon, but
by no means un-heard-of.

I'd suggest running some read/write disk tests to start with. Also
check for kernel errata.

regards, tom lane