pgsql 9.0.1 table corruption

From: Dan Biagini <dbiagini(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: pgsql 9.0.1 table corruption
Date: 2011-04-14 15:10:47
Message-ID: BANLkTi=ZBPm4rNBfwTtwDgNHucJw=B2TxQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

update media_status set updated_at = now() at time zone 'UTC';
2011-04-14 00:15:15 UTC ERROR: could not read block 3 in file
"base/16384/16543": read only 0 of 8192 bytes
2011-04-14 00:15:15 UTC STATEMENT: update media_status set updated_at
= now() at time zone 'UTC';

Examining the corrupted files in the filesystem (linux), they aren't
zero bytes:

ll base/16384/16485
-rwx------ 1 postgres postgres 16384 2011-04-07 09:43 base/16384/16485

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? Does that
providing any clues as to what may have happened?

Is there any way to determine how/when this corruption may have occurred?

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).

Thanks,
Dan

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2011-04-14 16:08:27 Re: pgsql 9.0.1 table corruption
Previous Message Harald Fuchs 2011-04-14 14:54:18 Re: Adding a default value to a column after it exists