La mia prima esperienza nel ripristino di un database Postgres dopo un crash (pagina non valida nel blocco 4123007 della base di dati relatton base/16490)

Voglio condividere con voi la mia prima esperienza di successo nel ripristinare la piena funzionalità di un database Postgres. Ho iniziato a conoscere il DBMS Postgres sei mesi fa, e prima di questa esperienza non avevo affatto esperienza nella gestione di database.

Lavoro come ingegnere semi-DevOps in una grande azienda IT. La nostra azienda si occupa dello sviluppo di software per servizi ad alta richiesta, e io sono responsabile del funzionamento, della manutenzione e del deployment. Mi è stato assegnato un compito standard: aggiornare un'applicazione su un server. L'applicazione è scritta in Django, durante l'aggiornamento vengono eseguite delle migrazioni (modifica della struttura del database), e prima di questo processo eseguiamo un dump completo del database tramite il programma standard pg_dump, giusto per essere sicuri.

Durante il backup è emerso un errore imprevisto (versione Postgres – 9.5):

pg_dump: il dumping dei contenuti della tabella “ws_log_smevlog” è fallito: PQgetResult() fallito.
pg_dump: Messaggio di errore dal server: ERRORE: pagina non valida nel blocco 4123007 della base di dati relatton base/16490/21396989
pg_dump: Il comando era: COPY public.ws_log_smevlog [...]
pg_dunp: [archivio parallelo] un processo worker è terminato inaspettatamente

Errore «pagina non valida nel blocco» indica problemi a livello di filesystem, il che è molto preoccupante. Su vari forum hanno suggerito di fare FULL VACUUM con l'opzione zero_damaged_pages per risolvere questo problema. Vediamo di provare...

Preparazione al ripristino

ATTENZIONE! Assicurati di fare un backup di Postgres prima di qualsiasi tentativo di ripristinare il database. Se hai una macchina virtuale, ferma il database e fai uno snapshot. Se non puoi fare uno snapshot, ferma il database e copia il contenuto della cartella Postgres (inclusi i file wal) in un luogo sicuro. L'aspetto fondamentale è non peggiorare la situazione. Leggi ClusterFirst.

Poiché in generale il database funzionava, mi sono limitato a un backup standard del database, escludendo però la tabella con i dati danneggiati (opzione -T, —exclude-table=TABLE in pg_dump).

Il server era fisico, non era possibile fare uno snapshot. Il backup è stato fatto, andiamo avanti.

Verifica del filesystem

Prima di tentare di ripristinare il database, è necessario assicurarsi che il filesystem stesso sia in ordine. E nel caso di errori, correggili, altrimenti si rischia di peggiorare la situazione.

Nel mio caso, il filesystem con il database era montato in «/srv» e il tipo era ext4.

Fermiamo il database: systemctl stop postgresql@9.5-main.service e controlliamo che il file system non sia in uso e che possa essere smontato con il comando lsof:
lsof +D /srv

Ho dovuto fermare anche il database redis, poiché anch'esso lo utilizzava. «/srv»Dopo, l'ho smontato /srv (umount).

Il controllo del file system è stato eseguito con l'utility e2fsck con l'opzione -f (Forza il controllo anche se il file system è contrassegnato come pulito):

Poi, con l'utility dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep checked) è possibile verificare che il controllo sia stato effettivamente effettuato:

e2fsck indica che non sono stati trovati problemi a livello di file system ext4, il che significa che possiamo continuare a tentare di ripristinare il database, più precisamente tornare a vacuum full (ovviamente, è necessario rimontare il file system e avviare il database).

Se hai un server fisico, controlla lo stato dei dischi (attraverso smartctl -a /dev/XXX) o del controller RAID, per assicurarti che il problema non sia a livello hardware. Nel mio caso, il RAID era 'hardware', quindi ho chiesto all'amministratore locale di controllare lo stato del RAID (il server era a diverse centinaia di chilometri da me). Ha detto che non ci sono errori, il che significa che possiamo sicuramente iniziare il ripristino.

Tentativo 1: zero_damaged_pages

Ci connettiamo al database tramite psql con un account che ha diritti di superutente. Abbiamo bisogno proprio del superutente, poiché l'opzione zero_damaged_pages può essere modificata solo da lui. Nel mio caso è postgres:

psql -h 127.0.0.1 -U postgres -s [database_name]

Opzione zero_damaged_pages è necessaria per ignorare gli errori di lettura (dal sito postgrespro):

Quando viene identificato un'intestazione di pagina danneggiata, Postgres Pro solitamente riporta un errore e interrompe la transazione corrente. Se il parametro zero_damaged_pages è attivato, invece, il sistema genera un avviso, azzera la pagina danneggiata in memoria e continua l'elaborazione. Questo comportamento distrugge i dati, ovvero tutte le righe nella pagina danneggiata.

Attiviamo l'opzione e proviamo a fare un full vacuum della tabella:

VACUUM FULL VERBOSE

Sfortunatamente, fallimento.

Ci siamo imbattuti in un errore simile:

INFO: vacuuming "“public.ws_log_smevlog”
WARNING: invalid page in block 4123007 of relation base/16400/21396989; zeroing out page
ERROR: unexpected chunk number 573 (expected 565) for toast value 21648541 in pg_toast_106070

pg_toast – meccanismo di memorizzazione dei 'dati lunghi' in PostgreSQL, se non possono essere posizionati in una pagina (per impostazione predefinita 8kb).

Tentativo 2: reindex

Il primo consiglio trovato su Google non ha funzionato. Dopo alcuni minuti di ricerca, ho trovato un secondo consiglio: fare reindex della tabella danneggiata. Questo consiglio l'ho visto in molti posti, ma non mi ispirava fiducia. Facciamo il reindex:

reindex table ws_log_smevlog

reindex è terminato senza problemi.

Tuttavia, questo non ha aiutato, VACUUM FULL si è chiuso in modo anomalo con un errore simile. Poiché ero abituato ai fallimenti, ho continuato a cercare consigli su Internet e ho trovato qualcosa di piuttosto interessante. articolo.

Tentativo 3: SELECT, LIMIT, OFFSET

Nell'articolo sopra si suggeriva di esaminare la tabella riga per riga ed eliminare i dati problematici. Per prima cosa era necessario rivedere tutte le righe:

for ((i=0; i/dev/null || echo $i; done

Nel mio caso, la tabella conteneva 1 628 991 righe! In effetti, sarebbe stato necessario occuparsi della partizionamento dei dati, ma questo è un argomento per una discussione separata. Era sabato, ho avviato questo comando in tmux e sono andato a dormire:

for ((i=0; i/dev/null || echo $i; done

Al mattino ho deciso di controllare la situazione. Con mia sorpresa, ho scoperto che in 20 ore erano stati scansionati solo il 2% dei dati! Non volevo aspettare 50 giorni. Un altro fallimento completo.

Ma non mi sono arreso. Sono diventato curioso del motivo per cui la scansione stesse impiegando così tanto tempo. Dalla documentazione (ancora su postgrespro) ho appreso che:

OFFSET indica di saltare il numero specificato di righe prima di iniziare a restituire le righe.
Se vengono specificati sia OFFSET che LIMIT, il sistema prima salta le righe OFFSET e poi inizia a contare le righe per il limite LIMIT.

Quando si applica LIMIT, è importante utilizzare anche la clausola ORDER BY, affinché le righe del risultato vengano restituite in un ordine specifico. Altrimenti, verranno restituiti sottoinsiemi di righe imprevedibili.

È evidente che il comando sopra scritto era errato: in primo luogo, non c'era order by, quindi il risultato potrebbe essere errato. In secondo luogo, Postgres doveva prima scansionare e saltare le righe OFFSET, e con l'aumentare di OFFSET le prestazioni sarebbero diminuite ulteriormente.

Tentativo 4: fare un dump in formato testo

Poi mi è venuta in mente un'idea che sembrava geniale: fare un dump in formato testo e analizzare l'ultima riga registrata.

Ma prima, diamo un'occhiata alla struttura della tabella ws_log_smevlog:

Nel nostro caso abbiamo una colonna "id", che conteneva un identificatore unico (contatore) della riga. Il piano era il seguente:

1. Iniziamo a generare un dump in formato testuale (sotto forma di comandi sql)
2. In un certo momento, la creazione del dump sarebbe stata interrotta a causa di un errore, ma il file di testo sarebbe comunque stato salvato su disco
3. Guardo la fine del file di testo, in questo modo troviamo l'identificatore (id) dell'ultima riga che è stata salvata con successo

Ho iniziato a generare un dump in formato testuale:

pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f .\/my_dump.dump

La creazione del dump, come previsto, è stata interrotta dallo stesso errore:

pg_dump: Messaggio di errore dal server: ERROR: invalid page in block 4123007 of relation base\/16490\/21396989

Successivamente attraverso tail ho esaminato la fine del dump (tail -5 .\/my_dump.dump) ho scoperto che il dump si era interrotto alla riga con id 186 525. "Quindi, il problema è nella riga con id 186 526, è corrotta, deve essere eliminata!" – ho pensato. Ma, dopo aver eseguito la query nel database:
«select * from ws_log_smevlog where id=186529si è scoperto che questa riga era a posto… Le righe con gli indici 186 530 – 186 540 funzionavano anche senza problemi. Un'altra "idea geniale" è fallita. Più tardi ho capito perché è successo: quando si eliminano/modificano i dati da una tabella, non vengono eliminati fisicamente, ma contrassegnati come "tuple morte", successivamente arriva autovacuum e contrassegna queste righe come eliminate e consente di riutilizzarle. Per capire, se i dati nella tabella vengono modificati e autovacuum è attivato, non vengono memorizzati in modo sequenziale.

Tentativo 5: SELECT, FROM, WHERE id=

Gli insuccessi ci rendono più forti. Non bisogna mai arrendersi, bisogna andare fino in fondo e credere in se stessi e nelle proprie capacità. Quindi ho deciso di provare un'altra opzione: semplicemente esaminare tutte le registrazioni nel database una alla volta. Sapendo la struttura della mia tabella (vedi sopra), abbiamo un campo id, che è unico (chiave primaria). Nella tabella abbiamo 1 628 991 righe e id seguono un ordine, il che significa che possiamo semplicemente esaminarle una alla volta:

for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database  -c "SELECT * FROM ws_log_smevlog where id=$i" >\/dev\/null || echo $i; done

Se qualcuno non capisce, il comando funziona nel seguente modo: esamina riga per riga la tabella e invia stdout a /dev/null, ma se il comando SELECT fallisce, viene visualizzato il messaggio di errore (stderr viene inviato alla console) e viene visualizzata la riga contenente l'errore (grazie a ||, che significa che ci sono stati problemi con il select (il codice di ritorno del comando non è 0)).

Sono stato fortunato, ho creato indici sul campo id:

E questo significa che trovare la riga con l'id desiderato non dovrebbe richiedere molto tempo. In teoria dovrebbe funzionare. Bene, avviamo il comando in tmux e andiamo a dormire.

Al mattino ho scoperto che erano state esaminate circa 90.000 voci, che rappresentano poco più del 5%. Ottimo risultato se confrontato con il metodo precedente (2%)! Ma non volevo aspettare 20 giorni…

Tentativo 6: SELECT, FROM, WHERE id >= and id <

Il cliente aveva a disposizione un ottimo server per il database: un server a doppio processore Intel Xeon E5-2697 v2, nella nostra configurazione c'erano ben 48 thread! Il carico sul server era medio, potevamo prelevare circa 20 thread senza particolari problemi. Anche la memoria RAM era sufficiente: ben 384 gigabyte!

Perciò il comando doveva essere parallelizzato:

for ((i=1; i<1628991; i=$((i+1)) )); do psql -U my_user -d my_database  -c "SELECT * FROM ws_log_smevlog where id=$i" >\/dev\/null || echo $i; done

Qui avrei potuto scrivere uno script bello ed elegante, ma ho scelto il modo più veloce per parallelizzare: suddividere manualmente l'intervallo 0-1628991 in intervalli di 100.000 righe e avviare separatamente 16 comandi del tipo:

for ((i=N; i/dev/null || echo $i; done

Ma non è tutto. In teoria, connettersi al database richiede anche un certo tempo e risorse di sistema. Collegarsi a 1.628.991 righe non era molto sensato, siamo d'accordo. Quindi, facciamo in modo di estrarre 1000 righe invece di una sola con una singola connessione. Alla fine, il comando si è trasformato in questo:

for ((i=N; i=$i and id/dev/null || echo $i; done

Apriamo 16 finestre nella sessione tmux e avviamo i comandi:

1) for ((i=0; i=$i and id/dev/null || echo $i; done
2) for ((i=100000; i=$i and id/dev/null || echo $i; done
…
15) for ((i=1400000; i=$i and id/dev/null || echo $i; done
16) for ((i=1500000; i=$i and id/dev/null || echo $i; done

Dopo un giorno ho ricevuto i primi risultati! A sapere (i valori XXX e ZZZ non sono stati conservati):

ERROR:  missing chunk number 0 for toast value 37837571 in pg_toast_106070
829000
ERROR:  missing chunk number 0 for toast value XXX in pg_toast_106070
829000
ERROR:  missing chunk number 0 for toast value ZZZ in pg_toast_106070
146000

Questo significa che abbiamo tre righe che contengono un errore. Gli id della prima e della seconda registrazione problematica erano compresi tra 829 000 e 830 000, l'id della terza tra 146 000 e 147 000. Successivamente, dovevamo semplicemente trovare il valore esatto dell'id delle registrazioni problematiche. Per questo, esaminiamo il nostro intervallo di registrazioni problematiche con un passo di 1 e identifichiamo gli id:

for ((i=829000; i/dev/null || echo $i; done
829417
ERRORE:  numero di chunk inaspettato 2 (atteso 0) per il valore toast 37837843 in pg_toast_106070
829449
for ((i=146000; i/dev/null || echo $i; done
829417
ERRORE:  numero di chunk inaspettato ZZZ (atteso 0) per il valore toast XXX in pg_toast_106070
146911

Un finale felice

Abbiamo trovato le righe problematiche. Ci siamo connessi al database tramite psql e abbiamo provato a eliminarle:

my_database=# delete from ws_log_smevlog where id=829417;
DELETE 1
my_database=# delete from ws_log_smevlog where id=829449;
DELETE 1
my_database=# delete from ws_log_smevlog where id=146911;
DELETE 1

Con mia sorpresa, le registrazioni sono state eliminate senza alcun problema anche senza l'opzione zero_damaged_pages.

Poi mi sono connesso al database, ho fatto VACUUM FULL (penso che non fosse necessario), e infine ho fatto un backup con successo utilizzando pg_dump. Il dump è stato effettuato senza errori! Il problema è stato risolto in questo modo estremamente semplice. Non c'era limite alla mia gioia, dopo tanti fallimenti sono riuscito a trovare una soluzione!

Ringraziamenti e conclusione

Questo è stato il mio primo tentativo di ripristinare un database PostgreSQL reale. Questa esperienza la ricorderò a lungo.

E infine, vorrei ringraziare l'azienda PostgresPro per la documentazione tradotta in russo e per corsi online completamente gratuiti, che sono stati di grande aiuto durante l'analisi del problema.

Fonte: habr.com