4 ms·

I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories. This would not

by olavgg 6y ago

I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories. 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.

croon 6y ago
I've deployed Bitbucket and Postgres as a docker stack under the same subvolume and done snapshots on that. As long as you have WAL enabled in Postgres there shouldn't be an issue, and I've restored multiple times without problems.
mulander 6y ago
> I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories. 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 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.
- CodesInChaos 6y ago
  When I back up a database, I expect to get a consistent snapshot of the database, which includes all committed transactions. Uncommitted transactions can't be included in this, since there is no guarantee they'd ever commit. 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?
  - mulander 6y ago
    The only problem with atomic filesystem snapshots is the time required to perform recovery which can be avoided. The recovery process is of course reliable.
    - CodesInChaos 6y ago
      Does recovery take a long time? Do you have any numbers? I'd have expected recovery to take time proportional to the size of recent/uncommitted transactions, which should be quick, even for large databases.
      - mulander 6y ago
        I don't have experience with large SQLite databases. I had PostgreSQL take over 40 minutes for recovery on a 2 TB database set to checkpoint every 30 minutes. How long it takes will depend on the WAL size, the frequency of checkpoints and the operations that were performed before a crash/snapshot.
  - Annatar 6y ago
    That's not how database backup works: either you use a tool like RMAN, which knows about internal Oracle database structures, or you must shut a database down in order to do a cold backup. As there is no such thing as database shutdown in SQLite's case, if you want a clean backup before doing a ZFS snapshot (and shipping it off, else it's not a backup), you must shut down the application which is writing to the SQLite database.
    - CodesInChaos 6y ago
      Since power-loss is (hopefully) equivalent to an atomic storage snapshot, a database which doesn't produce a consistent backup via snapshots can't be safe against power loss and thus lacks the durability property of ACID. What's unsafe is using a naive file copy tool (e.g. `cp`), which non atomically copies a running database.
- jeltz 6y ago
  Are you sure? Since for PostgreSQL an atomic snapshot is enough. It is only when you do not have access to atomic snapshots that you strictly need to use the online backup api. And, yes, it will have to do a crash recovery when you first use the backup but if you cannot trust crash recovery you can't really trust your database at all.
  - mulander 6y ago
    You are correct and what I had in mind was the required recovery. I do trust recovery but performing it can be very time consuming.
- deleted 6y ago
  [deleted]
7steps2much 6y ago
It is important to mention that a "Remote" database could run on the same host ... 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).
CodesInChaos 6y ago
1. You can run Postgres locally using the same ZFS for storage you'd use for the sqlite database. This should come with the same backup properties as sqlite. 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.
- theamk 6y ago
  Git does use transactions, in a pretty clever way: the main storage is immutable -- so when you do a new commit, it creates all new files, and as a last step, updates HEAD/branch, using "rename" call which is atomic on all filesystems. 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.

by olavgg 6y ago

croon 6y ago

I've deployed Bitbucket and Postgres as a docker stack under the same subvolume and done snapshots on that. As long as you have WAL enabled in Postgres there shouldn't be an issue, and I've restored multiple times without problems.

mulander 6y ago

> I use Gitea(Github clone) locally with SQLite running on ZFS where I can take atomic snapshots of both the SQLite database and git repositories. 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 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.

CodesInChaos 6y ago
When I back up a database, I expect to get a consistent snapshot of the database, which includes all committed transactions. Uncommitted transactions can't be included in this, since there is no guarantee they'd ever commit. 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?
- mulander 6y ago
  The only problem with atomic filesystem snapshots is the time required to perform recovery which can be avoided. The recovery process is of course reliable.
  - CodesInChaos 6y ago
    Does recovery take a long time? Do you have any numbers? I'd have expected recovery to take time proportional to the size of recent/uncommitted transactions, which should be quick, even for large databases.
    - mulander 6y ago
      I don't have experience with large SQLite databases. I had PostgreSQL take over 40 minutes for recovery on a 2 TB database set to checkpoint every 30 minutes. How long it takes will depend on the WAL size, the frequency of checkpoints and the operations that were performed before a crash/snapshot.
- Annatar 6y ago
  That's not how database backup works: either you use a tool like RMAN, which knows about internal Oracle database structures, or you must shut a database down in order to do a cold backup. As there is no such thing as database shutdown in SQLite's case, if you want a clean backup before doing a ZFS snapshot (and shipping it off, else it's not a backup), you must shut down the application which is writing to the SQLite database.
  - CodesInChaos 6y ago
    Since power-loss is (hopefully) equivalent to an atomic storage snapshot, a database which doesn't produce a consistent backup via snapshots can't be safe against power loss and thus lacks the durability property of ACID. What's unsafe is using a naive file copy tool (e.g. `cp`), which non atomically copies a running database.
jeltz 6y ago
Are you sure? Since for PostgreSQL an atomic snapshot is enough. It is only when you do not have access to atomic snapshots that you strictly need to use the online backup api. And, yes, it will have to do a crash recovery when you first use the backup but if you cannot trust crash recovery you can't really trust your database at all.
- mulander 6y ago
  You are correct and what I had in mind was the required recovery. I do trust recovery but performing it can be very time consuming.
deleted 6y ago
[deleted]

7steps2much 6y ago

It is important to mention that a "Remote" database could run on the same host ... 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).

CodesInChaos 6y ago

1. You can run Postgres locally using the same ZFS for storage you'd use for the sqlite database. This should come with the same backup properties as sqlite. 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.

theamk 6y ago
Git does use transactions, in a pretty clever way: the main storage is immutable -- so when you do a new commit, it creates all new files, and as a last step, updates HEAD/branch, using "rename" call which is atomic on all filesystems. 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.