Recovering PostgreSQL Data When Blocks Are Corrupted

As part of our work on “PostgreSQL for Dolphins and Sea Lions,” I also enjoy reading the PostgreSQL mailing lists to see what kinds of problems come up and how they’re solved.

One email in particular caught my attention:

I’m unable to access one of the tables. Even a simple SELECT statement fails with the error below.

prod=# SELECT count(*) FROM schema.tablename;

WARNING:  page verification failed, calculated checksum 26618 but expected 52580
ERROR:    invalid page in block 43197 of relation base/24576/24578
CONTEXT:  parallel worker

Interpreting the Information

How should this information be interpreted?

• “parallel worker” ➜ PostgreSQL processed the query in parallel. Probably irrelevant in this case?

• “invalid page in block 43197” ➜ “page” and “block” are synonyms in the PostgreSQL universe, if the numerous sources on the Internet are to be believed? So the error message is actually nonsensical!?! In other words: Page/Block number 43197 is corrupted!

• “relation base/24576/24578” ➜ Which database object (table, index, etc.) is affected. More on this below…

• “calculated checksum” ➜ PostgreSQL assigns a checksum to each page when it is written to disk, which is verified upon reading. [ 1 ]. In this case, the check appears to have failed.
  Of course, this only works if checksum generation is enabled (default starting with v18).

    postgres=> SHOW data_checksums;
     data_checksums 
    ----------------
     on
  

About “relation”

Tables are called “relations” in PostgreSQL:

Relation is essentially a mathematical term for table. [ 2 ]

If we first take a look at the disk, it looks like this:

$ cd ${PGDATA}

$ ll -d base/*
drwx------ 2 dba dba  4096 Jul 20 17:53 base/1
drwx------ 2 dba dba 12288 Aug  5 21:33 base/24576
drwx------ 2 dba dba  4096 Jul 16 09:04 base/4
drwx------ 2 dba dba 12288 Aug  5 21:33 base/49204
drwx------ 2 dba dba 12288 Aug  5 21:33 base/5
drwx------ 2 dba dba  4096 Aug  5 21:33 base/57405
drwx------ 2 dba dba  4096 Aug  5 21:33 base/57406
drwx------ 2 dba dba  4096 Aug  5 21:33 base/57408
drwx------ 2 dba dba 36864 Aug  5 21:33 base/77107
drwx------ 2 dba dba  4096 Jul 20 18:05 base/pgsql_tmp

Here, we’ve listed all the databases first. If we want to find out the corresponding names, we have to look INSIDE the database instance:

postgres=# SELECT oid, datname AS database FROM pg_database;
  oid  |  datname  
-------+-----------
     5 | postgres
     1 | template1
     4 | template0
 49204 | dba
 24576 | test
 57405 | osm_ch
 57406 | osm_chx
 57408 | osm
 77107 | enswitch

So we already know that the corruption occurred in the test database. Let’s look one level deeper in the file system:

$ cd base/24576

$ ls -lrS
...
-rw------- 1 dba dba     835584 Mar 18 17:30 1255
-rw------- 1 dba dba    1294336 Jun 10 12:15 24578_fsm
-rw------- 1 dba dba  890937344 Jul 23 17:50 24578.4
-rw------- 1 dba dba 1062330368 Jun 10 12:15 24588.1
-rw------- 1 dba dba 1073741824 Jul 23 17:50 24588
-rw------- 1 dba dba 1073741824 Mar 18 18:08 24578.3
-rw------- 1 dba dba 1073741824 Mar 18 18:07 24578.2
-rw------- 1 dba dba 1073741824 Mar 18 17:36 24578.1
-rw------- 1 dba dba 1073741824 Jul 23 12:39 24578

All the database objects are located here. We can determine which table is involved by looking INSIDE the database instance:

postgres=# \connect test

test=# SELECT c.oid AS file, c.relname AS name, ns.nspname AS schema
     , CASE c.relkind
           WHEN 'r' THEN 'Ordinary Table'
           WHEN 'i' THEN 'Index'
           WHEN 'S' THEN 'Sequence'
           WHEN 'v' THEN 'View'
           WHEN 'm' THEN 'Materialized View'
           WHEN 'c' THEN 'Composite Type'
           WHEN 't' THEN 'TOAST Table'
           WHEN 'f' THEN 'Foreign Table'
           WHEN 'p' THEN 'Partitioned Table'
           WHEN 'I' THEN 'Partitioned Index'
           ELSE CONCAT('Unknown (', c.relkind, ')')
       END AS object_type
     , am.amname, c.relfilenode, c.reltablespace
  FROM pg_class AS c
  JOIN pg_namespace AS ns ON ns.oid = c.relnamespace
  LEFT JOIN pg_am AS am ON am.oid = c.relam
WHERE ns.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
  AND c.oid IN (24578, 24588, 1255)
;
 file  |   name    | schema     | object_type    | amname | relfilenode | reltablespace 
-------+-----------+------------+----------------+--------+-------------+---------------
 24578 | test      | public     | Ordinary Table | heap   |       24578 |             0
 24588 | test_pkey | public     | Index          | btree  |       24588 |             0
  1255 | pg_proc   | pg_catalog | Ordinary Table | heap   |           0 |             0

The affected object is therefore the “ordinary” table (Ordinary Table) test in the public schema within the test database.

Causing the Damage

How did we simulate the entire scenario—that is, cause the damage? The dd command is ideal for this:

$ dd if=/dev/urandom of=24578 bs=1 seek=353874683 count=128 conv=notrunc

If you then work backward from the error message at the beginning, you’ll see that it all adds up:

Block No. 43197 x 8 k/block ➜ 353,869,824 starting address; the end is at: 353,878,015, and we overwrote 128 bytes starting at address 353,874,683.

Now let’s try to read the data in the database again, and we’ll get exactly the right error message:

test=# SELECT * FROM test;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"

If you take a look at the error log, you’ll find the same error message there as well:

LOG:  page verification failed, calculated checksum 26618 but expected 52580
CONTEXT:  I/O worker executing I/O on behalf of process 595983
LOG:  invalid page in block 43197 of relation "base/24576/24578"
CONTEXT:  I/O worker executing I/O on behalf of process 595983
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
STATEMENT:  SELECT * FROM test;

Another way to check for checksum errors is with the following query:

postgres=# SELECT datid, datname, conflicts, checksum_failures, checksum_last_failure
  FROM pg_stat_database;
 datid |  datname  | conflicts | checksum_failures |     checksum_last_failure     
-------+-----------+-----------+-------------------+-------------------------------
     0 |           |         0 |                 0 | 
     5 | postgres  |         0 |                 0 | 
     1 | template1 |         0 |                 0 | 
     4 | template0 |         0 |                 0 | 
 49204 | dba       |         0 |                 0 | 
 24576 | test      |         0 |                27 | 2026-08-06 18:47:58.777398+02
 57405 | osm_ch    |         0 |                 0 | 
 57406 | osm_chx   |         0 |                 0 | 
 57408 | osm       |         0 |                 0 | 
 77107 | enswitch  |         0 |                 0 | 

Narrowing Down the Corruption

Let’s assume we have NO backups (you have to actively create, check, and test them yourself) and/or WAL archiving is not enabled (default: off), and we don’t want to lose the changes made since the last backup (from this morning at 2:00 a.m.)…

So how can I still recover the current data? pg_dump will also fail (it does the same thing as SELECT). pg_basebackup will also report that the checksum is incorrect and fail as well. Furthermore, this still doesn’t give me access to my data.

So, technically, we have to more or less work our way through the data line by line until we reach the point just before the corruption. Find the end of the corruption. And from there, work our way out line by line as well.

In practice, we do this by approaching the corruption from the top and bottom, checking up to the halfway point each time. [ 3 ]

To do this, we first need to find the upper and lower “ends” of our table. Typically, the primary key is suitable for this, which in our case is the id column of type serial and on which a SEQUENCE is defined:

test=# SELECT MIN(id), MAX(id) FROM test;
 min |   max    
-----+----------
  11 | 75930859

Then the search begins:

test=# SELECT * FROM test WHERE id < 40000000 ORDER BY id;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
test=# SELECT * FROM test WHERE id < 20000000 ORDER BY id;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
test=# SELECT * FROM test WHERE id < 10000000 ORDER BY id;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
test=# SELECT * FROM test WHERE id < 5000000 ORDER BY id;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
test=# SELECT * FROM test WHERE id < 2500000 ORDER BY id;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
      11 | Some text                     | 2026-07-23 17:46:13.710454
      61 | Some data to blow table up... | 2026-03-18 17:29:49.408976
test=# SELECT * FROM test WHERE id BETWEEN 2500000 AND 5000000 ORDER BY id;
ERROR:  invalid page in block 43197 of relation "base/24576/24578"
...

The start of our corruption is therefore somewhere in the range between row 2,500,000 and 5,000,000.

Then we move on to the fine-search range:

test=# SELECT * FROM test WHERE id = 4622079;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 4622079 | Some data to blow table up... | 2026-03-18 17:31:39.104354
(1 row)

We now repeat the same process starting from the “top” and ultimately arrive at a range of corrupted values from 4,622,080 to 4,622,186. So, for now, there are 107 rows within this corrupted range.

Recovering Data

To recover the data, we’ll first create an exact copy of our table (Note: Be sure to allocate enough disk space!):

test=# CREATE TABLE test_copy (LIKE test INCLUDING ALL);

And let’s start by moving all our data to a safe location:

test=# INSERT INTO test_copy SELECT * FROM test WHERE id < 4622080;

We can still recover the lower part of the data using a “sequential scan”:

test=# EXPLAIN SELECT * FROM test WHERE id < 4622080;
                            QUERY PLAN                            
------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1581458.80 rows=71211823 width=36)
   Filter: (id < 4622080)

This is no longer possible for the upper part of the data. Here, we must force the planner to perform an “index scan”:

test=# SET enable_seqscan = off;
SET

test=# EXPLAIN SELECT * FROM test WHERE id > 4622186;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Index Scan using test_pkey on test  (cost=0.57..2819292.47 rows=71211823 width=36)
   Index Cond: (id > 4622186)

test=# INSERT INTO test_copy SELECT * FROM test WHERE id > 4622186;

test=# SET enable_seqscan = on;

We have thus safely recovered all data located outside the corrupted block. Now, of course, the question arises: Can we recover any more?

Recovering More Data

To do this, we’ll create a second table and ignore the checksum errors. Note: From this point on, the checksum errors may suddenly “magically” disappear, but the corruption hasn’t gone away—it’s just no longer being detected. [ 4 ]

test=# CREATE TABLE test_copy2 (LIKE test INCLUDING ALL);

test=# SET ignore_checksum_failure = on;

Then we copy our rows in small batches from row 4622080 to 4622186:

test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id >= 4622080 and id < 4622090;
test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id >= 4622090 and id < 4622100;
test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id >= 4622100 and id < 4622110;
test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id >= 4622110 and id < 4622120;
test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id >= 4622120 and id < 4622123;
test=# --> Here is the hole!
test=# INSERT INTO test_copy2 SELECT * FROM test WHERE id > 4622127 and id <= 4622186;

Then we check the value ranges again:

test=# SELECT * FROM test_copy WHERE id between 4622075 and 4622080;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 4622075 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622076 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622077 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622078 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622079 | Some data to blow table up... | 2026-03-18 17:31:39.104354

test=# SELECT * FROM test_copy2 WHERE id between 4622075 and 4622085;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 4622080 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622081 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622082 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622083 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622084 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622085 | Some data to blow table up... | 2026-03-18 17:31:39.104354

test=# SELECT * FROM test_copy2 WHERE id between 4622180 and 4622190;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 4622180 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622181 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622182 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622183 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622184 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622185 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622186 | Some data to blow table up... | 2026-03-18 17:31:39.104354

test=# SELECT * FROM test_copy WHERE id between 4622180 and 4622190;
   id    |             data              |             ts             
---------+-------------------------------+----------------------------
 4622187 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622188 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622189 | Some data to blow table up... | 2026-03-18 17:31:39.104354
 4622190 | Some data to blow table up... | 2026-03-18 17:31:39.104354

And merge the two data sets back together:

test=# INSERT INTO test_copy SELECT * FROM test_copy2;
INSERT 0 104

test=# DROP TABLE test_copy2;
DROP TABLE

test=# DROP TABLE test CASCADE;
NOTICE:  drop cascades to default value for column id of table test_copy
DROP TABLE

test=# ALTER TABLE test_copy RENAME TO test;
ALTER TABLE

test=# CREATE SEQUENCE public.test_id_seq
    AS integer
    RESTART WITH 75930860
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

test=# ALTER SEQUENCE public.test_id_seq OWNER TO dba;
test=# ALTER SEQUENCE public.test_id_seq OWNED BY public.test.id;
test=# ALTER TABLE ONLY public.test ALTER COLUMN id SET DEFAULT nextval('public.test_id_seq'::regclass);

But by now, at the very latest, it’s high time to seriously consider setting up a backup…

Sources

• Christophe Pettus, PostgreSQL Experts, 2026-08-05: All Your GUCs in a Row: ignore_checksum_failure
• Christophe Pettus, PostgreSQL Experts, 2026-08-06: All Your GUCs in a Row: ignore_invalid_pages
• Ashutosh Sharma pg_surgery — perform low-level surgery on relation data
• PostgreSQL Server Configuration: zero_damaged_pages

This page was translated using deepl.com.