2 ms·

Question: > Do not use `cp` to back up SQLite databases. It is not transactionally safe. I’m curious what happens. Does this mean that it may cause side effec

by jchw 5y ago

Question: > Do not use `cp` to back up SQLite databases. It is not transactionally safe. I’m curious what happens. Does this mean that it may cause side effects from transactions that are not committed to become visible? I guess if you could snapshot the database file and WAL and etc. simultaneously this isn’t an issue, right? Because otherwise it would be a problem for a database any time your program or machine crashed.

tedunangst 5y ago
Ignore the journal and wal for now (though they certainly contribute to backup complexity). You have a 70GB database file with "apple" near the start and "banana" near the end. You start cp and it copies apple. The application updates apple to "cat", and then banana to "dog". Finally cp copies dog to backup. You now have a database which contains "apple" and "dog" which never existed at any point in the original timeline.
- jchw 5y ago
  OK. So CoW snapshotting and other techniques that could take the files at a given point in time would not be subject to this issue? Just to be completely clear. Or, are there other issues?
  - mbreese 5y ago
    I always thought that any file based solution was a risk when copying live databases or file systems. You should always pause writes before your snapshot, but CoW does make this process significantly faster. I’ve never done this with a SQLite database, but I have done it with running VM file systems (with a CoW backend). And the filesystem pause was always very short.