ZFS snapshots may not result in a consistent databases backup of sqlite. You should use VACUUM INTO and then do a snapshot or use the sqlite Backup API.
See my other comment: https://www.sqlite.org/backup.html
Essentially, while you have a proper atomic snapshot of changes on disk the in flight transactions won't be there and you will be on the mercy of having a sucessful recovery from the journal/wal if you have that.
From your answer it sounds like I'd get such a snapshot (even if it relies on recovery from a journal internally). So I don't understand why you see a problem here. Is the recovery process unreliable?
> 1.2. Backup or restore while a transaction is active
> Systems that run automatic backups in the background might try to make a backup copy of an SQLite database file while it is in the middle of a transaction. The backup copy then might contain some old and some new content, and thus be corrupt.
> The best approach to make reliable backup copies of an SQLite database is to make use of the backup API that is part of the SQLite library. Failing that, it is safe to make a copy of an SQLite database file as long as there are no transactions in progress by any process. If the previous transaction failed, then it is important that any rollback journal (the -journal file) or write-ahead log (the -wal file) be copied together with the database file itself.
So no, it doesn't appear that it is safe in general to just copy the file whenever. If there are transactions in progress, things can go wrong. I don't quite understand why (isn't this the same as if a power failure happens, and SQLite is resistant to that?), but this is what the docs say.
It might be different with ZFS, since the backups are atomic (and this doc might assume that they are not), but I'm not 100% sure I would rely on it.
I'd have expected recovery to take time proportional to the size of recent/uncommitted transactions, which should be quick, even for large databases.
What's unsafe is using a naive file copy tool (e.g. `cp`), which non atomically copies a running database.
I run PostgreSQL on my VPS alongside gitea and some other services, and I can take snapshots of it just fine (assuming I don't take a snapshot as it is writing to disk, but that problem exists with SQLite as well).
2. I doubt that the git part of your setup can be snapshotted correctly while an operation is in progress, since git doesn't use transactions.
So you can do pretty bad things to git and it would still work. Atomic snapshots are absolutely no problem. Copying the directory while in progress is fine.
If you try very hard, you can still make a bad backup -- say you were copying files from remote host, and backup got interrupted after HEAD but before the new data. In this case, there is reflog -- append-only file which keeps HEAD history. Using this file, you would be able to manually recover into previous state.
This would not be possible if I used a remote database, as there is no way to do a atomic snapshot of both the remote database on another filesystem(even if its ZFS) and the git repository.
Developers of multiple services, please support SQLite and don't lock yourself to a single database like PostgreSQL and MySQL. Even if the service is embedded in a Docker Container it is still painful to manage.