Periodic database dumps to ensure consistent backups

AlexandreRatte commented 1 week ago

Describe what you are trying to accomplish and why in non technical terms

I want to be able to perform periodic sqlite3 database dumps so that I can have a database backup adhering to the ACID principle. When used in conjunction with my preferred backup solution, this will enable me to restore my Frigate workload and its recordings in a known working state in case of disaster.

Describe the solution you'd like

I propose starting small by supporting a single optional daily database dump at midnight.

This could be accomplished by adding a single new database.daily_db_dumps boolean value in config.yaml with a default value of false. At midnight, trigger the .dump sqlite3 statement and output to the location defined by database.path with the added .dump.yyyyMMdd suffix.

Example:

database:
  # The path to store the SQLite DB (default: shown below)
  path: /custom_location/db/frigate.db
  # Optional: Enable daily database backups at midnight where the file name is the same as the above path, with the added .dump.yyyyMMdd suffix.
  daily_db_dumps: true

With the above example configuration, at midnight on 2024-06-26, Frigate would dump the daily backup into /custom_location/db/frigate.db.dump.20240626

Nice-to-haves:

Ability to schedule the time of day for the backup's execution.
Compress the database dump.
Keep only the latest X number of daily backups.

Describe alternatives you've considered

Having my backup solution trigger a pre-backup script that would exec into the running container, ensure the sqlite3 client is installed and execute sqlite3 frigate.db .dump | gzip -c > frigate.dump.gz. Perhaps this could actually be performed on the container's host instead?

Additional context

N/A

blakeblackshear commented 1 week ago

Any backup capabilities built in to frigate would almost certainly be based on https://litestream.io/

AlexandreRatte commented 1 week ago

I had a quick glance at Litestream and indeed having the ability to perform point-in-time restores sounds amazing. However, I for one would be more than happy to have a single consistent daily database dump I could then backup using my existing backup solution.

As I understand it, Litestream appears to require object storage, an NFS share or an SFTP server in order to perform its replication. I don't know how big of a slice of Frigate's user base is made up of homeowners/homelabbers like myself, but I suspect most of those users would prefer something adhering to the KISS principle.

Besides, as the Litestream maintainers pointed out, cron-based backups is a viable option and seems like a fairly low hanging fruit in terms of implementation.

That's just my $0.02, I will most definitely defer to your better judgment on this matter and I really appreciate the work you're putting into this project: Frigate rocks! 🍻

blakeblackshear commented 1 week ago

Sqlite is an acid compliant database. You can simply stop frigate and make a copy of the frigate.db file whenever you want.

mrplow commented 6 days ago

I use https://github.com/tiredofit/docker-db-backup for my compose backups, supports lots of DBs, including SQLite.

Litestream sounds like it would add a lot of functionality, but tiredofit/docker-db-backup is simple and doesn't require any additional code in any of the docker services I use

blakeblackshear / frigate