Re: Urgent !!!! Tables inaccessible postgres v17.6

On Thu, Aug 6, 2026 at 10:57 AM mahamood hussain <hussain(dot)ieg(at)gmail(dot)com>
wrote:

> Thanks for the suggestion. I confirmed that base/16388/447758 is the heap
> file for table, so unfortunately it isn't an index that can simply be
> rebuilt.
>
> heap_size | index_size | total_size
> -----------+------------+------------
> 11 GB | 5694 MB | 17 GB
>
> I'm fairly new to PostgreSQL, so could you provide a bit more context on
> the binary search approach you mentioned? I'd appreciate it if you could
> explain the steps involved and how it helps identify the records on the
> corrupted page.
>

They don't teach the binary search algorithm in Comp Sci anymore? ☹️

Anyway... use ORDER BY, OFFSET, LIMIT and the ROW_NUMBER() function to find
the offending records via "divide and conquer".

> Regarding your last point, do you think this corruption could be related
> to the PostgreSQL version we're currently running? Is this a known issue
> that has been fixed in PostgreSQL 17.10, or are you recommending the
> upgrade simply because we're not on the latest minor release?
>
> Also, could you help me understand what typically causes page corruption
> like this? Are there common root causes, and what best practices or
> preventive measures would you recommend to minimize the risk of this
> happening in the future?
>
> On Thu, Aug 6, 2026 at 8:14 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
> wrote:
>
>> Hmm.
>>
>> If base/16388/447758 is an index, then you could just drop it and
>> recreate it, but you're probably not that lucky.
>>
>> How big is the table? Using a binary search method with ORDER BY and
>> OFFSET, you can find the records on the offending page. Exclude them when
>> doing a COPY TO, then recreate the table.
>>
>> And, of course, upgrade to 17.10.
>>
>> On Thu, Aug 6, 2026 at 10:27 AM mahamood hussain <hussain(dot)ieg(at)gmail(dot)com>
>> wrote:
>>
>>> Hi Ron,
>>>
>>> Thanks for the quick response.
>>>
>>> Unfortunately, I do have backups, but they're also reporting the same
>>> checksum error.
>>> full backup: 20260801-203001F
>>> timestamp start/stop: 2026-08-01 20:30:01-07 / 2026-08-01
>>> 21:30:23-07
>>> wal start/stop: 00000001000007F900000055 /
>>> 000000010000080800000030
>>> database size: 1403.4GB, database backup size: 1403.4GB
>>> repo1: backup set size: 197.7GB, backup size: 197.7GB
>>>
>>> diff backup: 20260801-203001F_20260802-023002D
>>> timestamp start/stop: 2026-08-02 02:30:02-07 / 2026-08-02
>>> 02:35:44-07
>>> wal start/stop: 000000010000086A000000B6 /
>>> 000000010000086A000000B6
>>> database size: 1407.7GB, database backup size: 221.5GB
>>> repo1: backup set size: 198.8GB, backup size: 28.6GB
>>> backup reference total: 1 full
>>> error(s) detected during backup
>>>
>>> Our backup strategy is weekly full backups with daily incremental
>>> backups.
>>>
>>> The database is hosted on Azure Premium SSD v2, using four striped disks.
>>>
>>> How serious does this look to you? Do you have any recommendations on
>>> the fastest way to recover from this? At the moment, I'm concerned that
>>> both the production copy and the backups appear to be affected.
>>>
>>> On Thu, Aug 6, 2026 at 7:47 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com>
>>> wrote:
>>>
>>>> On Thu, Aug 6, 2026 at 10:11 AM mahamood hussain <hussain(dot)ieg(at)gmail(dot)com>
>>>> wrote:
>>>>
>>>>> Hi Team,
>>>>>
>>>>> I need some urgent help. I'm unable to access one of the tables. Even
>>>>> a simple SELECT statement fails with the error below. Could someone
>>>>> please help investigate and fix this issue?
>>>>>
>>>>> prod=# SELECT count(*) FROM schema.tablename;
>>>>>
>>>>> WARNING: page verification failed, calculated checksum 50897 but expected 50048
>>>>> ERROR: invalid page in block 696770 of relation base/16388/447758
>>>>> CONTEXT: parallel worker
>>>>>
>>>>> Any assistance would be greatly appreciated. Thanks!
>>>>>
>>>>
>>>> * Have you tested your backup/restore process lately?
>>>> * How old is the latest backup?
>>>> * Do the system logs show any errors around that time?
>>>> * What kind of disks do you have? One might be dying.
>>>>
>>>> --
>>>> Death to <Redacted>, and butter sauce.
>>>> Don't boil me, I'm still alive.
>>>> <Redacted> lobster!
>>>>
>>>
>>
>> --
>> Death to <Redacted>, and butter sauce.
>> Don't boil me, I'm still alive.
>> <Redacted> lobster!
>>
>

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!