Sauvegarder SQLite en ligne sur un serveur dédié

Une sauvegarde annoncée comme réussie, mais dont la restauration ne contient pas les derniers enregistrements : ce type d’incident est particulièrement facile à mal diagnostiquer dans les services de fichiers qui utilisent SQLite en mode WAL. La réussite d’une copie de fichier prouve uniquement que les octets ont été transférés, pas que la base de données est cohérente. Sur un serveur dédié qui enregistre en continu des index de fichiers, des états de tâches ou des inventaires de sauvegarde, il faut distinguer deux étapes : produire une copie et démontrer qu’elle est exploitable.

1. Comprendre les limites du WAL et de la sauvegarde

En mode WAL, les modifications validées peuvent d’abord être conservées dans le fichier -wal, avant d’être reportées dans le fichier principal de la base lors d’un point de contrôle. Exécuter cp service.db backup.db pendant que le service fonctionne risque donc d’omettre ces modifications. Copier successivement le fichier principal, le WAL et le fichier de mémoire partagée ne garantit pas non plus un instantané correspondant à un même instant.

L’interface de sauvegarde en ligne lit un contenu cohérent par une connexion à la base, au lieu de copier aveuglément des fichiers en cours de modification. Elle convient aux services dont les écritures ne peuvent pas être interrompues, mais impose tout de même une charge de lecture et d’écriture vers la destination. Une sauvegarde hors ligne est plus simple lorsque toutes les connexions peuvent être fermées. L’arrêt des écritures doit toutefois englober les tâches en arrière-plan : bloquer uniquement les requêtes de premier plan ne suffit pas.

Le WAL n’est pas une sauvegarde hors site, et un miroir n’est pas non plus une sauvegarde. Une copie fiable doit être placée hors du périmètre de défaillance de la base source et faire l’objet d’une vérification par lecture indépendante.

Les serveurs dédiés OnuCloud disposent en standard de deux disques NVMe de 1To, chacun directement connecté, sans RAID matériel. Écrire la copie sur l’autre disque permet de séparer une partie des charges de lecture et d’écriture, mais les deux disques restent dans la même machine : cela ne remplace pas une conservation hors site.

2. Préparer un jeu d’essai reproductible avec des écritures continues

L’expérience suivante convient notamment à Debian 12 et aux environnements disposant de Python 3 et du module sqlite3 de sa bibliothèque standard. Commencez par vérifier le module, puis créez un répertoire temporaire distinct. Toutes les opérations portent uniquement sur les fichiers de test : elles ne touchent pas à la base de production et ne nécessitent aucune modification des services système.

python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
umask 077
export LAB_DIR="$(mktemp -d)"
printf '%s\n' "$LAB_DIR"

Le jeu d’essai comporte deux tables : events conserve les enregistrements détaillés et totals une valeur cumulée. Chaque transaction ajoute à la fois un enregistrement et une unité au cumul. La copie doit donc respecter la règle suivante : la somme des montants détaillés doit être égale à la valeur cumulée. Cette condition est plus probante que la simple présence d’un fichier et illustre la différence entre intégrité structurelle et validité métier.

Le script ci-dessous intègre un thread d’écriture et la procédure de sauvegarde. Il utilise le répertoire défini à l’étape précédente. Recréez ce répertoire à chaque nouvel essai pour éviter de mélanger les données avec celles d’un essai antérieur. Une base de production est généralement plus complexe : ses relations entre tables et ses exigences de version applicative doivent être définies séparément. Ce modèle de comptage ne peut pas y être transposé tel quel.

3. Produire et contrôler la copie avec l’interface de sauvegarde en ligne

Le script attend qu’une première transaction soit validée, puis effectue la sauvegarde en ligne pendant que le thread d’écriture poursuit son activité. La destination est d’abord un fichier temporaire. Celui-ci n’est renommé en copie de test définitive qu’après validation de l’intégrité, des clés étrangères et des invariants métier. Toute exception fait échouer le script, sans publier de nouvelle copie.

python3 - <<'PY'
import os
import sqlite3
import threading
import time
from pathlib import Path

base = Path(os.environ["LAB_DIR"])
source = base / "source.db"
pending = base / "backup.pending.db"
published = base / "backup.db"

db = sqlite3.connect(source)
mode = db.execute("PRAGMA journal_mode=WAL").fetchone()[0]
if mode.lower() != "wal":
    raise RuntimeError("WAL mode unavailable")
db.executescript("""
CREATE TABLE events (
    n INTEGER PRIMARY KEY,
    amount INTEGER NOT NULL
);
CREATE TABLE totals (
    id INTEGER PRIMARY KEY CHECK (id = 1),
    balance INTEGER NOT NULL
);
INSERT INTO totals VALUES (1, 0);
""")
db.close()

stop = threading.Event()
ready = threading.Event()
errors = []

def writer():
    conn = None
    try:
        conn = sqlite3.connect(source, timeout=5)
        while not stop.is_set():
            with conn:
                conn.execute("INSERT INTO events(amount) VALUES (1)")
                conn.execute(
                    "UPDATE totals SET balance = balance + 1 WHERE id = 1"
                )
            ready.set()
            time.sleep(0.01)
    except Exception as exc:
        errors.append(exc)
        ready.set()
    finally:
        if conn is not None:
            conn.close()

worker = threading.Thread(target=writer)
worker.start()
try:
    if not ready.wait(5):
        raise RuntimeError("Writer did not become ready")
    if errors:
        raise RuntimeError("Writer failed") from errors[0]
    src = sqlite3.connect(source, timeout=5)
    dst = sqlite3.connect(pending, timeout=5)
    try:
        src.backup(dst, pages=128, sleep=0.01)
    finally:
        dst.close()
        src.close()
finally:
    stop.set()
    worker.join()

if errors:
    raise RuntimeError("Writer failed") from errors[0]

check = sqlite3.connect(pending)
try:
    result = check.execute("PRAGMA integrity_check").fetchall()
    if result != [("ok",)]:
        raise RuntimeError("Integrity check failed")
    if check.execute("PRAGMA foreign_key_check").fetchall():
        raise RuntimeError("Foreign key check failed")
    count, amount = check.execute(
        "SELECT COUNT(*), COALESCE(SUM(amount), 0) FROM events"
    ).fetchone()
    balance = check.execute(
        "SELECT balance FROM totals WHERE id = 1"
    ).fetchone()[0]
    if not (count > 0 and count == amount == balance):
        raise RuntimeError("Business invariant failed")
finally:
    check.close()

os.replace(pending, published)
print(f"Verified rows: {count}")
print(published)
PY

pages=128 désigne le nombre de pages de la base traitées à chaque itération, et non un nombre d’octets. Le paramètre sleep sert principalement à espacer les nouvelles tentatives lorsque la base est occupée ; ce n’est pas un limiteur de débit fixe. En production, consignez la durée et les causes d’échec, et fixez une échéance pour l’ensemble de la tâche. Des écritures soutenues peuvent prolonger la sauvegarde : un dépassement de délai doit être considéré comme un échec, et non conduire à déclarer un fichier incomplet comme réussi.

Le renommage sert ici à distinguer la copie candidate de la copie validée ; il ne garantit pas la persistance des données en cas de coupure de courant. Un processus de sauvegarde de production doit également définir ses garanties de persistance en tenant compte de la synchronisation des fichiers, de celle des répertoires et de la sémantique du stockage cible.

4. Restaurer en isolation sans utiliser la source pour valider la copie

Une fois la sauvegarde terminée, la base source peut déjà avoir reçu de nouvelles écritures. Il ne faut donc pas exiger que le nombre de lignes de la copie soit égal à celui obtenu en interrogeant la source plus tard. Vérifiez d’abord la cohérence propre de la copie, puis évaluez sa fraîcheur à partir de la fenêtre de sauvegarde. Une sauvegarde en ligne n’est pas une réplication continue et ne permet pas automatiquement une restauration à n’importe quel instant.

Créer une copie de travail de restauration à partir de la copie définitive

Les connexions à la copie de test étant fermées, celle-ci peut être copiée hors ligne dans un autre répertoire. Le code suivant ouvre la copie de travail en lecture seule pour vérifier la structure des tables et les relations essentielles. Ne dirigez pas la connexion du test de restauration vers source.db : vous vérifieriez alors la base d’origine.

mkdir "$LAB_DIR/restore"
cp "$LAB_DIR/backup.db" "$LAB_DIR/restore/service.db"
python3 - <<'PY'
import os
import sqlite3
from pathlib import Path

path = Path(os.environ["LAB_DIR"]) / "restore" / "service.db"
conn = sqlite3.connect(path.as_uri() + "?mode=ro", uri=True)
try:
    print(conn.execute("PRAGMA integrity_check").fetchall())
    print(conn.execute("PRAGMA foreign_key_check").fetchall())
    print(conn.execute("""
        SELECT
            (SELECT COALESCE(SUM(amount), 0) FROM events),
            (SELECT balance FROM totals WHERE id = 1)
    """).fetchone())
finally:
    conn.close()
PY

Le test est concluant si le contrôle d’intégrité renvoie ok, si le contrôle des clés étrangères ne renvoie aucune ligne et si les deux valeurs cumulées sont égales. Le jeu d’essai ne comporte aucune clé étrangère : un résultat vide prouve donc seulement que la procédure de contrôle peut s’exécuter. Pour une application réelle, conservez les véritables contraintes afin de les vérifier.

La validation en production doit aussi inclure l’ouverture de la copie de travail avec une version compatible de l’application, puis la lecture des index de fichiers ou des listes de tâches. Désactivez les envois d’e-mails, les rappels applicatifs et les tâches automatiques. Pouvoir ouvrir la base ne signifie pas que les fichiers externes sont tous présents.

5. Faire de cet essai un critère d’acceptation des sauvegardes

Avant la mise en production, conservez au minimum quatre éléments de preuve : le chemin de la base source et la version de l’application, les heures de début et de fin de sauvegarde, les résultats des contrôles et les résultats de lecture en isolation. Les horaires servent à évaluer la fraîcheur des données, pas à désigner un point de coupure transactionnel précis. Les applications qui exigent un point de restauration clairement défini doivent prévoir séparément des numéros de séquence de validation et des objectifs de restauration.

Restreignez les droits d’accès au répertoire de sauvegarde pour éviter que les jetons, les données utilisateur ou les paramètres de tâches contenus dans la base ne soient exposés avec la copie. Après le transfert hors site, vérifiez à nouveau les sommes de contrôle des fichiers et répétez le test de restauration. Des sommes de contrôle identiques prouvent uniquement l’intégrité du transfert ; elles ne remplacent pas la validation de la base.

En cas d’échec, conservez la dernière copie validée et traitez séparément les fichiers candidats. Ne remplacez pas un résultat de sauvegarde réussi par un fichier vide. Si la base référence des pièces jointes, des fragments d’index ou d’autres bases de données, la sauvegarde en ligne d’une seule base n’assure pas l’atomicité entre ces ressources. Il faut alors coordonner l’ensemble en interrompant les écritures applicatives ou en utilisant un manifeste de versions.

Le critère final n’est pas « la commande de copie a réussi », mais « la copie existe indépendamment, les contrôles structurels sont concluants, les relations métier sont respectées et l’application peut la lire en isolation ». Ce n’est que lorsque ces quatre conditions sont réunies que la sauvegarde possède une capacité de restauration vérifiable.

Questions fréquentes