Recupero dei dati da PostgreSQL in caso di blocchi danneggiati

Nel quadro del nostro lavoro sul tema “PostgreSQL per delfini e leoni marini”, mi piace seguire anche le mailing list di PostgreSQL per vedere quali problemi si presentano e come vengono risolti.

Un’e-mail ha suscitato particolarmente il mio interesse:

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

Interpretazione delle informazioni

Come va interpretata questa informazione?

• “parallel worker” ➜ PostgreSQL ha elaborato la query in parallelo. Probabilmente irrilevante in questo caso?

• “invalid page in block 43197” ➜ “page” e “block” sono sinonimi nell’universo PostgreSQL, se si deve credere alle numerose fonti su Internet? Quindi il messaggio di errore è in realtà assurdo!?! Quindi: la pagina/il blocco numero 43197 è danneggiato!

• “relation base/24576/24578” ➜ Quale oggetto del database (tabella, indice, ecc.) è interessato. Maggiori dettagli più avanti…

• “calculated checksum” ➜ PostgreSQL assegna a ogni pagina, al momento della scrittura su disco, un checksum che viene verificato in fase di lettura. [ 1 ]. In questo caso sembra che la verifica abbia dato esito negativo.
  Naturalmente ciò è possibile solo se il calcolo del checksum è attivato (impostazione predefinita a partire dalla v18).

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

Informazioni sulla “relation”

In PostgreSQL le tabelle sono denominate “relations”:

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

Se diamo prima un’occhiata al contenuto del disco, la situazione si presenta come segue:

$ 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

Qui abbiamo innanzitutto elencato tutti i database. Se vogliamo conoscere la corrispondenza con i nomi, dobbiamo guardare ALL’INTERNO dell’istanza del database:

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

Sappiamo quindi già che il danno si è verificato nel database test. Diamo quindi un’occhiata al file system scendendo di un livello:

$ 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

Qui si trovano tutti gli oggetti del database. Per sapere di quale tabella si tratta, dobbiamo nuovamente controllare ALL’INTERNO dell’istanza del database:

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’oggetto interessato è quindi la tabella “ordinaria” (Ordinary Table) test nello schema public nel database test.

Danneggiare

Come abbiamo simulato l’intero processo, ovvero come abbiamo causato il danno? Il comando dd è perfetto per questo scopo:

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

Se poi si fa il calcolo a ritroso rispetto al messaggio di errore iniziale, si vede che i dati corrispondono:

Blocco n. 43197 x 8 k/blocco ➜ 353'869'824 indirizzo iniziale, la fine è a: 353'878'015 e abbiamo sovrascritto 128 byte a partire dall’indirizzo 353'874'683.

Ora proviamo a leggere (di nuovo) i dati nel database e otteniamo esattamente il messaggio di errore corretto:

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

Se si dà un’occhiata al log degli errori, si trova lì lo stesso messaggio di errore:

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;

Un’altra possibilità per verificare gli errori di checksum consiste nell’eseguire la seguente 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 | 

Individuare il danno

Supponiamo di NON avere alcun backup (è necessario crearli, verificarli e testarli attivamente) e/o che l’archiviazione WAL non sia attivata (disattivata per impostazione predefinita) e che le modifiche apportate dall’ultimo backup (effettuato questa mattina alle 02:00) non debbano andare perse…

Come posso quindi recuperare i dati attuali? Anche pg_dump fallirà (fa la stessa cosa di SELECT). Anche pg_basebackup segnalerà che il checksum non è corretto e fallirà a sua volta. Inoltre, in questo modo non riesco comunque a recuperare i miei dati.

Dobbiamo quindi, tecnicamente, setacciare più o meno riga per riga fino a raggiungere il punto precedente al danneggiamento. Trovare la fine del danneggiamento. E da lì proseguire, sempre riga per riga.

In pratica lo facciamo avvicinandoci al punto di corruzione dall’alto e dal basso, controllando sempre fino a metà. [ 3 ]

Per farlo, dobbiamo innanzitutto individuare l’“estremità” superiore e inferiore della nostra tabella. A tal fine è tipicamente utile la chiave primaria, che nel nostro caso è la colonna id di tipo serial e su cui è definita una SEQUENCE:

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

A questo punto inizia la ricerca:

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

L’inizio del nostro danneggiamento si trova quindi da qualche parte nell’intervallo compreso tra la riga 2'500'000 e la riga 5'000'000.

Passiamo quindi alla ricerca di precisione:

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)

Ripetiamo ora la stessa procedura partendo «dall’alto» e arriviamo infine a un intervallo di valori della corruzione compreso tra 4.622.080 e 4.622.186. Ci sono quindi, per il momento, 107 righe all’interno di questa corruzione.

Recupero dei dati

Per recuperare i dati, creiamo innanzitutto una copia esatta della nostra tabella (attenzione: ricordarsi di predisporre spazio su disco sufficiente!):

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

E per il momento mettiamo al sicuro tutti i nostri dati:

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

Possiamo ancora recuperare la parte inferiore dei dati tramite una “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)

Per la parte superiore dei dati, invece, ciò non è più possibile. Qui dobbiamo costringere il Planner a eseguire una “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;

In questo modo avremmo messo al sicuro, per il momento, tutti i dati che si trovano al di fuori del blocco danneggiato. Ora sorge naturalmente la domanda: è possibile recuperare altro?

Recuperare più dati

A tal fine creiamo una seconda tabella e ignoriamo gli errori di checksum. Attenzione: da questo punto in poi gli errori di checksum possono improvvisamente scomparire “come per magia”, ma i danni non sono stati eliminati, semplicemente non vengono più rilevati. [ 4 ]

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

test=# SET ignore_checksum_failure = on;

Quindi copiamo le nostre righe a piccoli gruppi dalla riga 4.622.080 alla 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;

Poi ricontrolliamo gli intervalli di valori:

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

E riuniamo nuovamente i due set di dati:

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

A questo punto, però, è davvero giunto il momento di pensare seriamente a un backup…

Fonti

• 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

Questa pagina è stata tradotta con deepl.com.