4 ms·

I run my backups like this: OUT="${i}.sql.zst" PART="${OUT}.part" sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" - mv

I run my backups like this: OUT="${i}.sql.zst" PART="${OUT}.part" sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" - mv "${PART}" "${OUT}" That doesn't block writers (when the writer uses WAL), and gives me a dump that's compressed well while also being easy to sync. My Home Assistant DB is 1.8GB, my dump is 286MB compressed, and I'd guess 90% of that is consistent from one day to the next.

formerly_proven 3mo ago
> That doesn't block writers (when the writer uses WAL) Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.
- andrewaylett 3mo ago
  I seem to recall having trouble with the read only flag and the backup API, but it's quite likely the problem was mine rather than with SQLite. Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.
arjie 3mo ago
What do you backup from your Home Assistant? The default backups are huge, but I finally settled for just the config and I leave the videos and caches off. I also leave off all the HACS downloaded repos. I'm wondering if I'm missing out by doing what I'm doing. What's in the DB that makes the HA DB that big? You keep lots of historical time-series?
- andrewaylett 3mo ago
  I've got quite a lot of historical data, yes. I don't back up `media` or `share`, but I do back up everything else using the automatic backup feature. Automatic backups are around 11GB each. That includes all the "apps", which includes a whole load of data that's not really related to HA :). The SQLite backup script is part of my separate backup routine that predates HA's backups, and eventually makes it into a Borg backup hosted on rsync.net. Belt and braces.
  - arjie 3mo ago
    Thank you for responding. Yeah I found that the HA automatic backup system creates extra large backups. I have only my own version of your SQLite backup. I suppose I shall learn the risks of this method with the first failure haha!
second_route 3mo ago
Nice. I switched to .backup for live DBs because .dump locked me out once. The .part + mv trick is clean though.

formerly_proven 3mo ago

> That doesn't block writers (when the writer uses WAL) Neither does VACUUM INTO or ".backup" (which uses the backup API) or sqlite3_rsync or litestream.

andrewaylett 3mo ago
I seem to recall having trouble with the read only flag and the backup API, but it's quite likely the problem was mine rather than with SQLite. Anyway, the sync-friendly output is the really important part for me, because it means I can point borg at the zstd-compressed file and it'll only need to store what's changed.

arjie 3mo ago

What do you backup from your Home Assistant? The default backups are huge, but I finally settled for just the config and I leave the videos and caches off. I also leave off all the HACS downloaded repos. I'm wondering if I'm missing out by doing what I'm doing. What's in the DB that makes the HA DB that big? You keep lots of historical time-series?

andrewaylett 3mo ago
I've got quite a lot of historical data, yes. I don't back up `media` or `share`, but I do back up everything else using the automatic backup feature. Automatic backups are around 11GB each. That includes all the "apps", which includes a whole load of data that's not really related to HA :). The SQLite backup script is part of my separate backup routine that predates HA's backups, and eventually makes it into a Borg backup hosted on rsync.net. Belt and braces.
- arjie 3mo ago
  Thank you for responding. Yeah I found that the HA automatic backup system creates extra large backups. I have only my own version of your SQLite backup. I suppose I shall learn the risks of this method with the first failure haha!

second_route 3mo ago

Nice. I switched to .backup for live DBs because .dump locked me out once. The .part + mv trick is clean though.