Récupérer des données PostgreSQL en cas de blocs corrompus

Dans le cadre de nos travaux sur le thème « PostgreSQL pour les dauphins et les otaries », j’aime bien suivre les discussions sur les listes de diffusion PostgreSQL pour voir quels problèmes se posent et comment ils sont résolus.

Un e-mail a particulièrement retenu mon 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

Interprétation des informations

Comment interpréter cette information ?

• « parallel worker » ➜ PostgreSQL a traité la requête en parallèle. Probablement sans importance dans ce cas ?

• « invalid page in block 43197 » ➜ « page » et « block » sont-ils synonymes dans l’univers PostgreSQL, si l’on en croit les nombreuses sources disponibles sur Internet ? Le message d’erreur est donc en réalité absurde !?! En résumé : la page/le bloc numéro 43197 est corrompu !

• « relation base/24576/24578 » ➜ Quel objet de la base de données (table, index, etc.) est concerné ? Plus d’informations à ce sujet ci-dessous…

• « calculated checksum » ➜ PostgreSQL attribue à chaque page une somme de contrôle lors de l’écriture sur le disque, qui est vérifiée lors de la lecture. [ 1 ]. Dans ce cas, la vérification semble avoir échoué.
  Cela ne fonctionne bien sûr que si le calcul de la somme de contrôle est activé (par défaut à partir de la v18).

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

À propos de la « relation »

Dans PostgreSQL, les tables sont appelées « relations » :

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

Si nous examinons d’abord l’ensemble sur le disque, cela se présente comme suit :

$ 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

Nous avons d’abord répertorié ici toutes les bases de données. Si nous voulons connaître la correspondance avec les noms, nous devons regarder DANS l’instance de la base de données :

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

Nous savons donc déjà que le problème s’est produit dans la base de données test. Examinons donc le système de fichiers un niveau plus bas :

$ 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

C’est là que se trouvent tous les objets de la base de données. Pour savoir de quelle table il s’agit, nous devons à nouveau consulter L’INSTANCE DE LA BASE DE DONNÉES :

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

L’objet concerné est donc la table « ordinaire » (Ordinary Table) test du schéma public dans la base de données test.

Provoquer la panne

Comment avons-nous simulé tout cela, c’est-à-dire provoqué la panne ? La commande dd est parfaitement adaptée à cet effet :

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

Si l’on remonte ensuite jusqu’au message d’erreur du début, on constate que tout concorde :

Bloc n° 43197 x 8 ko/bloc ➜ adresse de départ 353 869 824, la fin se situant à 353 878 015, et nous avons écrasé 128 octets à partir de l’adresse 353 874 683.

Essayons maintenant de lire à nouveau les données dans la base de données : nous obtenons exactement le bon message d’erreur :

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

Si l’on jette un œil au journal des erreurs, on y trouve également le même message d’erreur :

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;

Une autre possibilité pour vérifier les erreurs de somme de contrôle consiste à utiliser la requête suivante :

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 | 

Localiser la corruption

Partons du principe que nous n’avons PAS de sauvegardes (il faut s’en occuper soi-même et les vérifier ou les tester) et/ou que l’archivage WAL n’est pas activé (désactivé par défaut) et que les modifications effectuées depuis la dernière sauvegarde (ce matin à 2 h) ne doivent pas être perdues…

Comment puis-je donc encore récupérer les données actuelles ? pg_dump échouera également (il fait la même chose que SELECT). pg_basebackup signalera lui aussi que la somme de contrôle est incorrecte et échouera également. De plus, cela ne me permet toujours pas d’accéder à mes données.

Techniquement, nous devons donc plus ou moins passer au crible ligne par ligne jusqu’à la partie précédant la corruption. Trouver la fin de la corruption. Et à partir de là, remonter également ligne par ligne.

En pratique, nous procédons en nous approchant de la corruption par le haut et par le bas, en vérifiant toujours jusqu’à la moitié à chaque fois. [ 3 ]

Pour cela, nous devons d’abord trouver les « extrémités » supérieure et inférieure de notre table. Pour cela, on utilise généralement la clé primaire, qui est dans notre cas la colonne id de type serial et sur laquelle repose donc une SEQUENCE :

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

Puis la recherche commence :

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

Le début de notre corruption se situe donc quelque part entre la ligne 2 500 000 et la ligne 5 000 000.

Nous passons ensuite à la recherche affinée :

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)

Nous répétons maintenant le même processus en partant du « haut » et obtenons finalement une plage de valeurs corrompues allant de 4 622 080 à 4 622 186. Il y a donc dans un premier temps 107 lignes concernées par cette corruption.

Récupération des données

Pour récupérer les données, nous commençons par créer une copie exacte de notre table (attention : n’oubliez pas de prévoir suffisamment d’espace disque !) :

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

Et nous mettons d’abord toutes nos données en sécurité :

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

Nous pouvons encore récupérer la partie inférieure des données à l’aide d’un « 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)

Pour la partie supérieure des données, cela n’est plus possible. Ici, nous devons forcer le planificateur à effectuer un « 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;

Nous avons ainsi mis en sécurité toutes les données situées en dehors du bloc corrompu. La question se pose naturellement de savoir si l’on peut aller plus loin ?

Récupérer davantage de données

Pour cela, nous créons une deuxième table et ignorons les erreurs de somme de contrôle. Attention : à partir de ce moment, les erreurs de somme de contrôle peuvent soudainement disparaître « comme par magie », mais les corruptions ne sont pas pour autant éliminées ; elles ne sont simplement plus détectées. [ 4 ]

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

test=# SET ignore_checksum_failure = on;

Nous copions ensuite nos lignes par petits lots, de la ligne 4 622 080 à la ligne 4 622 186 :

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;

Nous vérifions ensuite à nouveau les plages de valeurs :

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

Et nous fusionnons à nouveau les deux ensembles de données :

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

Mais à ce stade au plus tard, il est grand temps de réfléchir sérieusement à une sauvegarde…

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
• pageinspect — low-level inspection of database pages
• Radim Marek, boringSQL, 2026-10-09: Postgres relation files, filed under base/

Cette page a été traduite avec deepl.com.