7 ms·

> If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file. The first half of the file is pre-u

by bambax 1y ago

> If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file. The first half of the file is pre-update, the second half file is post-update, and they don’t match. When I try to open the database locally, I get an error Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption. For replicating sqlite databases safely there is https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream

quotemstr 1y ago
> Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption Do people really not understand how file storage works? I cannot rightly apprehend the confusion of ideas that would produce an attempt to copy a volatile database without synchronization and expect it to work.
- ahazred8ta 1y ago
  > I cannot rightly apprehend the confusion of ideas I see you are a man of culture.
  - kccqzy 1y ago
    Charles Babbage is smart, but either he lacks empathy to understand other people or he's just saying that deliberately for comedic effect.
    - jerf 1y ago
      It was early days... very early days. He didn't have the benefit of trying to help his (metaphorical) grandparents get their emails or worked under a manager who thinks 2023-era ChatGPT is only slightly less reliable than the Standard Model of Physics, if not slightly more.
    - bambax 1y ago
      Oh he definitely lacked empathy. But things haven't improved much. Today we have "prompt engineers" whose only job is to input the right question in order to get the right answer.
- kccqzy 1y ago
  The confusion of ideas here is understandable IMO: people assume everything is atomic. Databases of course famously have ACID guarantees. But it's easy for people to assume copying is also an atomic operation. Honestly if someone works too much with databases and not enough with filesystems it's a mistake easily made.
creatonez 1y ago
> You can't copy the file of a running, active db receiving updates, that can only result in corruption To push back against "only" -- there is actually one scenario where this works. Copying a file or a subvolume on Btrfs or ZFS can be done atomically, so if it's an ACID database or an LSM tree, in the worst case it will just rollback. Of course, if it's multiple files you have to take care to wrap them in a subvolume so that all of them are copied in the same transaction, simply using `cp --reflink=always` won't do. Possibly freezing the process with SIGSTOP would yield the same result, but I wouldn't count on that
- lmz 1y ago
  It can't be done without fs specific snapshots - otherwise how would it distinguish between a cp/rsync needing consistent reads vs another sqlite client wanting the newest data?
  - o11c 1y ago
    Obligatory "LVM still exists and snapshots are easy enough to overprovision for"
    - HumanOstrich 1y ago
      Taking an LVM snapshot and then copying the sqlite database from that is sufficient to keep it from being corrupted, but you can have incomplete transactions that will be rolled back during crash recovery. The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata. To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot. PRAGMA wal_checkpoint(FULL); BEGIN IMMEDIATE; -- locks out writers . /* trigger your LVM snapshot here */ COMMIT; You can also use fsfreeze to get the same level of safety: sudo fsfreeze -f /mnt/data # (A) flush dirty pages & block writes lvcreate -L1G -s -n snap0 /dev/vg0/data sudo fsfreeze -u /mnt/data # (B) thaw, resume writes Bonus - validate the snapshotted db file with: sqlite3 mydb-snapshot.sqlite "PRAGMA integrity_check;"
yellow_lead 1y ago
Litestream looks interesting but they are still in beta, and seem to have not had a release in over a year, although SQLite doesn't move that quickly. Is Litestream still an active project?
- clintonb 1y ago
  Despite the beta label and lack of a 1.x release, I would consider the project pretty stable. We've used it in production for over 18 months to support an offline-first point of sale system. We haven't had any issues with Litestream.
  - yellow_lead 1y ago
    That's great, thanks for sharing!
- acrispino 1y ago
  seems like a new release is being worked on: https://github.com/benbjohnson/litestream/pull/636 https://github.com/benbjohnson/litestream/pull/636
pixl97 1y ago
>You can't copy the file of a running, active db receiving updates, that can only result in corruption There is a slight 'well akshully' on this. A DB flush and FS snapshot where you copy the snapshotted file will allow this. MSSQL VSS snapshots would be an example of this.
- tpmoney 1y ago
  Similarly you can rsync a Postgres data directory safely while the db is running, with the caveat that you likely lose any data written while the rsync is running. And if you want that data, you can get it with the WAL files. It’s been years since I needed to do this, but if I remember right, you can clone an entire pg db live with a `pg_backup_start()`, rsync the data directory, pg_backup_stop() and rsync the WAL files written since backup start.
  - edoceo 1y ago
    For moving DBs where I'm allowed minutes of downtime I do rsync (slow) first from the live, while hot, then just stop that one, then rsync again (fast) then make the new one hot. Works a treat when other (better) method are not available.
- bob1029 1y ago
  MSSQL also offers a virtual backup device interface that 3rd party tools can implement. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/vdi-reference/reference-virtual-device-interface https://learn.microsoft.com/en-us/sql/relational-databases/b...
wswope 1y ago
The built-in .backup command is also intended as an official tool for making “snapshotted” versions of a live db that can be copied around.
- remram 1y ago
  This leverages a dedicated on-line backup API: https://sqlite.org/backup.html https://sqlite.org/backup.html
jmull 1y ago
If the corruption is detectable and infrequent enough for your purposes, then it does work, with a simple “retry until success” loop. (That’s how TCP works, for example.)
- benbjohnson 1y ago
  Not all corruption is detectable. You could make a copy during a transaction where only a subset of the transactions saved pages are persisted but all branch & leaf pages are pointed to correctly. That would give you a state of the database that never actually existed and break atomicity.
  - jmull 1y ago
    > Not all corruption is detectable. Well, I don't know rsync that well. If you're saying it doesn't detect changes to files while it's being copied, then I'll believe you. And, as far as I know, it's impossible to detect absolutely all corruption. But you can pretty easily detect, e.g., that a file has or has not changed since before you copied it to after, on a system with a basically functioning filesystem and clock, with a reasonable/useful level of confidence.
lknuth 1y ago
While I run and love litestream on my own system, I also like that they have a pretty comprehensive guide on how to do something like this manually, via built-in tools: https://litestream.io/alternatives/cron/ https://litestream.io/alternatives/cron/
yard2010 1y ago
Litestream is really cool! I'm planning to use it to backup and restore my SQLite in the container level, just like what that ex-google guy who started a startup of a small KVM and had a flood in his warehouse while on vacation did. If I'm not mistaken. I would link here the perfect guide he wrote but there's 0 chance I'll find it. If you understand the reference please post the link.
- mtlynch 1y ago
  Haha, that sounds like me. Here's the writeup you're talking about: https://mtlynch.io/litestream/ https://mtlynch.io/litestream/ And here's the flooding story: https://mtlynch.io/solo-developer-year-6/#the-most-terrifying-10-minutes-of-2023 https://mtlynch.io/solo-developer-year-6/#the-most-terrifyin... Sidenote: I still use Litestream in every project where I use SQLite.
rsync 1y ago
"For replicating sqlite databases safely there is (litestream) ..." A reminder that litestream can run over plain old SFTP[1] which means you can stream database replication to just about any UNIX endpoint over SSH. I have a favorite[2] but any SFTP server will do ... [1] https://github.com/benbjohnson/litestream/issues/140 https://github.com/benbjohnson/litestream/issues/140 [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html https://www.rsync.net/resources/notes/2021-q3-rsync.net_tech...
- aitchnyu 1y ago
  Whats the closest counterpart in Postgres ecosystem?
  - j16sdiz 1y ago
    log shipping[1] or logical replication[2] [1] https://www.postgresql.org/docs/current/warm-standby.html https://www.postgresql.org/docs/current/warm-standby.html [2] https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....
  - rsync 1y ago
    This isn't what you mean but I can't resist: pg_dump -U postgres db | ssh user@rsync.net "dd of=db_dump"
gerdesj 1y ago
"If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file" Wot? There are multiple ways of snapshotting/checkpointing, starting at the virty level and working on down the stack through the application level.
yellowapple 1y ago
Litestream is overkill for this; "VACUUM foo INTO foo.db;" is all you need. https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html

by bambax 1y ago

quotemstr 1y ago

> Of course! You can't copy the file of a running, active db receiving updates, that can only result in corruption Do people really not understand how file storage works? I cannot rightly apprehend the confusion of ideas that would produce an attempt to copy a volatile database without synchronization and expect it to work.

ahazred8ta 1y ago
> I cannot rightly apprehend the confusion of ideas I see you are a man of culture.
- kccqzy 1y ago
  Charles Babbage is smart, but either he lacks empathy to understand other people or he's just saying that deliberately for comedic effect.
  - jerf 1y ago
    It was early days... very early days. He didn't have the benefit of trying to help his (metaphorical) grandparents get their emails or worked under a manager who thinks 2023-era ChatGPT is only slightly less reliable than the Standard Model of Physics, if not slightly more.
  - bambax 1y ago
    Oh he definitely lacked empathy. But things haven't improved much. Today we have "prompt engineers" whose only job is to input the right question in order to get the right answer.
kccqzy 1y ago
The confusion of ideas here is understandable IMO: people assume everything is atomic. Databases of course famously have ACID guarantees. But it's easy for people to assume copying is also an atomic operation. Honestly if someone works too much with databases and not enough with filesystems it's a mistake easily made.

creatonez 1y ago

> You can't copy the file of a running, active db receiving updates, that can only result in corruption To push back against "only" -- there is actually one scenario where this works. Copying a file or a subvolume on Btrfs or ZFS can be done atomically, so if it's an ACID database or an LSM tree, in the worst case it will just rollback. Of course, if it's multiple files you have to take care to wrap them in a subvolume so that all of them are copied in the same transaction, simply using `cp --reflink=always` won't do. Possibly freezing the process with SIGSTOP would yield the same result, but I wouldn't count on that

lmz 1y ago
It can't be done without fs specific snapshots - otherwise how would it distinguish between a cp/rsync needing consistent reads vs another sqlite client wanting the newest data?
- o11c 1y ago
  Obligatory "LVM still exists and snapshots are easy enough to overprovision for"
  - HumanOstrich 1y ago
    Taking an LVM snapshot and then copying the sqlite database from that is sufficient to keep it from being corrupted, but you can have incomplete transactions that will be rolled back during crash recovery. The problem is that LVM snapshots operate at the block device level and only ensure there are no torn or half-written blocks. It doesn't know about the filesystem's journal or metadata. To get a consistent point-in-time snapshot without triggering crash-recovery and losing transactions, you also need to lock the sqlite database or filesystem from writes during the snapshot. PRAGMA wal_checkpoint(FULL); BEGIN IMMEDIATE; -- locks out writers . /* trigger your LVM snapshot here */ COMMIT; You can also use fsfreeze to get the same level of safety: sudo fsfreeze -f /mnt/data # (A) flush dirty pages & block writes lvcreate -L1G -s -n snap0 /dev/vg0/data sudo fsfreeze -u /mnt/data # (B) thaw, resume writes Bonus - validate the snapshotted db file with: sqlite3 mydb-snapshot.sqlite "PRAGMA integrity_check;"

yellow_lead 1y ago

Litestream looks interesting but they are still in beta, and seem to have not had a release in over a year, although SQLite doesn't move that quickly. Is Litestream still an active project?

clintonb 1y ago
Despite the beta label and lack of a 1.x release, I would consider the project pretty stable. We've used it in production for over 18 months to support an offline-first point of sale system. We haven't had any issues with Litestream.
- yellow_lead 1y ago
  That's great, thanks for sharing!
acrispino 1y ago
seems like a new release is being worked on: https://github.com/benbjohnson/litestream/pull/636 https://github.com/benbjohnson/litestream/pull/636

pixl97 1y ago

>You can't copy the file of a running, active db receiving updates, that can only result in corruption There is a slight 'well akshully' on this. A DB flush and FS snapshot where you copy the snapshotted file will allow this. MSSQL VSS snapshots would be an example of this.

tpmoney 1y ago
Similarly you can rsync a Postgres data directory safely while the db is running, with the caveat that you likely lose any data written while the rsync is running. And if you want that data, you can get it with the WAL files. It’s been years since I needed to do this, but if I remember right, you can clone an entire pg db live with a `pg_backup_start()`, rsync the data directory, pg_backup_stop() and rsync the WAL files written since backup start.
- edoceo 1y ago
  For moving DBs where I'm allowed minutes of downtime I do rsync (slow) first from the live, while hot, then just stop that one, then rsync again (fast) then make the new one hot. Works a treat when other (better) method are not available.
bob1029 1y ago
MSSQL also offers a virtual backup device interface that 3rd party tools can implement. https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/vdi-reference/reference-virtual-device-interface https://learn.microsoft.com/en-us/sql/relational-databases/b...

wswope 1y ago

The built-in .backup command is also intended as an official tool for making “snapshotted” versions of a live db that can be copied around.

remram 1y ago
This leverages a dedicated on-line backup API: https://sqlite.org/backup.html https://sqlite.org/backup.html

jmull 1y ago

If the corruption is detectable and infrequent enough for your purposes, then it does work, with a simple “retry until success” loop. (That’s how TCP works, for example.)

benbjohnson 1y ago
Not all corruption is detectable. You could make a copy during a transaction where only a subset of the transactions saved pages are persisted but all branch & leaf pages are pointed to correctly. That would give you a state of the database that never actually existed and break atomicity.
- jmull 1y ago
  > Not all corruption is detectable. Well, I don't know rsync that well. If you're saying it doesn't detect changes to files while it's being copied, then I'll believe you. And, as far as I know, it's impossible to detect absolutely all corruption. But you can pretty easily detect, e.g., that a file has or has not changed since before you copied it to after, on a system with a basically functioning filesystem and clock, with a reasonable/useful level of confidence.

lknuth 1y ago

While I run and love litestream on my own system, I also like that they have a pretty comprehensive guide on how to do something like this manually, via built-in tools: https://litestream.io/alternatives/cron/ https://litestream.io/alternatives/cron/

yard2010 1y ago

Litestream is really cool! I'm planning to use it to backup and restore my SQLite in the container level, just like what that ex-google guy who started a startup of a small KVM and had a flood in his warehouse while on vacation did. If I'm not mistaken. I would link here the perfect guide he wrote but there's 0 chance I'll find it. If you understand the reference please post the link.

mtlynch 1y ago
Haha, that sounds like me. Here's the writeup you're talking about: https://mtlynch.io/litestream/ https://mtlynch.io/litestream/ And here's the flooding story: https://mtlynch.io/solo-developer-year-6/#the-most-terrifying-10-minutes-of-2023 https://mtlynch.io/solo-developer-year-6/#the-most-terrifyin... Sidenote: I still use Litestream in every project where I use SQLite.

rsync 1y ago

"For replicating sqlite databases safely there is (litestream) ..." A reminder that litestream can run over plain old SFTP[1] which means you can stream database replication to just about any UNIX endpoint over SSH. I have a favorite[2] but any SFTP server will do ... [1] https://github.com/benbjohnson/litestream/issues/140 https://github.com/benbjohnson/litestream/issues/140 [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html https://www.rsync.net/resources/notes/2021-q3-rsync.net_tech...

aitchnyu 1y ago
Whats the closest counterpart in Postgres ecosystem?
- j16sdiz 1y ago
  log shipping[1] or logical replication[2] [1] https://www.postgresql.org/docs/current/warm-standby.html https://www.postgresql.org/docs/current/warm-standby.html [2] https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication....
- rsync 1y ago
  This isn't what you mean but I can't resist: pg_dump -U postgres db | ssh user@rsync.net "dd of=db_dump"

gerdesj 1y ago

"If it takes a long time to copy a database and it gets updated midway through, rsync may give me an invalid database file" Wot? There are multiple ways of snapshotting/checkpointing, starting at the virty level and working on down the stack through the application level.

yellowapple 1y ago

Litestream is overkill for this; "VACUUM foo INTO foo.db;" is all you need. https://www.sqlite.org/lang_vacuum.html https://www.sqlite.org/lang_vacuum.html