Backing Up the pyDE1 Database
When and Why
Backup strategies are always a subject of debate. If you’re reading this, I’m assuming you have already made some decision around your strategy. This page discusses some of the options for database compression as well as some hints about how to automate backups.
Even if you aren’t scheduling periodic backups, when a schema update is automatically performed from schema 2 or later, the database is backed up. This backup is in the same folder as the “live” database. It may be deleted when deemed appropriate.
Manual Backup
A manual backup of the pyDE1 database is possible using sqlite3
commands.
As with any connection to the database, it should be done using the user
that owns the database, pyde1
by default.
In an interactive session, one way to accomplish this is with
sudo -u pyde1 sqlite3 /var/lib/pyde1/pyde1.sqlite3
The backup
command can be executed from the prompt
sqlite> .help backup
.backup ?DB? FILE Backup DB (default "main") to FILE
--append Use the appendvfs
--async Write to FILE without journal and fsync()
Compression Options
The pyDE1 database is highly compressible. Selection of a compression tool
will depend on what has been installed on the system and the value of the
time vs. compression (disk space) tradeoff. Most Linux-based systems already
have gzip
installed. It is a reasonable compressor in terms of speed and
ratio. Mainly as it is already installed, it is the default for the post-facto
compression of backups made during the schema-upgrade process.
Here are some example compression times and results. They were tested on
a Raspberry Pi 3B+ with a 64 GB microSD, probably an upper-grade SanDisk.
The database has 3196 rows in the sequence
table.
Uncompressed it is 511.5 MB. Default compression was used for each program,
unless noted in the table. Time is “real” from the time
utility.
Program |
Time |
Compressed |
Fraction |
|
0:15 |
143.1 MB |
28.0 % |
|
0:23 |
101.2 MB |
19.8 % |
|
0:43 |
100.2 MB |
19.6 % |
|
1:26 |
98.6 MB |
19.3 % |
|
1:59 |
86.8 MB |
17.0 % |
|
32:36 |
76.6 MB |
15.0 % |
|
15:54 |
65.6 MB |
12.8 % |
For scheduled backups, xz
at the default compression level provides
the greatest disk-space savings of those tested. It may be “too slow”
even for overnight runs with very large databases.
Compression speed seems significantly faster on the Raspberry Pi 4, with xz compression taking about half the time (7:24).
Scheduled Backups
As pyDE1 configures its database to be multi-user, it is possible to schedule
backups with standard OS tools, such as cron
. One approach is to use
a script that performs the backup to a unique name and then compresses it
when complete.
#!/bin/sh
filename=$(date +'/home/pyde1/db_backup/pyde1.%Y-%m-%d_%H%M.sqlite3')
sqlite3 /var/lib/pyde1/pyde1.sqlite3 ".backup $filename"
xz $filename
and then scheduling that script for execution by pyde1
.
One way to do this is to edit the crontab
for the pyde1
user.
Do not run as root
as doing so may cause database files
to be owned by root
, preventing access by pyde1
.
sudo -u pyde1 crontab -e