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

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:

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

(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. ...)

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

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, ...).

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?

2) are there any extensions installed - especially third-party ones

regards

--
Tomas Vondra