PostgreSQL – Missing chunk 0 for toast value in pg_toast
- Please do read this link before doing anything below
- Remember to perform a full backup before continuing with the steps below
What to do when you see similar errors in your pg_log?
STATEMENT: SELECT id, authz_id, org_id, name, environment, last_updated_by, created_at, updated_at, serialized_object FROM nodes WHERE (org_id = $1 AND name = $2) LIMIT 1 ERROR: unexpected chunk number 1 (expected 0) for toast value 5268264 in pg_toast_16444 STATEMENT: SELECT id, authz_id, org_id, name, environment, last_updated_by, created_at, updated_at, serialized_object FROM nodes WHERE (org_id = $1 AND name = $2) LIMIT 1 ERROR: missing chunk number 4 for toast value 5268263 in pg_toast_16444
The error above are associated with data corruption in your TOAST tables. These were the steps I took to resolve the issue. Firstly, identify the table with issues.
select pg_toast_16444::regclass; regclass -------- nodes
Now we know that the TOAST table having issue is associated with nodes tables (I know it’s pretty obvious in the logs!).
Step 1: Reindex the table
REINDEX table pg_toast.pg_toast_16444; REINDEX table nodes;
Step 2: Vacuum and analyze the table
VACUUM ANALYZE nodes;
Step 3: If it doesn’t work, identify the corrupted rows and delete them (This will incur data loss!).
for ((i=0; i<"Number_of_rows_in_nodes"; i++ )); do psql -U "Username" "Database Name" -c "SELECT * FROM nodes LIMIT 1 offset $i" >/dev/null || echo $i; done
Step 3a: The mini script above will list the rows which are having data corruption. In this case, row 11.
ERROR: missing chunk number 0 for toast value 5268251 in pg_toast_16444 11
Step 3b: Select the primary key of row 11.
psql> select id from nodes limit 1 offset 11; id ---------------------------------- 00000000000068f26b60aa5ef533cc85 (1 row)
Step 3c: Delete that row
psql> delete from nodes where id ='00000000000068f26b60aa5ef533cc85'
Step 4: Verify if issue has been resolved (No error mesages)
psql> VACUUM FULL nodes;