Backup e ripristino in SQLite: .backup, VACUUM INTO e API online

Perché non puoi semplicemente fare cp del file

Un database SQLite è un file singolo, quindi viene la tentazione di farne il backup con una semplice copia. A volte funziona. Spesso no.

Possono andare storte due cose:

• Un'altra connessione sta scrivendo proprio mentre copi. Il file di destinazione finisce con una transazione applicata a metà, e risulta corrotto all'apertura.
• Il database è in modalità WAL (quella predefinita nella maggior parte delle app moderne). Le modifiche recenti vivono in un file separato, database.db-wal. Se copi solo il file principale, hai perso dati senza accorgertene.

SQLite ti dà strumenti adatti a questo scopo. Gestiscono i lock, il contenuto del WAL e le scritture concorrenti senza sorprese. Usa quelli invece di cp.

Il comando .backup

Il modo più rapido per fare il backup di un database dalla CLI è il comando punto .backup:

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

Così scrivi una copia completa di app.db in backup.db. Funziona anche se altri processi stanno leggendo o scrivendo il database: l'API di backup prende una serie di piccoli lock invece di uno grande, copia le pagine in modo incrementale e ricopia le pagine che vengono modificate durante la copia.

L'output è un database SQLite pienamente utilizzabile. Aprilo come qualsiasi altro:

sqlite3 backup.db
sqlite> .tables

Puoi anche fare tutto con un solo comando di shell, che è l'aspetto tipico della maggior parte dei cron job:

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

Un file in ingresso, un file in uscita. Nessun giro di dump e ripristino, nessun parsing di SQL: solo pagine copiate a livello di storage.

VACUUM INTO per una copia compattata

VACUUM INTO è uno strumento correlato ma diverso. Scrive in un nuovo file una copia del database ricostruita da zero:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('first'), ('second'), ('third');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'wrote backup' AS status;

Il risultato è lo stesso database logico, ma riscritto da capo: ogni pagina compattata, niente frammentazione, niente pagine libere lasciate dalle righe eliminate. Così il file di backup è il più piccolo possibile.

Quando scegliere l'uno o l'altro:

• .backup: backup di routine e frequenti. Più veloce, convive bene con le scritture concorrenti, fedele byte per byte.
• VACUUM INTO: snapshot periodici in cui vuoi anche un file ordinato e di dimensione minima. Più lento perché riscrive tutto, e tiene un lock di scrittura sull'origine per tutta la durata.

Entrambi producono un file .db valido che puoi aprire subito.

L'API di backup online dal codice dell'applicazione

Dentro un'applicazione non lanci sqlite3 dalla shell. Usi l'API di backup online che il tuo driver mette a disposizione. Nel modulo sqlite3 della libreria standard di Python è Connection.backup:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

Il metodo backup copia le pagine da source a dest mentre le altre connessioni continuano a lavorare. Puoi anche passare pages= per copiare a blocchi e progress= per ricevere una callback: utile con database grandi, quando vuoi limitare la velocità della copia o mostrare l'avanzamento.

La maggior parte dei driver negli altri linguaggi espone la stessa API C (sqlite3_backup_init, _step, _finish) con un nome simile. La struttura è sempre questa: apri l'origine, apri la destinazione, scorri le pagine, concludi.

Backup mentre il database è in uso

Qui SQLite dà il meglio di sé senza farsi notare. Sia .backup sia l'API di backup online sono pensati per backup a caldo: il database di origine può restare aperto e attivo per tutto il tempo.

Cosa succede davvero:

1. Il backup prende un lock condiviso e inizia a copiare le pagine.
2. Se chi scrive modifica una pagina non ancora copiata, il backup se ne accorge e la rilegge.
3. La copia termina quando ogni pagina è coerente.

Non devi fermare l'app, chiudere le connessioni o pianificare un fermo. Su un database molto attivo il backup può impiegare qualche ciclo in più per stabilizzarsi, ma ci arriverà. Il file di destinazione che ottieni rappresenta uno snapshot coerente di un preciso istante.

Una cosa da sapere: se usi la modalità WAL, esegui ogni tanto PRAGMA wal_checkpoint(TRUNCATE); per evitare che il file WAL cresca senza limiti. Il backup gestisce già correttamente il WAL: questa è solo buona igiene generale del WAL.

Ripristinare da un backup

Ripristinare un database SQLite è insolitamente noioso, ed è proprio questo il bello. Il file di backup è un database. Per usarlo, aprilo e basta:

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

Per ripristinarlo sopra un database attivo, per esempio per recuperare dopo una perdita di dati, la sequenza sicura è:

1. Ferma ogni processo che ha il database aperto.
2. Elimina i file esistenti app.db, app.db-wal e app.db-shm. I file WAL/SHM rimasti dal vecchio database confonderebbero SQLite se abbinati al file principale ripristinato.
3. Copia il backup al suo posto: cp backup.db app.db.
4. Riavvia l'applicazione.

I file -wal e -shm contano. Se salti il passo 2, SQLite potrebbe provare ad applicare un WAL obsoleto sopra il file principale ripristinato, e otterresti corruzione o dati stranamente mescolati.

Dall'interno della CLI c'è anche un comando .restore, lo speculare di .backup:

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

Sovrascrive il contenuto del database connesso con quello di backup.db. Usa la stessa API di backup online, al contrario.

.dump è un altro strumento

Nei tutorial più vecchi troverai riferimenti a .dump. Non è un backup nello stesso senso: produce un file di testo SQL con istruzioni CREATE e INSERT:

sqlite3 app.db .dump > app.sql

Per ripristinare, riesegui l'SQL:

sqlite3 new.db < app.sql

È utile per migrare tra versioni di SQLite, confrontare gli schemi in git o spostare dati verso un altro motore di database. È più lento, più voluminoso e meno fedele di .backup (collation personalizzate, colonne generate e alcuni pragma possono richiedere attenzione in più). Per un vero backup di un database in uso, preferisci .backup o VACUUM INTO.

Una routine di backup sensata

Per la maggior parte delle app, questa combinazione funziona bene:

• Un .backup pianificato: ogni ora, ogni giorno, in base a quanti dati puoi permetterti di perdere. Economico, veloce, a caldo.
• Un VACUUM INTO settimanale in un percorso separato. Intercetta eventuali derive, ti dà uno snapshot compattato e mette alla prova un percorso di codice diverso.
• Una politica di conservazione: tieni gli ultimi N backup giornalieri e gli ultimi M settimanali. I database SQLite si comprimono bene, quindi vale la pena fare gzip backup.db a posteriori.
• Ogni tanto ripristinane uno ed eseguici qualche query. Un backup mai testato è una speranza, non un backup.

# Ogni giorno, in cron:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# Ogni settimana:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

Entrambi i comandi si possono eseguire in sicurezza mentre l'app serve richieste.

Prossimo passo: le impostazioni PRAGMA

I backup sono un aspetto operativo; regolare il comportamento a runtime è un altro. SQLite espone le sue manopole attraverso le istruzioni PRAGMA: modalità del journal, livello di sincronizzazione, dimensione della cache, applicazione delle chiavi esterne. La prossima pagina passa in rassegna quelle che vale la pena conoscere.