Hi pá 21. 8. 2026 v 13:01 odesílatel Tomas Vondra <[email protected]> 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 > >
