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?
Regards
Pavel
-- pg_database before corruption
-[ RECORD 1
]-+--------------------------------------------------------------------------------------------------------------
oid | 4267629
datname | jira_prod
datdba | 16384
encoding | 6
datcollate | C
datctype | C
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 13779
datfrozenxid | 2629952536
datminmxid | 1
dattablespace | 1663
datacl |
{svc_jira=CTc/svc_jira,role_jira_connect=Tc/svc_jira,role_jira_ddl=C/svc_jira,svc_sync_esb_jiradb=c/svc_jira}
-- pg_database after corruption
-[ RECORD 1
]-+--------------------------------------------------------------------------------------------------------------
oid | 4267629
datname | jira_prod
datdba | 16384
encoding | 6
datcollate | C
datctype | C
datistemplate | f
datallowconn | t
datconnlimit | -1
datlastsysoid | 13779
datfrozenxid | 2632962334
datminmxid | 1
dattablespace | 1663
datacl |
{svc_jira=CTc/svc_jira,role_jira_connect=Tc/svc_jira,role_jira_ddl=C/svc_jira,svc_sync_esb_jiradb=c/svc_jira}
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
-- pg_class before corruption
-[ RECORD 1
]-------+-------------------------------------------------------------------------
oid | 4269884
relname | AO_544E33_AUDIT_LOG_ENTRY
relnamespace | 2200
reltype | 4269886
reloftype | 0
relowner | 16384
relam | 2
relfilenode | 16352338
reltablespace | 0
relpages | 251034
reltuples | 2.260101e+06
relallvisible | 249657
reltoastrelid | 16352341
relhasindex | t
relisshared | f
relpersistence | p
relkind | r
relnatts | 33
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | d
relispartition | f
relrewrite | 0
relfrozenxid | 2713551167
relminmxid | 4610
relacl |
{svc_jira=arwdDxt/svc_jira,svc_birep=r/svc_jira,role_jira_ro=r/svc_jira}
reloptions |
relpartbound |
-[ RECORD 1 ]-------+-----------------
oid | 16352341
relname | pg_toast_4269884
relnamespace | 99
reltype | 0
reloftype | 0
relowner | 16384
relam | 2
relfilenode | 16352341
reltablespace | 0
relpages | 17866545
reltuples | 6.9890744e+07
relallvisible | 17866545
reltoastrelid | 0
relhasindex | t
relisshared | f
relpersistence | p
relkind | t
relnatts | 3
relchecks | 0
relhasrules | f
relhastriggers | f
relhassubclass | f
relrowsecurity | f
relforcerowsecurity | f
relispopulated | t
relreplident | n
relispartition | f
relrewrite | 0
relfrozenxid | 2713551175
relminmxid | 4610
relacl |
reloptions |
relpartbound |
-- result of verify_heapam after corruption (before corruption all was ok)
select * from verify_heapam('"AO_544E33_AUDIT_LOG_ENTRY"'::regclass,false,true);
blkno | offnum | attnum | msg
--------+--------+--------+-----------------------------------------------
123628 | 1 | 5 | toast value 60360840 not found in toast table
126143 | 1 | 5 | toast value 60361422 not found in toast table
130120 | 10 | 5 | toast value 60360537 not found in toast table
130168 | 3 | 5 | toast value 60361263 not found in toast table
213414 | 8 | 5 | toast value 60360976 not found in toast table
-- same result before/after corruption
select ctid,xmin,xmax,"ID" from "AO_544E33_AUDIT_LOG_ENTRY" where
ctid='(123628,1)';
ctid | xmin | xmax | ID
------------+------------+------+---------
(123628,1) | 2779428784 | 0 | 4475289
-- same result before/after corruption
SQL> SELECT * FROM
page_header(get_raw_page('"AO_544E33_AUDIT_LOG_ENTRY"',123628));;
lsn | checksum | flags | lower | upper | special | pagesize | version
| prune_xid
--------------+----------+-------+-------+-------+---------+----------+---------+-----------
D6C/46360830 | 0 | 1 | 96 | 152 | 8192 | 8192 | 4
| 0
-- same result before/after corruption
select * from (
select lp,
length(t_attrs[6]),
substr(t_attrs[6], 1, 1),
case
when get_bit(t_attrs[6], 0) = 1 then
'short'
else
'normal-size'
end,
case
when get_byte(t_attrs[6], 0) = 1 then
get_byte(t_attrs[6], 10) +
(get_byte(t_attrs[6], 11) << 8) +
(get_byte(t_attrs[6], 12) << 16) +
(get_byte(t_attrs[6], 13) << 24)
else 0
end as "toast chunk ID"
from
heap_page_item_attrs(
get_raw_page('"AO_544E33_AUDIT_LOG_ENTRY"',
123628),'"AO_544E33_AUDIT_LOG_ENTRY"'::regclass)) as x;
lp | length | substr | case | toast chunk ID
----+--------+--------+-------------+----------------
1 | 18 | \x01 | short | 60360840
-- same result before/after corruption
SQL> SELECT t_ctid, raw_flags, combined_flags
FROM heap_page_items(get_raw_page('"AO_544E33_AUDIT_LOG_ENTRY"',123628)),
LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL;
t_ctid | raw_flags
| combined_flags
-------------+-----------------------------------------------------------------------------------------------------------------------+--------------------
(123628,1) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {}
(123628,2) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,4) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,5) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,6) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,8) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,10) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,12) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,14) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,16) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
(123628,18) |
{HEAP_HASNULL,HEAP_HASVARWIDTH,HEAP_HASEXTERNAL,HEAP_XMIN_COMMITTED,HEAP_XMIN_INVALID,HEAP_XMAX_INVALID,HEAP_UPDATED}
| {HEAP_XMIN_FROZEN}
-- before corruption
select ctid,xmin,xmax,* from pg_toast.pg_toast_4269884 where chunk_id=60360840;
ctid | xmin | xmax | chunk_id | chunk_seq |
>
--------------+------------+------+----------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------->
(17880358,1) | 2779428784 | 0 | 60360840 | 0 | \x3c68746d6c ...
(17880358,2) | 2779428784 | 0 | 60360840 | 1 | \x616e206c616e6 ...
(17880358,3) | 2779428784 | 0 | 60360840 | 2 | \x616e3e0d0 ...
SELECT t_ctid, raw_flags, combined_flags
FROM heap_page_items(get_raw_page('pg_toast.pg_toast_4269884',17880358)),
LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL;
t_ctid | raw_flags |
combined_flags
--------------+----------------------------------------------------------+----------------
(17880358,1) | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
(17880358,2) | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
(17880358,3) | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
(17880358,4) | {HEAP_HASVARWIDTH,HEAP_XMIN_COMMITTED,HEAP_XMAX_INVALID} | {}
SELECT * FROM
heap_page_items(get_raw_page('pg_toast.pg_toast_4269884',17880358));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid
| t_infomask2 | t_infomask | t_hoff | t_bits | t_oid |
>
----+--------+----------+--------+------------+--------+----------+--------------+-------------+------------+--------+--------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------->
1 | 6160 | 1 | 2032 | 2779428784 | 0 | 0 |
(17880358,1) | 3 | 2306 | 24 | | |
\x8808990300..
2 | 4128 | 1 | 2032 | 2779428784 | 0 | 0 |
(17880358,2) | 3 | 2306 | 24 | | |
\x8808990301..
3 | 2096 | 1 | 2032 | 2779428784 | 0 | 0 |
(17880358,3) | 3 | 2306 | 24 | | | \x880899...
4 | 64 | 1 | 2032 | 2779428784 | 0 | 0 |
(17880358,4) | 3 | 2306 | 24 | | | \x880899...
(4 rows)
SELECT * FROM page_header(get_raw_page('pg_toast.pg_toast_4269884',17880358));
lsn | checksum | flags | lower | upper | special | pagesize | version
| prune_xid
--------------+----------+-------+-------+-------+---------+----------+---------+-----------
D6B/DA64E308 | 0 | 0 | 40 | 64 | 8192 | 8192 | 4
| 0
-- after corruption
t_ctid | raw_flags | combined_flags
--------+-----------+----------------
SELECT * FROM
heap_page_items(get_raw_page('pg_toast.pg_toast_4269884',17880358));
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid |
t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+--------+-------------+------------+--------+--------+-------+--------
1 | 0 | 3 | 0 | | | | |
| | | | |
2 | 0 | 3 | 0 | | | | |
| | | | |
3 | 0 | 3 | 0 | | | | |
| | | | |
4 | 0 | 3 | 0 | | | | |
| | | | |
SELECT * FROM page_header(get_raw_page('pg_toast.pg_toast_4269884',17880358));
lsn | checksum | flags | lower | upper | special | pagesize | version
| prune_xid
--------------+----------+-------+-------+-------+---------+----------+---------+-----------
D6E/8A8A6B38 | 0 | 0 | 40 | 8192 | 8192 | 8192 | 4
| 0
