4 ms·

sqlite3-rsync is in the makefile. I got sqlite-rsync to build by just checking out the main branch and going: ./configure make sqlite3-rsync I think t

by gary_0 2y ago

sqlite3-rsync is in the makefile. I got sqlite-rsync to build by just checking out the main branch and going: ./configure make sqlite3-rsync I think the rsync branch has been merged? I'm really excited for this new tool; I'm hoping to use it for nightly backups of a webapp I'm building that uses sqlite exclusively. Although I'm hesitant to rely on sqlite-rsync right away because it's so new.

simonw 2y ago
Thanks for that! I just updated my TIL to promote this recipe instead - I didn't realize it was A) in the Makefile and B) merged into the main branch.
ralferoo 2y ago
> I'm hoping to use it for nightly backups of a webapp I'm building that uses sqlite exclusively The easiest recipe for this is to use sqlite3 $SRC "vacuum into '$DEST'" $DEST must not already exist, personally I prefer to put the timestamp into the filename and keep the last n copies.
- gary_0 2y ago
  I'm aware of the existing backup methods, but AFAIK they don't do a delta of only the changed pages (unless I use separate software), and "vacuum into" doesn't work over SSH. I have no need to store a backup on the same physical server. Also, the backup server will keep snapshots, and with sqlite3-rsync I can just update a previous snapshot with the changed pages rather than redundantly transferring the whole database over every time.
  - simonw 2y ago
    Yeah, the unique benefit of sqlite-rsync looks to be efficiently backing up medium to large databases that don’t change enormously (so deltas are worthwhile) over an SSH connection. I particularly like that you don’t need double the disk space to create the temporary backup copy before downloading it.
pkhuong 2y ago
> I'm hesitant to rely on sqlite-rsync right away because it's so new. If you want something less new that only needs an S3-compatible endpoint, there's https://gist.github.com/pkhuong/555be66f5e573a2d56186d658fd80881 https://gist.github.com/pkhuong/555be66f5e573a2d56186d658fd8...

by gary_0 2y ago

simonw 2y ago

Thanks for that! I just updated my TIL to promote this recipe instead - I didn't realize it was A) in the Makefile and B) merged into the main branch.

ralferoo 2y ago

> I'm hoping to use it for nightly backups of a webapp I'm building that uses sqlite exclusively The easiest recipe for this is to use sqlite3 $SRC "vacuum into '$DEST'" $DEST must not already exist, personally I prefer to put the timestamp into the filename and keep the last n copies.

gary_0 2y ago
I'm aware of the existing backup methods, but AFAIK they don't do a delta of only the changed pages (unless I use separate software), and "vacuum into" doesn't work over SSH. I have no need to store a backup on the same physical server. Also, the backup server will keep snapshots, and with sqlite3-rsync I can just update a previous snapshot with the changed pages rather than redundantly transferring the whole database over every time.
- simonw 2y ago
  Yeah, the unique benefit of sqlite-rsync looks to be efficiently backing up medium to large databases that don’t change enormously (so deltas are worthwhile) over an SSH connection. I particularly like that you don’t need double the disk space to create the temporary backup copy before downloading it.

pkhuong 2y ago

> I'm hesitant to rely on sqlite-rsync right away because it's so new. If you want something less new that only needs an S3-compatible endpoint, there's https://gist.github.com/pkhuong/555be66f5e573a2d56186d658fd80881 https://gist.github.com/pkhuong/555be66f5e573a2d56186d658fd8...