SQLite3 の正しいバックアップ方法

稼働中に cp するとコピー先が壊れる

require "active_record"
system "rm -f main.db"
ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: "main.db")
ActiveRecord::Migration.verbose = false
ActiveRecord::Schema.define do
  create_table :users do |t|
    t.text :body
  end
end
class User < ActiveRecord::Base
end

Thread.start do
  loop { User.create!(body: "x" * 1.megabyte) }
end

p 100.times.all? {
  system %(cp main.db backup.db)
  system %(sqlite3 backup.db "PRAGMA integrity_check" >/dev/null 2>&1)
}

system %(sqlite3 backup.db "PRAGMA integrity_check")

実行結果

false

*** in database main ***
On tree page 397069 cell 0: invalid page number 459358
On tree page 397069 cell 335: invalid page number 458589
On tree page 397069 cell 334: invalid page number 457820
(snip)
On tree page 397069 cell 287: invalid page number 421677
On tree page 420908 cell 2: invalid page number 421165
On tree page 420908 cell 1: invalid page number 420909
Error: stepping, database disk image is malformed (11)

稼働中というのはアプリが動いている程度の意味ではなく「大容量のデータが絶え間なく INSERT されて続けている状況」を表わす。cp main.db backup.db に問題があり、コピー先の backup.db が壊れるがコピー元の main.db は壊れない。

稼働中は backup 命令が効かない

100 回ループしている部分を cp から backup 命令を使う方法に変更する。

100.times do
  system %(sqlite3 main.db ".backup backup.db")
end

実行結果

Error: database is locked

#<Thread:0x000000010d4ac910 test.rb:15 run> terminated with exception (report_on_exception is true):
/usr/local/var/rbenv/versions/3.2.2/lib/ruby/gems/3.2.0/gems/sqlite3-1.6.2-x86_64-darwin/lib/sqlite3/resultset.rb:170:in `step': SQLite3::BusyException: database is locked (ActiveRecord::StatementInvalid)
(snip)

最初の Error: database is locked は sqlite3 コマンド側のエラー。どのタイミングでも出る。壊れたバックアップファイルが作られない点は安心できる。

もう一方はアプリ側のエラー。つまりバックアップの失敗によりアプリ利用者に影響がでることがわかる。タイミングよってはエラーにならないこともある。

それであれば稼働していないときに cp すればいいのではないだろうか？ それでもよいがもし稼働していた場合に不整合なバックアップファイルが作られる危険があるため backup 命令の方が勝る。

まとめ

• 稼働中のデータベースファイルを壊すには？
  • データベースロックの仕組みがあるため壊すのは難しい
• バックアップファイルが壊れる原因は？
  • 稼働中のデータベースファイルを cp している
• 稼働中のアプリに例外を発生させるには？
  • 稼働中に backup 命令を使う
• もっとも安全にバックアップを取るには？
  • 稼働していない状態で backup 命令を使う