Re: [sqlite] backup open database
On Sun, 1 May 2005, D. Richard Hipp wrote: On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: i want to make a copy of a sqlite3 database file while it is open. is there anyway that i can tell programatically that any caches/journals are flushed out? or is there any way to get and exclusive lock i guess, before making a backup copy? 1. Execute the SQL statement "BEGIN EXCLUSIVE". 2. Make your backup copy of the database file. 3. Execute the SQL statement "COMMIT". The BEGIN EXCLUSIVE statement makes sure that the database file is locked and will not be changed while your are copying it. The COMMIT statement release the lock after you have finished making the copy. ignoring threads - would it be sufficient to just obtain an exclusive (fcntl) lock on the db? it seems to be: i do something like this for some code i have that backs up a hot nfs mounted sqlite db that has around 30 readers/writers doing about 1000 transactions per hour (eg. heavy - but not __extremely__ heavy load) - n times just blindly copy the db and try to open it. if the open succeeds run an integrity check. if the integrity check succeeds we've made a copy - exit. - finally, if the method above fails, obtain an exclusive lock on the db, copy it, run an integrity check, then (iff successful) release lock. i don't have any threads and all access to my db is wrapped in a single class so this ensures only one process in my system, on any given host, has the lock while the copy takes place. i've been using this method to take hourly snapshots of my db for the past 9 months, through several disk and power failures, with no issues. not that there aren't any - but it seems to work out alright. cheers. -a -- === | email :: ara [dot] t [dot] howard [at] noaa [dot] gov | phone :: 303.497.6469 | renunciation is not getting rid of the things of this world, but accepting | that they pass away. --aitken roshi ===
Re: [sqlite] backup open database
On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: > i want to make a copy of a sqlite3 database file while it is open. is > there anyway that i can tell programatically that any caches/journals > are flushed out? or is there any way to get and exclusive lock i > guess, before making a backup copy? > 1. Execute the SQL statement "BEGIN EXCLUSIVE". 2. Make your backup copy of the database file. 3. Execute the SQL statement "COMMIT". The BEGIN EXCLUSIVE statement makes sure that the database file is locked and will not be changed while your are copying it. The COMMIT statement release the lock after you have finished making the copy. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] backup open database
Alrighty: embedded hardware _does_ make sense. Some options: 0. gzip the dumped database. don't save indexes, etc. Rebuild the database on startup. You can even diff against older copies if your flash has a [significantly] limited number of write cycles. this is probably the best/easiest method, but if you don't have control of the startup routine, and can guarantee some code is run at shutdown, it probably won't work for you. 1. Examine the sqlite source code: write a routine that locks the main db exclusive, checks for the -journal file, and as necessary unlocks/waits until it can get a lock with the -journal file missing. Use this, then copy the database normally. This requires some coding, and gaining understanding of what sqlite uses various files for. It is the least invasive on your existing system, even if it's probably the hardest to get right. 2. Use a ramdisk, and put LVM on it. Or if this is not a linux or freebsd-based embedded box, alter your operating system to allow you to temporarily defer all filesystem operations "somehow". Fork the filesystem using LVM's snapshots (freebsd has a similar invention, ms windows has shadow copies, but i don't know as to their availability in the embedded world). If you can defer filesystem operations, simply copy the database+journal to another directory while all other filesystem operations are suspended. Use sqlite's open on the database+journal to recover it on the forked volume, then close: copy the resulting db to flash. This is actually really simple, and could be made very general for saving all kinds of configuration data. I use a method very similar to this for router configuration as I can simply do a snapshot and cpio directly into flash (skipping my temporaries as I do a O_EXCL->link- >unlink to guarantee atomicity), then delete the snapshot. It does (however) take advantage of the fact that sqlite is crashproof. It'll do it's recovery later. On Sun, 2005-05-01 at 10:37 +0800, Damian Slee wrote: > hi, > i had have a look, i may be able to use this. this will be in an embedded > hardware application, so i don't have a command line, only what i code in. > > i was hoping to do a binary copy of the db from a ram file system to flash > memory for permanent storage. flash is really slow, so a copy would be a lot > quicker than a series of .dump Inserts. which really requires sqlite running > on a flash file system as well. a copy is really all i want, but knowing > when everything is commited, or locking out other threads without them having > to close the db. > > thanks, > > damian > > > > > > > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: Sun 1/05/2005 1:07 AM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] backup open database > > > > Why is it inappropriate to use: > > sqlite originaldb ".dump" | sqlite backupdb > > > On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: > > hi all, > > i want to make a copy of a sqlite3 database file while it is open. is > > there anyway that i can tell programatically that any caches/journals are > > flushed out? or is there any way to get and exclusive lock i guess, before > > making a backup copy? > > > > my proposed application would have quite a few threads which may have the > > same sqlite db open for read or write (rarer), but i dont want to shut down > > the application to definitely know it is safe to copy. > > > > > > thanks, > > > > damian > > > >
RE: [sqlite] backup open database
hi, i had have a look, i may be able to use this. this will be in an embedded hardware application, so i don't have a command line, only what i code in. i was hoping to do a binary copy of the db from a ram file system to flash memory for permanent storage. flash is really slow, so a copy would be a lot quicker than a series of .dump Inserts. which really requires sqlite running on a flash file system as well. a copy is really all i want, but knowing when everything is commited, or locking out other threads without them having to close the db. thanks, damian From: Mrs. Brisby [mailto:[EMAIL PROTECTED] Sent: Sun 1/05/2005 1:07 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] backup open database Why is it inappropriate to use: sqlite originaldb ".dump" | sqlite backupdb On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: > hi all, > i want to make a copy of a sqlite3 database file while it is open. is there > anyway that i can tell programatically that any caches/journals are flushed > out? or is there any way to get and exclusive lock i guess, before making a > backup copy? > > my proposed application would have quite a few threads which may have the > same sqlite db open for read or write (rarer), but i dont want to shut down > the application to definitely know it is safe to copy. > > > thanks, > > damian
Re: [sqlite] backup open database
Why is it inappropriate to use: sqlite originaldb ".dump" | sqlite backupdb On Sun, 2005-05-01 at 00:28 +0800, Damian Slee wrote: > hi all, > i want to make a copy of a sqlite3 database file while it is open. is there > anyway that i can tell programatically that any caches/journals are flushed > out? or is there any way to get and exclusive lock i guess, before making a > backup copy? > > my proposed application would have quite a few threads which may have the > same sqlite db open for read or write (rarer), but i dont want to shut down > the application to definitely know it is safe to copy. > > > thanks, > > damian
