Comment copier plus rapidement des bases de données SQLite entre ordinateurs

• Présentation d’une méthode plus rapide pour copier une base de données SQLite entre ordinateurs
• Les index sont la principale cause du ralentissement de la copie de la base de données
• SQLite permet de vider une base dans un fichier texte avec la commande .dump
• Le fichier texte est plus petit que la base d’origine, et encore plus petit une fois compressé
• Cette méthode permet de copier de grosses bases de données plus vite et de manière plus fiable

---

Comment copier plus rapidement une base de données SQLite entre ordinateurs

• Explication d’une méthode pour copier une base de données SQLite stockée sur un serveur distant vers un ordinateur local
• Au début d’un projet, il est possible de la copier simplement avec la commande rsync
• Quand la base de données grossit, la copie devient plus lente et moins fiable

Créer un dump de la base de données dans un fichier texte

• SQLite permet de vider une base de données dans un fichier texte avec la commande .dump
• Ce fichier texte est composé d’instructions SQL et peut être plus petit que la base d’origine
• Les index sont réduits à une seule ligne dans le fichier texte, ce qui permet d’économiser de l’espace de stockage

Réduire l’espace utilisé grâce à la compression

• Le fichier texte devient encore plus petit une fois compressé
• Par exemple, si la base de données SQLite d’origine fait 3,4 Go, le fichier texte compressé avec gzip peut descendre à 240 Mo
• Télécharger ce fichier texte compressé permet d’accélérer nettement la copie de la base de données

Nouvelle commande ssh+rsync

• Création sur le serveur d’un fichier texte compressé avec gzip, copie vers l’ordinateur local, puis reconstruction de la base de données
• Générer sur le serveur le fichier texte compressé : ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• Copier le fichier vers l’ordinateur local : rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• Décompresser, reconstruire la base de données, puis supprimer le fichier local

Le dump de la base de données constitue une source de copie fiable

• Si des mises à jour surviennent pendant la copie de la base, rsync peut produire un fichier de base de données incorrect
• Générer un dump texte fournit une source de copie stable et permet d’éviter ce problème
• Cette méthode fait gagner du temps lors du travail sur de grosses bases de données et rend les téléchargements plus rapides et plus fiables

[GN⁺]

Commentaires Hacker News

• SQLite fournit un outil officiel. Il fonctionne au niveau des pages : la réplique envoie au côté source le hachage cryptographique de chaque page, puis la source renvoie le contenu complet des pages dont le hachage ne correspond pas
• Copier un fichier de base de données en cours d’utilisation peut le corrompre. On peut utiliser Litestream pour une réplication sûre
• Parmi les façons de copier une base de données entre ordinateurs, il y a l’idée d’envoyer le cercle et d’ignorer le reste
  • Le rsync incrémental est plus rapide, mais je ne suis pas d’accord avec l’affirmation selon laquelle envoyer des instructions SQL serait plus rapide qu’envoyer la base de données. Il faut exécuter les instructions SQL, puis effectuer l’optimisation et le vacuum
  • Il existe des scénarios où il faut « reconstruire incrémentalement » une base de données à partir d’un fichier CSV. Recréer la base depuis zéro est plus optimal, mais exécuter uniquement des insertions par lots dans une base vide en mémoire prend déjà 30 minutes
• L’utilitaire sqlite_rsync, lancé récemment, utilise une version de l’algorithme rsync optimisée pour la structure interne des bases SQLite. Il compare efficacement les pages de données internes et ne synchronise que les pages modifiées ou manquantes
• Stocker sous forme de fichier texte est inefficace. Utiliser VACUUM INTO pour enregistrer une base de données sqlite
  • La commande VACUUM est une alternative à l’API de sauvegarde, et la base de données de sauvegarde obtenue a une taille minimale, ce qui réduit les E/S du système de fichiers
• Il est surprenant de ne pas avoir utilisé la compression fournie par rsync. Compresser avec gzip avant le transfert pourrait être plus rapide
• Avec DuckDB, on peut exporter en Parquet pour réduire la taille des données. Le transfert et le chargement sont plus rapides
• SQLite propose une extension de session qui suit les changements des tables et génère des changesets/patchsets permettant de corriger une ancienne version d’une base de données SQLite
• On peut optimiser avec l’option --rsyncable de gzip. Elle réduit légèrement la compression, mais localise les différences afin qu’elles n’affectent pas toute la sortie compressée
  • On peut éviter de compresser la sortie du dump, laisser rsync calculer les différences entre l’ancien dump non compressé et le dump actuel, puis laisser rsync compresser le changeset transmis sur le réseau
• En 2008, quelqu’un a dû envoyer des sauvegardes sur plusieurs machines avec Postgres. Le réseau était saturé, donc il a utilisé udpcast pour transmettre la sauvegarde à toutes les destinations en une seule fois