https://litestream.io/alternatives/cron/

[ ] Litestream

  * Twitter
  * GitHub

  * Install
  * Guides
  * Reference
  * Blog
  * GitHub
  * Slack

Install

  * macOS
  * Linux (Debian)
  * Build from Source

General

  * Getting Started
  * Tips & Caveats
  * How it works
  * Alternatives

Infrastructure Guides

  * Running in a Docker container
  * Running in a Kubernetes cluster
  * Running as a Systemd service
  * Running as a Windows Service

Replica Guides

  * Replicating to Amazon S3
  * Replicating to Azure Blob Storage
  * Replicating to Backblaze B2
  * Replicating to DigitalOcean Spaces
  * Replicating to Scaleway Object Storage
  * Replicating to Google Cloud Storage
  * Replicating to Linode Object Storage
  * Replicating to an SFTP Server

Reference

  * Configuration File
  * Command: databases
  * Command: generations
  * Command: replicate
  * Command: restore
  * Command: snapshots
  * Command: version
  * Command: wal

On this page

  * Overview
      + Backup & compress
      + External storage using S3
      + Rolling backups
      + Monitoring backups
      + Restoring & testing backups
      + Configuring cron

Cron-based backup

Overview#

Sometimes Litestream can be overkill for projects with a small
database that do not have high durability requirements. In these
cases, it may be more appropriate to simply back up your database
daily or hourly. This approach can also be used in conjunction with
Litestream as a fallback. You can never have too many backups!

[?]
Do not use `cp` to back up SQLite databases. It is not
transactionally safe.

Backup & compress#

The sqlite3 command line interface comes with a built-in .backup
command which will safely backup your database to another file
location. You can run it directly from the command line:

sqlite3 /path/to/db '.backup /path/to/backup'

This will output your database to the file /path/to/backup. B-tree
databases like SQLite compress well so it's recommended to compress
your database:

gzip /path/to/backup

This will compress your database to a new file called /path/to/
backup.gz. You can then upload this file to external storage.

External storage using S3#

If you are using S3, you can use the AWS CLI to copy the file to an
S3 bucket:

aws s3 cp /path/to/backup.gz s3://mybucket/backup.gz

Rolling backups#

You can also add the day or hour to your filename to create a rolling
backup. For example, adding the hour to your filename gives you a
back up every hour for the last day. Newer backups for the same hour
will overwrite the previous one:

# 1-day, rolling hourly backup
aws s3 cp /path/to/backup.gz s3://mybucket/backup-`date +%H`.gz

# 1-month, rolling daily backup
aws s3 cp /path/to/backup.gz s3://mybucket/backup-`date +%d`.gz

# 1-month, rolling hourly backup
aws s3 cp /path/to/backup.gz s3://mybucket/backup-`date +%d%H`.gz

Monitoring backups#

It's recommended to call a "dead man" service after performing your
backup. This ensures that you will be notified if your backups stop
working for any reason. Dead Man's Snitch is one such service
although others also exist.

Restoring & testing backups#

A SQLite backup is simply the database file. To restore the database,
download it and decompress (if necessary) and move it into your
application's database path. Be sure to stop your application before
copying the database into place.

While it is rare for backups to go wrong with SQLite, it's always
good to regularly test your backups. To do this, simply download the
back up and run an integrity check against it:

sqlite3 /path/to/backup 'PRAGMA integrity_check'

Configuring cron#

cron is a built-in Unix tool for periodically running commands.
First, we'll create a script to run our backup commands. This is an
example that combines the commands from above.

#!/bin/bash -x

# Ensure script stops when commands fail.
set -e

# Backup & compress our database to the temp directory.
sqlite3 /path/to/db '.backup /tmp/db'
gzip /tmp/db

# Upload backup to S3 using a rolling daily naming scheme.
aws s3 cp /tmp/db.gz s3://mybucket/db-`date +%d`.gz

# Notify dead man that back up completed successfully.
curl -d s=$? https://nosnch.in/xxxxxxxxxx &> /dev/null

Then you can configure crontab to run this daily at midnight:

# Edit your cron jobs
crontab -e

# Add this to the end of the crontab
0 0 * * * /path/to/my_backup_script.sh