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

Hi Ron,

I asked an AI tool for some guidance, and it recommended the approach
below. To be honest, I don't fully trust AI for something this critical, so
I'd really appreciate an expert review.

Could you please take a look and let me know whether this is technically
correct and whether you'd recommend this approach?
------------------------------
Proposed recovery approach

*1. ignore_checksum_failure — try this before zero_damaged_pages*

The recommendation is to first enable:

SET ignore_checksum_failure = on;

The idea is that if the page header is still valid and only the checksum is
incorrect, PostgreSQL may still be able to read the page and recover most
or all of the tuples. Since this is non-destructive, it seems like a
reasonable first step.
------------------------------

*2. zero_damaged_pages — only as a last resort*

The recommendation is to use this only if all other recovery options fail,
since it permanently zeros the damaged page and destroys all rows on that
page.
------------------------------

*3. pg_surgery*

Use pg_surgery only if the corruption is limited to specific tuples rather
than the page itself.
------------------------------

*4. Binary search + COPY*

Use a binary search approach to identify the corrupted page/range and copy
out all the remaining data, excluding only the damaged page.
------------------------------

*5. Recover from a standby*

If a physical standby exists and doesn't have the same corruption, recover
the missing rows from there.
------------------------------

My situation is slightly different:

-

The corruption appears to have happened *about a week ago*, but we only
discovered it today.
-

The table is still *partially readable*.
-

Restoring the table from backup would mean losing approximately *one
week's worth of data*, which I'd like to avoid if possible.

Given your experience with PostgreSQL corruption, does the above recovery
order make sense? Is there anything you would change or recommend trying
first?

On Thu, Aug 6, 2026 at 8:42 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:

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