SQLiteのバックアップと復元 - .backupとVACUUM INTO

なぜ cp でコピーするだけではダメなのか

SQLite のデータベースはファイル 1 つで完結しているので、ついつい普通のファイルコピーでバックアップを取りたくなります。うまくいくこともありますが、失敗するケースも少なくありません。

落とし穴は主に 2 つ。

• コピーの最中に別のコネクションが書き込みを行っていると、コピー先のファイルに中途半端なトランザクションが混ざり、開いた瞬間に壊れていることがあります。
• データベースが WAL モード（最近のアプリではほぼデフォルト）で動いている場合、直近の変更は database.db-wal という別ファイルに溜まっています。メインのファイルだけコピーすると、気づかないうちにデータを取りこぼします。

こうした問題を避けるために、SQLite には専用のバックアップ機能が用意されています。ロックや WAL の内容、書き込みの並行処理まで面倒を見てくれるので、cp ではなくこちらを使いましょう。

.backup コマンドを使う

CLI から SQLite のバックアップを取る一番手軽な方法が、ドットコマンドの .backup です。

sqlite3 app.db
sqlite> .backup backup.db
sqlite> .quit

このコマンドで app.db の内容がまるごと backup.db にコピーされます。他のプロセスがデータベースを読み書きしている最中でも問題なく動作します。バックアップAPIは1つの大きなロックではなく細かいロックを順番に取得しながら、ページ単位で少しずつコピーし、コピー中に変更されたページがあれば再試行する仕組みになっているからです。

出力されるのはそのまま使えるSQLiteデータベースファイルです。普通のDBと同じように開けます。

sqlite3 backup.db
sqlite> .tables

シェルから一発でまるごと実行することもできます。cron に仕込むときはたいていこの形に落ち着きます。

sqlite3 app.db ".backup '/var/backups/app-$(date +%Y%m%d).db'"

ファイルを 1 つ入れて、ファイルを 1 つ出すだけ。ダンプ → リストアの往復もなければ、SQL のパース処理も走りません。あくまでストレージ層でページをそのままコピーするイメージです。

VACUUM INTO でコンパクト化されたコピーを作る

VACUUM INTO は、似ているようで役割の違うコマンドです。データベースを新しくきれいに組み直したコピーを、別ファイルとして書き出してくれます:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);
INSERT INTO notes (body) VALUES ('1番目'), ('2番目'), ('3番目');
DELETE FROM notes WHERE id = 2;

VACUUM INTO 'notes-backup.db';

SELECT 'バックアップを書き込みました' AS status;

結果として得られるのは論理的には同じデータベースですが、まっさらな状態から書き直されたものです。各ページはぎっしり詰まっていて、断片化もなく、削除された行が残した空きページもありません。そのぶんバックアップファイルは可能な限り小さくなります。

どちらを使うべきか:

• .backup — 定期的・頻繁なバックアップ向け。高速で、書き込み中の他プロセスとも干渉せず、バイト単位で忠実にコピーされます。
• VACUUM INTO — 定期的なスナップショットを取りつつ、ファイルサイズも最小限にきれいにまとめたい場合に。全体を書き直すぶん遅く、処理中はソース側に書き込みロックがかかります。

どちらの方法でも、すぐに開ける有効な .db ファイルが生成されます。

アプリケーションコードから使う sqlite オンラインバックアップ API

アプリケーションの中では、わざわざ sqlite3 コマンドをシェル経由で呼び出したりはしません。各ドライバが公開しているオンラインバックアップ API を使います。Python 標準ライブラリの sqlite3 モジュールなら、Connection.backup がそれにあたります:

import sqlite3

source = sqlite3.connect("app.db")
dest = sqlite3.connect("backup.db")

with dest:
    source.backup(dest)

source.close()
dest.close()

backup メソッドは、他の接続が動いている最中でも source から dest へページをコピーしてくれます。pages= を指定すればチャンク単位でコピーでき、progress= を渡せばコールバックを受け取れます。大きなデータベースでコピー速度を抑えたいときや、進捗表示をしたいときに便利です。

他言語のドライバの多くも、同じ C API（sqlite3_backup_init、_step、_finish）を似たような名前で公開しています。流れはどれも同じで、ソースを開く、コピー先を開く、ページを step で進める、finish で締める、という形です。

稼働中のデータベースをバックアップする（sqlite ホットバックアップ）

ここが SQLite のさりげなく光るところです。.backup コマンドもオンラインバックアップ API も、ホット バックアップ前提で設計されています。つまり、ソース側のデータベースは開いたまま、アクセスされたままで構いません。

実際の動きはこうです。

1. バックアップは共有ロックを取得してページのコピーを開始します。
2. まだコピーされていないページをライターが書き換えた場合、バックアップ側がそれを検知して読み直します。
3. すべてのページが整合した時点でコピーが完了します。

アプリを止めたり、接続を切ったり、メンテ時間を確保したりする必要はありません。書き込みの多いデータベースだと収束までに少し余分にサイクルがかかることはありますが、ちゃんと収束します。出来上がるコピー先ファイルは、ある瞬間のスナップショットとして整合した状態になっています。

ひとつだけ注意。WAL モードを使っているなら、ときどき PRAGMA wal_checkpoint(TRUNCATE); を実行して WAL ファイルが無限に膨らまないようにしておきましょう。バックアップ自体は WAL を正しく扱ってくれるので、これはあくまで WAL を運用するうえでの一般的なお作法です。

バックアップから復元する（sqlite restore）

SQLite データベースの復元は驚くほど地味で、それが狙いです。バックアップファイルそのものが、もう立派なデータベースなのです。使うには、ただ開くだけ。

sqlite3 backup.db
sqlite> SELECT COUNT(*) FROM notes;

稼働中のデータベースに 上書き して復元する場合、たとえばデータ消失からのリカバリでは、次の手順を守るのが安全です。

1. 対象のデータベースを開いているプロセスをすべて停止する。
2. 既存の app.db、app.db-wal、app.db-shm を削除する。古いデータベースの WAL/SHM ファイルが残ったままだと、復元したメインファイルとの組み合わせで SQLite が混乱します。
3. バックアップを所定の場所にコピーする: cp backup.db app.db。
4. アプリケーションを再起動する。

ここで -wal と -shm ファイルの扱いが重要です。手順 2 を飛ばすと、SQLite が古い WAL を復元後のメインファイルに適用しようとして、データが破損したり、新旧のデータが妙に混ざった状態になったりします。

CLI の中からは、.backup の対になる .restore コマンドも使えます。

sqlite3 app.db
sqlite> .restore backup.db
sqlite> .quit

これを実行すると、接続中のデータベースの中身が backup.db の内容で上書きされます。仕組みとしては、先ほどと同じオンラインバックアップ API を逆方向に使っているだけです。

.dump は別物のツール

古いチュートリアルだと .dump を紹介していることがありますが、これは今まで説明してきた意味でのバックアップとは違います。CREATE 文と INSERT 文からなる SQL のテキストファイルを吐き出すコマンドです：

sqlite3 app.db .dump > app.sql

復元するには、保存しておいたSQLを流し込みます:

sqlite3 new.db < app.sql

これは SQLite のバージョン間で移行したり、Git でスキーマの差分を取ったり、別の DB エンジンへデータを移したいときに便利です。ただし .backup に比べると遅く、ファイルも大きく、情報も一部欠けます(カスタムコレーションや生成列、一部の pragma は別途対応が必要)。実運用中のデータベースを実際にバックアップしたいなら、.backup か VACUUM INTO を選ぶのが無難です。

現実的なバックアップ運用

ほとんどのアプリでは、次の組み合わせがうまく機能します。

• 定期的な .backup の実行 — 1 時間ごと、1 日ごとなど、許容できるデータロスに合わせて。軽量・高速で、稼働中のままホットバックアップが取れます。
• 週次で VACUUM INTO を別パスに出力。ドリフトを検出でき、コンパクト化されたスナップショットも手に入り、別のコードパスを通すことにもなります。
• 保持ポリシーを決める。日次バックアップは直近 N 個、週次は直近 M 個といった具合に。SQLite の DB ファイルは圧縮がよく効くので、後から gzip backup.db しておく価値は十分あります。
• たまに 1 つを実際にリストアして、いくつかクエリを投げてみる。検証していないバックアップは「祈り」であって、バックアップではありません。

# 毎日、cronで実行:
sqlite3 /var/lib/app/app.db ".backup '/var/backups/app-$(date +%F).db'"
gzip "/var/backups/app-$(date +%F).db"

# 毎週:
sqlite3 /var/lib/app/app.db "VACUUM INTO '/var/backups/app-weekly-$(date +%F).db'"

どちらのコマンドも、アプリがリクエストを処理している最中でも安全に実行できます。

次は: PRAGMA の設定

バックアップは運用面の話のひとつにすぎません。もうひとつ大事なのが、ランタイムの挙動をチューニングすることです。SQLite では PRAGMA 文を通じて各種の設定を操作できます。ジャーナルモード、synchronous レベル、キャッシュサイズ、外部キー制約の有効化などですね。次のページでは、押さえておきたい主要な PRAGMA を順に見ていきます。