Voglio condividere con voi la mia prima esperienza di successo nel ripristino completo del database Postgres. Ho iniziato a conoscere il DBMS Postgres sei mesi fa, e prima di questa esperienza non avevo affatto esperienza nella gestione dei database.
Lavoro come semi-DevOps engineer in una grande azienda IT. La nostra azienda si occupa dello sviluppo software per servizi ad alta domanda, 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 e durante l'aggiornamento vengono eseguite delle migrazioni (cambiamenti nella struttura del database), e prima di questo processo realizziamo un dump completo del database tramite il programma standard pg_dump, giusto per precauzione.
Durante la creazione del dump si è verificato un errore imprevisto (versione Postgres – 9.5):
pg_dump: il dumping dei contenuti della tabella “ws_log_smevlog” è fallito: PQgetResult() non riuscito.
pg_dump: messaggio di errore dal server: ERRORE: pagina non valida nel blocco 4123007 della relazione base/16490/21396989
pg_dump: il comando era: COPY public.ws_log_smevlog [...]
pg_dump: [architettura parallela] un processo worker ha terminato in modo imprevisto Errore «pagina non valida nel blocco» riguarda problemi a livello di file system, il che non è affatto positivo. Su vari forum è stato suggerito di fare FULL VACUUM con opzione zero_damaged_pages per risolvere questo problema. Beh, proviamo…
Preparazione per il recupero
ATTENZIONE! Assicurati di fare un backup di Postgres prima di qualsiasi tentativo di ripristino del database. Se hai una macchina virtuale, interrompi il database e fai uno snapshot. Se non è possibile fare uno snapshot, ferma il database e copia il contenuto della directory Postgres (inclusi i file wal) in un luogo sicuro. La cosa più importante è non peggiorare la situazione. Leggi .
Dato che in generale il mio database funzionava, mi sono limitato a un dump normale del database, ma ho escluso la tabella con i dati danneggiati (opzione -T, —exclude-table=TABLE in pg_dump).
Il server era fisico, non era possibile fare uno snapshot. Backup effettuato, procediamo.
Controllo del file system
Prima di tentare il ripristino del database, è necessario assicurarsi che il file system sia a posto. E in caso di errori, correggerli, poiché altrimenti si può solo peggiorare la situazione.
Nel mio caso il file system 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 utilizzato da nessuno e possa essere smontato con il comando lsof:
lsof +D /srv
Ho dovuto fermare anche il database redis, poiché lo stava utilizzando. «/srv»Successivamente ho smontato /srv (umount).
Il controllo del file system è stato eseguito con l'utility e2fsck con l'opzione -f (Controllo forzato anche se il file system è contrassegnato come pulito):
Successivamente, con l'utility dumpe2fs (sudo dumpe2fs /dev/mapper/gu2—sys-srv | grep checked) è possibile verificare che il controllo sia stato realmente effettuato:
e2fsck indica che non sono stati trovati problemi a livello di file system ext4, il che significa che si possono continuare i tentativi di ripristinare il database, ovvero tornare a vacuum full (è necessario rimontare il file system e riavviare il database).
Se hai un server fisico, assicurati di controllare lo stato dei dischi (tramite smartctl -a /dev/XXX) o del controller RAID per assicurarti che non ci siano problemi hardware. Nel mio caso, il RAID era 'hardware', quindi ho chiesto all'amministratore locale di verificare 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 recupero.
Tentativo 1: zero_damaged_pages
Ci colleghiamo al database tramite psql con un account che ha diritti di superutente. Abbiamo bisogno di un superutente perché solo lui può modificare l'opzione zero_damaged_pages 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 identificata un'intestazione di pagina danneggiata, Postgres Pro di solito segnala un errore e interrompe la transazione corrente. Se l'opzione zero_damaged_pages è attivata, invece, il sistema emette 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 eseguire un vacuum completo della tabella:
VACUUM FULL VERBOSE 
Sfortunatamente, fallimento.
Abbiamo riscontrato un errore simile:
INFO: pulizia "public.ws_log_smevlog"
WARNING: pagina non valida nel blocco 4123007 della relazione base/16400/21396989; azzeramento pagina
ERROR: numero di chunk inaspettato 573 (atteso 565) per il valore toast 21648541 in pg_toast_106070– meccanismo di archiviazione dei «dati lunghi» in PostgreSQL, se non possono essere contenuti in una singola pagina (per impostazione predefinita 8kb).
Tentativo 2: reindicizzazione
Il primo consiglio di Google non ha funzionato. Dopo diversi minuti di ricerca, ho trovato un secondo consiglio – fare reindicizzazione della tabella danneggiata. Questo consiglio l'avevo visto in molti posti, ma non mi ispirava fiducia. Facciamo reindicizzazione:
reindicizza la tabella ws_log_smevlog 
reindicizzazione è completata senza problemi.
Tuttavia, questo non ha aiutato, VACUUM FULL terminava in modo anomalo con un errore simile. Poiché ero abituato ai fallimenti, ho continuato a cercare consigli su internet e mi sono imbattuto in qualcosa di piuttosto interessante .
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 controllare tutte le righe:
for ((i=0; i/dev/null || echo $i; doneNel mio caso, la tabella conteneva 1 628 991 righe! Idealmente, era necessario occuparsi della , ma questo è un tema da discutere separatamente. Era sabato, ho lanciato questo comando in tmux e sono andato a dormire:
for ((i=0; i/dev/null || echo $i; doneAl mattino ho deciso di controllare come stessero le cose. Con mia sorpresa, ho scoperto che in 20 ore era stato scansionato solo il 2% dei dati! Non volevo aspettare 50 giorni. Un altro completo fallimento.
Ma non mi sono arreso. Sono diventato curioso del perché la scansione stesse impiegando così tanto tempo. Dalla documentazione (di nuovo su postgrespro) ho appreso:
OFFSET indica di saltare il numero specificato di righe prima di iniziare a restituire le righe.
Se sia OFFSET che LIMIT sono specificati, il sistema prima salta le righe OFFSET e poi inizia a contare le righe per limitare LIMIT.Utilizzando LIMIT, è importante utilizzare anche la clausola ORDER BY, affinché le righe risultanti vengano restituite in un ordine specifico. Altrimenti verranno restituiti sottoinsiemi imprevedibili di righe.
È evidente che il comando sopra riportato era errato: in primo luogo, non c'era order by, il risultato potrebbe essere stato errato. In secondo luogo, Postgres doveva prima scansionare e saltare le righe OFFSET, e all'aumentare di OFFSET le performance sarebbe stata ancora più ridotta.
Tentativo 4: estrarre il dump in formato testo
Poi mi è venuta in mente un'idea apparentemente geniale: estrarre il dump in formato testo e analizzare l'ultima riga registrata.
Ma prima di tutto, esaminiamo la struttura della tabella ws_log_smevlog:
Nel nostro caso, abbiamo una colonna «id», che contiene un identificativo unico (contatore) per la riga. Il piano era il seguente:
- Iniziamo a estrarre il dump in formato testo (come comandi sql)
- A un certo punto, l'estrazione del dump si sarebbe interrotta a causa di un errore, ma il file di testo sarebbe comunque rimasto salvato su disco.
- Guardiamo la fine del file di testo, trovando così l'identificativo (id) dell'ultima riga che è stata estratta con successo.
Ho iniziato a estrarre il dump in formato testo:
pg_dump -U my_user -d my_database -F p -t ws_log_smevlog -f ./my_dump.dumpL'estrazione del dump, come previsto, si è interrotta con lo stesso errore:
pg_dump: Messaggio di errore dal server: ERROR: pagina non valida nel blocco 4123007 della relazione base/16490/21396989 Poi, attraverso tail ho esaminato la fine del dump (tail -5 ./my_dump.dump) e ho scoperto che il dump si era interrotto alla riga con id 186 525. “Quindi, il problema è nella riga con id 186526, è corrotta e devo eliminarla!” – pensai. Ma, facendo una query nel database:
«seleziona * da ws_log_smevlog dove id=186529» si è scoperto che questa riga è a posto... Anche le righe con indici 186 530 — 186 540 hanno funzionato senza problemi. Un'altra «idea geniale» è fallita. Più tardi ho capito perché è successo: quando si eliminano o si modificano i dati della tabella, non vengono eliminati fisicamente, ma contrassegnati come «tupla morta», poi arriva autovacuum e contrassegna queste righe come eliminate e consente di riutilizzarle. Per capire, se i dati nella tabella cambiano e autovacuum è attivato, allora non vengono memorizzati in modo sequenziale.
Tentativo 5: SELECT, FROM, WHERE id=
Le difficoltà ci rendono più forti. Non bisogna mai arrendersi, bisogna andare fino in fondo e credere in se stessi e nelle proprie capacità. Perciò ho deciso di provare un altro modo: semplicemente esaminare ogni record nel database uno 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 in ordine, il che significa che possiamo semplicemente esaminarle una per una:
for ((i=1; i/dev/null || echo $i; doneSe qualcuno non lo capisce, il comando funziona come segue: analizza la tabella riga per riga e invia stdout in /dev/null, ma se il comando SELECT fallisce, viene visualizzato un messaggio di errore (stderr viene inviato alla console) e viene visualizzata una riga contenente l'errore (grazie a ||, che indica che ci sono stati problemi con il select (il codice di ritorno del comando non è 0)).
Sono stato fortunato, avevo 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. Beh, eseguiamo il comando in tmux e andiamo a dormire.
Al mattino ho scoperto che erano state esaminate circa 90.000 registrazioni, 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 >= e id <
Per il database del cliente era stato riservato un ottimo server: dual-processor Intel Xeon E5-2697 v2, nella nostra posizione c'erano ben 48 thread! Il carico sul server era medio, quindi potevamo prelevare senza troppi problemi circa 20 thread. C'era anche abbastanza memoria RAM: ben 384 gigabyte!
Pertanto, il comando doveva essere parallelizzato:
for ((i=1; i/dev/null || echo $i; doneQui potevi scrivere uno script bello ed elegante, ma ho scelto il modo più rapido per parallelizzare: ho suddiviso manualmente l'intervallo da 0 a 1628991 in blocchi da 100.000 record e ho avviato separatamente 16 comandi del tipo:
for ((i=N; i/dev/null || echo $i; doneMa non è tutto. In teoria, connettersi al database richiede anche del tempo e risorse di sistema. Connettere 1.628.991 non era molto sensato, ammettiamolo. Quindi, estraiamo 1000 righe invece di una ad ogni connessione. Di conseguenza, il comando è diventato questo:
for ((i=N; i=$i and id/dev/null || echo $i; doneApriamo 16 finestre in una sessione tmux e avviamo i comandi:
1) per ((i=0; i=$i and id/dev/null || echo $i; done 2) per ((i=100000; i=$i and id/dev/null || echo $i; done … 15) per ((i=1400000; i=$i and id/dev/null || echo $i; done 16) per ((i=1500000; i=$i and id/dev/null || echo $i; done
Il giorno dopo ho ricevuto i primi risultati! In particolare (i valori XXX e ZZZ non sono più stati mantenuti):
ERRORE: chunk mancante numero 0 per il valore toast 37837571 in pg_toast_106070
829000
ERRORE: chunk mancante numero 0 per il valore toast XXX in pg_toast_106070
829000
ERRORE: chunk mancante numero 0 per il valore toast ZZZ in pg_toast_106070
146000Questo significa che abbiamo tre righe contenenti un errore. Gli id della prima e della seconda registrazione problematica si trovavano tra 829 000 e 830 000, l'id della terza – tra 146 000 e 147 000. Successivamente, dovevamo semplicemente trovare il valore esatto degli id delle registrazioni problematiche. A tal fine, esaminiamo il nostro intervallo con registrazioni problematiche per passo 1 e identifichiamo gli id:
per ((i=829000; i/dev/null || echo $i; done 829417 ERRORE: numero di chunk imprevisto 2 (previsto 0) per il valore toast 37837843 in pg_toast_106070 829449 per ((i=146000; i/dev/null || echo $i; done 829417 ERRORE: numero di chunk imprevisto ZZZ (previsto 0) per il valore toast XXX in pg_toast_106070 146911
Un finale felice
Abbiamo trovato le righe problematiche. Accediamo al database tramite psql e proviamo a eliminarle:
my_database=# delete from ws_log_smevlog where id=829417;
CANCELLA 1
my_database=# delete from ws_log_smevlog where id=829449;
CANCELLA 1
my_database=# delete from ws_log_smevlog where id=146911;
CANCELLA 1Con 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 farlo non fosse necessario), e infine ho effettuato un backup con successo usando pg_dump. Il dump è stato eseguito senza alcun errore! Sono riuscito a risolvere il problema in questo modo semplice. La gioia era immensa, dopo tanti fallimenti, finalmente ho trovato la soluzione!
Riconoscimenti e conclusioni
Questa è stata la mia prima esperienza nel ripristinare un reale database Postgres. Ricorderò a lungo questa esperienza.
E infine, vorrei ringraziare l'azienda PostgresPro per la documentazione tradotta in italiano e per , che sono stati di grande aiuto durante l'analisi del problema.
Fonte: habr.com
