コンピュータ間でSQLiteデータベースをより高速にコピーする方法

はじめに

• SQLite データベースを rsync で直接コピーすると、インデックスなどの影響でファイルサイズが大きくなり、低速かつ不安定になる。
• そのため、.dump を使ってテキストベースで圧縮・復元する方法が提案されている。

本論

• .dump コマンドは DB 全体を SQL テキストとして出力し、インデックスはコマンド1行に置き換えられるため、ファイルサイズが小さくなる。

  sqlite3 my_database.db .dump > my_database.db.txt  
  

• テキストファイルは gzip で圧縮すると、さらに容量を削減できる。

  sqlite3 my_database.db .dump | gzip -c > my_database.db.txt.gz  
  

• サーバーで圧縮版を生成し、ローカルにコピーしてから復元する手順に切り替える。

  ssh username@server "sqlite3 db.db .dump | gzip -c > db.txt.gz"  
  rsync --progress username@server:db.txt.gz .  
  gunzip db.txt.gz  
  cat db.txt | sqlite3 restored.db  
  

• 元の DB ファイル 3.4GB → ダンプテキスト 1.3GB → gzip 圧縮版 240MB となり、約14分の1まで削減。

• 従来の rsync 方式では、転送中に DB が変更されると database disk image is malformed エラーが発生する可能性がある。

• テキストダンプ方式では、コピー開始後に内容が変わるリスクがないため、一貫したバックアップが可能。

結論

• .dump + 圧縮 + 復元方式は、大容量 SQLite 転送時の速度と安定性の両方を改善する。
• 特にインデックスの多い DB に効果的で、転送失敗や破損の可能性を減らせる。
• 大容量の SQLite を頻繁に扱うなら、導入する価値のある実用的な最適化手法である。

[ng0301]

なぜこのような作業が必要なのか、背景が気になりますね。

[winterjung]

原文ではバックアップ＆分析用とのことですね。おそらくローカルで duckdb のようなもので分析したかったのではないかと思います。