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