PostgreSQL Tutorial: Dealing with corrupt TOAST values

Summary: in this tutorial, you will learn how to troubleshooting the corrupt TOAST values in PostgreSQL.

Table of Contents

Introduction

In rare cases, some TOAST values related to a table get corrupt, maybe due to some hardware or filesystem issues, even a PostgreSQL bug. If you encounter an error message like following, it means that there are some TOAST values corrupted in your database.

ERROR: unexpected chunk size 226 (expected 175) in final chunk 1 for toast value 8846932 in pg_toast_2877446
STATEMENT: SELECT * FROM bad_table WHERE ...

TOAST tables are where PostgreSQL stores offline compressed data for large values, like big text fields, geometries, large arrays, and BLOBs. What the server is telling you in that error message is that the size of the compressed block in the TOAST table doesn’t match its metadata, and as a result PostgreSQL can’t decompress it.

How to do it…

The first step is to find the corrupt table to repair it. The log gives the TOAST table name. But the problem is to identify which main table the TOAST table belongs to.

For finding the related main table, you can use the following SQL:

SELECT c.oid, n.nspname AS schema, c.relname
FROM pg_class AS c
JOIN pg_namespace AS n ON c.relnamespace = n.oid
WHERE c.reltoastrelid = (
        SELECT oid FROM pg_class AS t
          WHERE t.relname = 'pg_toast_2877446'
      );

In the WHERE condition, the relname constant value is corresponding to the TOAST table name in the error message. This will give you a result like the following, where the schema / relname is the corrupt table.

   oid   | schema |  relname
---------+--------+-----------
 2825248 | public | bad_table
(1 row)

Then, what to do about the bad TOAST record?

Repair corrupt table

Obviously, if you have a backup from just before the corruption occurred, the answer is to restore that backup. But if you don’t, you need to repair it by hand. Let’s create a new table at first:

CREATE TABLE new_table (LIKE bad_table INCLUDING ALL);

Then, traverse the table using a primay key index, and process the rows one by one. If the row data is good, just insert it into the new table. If the TOASTed value in a row is corrupt, rewrite the TOASTed column to a constant value ‘!!! BAD DATA !!!’. We do that with a simple DO script like this:

SET enable_seqscan TO off;

DO $$
DECLARE
  row    bad_table%ROWTYPE;
  rowkey bad_table.pkey%TYPE;
BEGIN
  FOR rowkey IN SELECT pkey FROM bad_table LOOP
    BEGIN
      INSERT INTO new_table
        SELECT * FROM bad_table WHERE pkey = rowkey;
    EXCEPTION
      WHEN others THEN
        SELECT * INTO row
          FROM bad_table WHERE pkey = rowkey;
        row.bad_column := '!!! BAD DATA !!!';
		INSERT INTO new_table SELECT (row).*;
    END;
  END LOOP;
END;
$$;

The goal here is to reach into the TOASTed value for each row (the large-text bad_column field) and de-compress it, which should raise any errors you’re going to get. Of course, you need to customize the above for your own tables.

Finally, we can swap the two table’s name:

ALTER TABLE bad_table RENAME TO old_table;
ALTER TABLE new_table RENAME TO bad_table;

Once you’ve done this, you should have a clean table free of TOAST corruption.