Wie man SQLite-Datenbanken schneller zwischen Computern kopiert

• Eine Erklärung für eine schnellere Methode, SQLite-Datenbanken zwischen Computern zu kopieren
• Indizes der Datenbank sind der Hauptgrund dafür, dass das Kopieren langsamer wird
• Mit dem SQLite-Befehl .dump kann die Datenbank in eine Textdatei exportiert werden
• Die Textdatei ist kleiner als die ursprüngliche Datenbank und wird durch Komprimierung noch kleiner
• Mit dieser Methode lassen sich große Datenbanken schneller und zuverlässiger kopieren

---

Wie man SQLite-Datenbanken schneller zwischen Computern kopiert

• Eine Erklärung, wie man eine auf einem Remote-Server gespeicherte SQLite-Datenbank auf einen lokalen Computer kopiert
• In frühen Projektphasen lässt sie sich einfach mit dem Befehl rsync kopieren
• Wenn die Datenbank wächst, wird das Kopieren langsamer und weniger zuverlässig

Einen Datenbank-Dump als Textdatei erstellen

• SQLite kann mit dem Befehl .dump eine Datenbank in eine Textdatei exportieren
• Diese Textdatei besteht aus SQL-Anweisungen und kann kleiner sein als die ursprüngliche Datenbank
• Indizes werden in der Textdatei auf einzelne Zeilen reduziert, wodurch Speicherplatz gespart werden kann

Speicherplatz durch Komprimierung sparen

• Die Textdatei wird durch Komprimierung noch kleiner
• Zum Beispiel wird aus einer ursprünglichen SQLite-Datenbank von 3,4 GB eine mit gzip komprimierte Textdatei von 240 MB
• Wenn man die komprimierte Textdatei herunterlädt, geht das Kopieren der Datenbank deutlich schneller

Neuer ssh+rsync-Befehl

• Auf dem Server wird eine mit gzip komprimierte Textdatei erzeugt, auf den lokalen Computer kopiert und anschließend die Datenbank wiederhergestellt
• Erzeugen der komprimierten Textdatei auf dem Server: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
• Kopieren der Datei auf den lokalen Computer: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
• Danach entpacken, die Datenbank wiederherstellen und die lokale Datei löschen

Datenbank-Dumps sind eine stabile Quelle zum Kopieren

• Wenn während des Kopierens Aktualisierungen stattfinden, kann rsync eine fehlerhafte Datenbankdatei erzeugen
• Durch das Erstellen eines Text-Dumps wird dieses Problem gelöst, weil damit eine stabile Quelle zum Kopieren bereitsteht
• Diese Methode spart bei großen Datenbanken Zeit und macht Downloads schneller und zuverlässiger

[GN⁺]

Hacker-News-Kommentare

• SQLite stellt ein offizielles Tool bereit. Es arbeitet auf Seitenebene: Die Replik sendet den kryptografischen Hash jeder Seite an die Quellseite, und die Quellseite sendet den vollständigen Inhalt der Seiten erneut, deren Hash nicht übereinstimmt
• Das Kopieren einer laufenden Datenbankdatei kann zu Beschädigungen führen. Für eine sichere Replikation kann Litestream verwendet werden
• Eine Methode, eine Datenbank zwischen Computern zu kopieren, besteht darin, den Kreis zu senden und den Rest zu ignorieren
  • Inkrementelles rsync ist schneller, aber ich stimme nicht der Behauptung zu, dass das Senden von SQL-Anweisungen schneller sei als das Senden der Datenbank. Man muss die SQL-Anweisungen ausführen sowie Optimierungs- und Vacuum-Arbeiten durchführen
  • Es gibt Szenarien, in denen man eine Datenbank aus CSV-Dateien „inkrementell neu aufbauen“ muss. Es ist zwar optimaler, die Datenbank von Grund auf neu zu erstellen, aber allein das Ausführen von Batch-Inserts in eine leere In-Memory-Datenbank dauert 30 Minuten
• Das kürzlich veröffentlichte Utility sqlite_rsync verwendet eine Version des rsync-Algorithmus, die für die interne Struktur von SQLite-Datenbanken optimiert ist. Es vergleicht interne Datenseiten effizient und synchronisiert nur geänderte oder fehlende Seiten
• Das Speichern als Textdatei ist ineffizient. Zum Speichern einer SQLite-Datenbank sollte VACUUM INTO verwendet werden
  • Der VACUUM-Befehl ist eine Alternative zur Backup-API; die resultierende Backup-Datenbank hat eine minimale Größe, wodurch Dateisystem-I/O reduziert wird
• Es ist überraschend, dass die von rsync bereitgestellte Komprimierungsfunktion nicht verwendet wurde. Eine Komprimierung mit gzip vor der Übertragung könnte schneller sein
• In DuckDB kann man nach Parquet exportieren, um die Datenmenge zu verringern. Übertragung und Laden sind schneller
• SQLite bietet die Session-Erweiterung, um Änderungen an Tabellen nachzuverfolgen und Changesets/Patchsets zu erzeugen, mit denen sich ältere Versionen einer SQLite-Datenbank patchen lassen
• Mit der Option --rsyncable von gzip kann weiter optimiert werden. Sie reduziert die Komprimierung leicht, lokalisiert aber Unterschiede, sodass nicht die gesamte komprimierte Ausgabe beeinflusst wird
  • Man kann die Komprimierung der Dump-Ausgabe überspringen und rsync die Unterschiede zwischen dem vorherigen unkomprimierten Dump und dem aktuellen Dump berechnen lassen; anschließend kann rsync das über das Netzwerk gesendete Changeset komprimieren
• Es gibt die Erfahrung aus dem Jahr 2008, mit Postgres Backups an mehrere Maschinen senden zu müssen. Das Netzwerk war ausgelastet, daher wurde udpcast verwendet, um das Backup auf einmal an alle Ziele zu senden