Re: toast table corrupted by vacuum - missing chunk number 0 for toast value

Hi

pá 21. 8. 2026 v 13:01 odesílatel Tomas Vondra <tomas(at)vondra(dot)me> napsal:

>
>
> On 7/12/26 20:48, Pavel Stehule wrote:
> > Hi,
> >
> > My customer reported corrupted toast tables on PostgreSQL 14.20. He uses
> > wal archiving - so it was possible to read data from broken pages. The
> > corrupted rows are detected by SELECT command.
> >
> > My customer prepared two instances: before corruption, after
> > corruption. There were more corrupted pages. All checks that I found
> > from extensions amcheck, pg_visibility are ok on instance before
> > corruption. Corrupted instances have problems with toast and toast
> > tables have problems with visibility map. Index on the toast table is ok.
> >
> > The corrupted toast page contains four tuples. Related main table was
> > not updated. There was only one operation over the toast page - pruning
> > (forced by vacuum executed by cron).
> >
> > WAL: 0000000100000D6E0000008A
> > rmgr: Heap2 len (rec/tot): 59/ 99, tx: 0, lsn:
> > D6E/8A8A6AD0, prev D6E/8A8A6A68, desc: PRUNE latestRemovedXid 0
> > nredirected 0 ndead 4, blkref #0: rel 1663/4267629/16352341 blk 17880358
> FPW
> >
> > These vacuum doesn't reports any errors
> >
> > 2026-04-18 05:03:13 CEST [1688]: [46307-1] user=,db=,app=,client= LOG:
> > checkpoint starting: time
> > 2026-04-18 05:03:24 CEST [3760026]: [10-1]
> > user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
> > 15037.899 ms statement: VACUUM (VERBOSE, ANALYZE)
> > public."AO_544E33_AUDIT_LOG_ENTRY";
> > 2026-04-18 05:03:30 CEST [3760026]: [11-1]
> > user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
> > 5511.161 ms statement: VACUUM (VERBOSE, ANALYZE) public.jiraaction;
> > 2026-04-18 05:03:40 CEST [3760026]: [12-1]
> > user=postgres,db=jira_prod,app=vacuumdb,client=[local] LOG: duration:
> > 10052.323 ms statement: VACUUM (VERBOSE, ANALYZE)
> > public."AO_C5D949_ISSUE_SLA";
> >
> > After this operation, the toast page had only dead lp pointers and next
> > select fails on reported error.
> >
> > Attached some data collected before and after corruption.
> >
> > Can I collect some other data?
> >
>
> If you still have the backup + WAL, I'd start by doing two things:
>

Unfortunately we do not have it - it is too late now.

> (1) running pg_waldump on older WAL segments, to figure out when / how
> was the page modified before VACUUM kicked in
>

We did it - and there was not any change.

This is only one record related to this page

WAL: 0000000100000D6E0000008A
rmgr: Heap2 len (rec/tot): 59/ 99, tx: 0, lsn:
D6E/8A8A6AD0, prev D6E/8A8A6A68, desc: PRUNE latestRemovedXid 0
nredirected 0 ndead 4, blkref #0: rel 1663/4267629/16352341 blk
17880358 FPW

looks so broken rows was not updated - xmin is same for main rows, and
toast rows

>
> (2) restoring the cluster to LSN shortly before D6E/8A8A6AD0
>
> (3) dumping the page somehow (copying the 8K block, looking at it using
> pageinspect, etc. ...)
>

please, see
https://www.postgresql.org/message-id/attachment/199738/investigation.txt

>
> (4) promote the instance, run a manual vacuum and see if it gets
> corrupted again
>

we did it - "without success" - table was not corrupted

>
> If there are more corrupted pages, then do this for multiple of them,
> and see if there's some similarity (e.g. all the corruption may be tied
> to the same XID, or maybe it's multixacts, ...).
>

there was more corrupted rows, unfortunately we did check just one page

I have to ask Roman if amcheck reported other issues. I think so not, but I
am not sure

>
>
> It'd also be good to get some additional info:
>
> 1) history of the cluster - How old is it? Did it start as PG14, or is
> it an older cluster that went through pg_upgrade?
>

I'll forward these questions to Roman Sindelar - maintainer of the
corrupted database

Regards

Pavel

>
> 2) are there any extensions installed - especially third-party ones
>
>
> regards
>
> --
> Tomas Vondra
>
>