Re: [sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
Thanks for the responses. I'll probably write a little c++ program to use the online backup API. Josh On Apr 5, 2010, at 3:07 PM, D. Richard Hipp wrote: > > On Apr 5, 2010, at 5:42 PM, Joshua Redstone wrote: > >> Hi sqlite-users, >> I'm looking for an easy way to backup a sqlite3 database while it is >> in use. If I could get away with linux 'cp', that'd be easier than >> writing c++ code according to the online backup API >> (http://www.sqlite.org/backup.html >> ), and also it has the advantage that it would not block writes. >> Initially I thought that 'cp' would be safe because sqlite is crash- >> safe, but then I realized that 'cp' is not atomic, so it's possible >> it won't work. >> Thoughts? > > Right. "cp" is not atomic. "cp" will *usually* work, but sometimes > you will get unluck and the database will change in the middle of the > "cp" and what you end up with will be the first have of one database > and the second half of a different database. > > The backup API is safer. > > > >> Thanks, >> Josh >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > D. Richard Hipp > d...@hwaci.com > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
On 5 Apr 2010, at 10:42pm, Joshua Redstone wrote: > I'm looking for an easy way to backup a sqlite3 database while it is in use. > If I could get away with linux 'cp', that'd be easier than > writing c++ code according to the online backup API > (http://www.sqlite.org/backup.html), and also it has the advantage that it > would not block writes. Initially I thought that 'cp' would be safe because > sqlite is crash-safe, but then I realized that 'cp' is not atomic, so it's > possible it won't work. Correct. You may catch the database while it is being changed with, for example, a row changed and an index not updated yet. Either use the backup API as you mentioned or write code to read every row of every table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
On Apr 5, 2010, at 5:42 PM, Joshua Redstone wrote: > Hi sqlite-users, > I'm looking for an easy way to backup a sqlite3 database while it is > in use. If I could get away with linux 'cp', that'd be easier than > writing c++ code according to the online backup API > (http://www.sqlite.org/backup.html > ), and also it has the advantage that it would not block writes. > Initially I thought that 'cp' would be safe because sqlite is crash- > safe, but then I realized that 'cp' is not atomic, so it's possible > it won't work. > Thoughts? Right. "cp" is not atomic. "cp" will *usually* work, but sometimes you will get unluck and the database will change in the middle of the "cp" and what you end up with will be the first have of one database and the second half of a different database. The backup API is safer. > Thanks, > Josh > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
Just a little logic: if you want some backup mechanism that won't block writers then what state the backed up database should be in? In the state that was when backup process started or in the state that was when backup process ended? If the former then how would you distinguish file contents that was from the beginning from the one written by somebody in the middle of the process? If the latter then how would you catch changes made in the middle of the process to some parts of database that were already copied? There's no answers to those questions unless your database engine is a single process one or you blocked away all writers while you are making backup. And the answer to your question is no, you cannot backup SQLite database on-the-fly by issuing a simple 'cp'. Pavel On Mon, Apr 5, 2010 at 5:42 PM, Joshua Redstone wrote: > Hi sqlite-users, > I'm looking for an easy way to backup a sqlite3 database while it is in use. > If I could get away with linux 'cp', that'd be easier than > writing c++ code according to the online backup API > (http://www.sqlite.org/backup.html), and also it has the advantage that it > would not block writes. Initially I thought that 'cp' would be safe because > sqlite is crash-safe, but then I realized that 'cp' is not atomic, so it's > possible it won't work. > Thoughts? > Thanks, > Josh > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is it safe to backup an in-use sqlite3 database by copying the underlying database file?
Hi sqlite-users, I'm looking for an easy way to backup a sqlite3 database while it is in use. If I could get away with linux 'cp', that'd be easier than writing c++ code according to the online backup API (http://www.sqlite.org/backup.html), and also it has the advantage that it would not block writes. Initially I thought that 'cp' would be safe because sqlite is crash-safe, but then I realized that 'cp' is not atomic, so it's possible it won't work. Thoughts? Thanks, Josh ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
