Dan Biagini <dbiagini@gmail.com> writes:
> I have a 9.0.1 database with two corrupted tables (one table has 20
> rows, the other 140). The tables *seem* fine for read/select
> operations, but updating certain rows in the table produce error
> messages:
> update media set updated_at = now() at time zone 'UTC';
> ERROR: could not read block 2 in file "base/16384/16485": read only 0
> of 8192 bytes
The UPDATE scan itself wouldn't read off the end of the table.
I speculate that what is happening here is that you have a unique
index, so it's trying to verify that the uniqueness constraint isn't
violated, which means fetching via the index, and there are corrupt
TID pointers in the index (pointing to blocks that don't exist).
> I ran a "vacuum(FULL, VERBOSE) <table>" command and the corruption (or
> at least the errors on update) has disappeared. Is it expected that a
> "vacuum(FULL)" command would/could fix table corruption?
In 9.0, vacuum full would rebuild the index(es), so that would fix it
according to this theory.
> I suspect that it may have occurred during a filesystem level backup
> (ie pg_start_backup(), tar -czf..., pg_stop_backup()), as I performed
> a backup and moved the database to a different system. After
> restoring the files and starting postgres I began getting these
> errors. I have tried restoring multiple times with the same tar
> archive with the same results (on different systems).
If you simply unpacked the tar archive and started a postmaster on that,
you'd be pretty much guaranteed to get a corrupt database. The tar
archive is *not* a valid snapshot by itself --- you have to replay
whatever WAL was generated during the archiving sequence in order to get
to a consistent database state. So the whole thing sounds like a pretty
foreseeable consequence of that. You've probably got some other issues
in that database :-(. VACUUM FULL would be enough to rebuild indexes,
but it cannot fix inconsistencies in the heap data.
regards, tom lane