most reliable way to copy [SQLite files] safely ?
I'm not sure I'm understanding the words safe & reliable in this
context. Partly this is because sqlite is a single-user database
and you didn't reveal how participants open / select / update /
close in your environment. I can share what I know.
I'm not seeing any "multi file consistency" issues here,
so treating your problem as "copy one file" multiple
times seems suitable.
If, say, a pair of recently updated files were tightly bound
to one another, then I would advocate putting them in their
own uniquely named directory and doing atomic rename on
We are replicating a source file to a destination location.
Doesn't matter whether src & dst are on same filesystem,
are on two different locally mounted filesystems,
or are on different hosts.
Begin by copying to a temp file on the destination filesystem.
Roll a random number and generate a new unique temp filename.
No one has ever used this name before, no one knows to look
for it, so it is free of conflict from readers and writers.
Use whatever method you like to copy from src to temp.
Alter the buffer size to find what's well suited to
your setup, perhaps using
/bin/dd bs=1M ....
Suffer CTRL/C restarts, network interruptions, reboots,
and other calamities, and then pick up where you left off,
appending to a partially-written temp file.
At the end, consider computing SHA3 hash of both temp and src,
to verify the bits made it there alive.
$ mv temp dst, or use the
sys call. We are taking advantage of the fact
that this is an atomic rename.
So readers or writers that attempt to open(dst)
will either see all the old bits, or all
the new bits, and never a partly copied file,
never a corrupt file.
This is a battle tested setup that works reliably
in a wide range of practical deployments.
The qmail maildir
format for inboxes manages to offer reliable service
even over flakey Sun NFS mounts using this technique.
The highly efficient
utility accomplishes reliable network transfers
via this technique.
Some minor caveats:
An interrupted transfer might leave
behind a turd file. No harm done, just a little clutter
and some disk blocks allocated. You should be able to both
identify and delete ancient (day old?) temp files.
It took you some amount of time to read
the source and write the destination.
That is a "race window".
To make it slightly less racy,
you might examine source timestamp or data,
and re-copy relevant portions of the file.
Given that the bulk of the file is already
at the destination, the 2nd pass might go quicker.
There's still a race.
This tends to work well with append-only
syslog text files, where 2nd pass appends
the occasional late-arriving log message.
For a belt-and-suspenders approach,
do something like this:
$ shasum *.db > manifest.txt
Transfer the manifest along with the DB files.
Verify the manifest at the far end.
Now two applications, one which copies
and one which computes hashes, would have
to be buggy in the same way in order for
an error to sneak past your end-to-end checking.
GPG signatures offer a similar approach.
(There are still perverse FS failure modes
related to caching and to RAID which could
let such a check pass yet deliver bad files
at a later date. Not a big concern in most
practical systems we see nowadays. According
to your level of paranoia, you might find zfs
to be a suitable backend store.)
Sqlite writers should COMMIT prior to
your open + read of the file.
Speaking for myself, I would have writers
close the file, e.g. by exiting, before the read.