PostgreSQL – Missing chunk 0 for toast value in pg_toast

PostgreSQL – Missing chunk 0 for toast value in pg_toast

Note:

• 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;

Regards,
Wei Shan