SQLite in produzione su un VPS: limiti e backup

Quando SQLite è il database di produzione giusto su un VPS

Eseguire SQLite in produzione su un VPS è la scelta giusta per la maggior parte delle applicazioni di piccole dimensioni. Il motivo è semplice: un solo processo su una sola macchina che scrive in un solo file non ha bisogno di un server di database. Non c'è alcun daemon da supervisionare, nessuna porta da proteggere con il firewall, nessuna password da ruotare e nessuna seconda macchina da mantenere operativa. Una query è una chiamata di funzione, non un round trip di rete. Una pagina che esegue quaranta query comporta quindi quaranta chiamate di funzione.

Il limite è circoscritto, ma reale. SQLite consente un solo writer alla volta per l'intero file del database e il file non può essere condiviso tra due macchine. Entrambi i limiti sono compatibili con un singolo VPS che esegue una singola applicazione. Diventano entrambi bloccanti non appena si supera questo modello. Questa guida descrive le impostazioni che rendono SQLite sicuro su un server, il backup continuo con Litestream e il punto in cui è necessario fermarsi.

Installare prima lo strumento da riga di comando. Tutto ciò che segue è stato eseguito su Ubuntu 24.04.

sudo apt update
sudo apt install -y sqlite3
sqlite3 --version

Il comando stampa una versione che inizia con 3., seguita da una data di compilazione e da un hash del codice sorgente. A luglio 2026, Ubuntu 24.04 include SQLite 3.45.1. Probabilmente l'applicazione non utilizza questo eseguibile: la maggior parte dei runtime dei linguaggi include una propria copia della libreria SQLite, spesso più recente. Verificare quindi la versione restituita dal driver del database prima di usare una funzionalità recente.

Perché la modalità WAL è la prima impostazione da modificare

Per impostazione predefinita, SQLite usa un journal di rollback. Prima di modificare una pagina, copia la pagina originale in un file -journal, quindi modifica il database direttamente. Per eseguire questa operazione in sicurezza, acquisisce un lock esclusivo sull'intero file. Di conseguenza, ogni lettore resta in attesa mentre è in corso una scrittura. Su un laptop questo passa inosservato. Su un server web, una scrittura lenta rallenta ogni richiesta che accede al database.

La modalità WAL (write-ahead log) inverte l'ordine delle operazioni. Un writer aggiunge le nuove pagine a un file -wal separato e lascia invariato il database principale. I reader continuano a leggere il file principale nello snapshot acquisito all'inizio. I reader quindi non bloccano il writer e il writer non blocca i reader. In seguito, un checkpoint copia le pagine accumulate nel WAL nel database principale. Questa singola modifica è il fattore principale che rende SQLite utilizzabile dietro un'applicazione web.

Attivare la modalità WAL e verificare che sia persistente

mkdir -p ~/app
sqlite3 ~/app/app.db "PRAGMA journal_mode=WAL;"

Il comando stampa wal. Questo output non è decorativo. PRAGMA journal_mode restituisce la modalità effettiva del database, quindi una risposta delete indica che la modifica non è riuscita e che il database usa ancora il journal di rollback.

La modalità WAL è persistente. È un flag nell'header del database, non un'impostazione della connessione. Perciò va impostata una sola volta per ogni file di database e tutte le connessioni successive la ereditano, anche dopo un riavvio. Verificalo con una nuova connessione.

sqlite3 ~/app/app.db "PRAGMA journal_mode;"

Ora crea una tabella e controlla quali file vengono creati su disco.

sqlite3 ~/app/app.db <<'SQL'
CREATE TABLE IF NOT EXISTS notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('first row');
SQL
ls -l ~/app/

Ora ci sono tre file: app.db, app.db-wal e app.db-shm. Il file -wal contiene le pagine sottoposte a commit che non sono ancora state sottoposte a checkpoint. Il file -shm è un indice di memoria condivisa che ogni connessione mappa, in modo che tutte concordino sul contenuto del WAL. Entrambi fanno parte del database e non sono file temporanei. Se copi solo app.db mentre l'applicazione è in esecuzione, ottieni un file a cui mancano tutti i commit più recenti. Se elimini app.db e lasci gli altri due file al loro posto, SQLite applicherà quelle pagine WAL obsolete al nuovo file creato con quel nome. In questo modo si può danneggiare un database appena creato mentre si tenta di reimpostarne uno.

Le impostazioni di connessione necessarie per ogni applicazione in produzione

Solo journal_mode viene memorizzata nel database. Tutte le altre impostazioni riportate di seguito sono specifiche della connessione. L'applicazione deve quindi eseguirle su ogni connessione che apre, incluse tutte le connessioni create in background da un pool.

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 5000;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;

busy_timeout = 5000 indica a SQLite di continuare a ritentare l'accesso a un database bloccato per un massimo di 5000 millisecondi prima di restituire database is locked. Il valore predefinito è 0. Per impostazione predefinita, quindi, SQLite ha esito negativo immediatamente quando due operazioni di scrittura si sovrappongono per la prima volta. Impostare questo singolo valore elimina la maggior parte degli errori di blocco attribuiti a SQLite.

synchronous = NORMAL è l'impostazione corretta in modalità WAL, ma è importante comprenderne il compromesso. Con FULL, SQLite esegue fsync sul WAL a ogni commit. Con NORMAL, esegue la sincronizzazione durante i checkpoint. La documentazione di SQLite è chiara su ciò a cui si rinuncia: dopo un'interruzione di corrente o un riavvio forzato, le transazioni non sono più persistenti. La perdita di corrente non può danneggiare il database; si perdono semplicemente gli ultimi commit che non avevano ancora raggiunto il disco. Su un VPS, questo è solitamente il compromesso corretto, perché elimina un fsync dal percorso di ogni singola scrittura.

foreign_keys = ON è disattivata per impostazione predefinita per garantire la compatibilità con le versioni precedenti e viene configurata per singola connessione. Uno schema pieno di clausole REFERENCES non impone alcun vincolo finché ogni connessione non attiva questa impostazione.

Un'altra impostazione diventa importante solo in seguito. SQLite esegue automaticamente un checkpoint quando il WAL supera 1000 pagine. L'operazione viene eseguita dalla connessione che in quel momento completa una transazione. Di per sé, questo comportamento è adeguato. Diventa però un problema quando è in esecuzione Litestream, perché Litestream deve poter controllare quando vengono eseguiti i checkpoint.

Perché database is locked continua a verificarsi dopo aver impostato busy_timeout

Questo è l’errore che spinge molti a tornare a Postgres e ha una causa precisa.

Un busy timeout installa un busy handler, ma SQLite non garantisce che lo chiami.

Se SQLite determina che la chiamata al busy handler potrebbe causare un deadlock, restituisce SQLITE_BUSY all’applicazione invece di chiamare il busy handler.

Il deadlock che SQLite evita si verifica quando una transazione viene aggiornata. In SQLite, un BEGIN senza argomenti significa BEGIN DEFERRED. Se la prima istruzione dopo di esso è un SELECT, ci si trova in una transazione di lettura. Quando un successivo UPDATE nella stessa transazione deve diventare una transazione di scrittura e un’altra connessione ha eseguito una scrittura dall’inizio della lettura, SQLite non può metterla in attesa. Lo snapshot è già obsoleto e l’attesa causerebbe un deadlock tra le due connessioni. La documentazione descrive direttamente il risultato:

Le istruzioni di scrittura successive aggiorneranno la transazione a una transazione di scrittura se possibile, oppure restituiranno SQLITE_BUSY.

Il timeout di 5000 millisecondi non viene mai consultato. L’errore arriva immediatamente. Per questo sembra che l’impostazione non abbia avuto effetto.

La correzione consiste in una parola.

BEGIN IMMEDIATE;
UPDATE notes SET body = 'edited' WHERE id = 1;
COMMIT;

BEGIN IMMEDIATE acquisisce il lock di scrittura all’inizio, prima di eseguire qualsiasi lettura. Non avviene alcun aggiornamento, quindi non c’è alcun deadlock da evitare. Il busy handler può quindi intervenire e la connessione attende il proprio turno invece di fallire. Mantieni differite le transazioni di sola lettura. Qualsiasi transazione che contiene una scrittura deve essere immediata.

La seconda causa degli errori di lock è più difficile da individuare: mantenere aperta una transazione di scrittura durante attività lente. SQLite serializza gli scrittori. Una transazione che si apre, chiama un’API esterna attraverso la rete e poi esegue il commit blocca quindi tutti gli altri scrittori per tutta la durata della chiamata. Leggi i dati necessari, chiudi la transazione, esegui l’attività lenta, quindi apri una breve transazione di scrittura per salvare il risultato.

Backup continuo con Litestream

Una copia notturna può perdere fino a un giorno di scritture, mentre l'esecuzione di cp su un database SQLite attivo può produrre una copia che non si apre. Esistono due opzioni sicure. sqlite3 app.db ".backup /path/to/backup.db" usa l'interfaccia di backup online di SQLite e funziona su un database in uso. Litestream va oltre: monitora il WAL e invia continuamente le modifiche allo storage a oggetti. In questo modo la perdita di dati nel caso peggiore passa da un giorno a circa un secondo.

Litestream è un singolo binario Go che viene eseguito accanto all'applicazione. Non si trova tra l'applicazione e il database. L'applicazione continua a scrivere in SQLite esattamente come prima, mentre Litestream legge il WAL e carica le modifiche.

cd /tmp
curl -fsSL -O https://github.com/benbjohnson/litestream/releases/download/v0.5.14/litestream-0.5.14-linux-x86_64.deb
sudo dpkg -i litestream-0.5.14-linux-x86_64.deb
litestream version

v0.5.14 è la release documentata dalla pagina ufficiale di installazione per Linux a luglio 2026, mentre v0.5.15 è stata pubblicata il 21 luglio 2026. Modifica la versione in entrambe le righe in modo che corrisponda al tag corrente nella pagina delle release. Se il tuo VPS usa arm64, scarica invece il pacchetto arm64 corrispondente.

Il file di configurazione si trova in /etc/litestream.yml. Inizia con una replica in un file locale. In questo modo verifichi l'intero flusso senza dover configurare credenziali cloud.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      type: file
      path: /var/backups/litestream/app

Il campo è replica, al singolare. Litestream 0.5 ha sostituito l'array replicas della serie 0.3 con un singolo blocco di replica. Una configurazione con due voci ora non supera l'avvio. Molte guide di terze parti mostrano ancora il vecchio array. Usa quindi la struttura precedente invece del primo esempio trovato tramite una ricerca. La serie 0.5 ha anche rinominato il sottocomando litestream wal in litestream ltx, perché il formato del backup su disco è cambiato.

Verifica che la configurazione venga analizzata correttamente prima di abilitare qualsiasi funzione.

sudo litestream databases -config /etc/litestream.yml

Verifica quindi manualmente il percorso completo. Questa forma ignora il file di configurazione e replica un database in un singolo percorso.

mkdir -p /tmp/replica
litestream replicate ~/app/app.db file:///tmp/replica/app

Il comando viene eseguito in primo piano e continua a funzionare. In una seconda shell, scrivi una riga e ripristina la replica in un nuovo file.

sqlite3 ~/app/app.db "INSERT INTO notes (body) VALUES ('written after replication started');"
litestream restore -o /tmp/restored.db file:///tmp/replica/app
sqlite3 /tmp/restored.db "SELECT count(*) FROM notes;"

Il conteggio include la nuova riga. Se non la include, la modifica non è ancora stata sincronizzata. Litestream invia le modifiche con una frequenza sync-interval, che per impostazione predefinita è di 1 secondo. Attendi e ripeti il ripristino. Quel secondo è anche il tuo punto di ripristino. Un arresto anomalo può perdere al massimo le scritture dell'ultimo intervallo di sincronizzazione. Nessuna configurazione può ridurre questa perdita a zero.

Per lo storage reale, sostituisci il blocco di replica con un URL S3. Funziona con Amazon S3 e con storage a oggetti compatibile con S3 di altri provider.

dbs:
  - path: /home/appuser/app/app.db
    replica:
      url: s3://your-bucket-name/app
      region: us-east-1

snapshot:
  interval: 24h
  retention: 24h

Non inserire le credenziali in quel file. Litestream legge LITESTREAM_ACCESS_KEY_ID e LITESTREAM_SECRET_ACCESS_KEY dall'ambiente. Inseriscile quindi in un drop-in di systemd di proprietà di root, con permessi 600.

I valori degli snapshot riportati sopra sono quelli predefiniti, ma il valore predefinito della conservazione può essere sorprendente. La conservazione indica per quanto tempo Litestream mantiene gli snapshot e i file a essi associati. Indica quindi anche quanto indietro nel tempo puoi eseguire un ripristino. Ventiquattro ore significa che una migrazione errata rilevata mercoledì mattina è già irrecuperabile dallo stato di lunedì. Imposta retention: 168h su una settimana e sostieni il costo dello storage aggiuntivo.

Verifica il ripristino prima di averne bisogno

litestream restore -o /tmp/check.db /home/appuser/app/app.db
sqlite3 /tmp/check.db "PRAGMA integrity_check;"
sqlite3 /tmp/check.db "SELECT count(*) FROM notes;"

Dato un percorso del database, litestream restore cerca la replica corrispondente in /etc/litestream.yml e la scarica. PRAGMA integrity_check stampa ok per un file integro; qualsiasi altro output indica che la copia ripristinata non è utilizzabile. Esegui questa procedura secondo una pianificazione con un servizio e un timer systemd e leggi l'output. Finché non hai ripristinato almeno una volta un backup, non sai se funziona.

Eseguire Litestream con systemd

Il pacchetto Debian installa un'unità litestream che legge /etc/litestream.yml.

sudo systemctl enable litestream
sudo systemctl start litestream
sudo journalctl -u litestream -f

Un output corretto elenca ogni database dalla configurazione e poi rimane inattivo, a parte le righe di sincronizzazione periodiche. Un errore no such file or directory relativo al percorso del database indica che il percorso nella configurazione è errato oppure che il processo non può leggerlo. Per impostazione predefinita, l'unità viene eseguita come root, con privilegi superiori a quelli necessari per questo lavoro. Litestream deve poter leggere e scrivere sia il database sia la directory che lo contiene, perché utilizza i file -wal e -shm accanto al database. Perciò assegnagli l'account già utilizzato dalla tua applicazione.

# /etc/systemd/system/litestream.service.d/override.conf
[Service]
User=appuser
Group=appuser

Applica la configurazione con sudo systemctl daemon-reload e sudo systemctl restart litestream. Configurare un account di servizio dedicato con privilegi minimi richiede pochi minuti. Questa scelta distingue un agente di backup da un secondo processo root sul server.

Un dettaglio sull'ordine di avvio è importante se ricrei la macchina da zero. Devi ripristinare il database prima di avviare l'applicazione. litestream restore accetta -if-db-not-exists, che restituisce 0 quando il file esiste già. Perciò è sicuro eseguirlo a ogni avvio. Inseriscilo in una riga ExecStartPre dell'unità dell'applicazione. In questo modo, un VPS nuovo scarica il database, mentre uno esistente non esegue alcuna operazione. litestream replicate dispone di un flag -restore-if-db-not-exists corrispondente, se preferisci gestire tutto in un unico punto.

Quando SQLite non è adatto su un VPS

File system di rete. Questo è il limite che non si può aggirare con la configurazione. La modalità WAL richiede che ogni processo che usa il database condivida una piccola area di memoria, fornita dal file -shm. La documentazione di SQLite enuncia la regola senza eccezioni:

Tutti i processi che usano un database devono essere eseguiti sullo stesso computer host; WAL non funziona su un file system di rete.

Un database su una condivisione NFS (network file system) o SMB montata può quindi danneggiarsi e nessuna pragma può impedirlo. Qui è importante distinguere due casi che spesso vengono confusi. Un dispositivo a blocchi di rete, ovvero il tipo di archiviazione aggiuntiva collegato dalla maggior parte dei provider VPS, appare a Linux come un normale disco con un normale file system, e questo va bene. Una condivisione di file montata è diversa.

Un secondo server applicativo. Nessuna impostazione consente di usare SQLite in questo modo. Quando servono due macchine per fornire gli stessi dati, serve un database che comunichi attraverso la rete. Pianifica questa migrazione finché hai ancora tempo per farlo.

Carichi di lavoro con molte scritture. Il limite di un writer alla volta è una proprietà del formato del file, non un parametro configurabile. Le scritture brevi sono rapide perché ogni commit viene aggiunto al WAL. Per questo motivo, il throughput dipende più dalla latenza del disco nelle scritture di piccole dimensioni che dalla CPU. Consulta Archiviazione NVMe e SSD SATA su un VPS per vedere l'effetto di questa differenza. Il problema reale sono le transazioni lunghe, perché mettono in coda tutti gli altri writer.

Query analitiche. SQLite è un row store progettato per le transazioni. Una dashboard che analizza cento milioni di righe richiede uno strumento diverso per un'attività diversa. DuckDB a confronto con SQLite per l'uso sui server descrive il limite tra i due casi.

VACUUM durante la replica. Un VACUUM completo riscrive l'intero file del database. Litestream deve quindi caricarlo di nuovo per intero. La documentazione di Litestream sconsiglia di eseguire questa operazione mentre la replica è attiva. Arresta il replicatore, esegui il vacuum, riavvialo e prevedi un nuovo snapshot completo.

Due replicatori sullo stesso database. Non eseguire mai due processi Litestream sullo stesso database o sulla stessa destinazione di replica. La documentazione specifica chiaramente che impedire questa situazione è una tua responsabilità. In caso contrario, la replica potrebbe non essere ripristinabile.

Cosa non copre Litestream

Litestream protegge il file del database e nient'altro. I file caricati, la configurazione dell'applicazione, i certificati TLS (Transport Layer Security) e i file delle unità sono ancora a tuo carico. Abbinalo a backup crittografati esterni al server con restic eseguiti secondo una pianificazione, così entrambe le parti sono coperte. Se la macchina è nuova, i primi dieci minuti su un nuovo VPS illustrano la configurazione dell'account utente e del firewall che questa guida presuppone sia già stata completata.

FAQ

SQLite è sufficiente per un'applicazione di produzione?

Per un'applicazione su un solo server, sì, a condizione di attivare la modalità WAL, impostare un timeout di attesa e creare backup continuamente. I limiti rilevanti sono strutturali: un solo writer alla volta e un solo computer host. Un'applicazione che rientra in questi limiti dispone di un database senza un passaggio sulla rete e senza un processo separato da monitorare. Un'applicazione che non rientra in questi limiti ha bisogno di un database client-server, e nessuna ottimizzazione può modificare questo requisito.

Perché continuo a ricevere database is locked dopo aver impostato busy_timeout?

Perché SQLite salta il busy handler quando l'attesa potrebbe causare un deadlock. Una transazione che inizia con un semplice BEGIN è differita: un SELECT iniziale la inserisce in una transazione di lettura, mentre una scrittura successiva deve eseguire un upgrade. Se nel frattempo un'altra connessione ha eseguito una scrittura, SQLite restituisce subito SQLITE_BUSY invece di chiamare il busy handler, perché lo snapshot di lettura è ormai obsoleto. Inizia con BEGIN IMMEDIATE qualsiasi transazione che eseguirà una scrittura, così il write lock viene acquisito subito e il timeout viene applicato.

Posso mantenere il database SQLite su uno storage di rete?

Non su un filesystem di rete come NFS o SMB. La modalità WAL richiede che tutti i processi condividano la memoria tramite il file -shm, e la documentazione di SQLite stabilisce che ogni processo che usa il database deve trovarsi sullo stesso computer host. Un network block device collegato dal provider è diverso: Linux lo vede come un normale disco con un normale filesystem e SQLite funziona anche in questo caso.

Mi serve Litestream se eseguo già backup notturni?

Dipende dalla quantità di dati che puoi permetterti di perdere. Un job notturno può comportare la perdita di fino a ventiquattro ore di scritture. Litestream esegue la sincronizzazione circa una volta al secondo, quindi un crash comporta la perdita approssimativa dell'ultimo secondo. È anche più sicuro che copiare il file del database con cp, perché quest'operazione può acquisire il database durante una scrittura. Litestream copre solo il database, quindi mantieni attivo anche un backup generale dei file.