PostgreSQL Daten retten bei korrupten Blocks

Bei unseren Arbeiten zum Thema “PostgreSQL für Delfine und Seelöwen” lese ich gerne auch auf den PostgreSQL Mailing-Listen mit, um zu sehen, welche Probleme so auftreten und wie sie gelöst werden.

Ein Mail hat mich besonders interessiert:

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

Interpretation der Informationen

Wie ist diese Information zu interpretieren?

• “parallel worker” ➜ PostgreSQL hat das Query parallel abgearbeitet. Wahrscheinlich irrelevant in diesem Fall?

• “invalid page in block 43197” ➜ “page” und “block” sind im PostgreSQL Universum Synonyme, wenn man den zahlreichen Quellen im Internet glauben darf? Also ist die Fehlermeldung eigentlich widersinnig!?! Also: Page/Block Nummer 43197 ist kaputt!

• “relation base/24576/24578” ➜ Welches Datenbank-Objekt (Tabelle, Index, etc.) davon betroffen ist. Mehr dazu weiter unten…

• “calculated checksum” ➜ PostgreSQL versieht jede Page beim Schreiben auf Platte mit einer Checksumme, welche beim Lesen überprüft wird. [ 1 ]. In diesem Fall scheint die Prüfung fehlgeschlagen zu sein.
  Das geht natürlich nur, wenn das Checksummen-Bilden aktiviert ist (default ab v18).

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

Zur “relation”

Tabellen werden in PostgreSQL “relations” genannt:

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

Wenn wir uns das Ganze zuerst mal auf Platte anschauen sieht das wie folgt aus:

$ 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

Hier haben wir zuerst mal alle Datenbanken gelistet. Wenn wir die Zuordnung zu den Namen erfahren wollen, müssen wir IN der Datenbank-Instanz schauen:

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

Wir wissen also jetzt schon mal, dass der Schaden in der Datenbank test entstanden ist. Also schauen wir auf dem Dateisystem mal eine Ebene tiefer:

$ 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

Hier liegen alle Datenbank-Objekte rum. Um welche Tabelle es sich handelt erfahren wir wiederum IN der Datenbank-Instanz:

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

Es handelt sich also beim betroffenen Objekt um die “gewöhnliche” Tabelle (Ordinary Table) test im Schema public in der Datenbank test.

Kaputt machen

Wie haben wir jetzt das Ganze simuliert, sprich, kaputt gemacht? Hierzu eignet sich der Befehl dd ausgezeichnet:

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

Wenn man das dann auf die Fehlermeldung am Anfang zurück rechnet sieht man, dass es passt:

Block Nr. 43197 x 8 k/block ➜ 353'869'824 Anfangsadresse, das Ende liegt bei: 353'878'015 und überschrieben haben wir ab Adresse 353'874'683 128 bytes.

Jetzt versuchen wir die Daten in der Datenbank noch/wieder zu lesen und erhalten genau die richtige Fehlermeldung:

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

Falls man mal noch einen Blick ins Error Log wirft, findet man dort ebenfalls dieselbe Fehlermeldung:

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;

Eine weitere Möglichkeit, Checksummen-Fehler zu überprüfen besteht mit der folgenden Abfrage:

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 | 

Korruption eingrenzen

Gehen wir mal davon aus, dass wir KEINE Backups haben (muss man aktiv selber tun und prüfen bzw. testen) und/oder das WAL-Archiving nicht aktiviert ist (default off) und die Änderungen seit dem letzten Backup (von heute Morgen um 02:00) nicht verloren gehen sollen…

Wie kann ich also die aktuellen Daten noch retten? pg_dump wird ebenfalls fehlschlagen (macht das Selbe wie SELECT). pg_basebackup wird auch melden, dass die Checksumme nicht stimmt und ebenfalls fehlschlagen. Zudem komme ich so immer noch nicht wieder an meine Daten ran.

Wir müssen also technisch mehr oder weniger Zeile für Zeile bis vor die Korruption rausfieseln. Das Ende der Korruption finden. Und ab da ebenfalls Zeile für Zeile rausfieseln.

In der Praxis machen wir das, indem wir uns von oben und unten an die Korruption herantasten, indem wir immer jeweils bis zur Hälfte prüfen. [ 3 ]

Dazu müssen wir zuerst mal das obere und untere “Ende” unserer Tabelle finden. Hierzu eignet sich typischer Weise der Primary Key, welcher in unserem Fall die Spalte id vom Type serial ist und auf welchem damit eine SEQUENCE liegt:

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

Dann geht die Sucherei los:

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"
...

Der Anfang unserer Korruption liegt also irgendwo im Bereich zwischen Zeile 2'500'000 und 5'000'000.

Dann gehen wir in den Fein-Suche-Bereich über:

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)

Das selbe Spielchen machen wir jetzt noch von “oben” und gelangen zum Schluss an einen Wertebereich der Korruption von 4'622'080 bis 4'622'186. Es sind also erst mal 107 Rows innerhalb dieser Korruption.

Daten retten

Zum Retten der Daten erstellen wir zuerst einmal eine exakte Kopie unserer Tabelle (Achtung: Daran denken, genügend Diskplatz bereit zu stellen!):

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

Und bringen erst mal alle unsere Daten in Sicherheit:

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

Den unteren Teil der Daten können wir noch mittels eines “sequential scans” retten:

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)

Beim oberen Teil der Daten geht das dann nicht mehr. Hier müssen wir den Planner zu einem “index scan” zwingen:

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;

Somit hätten wir erst mal alle Daten in Sicherheit gebracht, welche ausserhalb des korrupten Blocks liegen. Jetzt stellt sich natürlich die Frage, ob noch mehr geht?

Mehr Daten retten

Zu diesem Zweck erstellen wir eine zweite Tabelle und ignorieren die Checksummen-Fehler. Achtung: Ab diesem Punkt können die Checksummen-Fehler plötzlich “magisch” verschwinden die Korruptionen sind aber dadurch nicht weg sondern werden einfach nicht mehr festegestellt. [ 4 ]

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

test=# SET ignore_checksum_failure = on;

Dann kopieren wir unsere Rows häppchenweise von Zeile 4622080 bis 4622186 weg:

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;

Dann überprüfen wir noch mal die Wertebereiche:

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

Und fügen die beiden Datensets wieder zusammen:

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

Spätestens jetzt aber ist es allerhöchste Zeit, sich ernsthaft Gedanken um ein Backup zu machen…

Quellen

• 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