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

Advertisements
  1. #1 by ringerc on August 15, 2017 - 02:47

    Please, PLEASE link to https://wiki.postgresql.org/wiki/Corruption and tell people to make a copy of their damaged DB *before* attempting this!

    Also, they should prefer to restore from a backup rather than try to fix it, if possible.

    It’s important to determine why this happened. It’s not normal PostgreSQL behaviour and indicates some form of corruption. Don’t just carry on blindly hoping everything will be OK from now on. If you can see some corruption, there’s usually other corruption you cannot see, and you might have hardware/memory issues etc too.

    • #2 by Wei Shan on August 15, 2017 - 13:42

      Hi ringerc,

      You are absolutely right. I should warn people about backing up before doing anything 🙂

      Just updated the post with the links and warnings.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: