2 ms·

"SQLite is just that one file" How do you get enough time to back up that file when it's in use? Lock the file until you've copied it? zfs?

threatripper 4y ago
They actually address this problem. https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html > The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database file, replacing any original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue without excessive delays while a backup of an online database is made.
- rahimnathwani 4y ago
  From the user perspective, then, a SQLite backup doesn't seem materially less complicated than a postgres backup. I understood GP's comment to mean that it's easier to back up SQLite because it's just a file [which you can just copy].
  - threatripper 4y ago
    It is just a file that you can copy when nobody is modifying data. In most simple cases you can just shut down the webserver for a minute to do database maintenance. Things that you already know how to do them. You don't have to remember any specific database commands for that. So the real difference is in the entry barrier.
    - e12e 4y ago
      I'm quite confident that if you can snapshot the vm/filesystem (eg VMware snapshot, or zfs snapshot) - you will have a working postgresql backup too...
    - deleted 4y ago
      [deleted]
LanternLight83 4y ago
BTRFS or ZFS sounds just fine to me, you could even create a small virtual disk for it on a host FS (less integrity, and one oight to snapshot the whole system anyways, but hypothetically you could do this just for atomic snapshots as long the rest of the system is cattle and those snapshots are properly backed up / replicated)
- TingPing 4y ago
  BTRFS recommends disabling CoW for DB files as it destroys performance.

threatripper 4y ago

They actually address this problem. https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html > The Online Backup API was created to address these concerns. The online backup API allows the contents of one database to be copied into another database file, replacing any original contents of the target database. The copy operation may be done incrementally, in which case the source database does not need to be locked for the duration of the copy, only for the brief periods of time when it is actually being read from. This allows other database users to continue without excessive delays while a backup of an online database is made.

rahimnathwani 4y ago
From the user perspective, then, a SQLite backup doesn't seem materially less complicated than a postgres backup. I understood GP's comment to mean that it's easier to back up SQLite because it's just a file [which you can just copy].
- threatripper 4y ago
  It is just a file that you can copy when nobody is modifying data. In most simple cases you can just shut down the webserver for a minute to do database maintenance. Things that you already know how to do them. You don't have to remember any specific database commands for that. So the real difference is in the entry barrier.
  - e12e 4y ago
    I'm quite confident that if you can snapshot the vm/filesystem (eg VMware snapshot, or zfs snapshot) - you will have a working postgresql backup too...
  - deleted 4y ago
    [deleted]

LanternLight83 4y ago

BTRFS or ZFS sounds just fine to me, you could even create a small virtual disk for it on a host FS (less integrity, and one oight to snapshot the whole system anyways, but hypothetically you could do this just for atomic snapshots as long the rest of the system is cattle and those snapshots are properly backed up / replicated)

TingPing 4y ago
BTRFS recommends disabling CoW for DB files as it destroys performance.