Re: ERROR: could not read block 4707 of relation 1663/16384/16564: Success

On 1 Srpen 2011, 8:27, Deniz Atak wrote:
> Deepak, Tom thanks for answering.
>
> Tom, we have psql 8.1.18. So you are right, this weird message is because
> of
> the old version. I will check with my colleague about the possible
> reasons.
> What can I do if there is a messed up table?

First of all, you should find out what caused the mess. This could be
really difficult as it might be a rare hw or sw glitch. Anyway consider
upgrading to 8.1.23 if possible.

If you have a fresh backup (i.e. one with all the data in the table), just
restore it and use it. You may even use just this particular table (just
move it using COPY).

If you need to recover the data, you'll have to play a bit with it as you
need to 'skip' all the corrupted blocks. The query reports block 4707 is
corrupted - how many blocks does the relation have?

You can skip the blocks using 'ctid' column, which is basically "(block
id, item id)" so to skip block 4707 you can do this

SELECT * FROM table WHERE (ctid < '(4707,0)'::ctid OR ctid >=
'(4708,0)'::ctid)

and if fails with another "could not read block" error, put there another
such condition.

Tomas