PostgreSQL Tutorial: Dealing with corrupted blocks

Summary: in this tutorial, you will learn how to troubleshooting the corrupted blocks in PostgreSQL.

Table of Contents

Introduction

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

ERROR: invalid page in block 54206178 of relation base/16389/2825248
STATEMENT: SELECT * FROM some_table WHERE ...

How to do it…

The first step is to find the corrupted object to repair it. The log gives the file location (for real, there are many files corresponding to the given path and you can find the precise file by considering the block). But the problem is to identify what this file is corresponding to. In the above case, that file was a normal table but until you know it, you can’t repair it.

For finding the corresponding database object, you can use the following SQL:

SELECT c.oid, n.nspname AS schema, c.relname,
  CASE c.relkind
  WHEN 'r' THEN 'table'
  WHEN 'i' THEN 'index'
  WHEN 't' THEN 'TOAST table'
  WHEN 'm' THEN 'materialized view'
  WHEN 'S' THEN 'sequence'
  ELSE 'other'
  END AS type
FROM pg_class AS c
JOIN pg_namespace AS n ON c.relnamespace = n.oid
WHERE c.relfilenode = 2825248;

In the WHERE condition, the relfilenode number is corresponding to the file number in the error message. This will give you a result like the following, where the type field is the object’s type and the schema / relname is the corrupted object.

   oid   | schema |  relname   | type
---------+--------+------------+-------
 2825248 | public | some_table | table
(1 row)

Repair an index

If the corrupted object is an index, it will be a good situation as an index can be rebuilt without data loss. The following command solved the situation:

REINDEX INDEX some_index;

Repair a table

In the case of a table, it seems that the vacuum command can be used. Also you can use the zero_damaged_pages parameter: it will avoid to raise an error when a damaged page is found, so the process will not interrupted, but the error becomes warning and some data may be lost.

SET zero_damaged_pages TO on;

The VACUUM mechanism will recreate the table and as a consequence clear the damages. It seems logical to use it in conjunction with the previous set. Let’s assume the damaged data will be lost.

VACUUM FULL some_table;

This command will block the table, create a new table, so you need to have enough free space for doing this processing. If you are bothered with blocking table, you may consider to use pg_repack, to reorganize the table with minimal locks.

Repair a TOAST table

In the case of a TOAST table, the above query may give you a result like the following:

  oid   |  schema  |     relname     |    type
--------+----------+-----------------+-------------
 124590 | pg_toast | pg_toast_124589 | TOAST table
(1 row)

Then you need to find which main table the TOAST table belongs to:

SELECT n.nspname AS schema, c.relname
FROM pg_class AS c
JOIN pg_namespace AS n ON c.relnamespace = n.oid
WHERE c.reltoastrelid = 124590;

Finally, you can repair the TOAST table by repairing the main table, as mentioned above.