Re: multiple tables got corrupted

I could see block read I/O errors in /var/log/syslog. if those error fixed
by OS team, will it require recovery.

Also can i use LIMIT and OFFSET to locate corrupted rows?

Thanks in advance

Regards,
Vasu Madhineni

On Wed, Sep 16, 2020, 01:58 Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> Try reading them "row by row" until it breaks. That is, SELECT * FROM ...
> LIMIT 1, then LIMIT 2 etc. For more efficiency use a binary search starting
> at what seems like a reasonable place looking at the size of the table vs
> the first failed block to make it faster, but the principle is the same.
> Once it fails, you've found a corrupt block...
>
> //Magnus
>
>
> On Tue, Sep 15, 2020 at 12:46 PM Vasu Madhineni <vasumdba1515(at)gmail(dot)com>
> wrote:
>
>> Is it possible to identify which rows are corrupted in particular tables.
>>
>> On Tue, Sep 15, 2020 at 5:36 PM Magnus Hagander <magnus(at)hagander(dot)net>
>> wrote:
>>
>>>
>>>
>>> On Tue, Sep 15, 2020 at 11:15 AM Vasu Madhineni <vasumdba1515(at)gmail(dot)com>
>>> wrote:
>>>
>>>> Hi All,
>>>>
>>>> In one of my postgres databases multiple tables got corrupted and
>>>> followed the below steps but still the same error.
>>>>
>>>> 1.SET zero_damaged_pages = on
>>>> 2. VACUUM ANALYZE, VACUUM FULL
>>>> but still same error.
>>>>
>>>
>>>
>>> That is a very destructive first attempt. I hope you took a full
>>> disk-level backup of the database before you did that, as it can ruin your
>>> chances for forensics and data recovery for other issues.
>>>
>>>
>>> moh_fa=# VACUUM FULL;
>>>> ERROR: could not read block 9350 in file "base/1156523/1270812":
>>>> Input/output error
>>>>
>>>> Tried to take backup of tables with pg_dump but same error. files exist
>>>> physically in base location.
>>>>
>>>> How to proceed on this, no backup to restore.
>>>>
>>>>
>>> This is clearly some sort of disk error, and with no backups to restore
>>> you will definitely be losing data.
>>>
>>> I'd start by figuring out which tables have no corruption and do work,
>>> and back those up (with pg_dump for example) as soon as possible to a
>>> different machine -- since it's not exactly unlikely that further disk
>>> errors will appear.
>>>
>>> Once you've done that, identify the tables, and then try to do partial
>>> recovery. For example, if you look at the file 1270812, how big it is?
>>> PostgreSQL is failing to read block 9350 which is 76595200 bytes into the
>>> file. If this is at the very end of the file, you can for example try to
>>> get the data out until that point with LIMIT. If it's in the middle of the
>>> file, it gets more ticky, but similar approaches can be done.
>>>
>>> Also, unless you are running with data checksums enabled, I wouldn't
>>> fully trust the data in the tables that you *can* read either. Since you
>>> clearly have disk issues, they may have caused corruption elsewhere as
>>> well, so whatever verification you can do against other tables, you should
>>> do as well.
>>>
>>>
>>> You'll of course also want to check any kernel logs or storage system
>>> logs to see if they can give you a hint as to what happened, but they are
>>> unlikely to actually give you something that will help you fix the problem.
>>>
>>>