Dan Biagini <dbiagini@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
