Sauvegarde SQLite : .backup, VACUUM INTO et API en ligne

Pourquoi un simple cp ne suffit pas

Une base SQLite tient dans un seul fichier, et c'est tentant de la sauvegarder avec une bête copie de fichier. Parfois ça passe. Souvent, non.

Deux choses peuvent foirer :

• Une autre connexion est en pleine écriture au moment de la copie. Le fichier de destination se retrouve avec une transaction à moitié appliquée — corrompu dès l'ouverture.
• La base tourne en mode WAL (le mode par défaut pour la plupart des applis modernes). Les modifications récentes sont stockées dans un fichier database.db-wal à part. Si vous ne copiez que le fichier principal, vous perdez des données sans même t'en rendre compte.

Heureusement, SQLite fournit de vrais outils pour ça. Ils gèrent le verrouillage, le contenu du WAL et les écritures concurrentes sans mauvaise surprise. Utilisez-les plutôt que cp.

La commande .backup

Le moyen le plus rapide de faire une sauvegarde SQLite depuis la CLI, c'est la commande pointée .backup :

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

Cette commande écrit une copie complète de app.db vers backup.db. Elle fonctionne même si d'autres processus sont en train de lire ou d'écrire dans la base : l'API de backup pose une série de petits verrous plutôt qu'un seul gros, copie les pages de façon incrémentale et retente les pages qui sont modifiées pendant la copie.

Le fichier produit est une base SQLite parfaitement utilisable. Ouvrez-la comme n'importe quelle autre :

sqlite3 backup.db
sqlite> .tables

Vous pouvez aussi tout faire en une seule commande shell, ce qui correspond à la forme finale que prennent la plupart des cron jobs :

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

Un fichier en entrée, un fichier en sortie. Pas d'aller-retour dump/restore, pas d'analyse SQL — juste des pages copiées au niveau de la couche de stockage.

VACUUM INTO pour obtenir une copie compactée

VACUUM INTO est un outil voisin, mais différent. Il écrit une copie reconstruite à neuf de la base dans un nouveau fichier :

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('première'), ('deuxième'), ('troisième');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'sauvegarde écrite' AS status;

Le résultat est la même base de données sur le plan logique, mais réécrite de zéro : chaque page est compactée au maximum, sans fragmentation ni pages libres laissées par des lignes supprimées. Du coup, le fichier de sauvegarde est aussi compact que possible.

Quand utiliser quoi :

• .backup — pour des sauvegardes régulières et fréquentes. Plus rapide, compatible avec des écritures concurrentes, et fidèle au bit près.
• VACUUM INTO — pour des snapshots périodiques quand vous voulez en plus un fichier propre et de taille minimale. Plus lent car tout est réécrit, et un verrou en écriture est posé sur la source pendant toute l'opération.

Les deux produisent un fichier .db valide, ouvrable immédiatement.

L'API de backup en ligne depuis le code applicatif

Dans une application, on ne lance pas sqlite3 en ligne de commande. On passe par l'API backup en ligne exposée par le driver. Avec le module sqlite3 de la stdlib Python, c'est Connection.backup :

import sqlite3

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

with dest:
    source.backup(dest)

source.close()
dest.close()

La méthode backup copie les pages de source vers dest pendant que les autres connexions continuent de tourner. Vous pouvez aussi passer pages= pour copier par morceaux, et progress= pour recevoir un callback — bien pratique sur les grosses bases quand vous voulez limiter la cadence ou afficher une progression.

La plupart des drivers dans d'autres langages exposent la même API C (sqlite3_backup_init, _step, _finish) sous un nom similaire. Le schéma reste toujours le même : ouvrir la source, ouvrir la destination, parcourir les pages, finaliser.

Sauvegarder une base SQLite en cours d'utilisation

C'est là que SQLite se montre vraiment malin, sans faire de bruit. Aussi bien .backup que l'API de backup en ligne sont pensés pour le hot backup — la base source peut rester ouverte et active pendant toute l'opération.

Concrètement, voici ce qui se passe :

1. La sauvegarde pose un verrou partagé et commence à copier les pages.
2. Si un writer modifie une page qui n'a pas encore été copiée, la sauvegarde le détecte et la relit.
3. La copie se termine une fois que toutes les pages sont cohérentes.

Pas besoin d'arrêter votre appli, de virer les connexions ou de planifier une fenêtre de maintenance. Sur une base très sollicitée, la sauvegarde peut demander quelques cycles supplémentaires pour converger, mais elle finit toujours par y arriver. Le fichier de destination obtenu représente un instantané cohérent à un instant T.

Un point à garder en tête : si vous utilisez le mode WAL, lance de temps en temps PRAGMA wal_checkpoint(TRUNCATE); pour éviter que le fichier WAL ne grossisse sans fin. La sauvegarde elle-même gère le WAL correctement — c'est juste de l'hygiène WAL classique.

Restaurer une base SQLite depuis une sauvegarde

Restaurer une base SQLite, c'est étonnamment ennuyeux — et c'est exactement le but. Le fichier de sauvegarde est une base de données. Pour t'en servir, il suffit de l'ouvrir :

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

Pour restaurer par-dessus une base active — par exemple après une perte de données — la procédure sûre est la suivante :

1. Arrêtez tous les processus qui ont la base ouverte.
2. Supprimez les fichiers app.db, app.db-wal et app.db-shm existants. Si des fichiers WAL/SHM de l'ancienne base traînent à côté du fichier principal restauré, SQLite va s'emmêler les pinceaux.
3. Mettez votre sauvegarde en place : cp backup.db app.db.
4. Relancez votre application.

Les fichiers -wal et -shm ne sont pas anodins. Si vous sautez l'étape 2, SQLite risque d'appliquer un WAL périmé sur le fichier principal fraîchement restauré, et vous vous retrouverez soit avec une base corrompue, soit avec des données bizarrement mélangées.

Depuis la CLI, il existe aussi une commande .restore, le pendant exact de .backup :

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

Cette opération écrase le contenu de la base actuellement connectée par celui de backup.db. En coulisses, c'est la même API de backup en ligne, mais utilisée dans le sens inverse.

.dump : un outil différent

Vous croiserez sûrement .dump dans de vieux tutos. Ce n'est pas une sauvegarde au sens strict du terme : la commande génère un fichier texte SQL composé d'instructions CREATE et INSERT :

sqlite3 app.db .dump > app.sql

Pour restaurer, il suffit de rejouer le SQL :

sqlite3 new.db < app.sql

C'est pratique pour migrer d'une version de SQLite à une autre, suivre l'évolution du schéma dans git, ou faire passer les données vers un autre moteur. Mais c'est plus lent, plus volumineux et plus approximatif que .backup (les collations personnalisées, les colonnes générées et certains pragmas peuvent demander une attention particulière). Pour une vraie sauvegarde sqlite d'une base en production, mieux vaut s'en tenir à .backup ou VACUUM INTO.

Une routine de sauvegarde sensée

Pour la plupart des applis, cette combinaison fonctionne très bien :

• Un .backup planifié — toutes les heures, tous les jours, selon la tolérance à la perte de données. C'est rapide, peu coûteux, et ça tourne à chaud.
• Un VACUUM INTO hebdomadaire vers un autre emplacement. Ça permet de détecter les dérives, d'obtenir un instantané compacté, et ça passe par un chemin de code différent.
• Une politique de rétention : on garde les N dernières sauvegardes quotidiennes, les M dernières hebdomadaires. Les bases SQLite se compressent bien, donc un petit gzip backup.db derrière vaut le coup.
• De temps en temps, restaurez-en une et lancez quelques requêtes dessus. Une sauvegarde jamais testée, c'est un espoir, pas une sauvegarde.

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

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

Les deux commandes peuvent être lancées sans crainte pendant que l'application continue de répondre aux requêtes.

Suite : les réglages PRAGMA

La sauvegarde, c'est un aspect du fonctionnement en production ; le réglage du comportement à l'exécution en est un autre. SQLite expose ses leviers via les instructions PRAGMA : mode de journalisation, niveau de synchronisation, taille du cache, vérification des clés étrangères. La page suivante passe en revue ceux qui méritent vraiment qu'on s'y attarde.