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 命令を使う