SQLiteの運用 — バックアップ・VACUUM・PRAGMA設定

SQLiteの運用は、他のデータベースに比べて驚くほど単純です。監視すべきプロセスはなく、バージョンアップの調整も要らず、バックアップは1つのファイルを保存するだけで済みます。

ただし「ファイルをコピーするだけ」が成立するのは、アプリが動いていないときに限られます。稼働中のデータベースを安全に扱うには、専用の手段を使う必要があります。

学習者

1ファイルなんだから、cp でコピーすればバックアップ完了だと思っていました。

先生

止まっているならそれで正しい。動いている最中だと、書き込みの途中の状態をコピーしてしまうことがあるんだ。

なぜ cp が危険なのか

cp はファイルを先頭から順に読みます。読んでいる最中にアプリが別の場所を書き換えると、コピー先には「前半は書き換え前、後半は書き換え後」という、どの時点にも存在しなかった状態が残ります。

さらに 第6章で見たWALモードでは、最新の変更が -wal ファイル側にあります。本体ファイルだけをコピーすると、直近のコミットがまるごと失われます。

VACUUM INTO — 一貫したスナップショットを作る

もっとも手軽で確実な方法が VACUUM INTO です。SQLite 3.27.0 以降で使えます。

構文： VACUUM INTO 'コピー先のファイルパス'

VACUUM INTO '/backup/app-2026-08-15.db';

この命令は、実行時点の一貫した状態を新しいファイルへ書き出します。アプリが動いたままでも安全で、しかも書き出しの過程で断片化が解消されるため、元より小さいファイルになります。

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

シェルから1行で実行できるため、cronに登録するだけで日次バックアップが完成します。

sqlite3 コマンドの .backup も同様に安全な方法です。こちらはWALファイルも含めて整合性を保ちます。

sqlite> .backup /backup/app.db

VACUUM — ファイルを整理する

VACUUM（INTO なし）は、データベースファイルそのものを再構築します。

VACUUM;

大量の DELETE を実行しても、SQLiteのファイルサイズは自動では小さくなりません。空いた領域は「フリーリスト」として再利用のために保持されるだけです。VACUUM はこれを解放し、ファイルを実際に縮めます。

どのくらい無駄があるかは、次のクエリで確認できます。

SELECT
  page_count * page_size / 1024 / 1024 AS total_mb,
  freelist_count * page_size / 1024 / 1024 AS free_mb
FROM pragma_page_count(), pragma_page_size(), pragma_freelist_count();

自動で少しずつ整理させたい場合は auto_vacuum を使います。

PRAGMA auto_vacuum = INCREMENTAL;

INCREMENTAL を選んだ場合、実際の解放は次の命令で行います。

PRAGMA incremental_vacuum(100);  -- 100ページ分だけ解放

破損していないか確認する

SQLiteのファイルが壊れることは稀ですが、第6章で触れたネットワークファイルシステムでの利用や、ディスク障害では起こり得ます。

PRAGMA integrity_check;

問題がなければ ok の1行が返ります。全ページを検証するため、大きなデータベースでは時間がかかります。

PRAGMA quick_check;

こちらは検証項目を絞った簡易版で、はるかに高速です。定期チェックには quick_check、異常を疑ったときには integrity_check という使い分けが実用的です。

外部キーの整合性は別の命令です。第4章で見た通り、外部キーが無効な状態で投入されたデータには違反が残っている可能性があります。

PRAGMA foreign_key_check;

実務で設定しておきたいPRAGMA

ここまでの章で登場した設定をまとめます。ファイルに保存されるものと接続ごとに必要なものを区別するのが重要です。

-- 初回のみ（ファイルに保存される）
PRAGMA journal_mode = WAL;
 
-- 接続ごとに実行する
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -20000;
PRAGMA temp_store = MEMORY;

接続を閉じる直前には、第7章で触れた統計情報の更新を入れておきます。

PRAGMA optimize;

WALファイルの管理

第6章で触れた通り、WALファイルは放置すると肥大化することがあります。上限を設けておくと安心です。

PRAGMA journal_size_limit = 67108864;  -- 64MB

これは、チェックポイント後にWALファイルをこのサイズまで切り詰めるという設定です。手動で完全に切り詰めたい場合は次を実行します。

PRAGMA wal_checkpoint(TRUNCATE);

バックアップ戦略を決める

用途によって、必要な保護のレベルは変わります。

#!/bin/sh
# 日次バックアップの例
set -eu
DEST="/backup/app-$(date +%Y%m%d).db"
sqlite3 /var/lib/app/app.db "VACUUM INTO '$DEST'"
gzip "$DEST"
find /backup -name 'app-*.db.gz' -mtime +30 -delete

よくあるハマりどころ

バックアップから復元できるか試していない

ファイルは毎日保存されているのに、復元したら壊れていた、というのはよくある事故です。取得したファイルに対して PRAGMA integrity_check を実行するところまでを、スクリプトに含めておきましょう。

sqlite3 "$DEST" "PRAGMA integrity_check" | grep -q '^ok$' || exit 1

VACUUM をトランザクションの中で実行しようとする

VACUUM はトランザクションの内側では実行できません。また、未完了のトランザクションが他にある状態でも失敗します。

WALファイルを消せばきれいになると思う

-wal ファイルには、まだ本体に反映されていない変更が入っています。アプリが動いている状態で削除すると、データが失われます。切り詰めたい場合は必ず wal_checkpoint を使ってください。

PRAGMAを設定したつもりで効いていない

journal_mode 以外のほとんどのPRAGMAは接続ごとの設定です。ORMやコネクションプールを使っている場合、新しい接続では初期値に戻っています。接続確立時のフックで必ず実行されているかを確認してください。

現在の値は、値を書かずに実行すれば確認できます。

PRAGMA busy_timeout;
PRAGMA foreign_keys;

ちゃんと使うためのポイント

• 稼働中のデータベースを cp でバックアップしない
• VACUUM INTO 'ファイル' が最も手軽で安全なスナップショット方法
• VACUUM はファイルを縮めるが、ロックと一時領域が必要。日常的には不要
• PRAGMA quick_check を定期チェック、integrity_check を異常時に使う
• PRAGMAは「ファイルに保存されるもの」と「接続ごとのもの」を区別する
• 接続時に WAL / busy_timeout / foreign_keys / synchronous、切断時に optimize
• バックアップは復元まで一度試して初めて完成する

最終章では、ここまで前提としてきた「1台のマシンの中で完結する」という制約を超える方法を扱います。Turso・libSQL・Cloudflare D1・Litestream といった選択肢と、SQLiteを本番に採用してよい場面の判断基準を整理します。

参考リンク

• SQLite公式 — VACUUM
• SQLite公式 — PRAGMA Statements
• SQLite公式 — Backup API