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

• Lorsqu’on transfère une grande base de données SQLite d’un serveur distant vers une machine locale, copier un dump SQL compressé avec gzip plutôt que le fichier .db d’origine peut réduire fortement le volume à transférer
• La principale cause de l’augmentation de la taille du fichier est liée aux index, qui dupliquent des données de table sur disque pour accélérer les requêtes
• Le .dump de SQLite convertit la base en texte composé d’instructions SQL, et les index y sont représentés non par leurs données réelles mais par une seule ligne CREATE INDEX, ce qui évite de transmettre des doublons
• Dans l’exemple, la base d’origine faisait 3,4 Go, le dump texte 1,3 Go, et la version compressée avec gzip 240 Mo ; le dump compressé était donc 14 fois plus petit que l’original
• Le fait de figer le fichier de dump avant la copie réduit aussi les cas où la base source change pendant le transfert et provoque l’erreur database disk image is malformed

---

Compresser un dump SQLite avant de le copier

• Pour une petite base SQLite, une copie directe avec rsync suffit souvent
  • Exemple : rsync --progress username@server:my_remote_database.db my_local_database.db
• Quand la base devient volumineuse, la copie directe peut devenir lente et peu fiable
  • Télécharger une base de 250 Mo depuis un serveur web sur une connexion internet domestique prend environ une minute
  • Pour des bases de plusieurs Go, l’attente augmente encore
• Le principal goulot d’étranglement de la taille vient des index
  • Les index accélèrent fortement les requêtes, mais ils font aussi grossir le fichier de base
  • Dans une base, un index précis occupait à lui seul la moitié de l’espace disque
  • Plutôt que de contenir des données uniques, les index dupliquent des données d’autres tables pour accélérer les recherches
• SQLite permet de dump l’intégralité de la base dans un fichier texte
  • Commande : sqlite3 my_database.db .dump > my_database.db.txt
  • Le fichier obtenu est composé d’instructions SQL comme CREATE TABLE, INSERT INTO et CREATE INDEX
  • Les index y sont enregistrés non sous forme de données d’index réelles, mais via une commande de création comme CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);
• Il est possible de reconstruire une base SQLite à partir du fichier de dump
  • Commande : cat my_database.db.txt | sqlite3 my_reconstructed_database.db
• Les dumps SQL contiennent beaucoup de répétitions, ce qui les rend très efficaces à compresser avec gzip
  • Exemple : sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
  • Comparaison des tailles :
    • Base SQLite d’origine : 3,4 Go
    • Dump texte : 1,3 Go
    • Texte compressé avec gzip : 240 Mo
  • La version compressée avec gzip était 14 fois plus petite que la base SQLite d’origine

Procédure concrète et gain de fiabilité

• La nouvelle méthode consiste à générer un dump compressé sur le serveur distant, à ne télécharger que ce fichier, puis à reconstruire la base en local
• La procédure de copie est la suivante
  • Générer sur le serveur un fichier texte compressé avec gzip : ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
  • Le copier en local : rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
  • Supprimer le fichier compressé temporaire sur le serveur : ssh username@server "rm my_remote_database.db.txt.gz"
  • Décompresser en local : gunzip my_local_database.db.txt.gz
  • Reconstruire la base locale : cat my_local_database.db.txt | sqlite3 my_local_database.db
  • Supprimer le fichier texte local : rm my_local_database.db.txt
• Le dump compressé sert aussi de source de copie cohérente
  • Si la base source est mise à jour pendant la longue copie d’un gros fichier, le début du fichier copié peut refléter l’état avant mise à jour et la fin l’état après mise à jour
  • Un tel fichier peut produire l’erreur database disk image is malformed à l’ouverture en local
• En créant le dump texte avant la copie, le fichier transmis par rsync ne change plus en cours de transfert
  • On a donc davantage de chances de copier un fichier texte complet et cohérent
• Si vous déplacez souvent de grandes bases SQLite, cette méthode peut réduire le temps de téléchargement et améliorer la fiabilité de la copie

[GN⁺]

Commentaires sur Hacker News

• Si on copie tel quel un fichier de base de données en cours d’exécution et recevant des mises à jour, il est évidemment voué à être corrompu.
  Pour répliquer en toute sécurité une base SQLite, il existe Litestream : https://github.com/benbjohnson/litestream

  • Litestream fonctionne même par-dessus un simple SFTP, donc on peut diffuser la réplication d’une base vers presque n’importe quel endpoint UNIX accessible en SSH.
    J’ai un service préféré, mais n’importe quel serveur SFTP peut faire l’affaire.
    [1] https://github.com/benbjohnson/litestream/issues/140
    [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
  • Dire qu’elle sera « forcément » corrompue admet des exceptions. Si on copie atomiquement un fichier ou un sous-volume sur Btrfs ou ZFS, avec une base ACID ou un arbre LSM, le pire scénario est simplement un rollback.
    Bien sûr, s’il y a plusieurs fichiers, il faut les regrouper dans un sous-volume pour qu’ils soient tous copiés dans la même transaction, et un simple cp --reflink=always ne suffit pas.
    Arrêter le processus avec SIGSTOP pourrait aussi donner un résultat similaire, mais je n’aimerais pas compter là-dessus.
  • La commande intégrée .backup est aussi l’outil officiel prévu pour créer une version snapshot d’une base en cours d’exécution afin de pouvoir la copier.
  • J’utilise Litestream avec succès sur mon système, mais j’aime aussi ce document qui explique assez en détail comment faire manuellement quelque chose de similaire avec uniquement les outils intégrés : https://litestream.io/alternatives/cron/
  • Litestream est vraiment génial. Je compte l’utiliser au niveau conteneur pour la sauvegarde et la restauration SQLite, comme l’a fait cet ancien de Google qui a monté une petite startup KVM après l’inondation de son entrepôt pendant ses vacances.
    Si je me souviens bien, il avait écrit un guide parfait, mais mes chances de le retrouver sont proches de zéro. Si quelqu’un voit de quelle référence il s’agit, ce serait bien de poster le lien.

• On dirait un article sur la copie de bases de données entre ordinateurs qui dessine juste un cercle en omettant le reste du hibou.
  Comme d’autres l’ont dit, un rsync incrémental serait bien plus rapide, mais ce qui me gêne encore plus, c’est qu’on dit qu’envoyer des requêtes SQL est plus rapide qu’envoyer la base, tout en oubliant complètement le fait qu’il faut exécuter ces requêtes SQL. Ensuite, il faut lancer /optimize/ puis /vacuum/.
  Je suis justement dans une situation où je dois « reconstruire incrémentalement » une base à partir de fichiers CSV, et dans mon cas il est plus optimal de tout recréer depuis zéro. Même après beaucoup d’optimisation, il faut encore 30 minutes rien que pour faire les insertions en lot dans une base vide en mémoire et créer les index.

  • J’espère que tu as vu https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite.
    C’est un très bon résumé des façons d’accélérer les insertions dans SQLite3.
  • Quelle que soit l’optimisation, ici l’important est de savoir où se trouve le goulot d’étranglement. Dans le cas de l’article, c’est la bande passante qui limite, et le CPU / l’I/O disque semblent suffisants.
    Il disait qu’il fallait 1 minute pour télécharger une base de 250 Mo ; moi, grâce à une fibre 1 Gbps, j’ai récupéré en 15 secondes une base de test SQLite de 2 Go depuis les serveurs de l’entreprise.
  • 30 minutes, ça semble long. Il y a énormément de données ? Je travaille sur l’initialisation d’une base SQLite à partir de beaucoup de données JSON, et j’ai trouvé un assez bon sweet spot de performance en accumulant les valeurs dans une liste puis en les insérant par paquets de 10 000, ce qui permet de charger plusieurs millions de lignes en quelques minutes.
    J’ai dû utiliser des astuces comme des filtres de Bloom et un cache LRU, mais je peux désormais construire une base de 6 Go en environ 20 minutes.

• SQLite a un outil officiel pour cet usage : https://www.sqlite.org/rsync.html
  Il fonctionne au niveau des pages.
  « Le protocole consiste à faire envoyer par la réplique le hash cryptographique de chaque page à la source, qui renvoie alors le contenu complet des pages dont le hash ne correspond pas. »

  • Oui, mais malheureusement l’équipe SQLite n’inclut pas cet outil dans le tarball autotools utilisé par la plupart des distributions et par brew pour empaqueter SQLite.
    Pour l’utiliser, il n’y a pas d’autre choix que de le compiler soi-même.

• L’utilitaire sqlite_rsync publié 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 puis ne synchronise que les pages modifiées ou absentes.
  Les astuces de l’article sont bonnes, mais maintenant il est plus simple d’utiliser l’utilitaire intégré :)
  Le fonctionnement est détaillé sur ce blog : https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

  • sqlite3_rsync est désormais intégré à la plateforme rsync.net.
    ssh [e-mail] sqlite3_rsync … blah blah …
    Il a été ajouté la semaine dernière, donc il n’est pas encore déployé dans toutes les régions, mais les premiers utilisateurs disent tous qu’il fonctionne exactement comme prévu.
  • sqlite_rsync ne peut être utilisé qu’en mode WAL. Et avec cette contrainte supplémentaire du mode WAL, le fichier de base doit se trouver sur un disque local.
    Pour la plupart des gens, c’est évidemment le cas, mais si ce n’est pas possible, on ne peut pas utiliser cet utilitaire.
  • Si on synchronise souvent, il faut augmenter la taille des pages à cause de la bande passante.

• Il est surprenant que la compression en transit fournie par rsync n’ait pas été essayée
  -z, --compress compresse les données du fichier pendant le transfert, et --compress-level=NUM permet de définir explicitement le niveau de compression
  Il est possible qu’il soit plus rapide de compresser avec gzip puis de transférer, mais c’est appréciable de pouvoir améliorer le transfert avec un simple flag

  • Si le problème de corruption a été mentionné, il vaudrait mieux utiliser sqlite3_rsync(https://sqlite.org/rsync.html) avec -z
    On obtient ainsi un rsync conscient des transactions SQLite et du WAL, avec en plus la compression en transit
  • L’idée clé est de sauter les index, et cela doit se faire avant la compression
    Pour ce genre de tâche, je streame directement le dump vers gzip. On peut souvent aussi trouver un moyen de le streamer directement vers la destination sans fichier intermédiaire
    En plus, cela permet de le stocker compressé côté destination. C’est particulièrement vrai si l’objectif est la sauvegarde plutôt qu’une réplication du pauvre
  • À mon avis, la compression n’est avantageuse que sur les réseaux lents
  • C’est indispensable. Utiliser rsync sur un fichier compressé contourne complètement le principal intérêt de rsync, à savoir le transfert différentiel basé sur rolling checksum
  • On aurait aussi pu utiliser --remove-source-files pour éviter de se reconnecter en SSH juste pour faire rm

• Le stockage sous forme de fichier texte est inefficace. Moi, je sauvegarde les bases SQLite avec VACUUM INTO
sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
  D’après https://sqlite.org/lang_vacuum.html, la commande VACUUM avec clause INTO est une alternative à l’API de sauvegarde qui crée une copie de sauvegarde de la base en cours d’utilisation. L’avantage est que la taille de la base de sauvegarde résultante est minimisée, ce qui peut réduire les E/S du système de fichiers

  • C’est élégant, mais cela ne résout pas le problème des index mentionné dans l’article. L’idée essentielle était d’éviter d’envoyer les données d’index sur un lien lent, or l’approche VACUUM INTO conserve les index
    Les fichiers texte peuvent être inefficaces tels quels, mais ils se compressent bien même avec des outils rudimentaires comme gzip. Je ne sais pas si le format binaire de SQLite se compresse aussi bien, mais c’est possible
  • Cette méthode préserve bien les index ? L’article disait que si le fichier SQLite était volumineux, c’était à cause des index

• Avec DuckDB, on peut faire la même chose en exportant en Parquet, ce qui donne des données d’une taille inférieure d’un facteur à un chiffre par rapport à des instructions SQL textuelles. Le transfert est plus rapide, et le chargement aussi
  https://duckdb.org/docs/stable/sql/statements/export.html

  • En ligne de commande, on peut faire comme ceci
    duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
    Sur ma base de test, c’était environ 20 % plus petit qu’un SQL texte compressé avec gzip

• SQLite dispose d’une extension de session capable de suivre les changements d’un ensemble de tables et de produire un changeset/patchset applicable à une version antérieure d’une base SQLite
  https://www.sqlite.org/sessionintro.html

  • Je n’ai encore vu aucun binding SQLite qui la prenne en charge. Donc, à moins d’écrire l’application en C ou de prévoir de patcher le binding de son langage, l’intérêt reste assez limité
    Sur l’un de mes projets, j’ai implémenté moi-même une sorte de session du pauvre en enregistrant toutes les requêtes SQL et leurs paramètres dans une base séparée, puis en synchronisant et rejouant cela. Pour une base d’environ 30 Go dont seulement ~0,1 % change chaque jour, cela fonctionne très bien
  • Quelqu’un l’a déjà utilisée ? J’ai lu la documentation, mais je n’ai jamais entendu parler de quelqu’un qui emploie réellement cette extension
  • Ce serait vraiment bien que SQLite ait une méthode native pour faire de la capture de données modifiées via des sessions ou un mécanisme similaire

• Si l’on synchronise régulièrement d’une ancienne version vers une plus récente, on peut encore optimiser avec l’option --rsyncable de gzip
  Le taux de compression baisse d’environ 1 %, mais les différences d’une version à l’autre ne se propagent pas à l’ensemble de la sortie compressée et restent localisées
  Une autre méthode consiste à ne pas compresser la sortie du dump, à laisser rsync calculer la différence entre l’ancien dump non compressé et le dump actuel, puis à ne compresser sur le réseau que l’ensemble des changements à envoyer. Autrement dit, il suffit d’utiliser rsync -z

• On dirait que tout cela pourrait être traité dans un seul pipeline
  ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

  • Si l’on active la compression SSH avec -oCompression=on ou -C dans l’appel SSH, gzip/gunzip peut être redondant
  • C’était aussi ma première idée. Et il paraît assez évident qu’il faudrait y ajouter pv