What I’m trying to do and why
A corollary to #231 is database-level snapshots. It would be great to be able to do binary backups of database instead of the current “SQL dump” approach. The downside of “SQL dumps” is they can take a very long time to load for large backups, and take a lot of extra space. By using filesystem snapshots or just locking the database for the backup’s duration, you get the binary files directly and restoring from backups is just like restoring the database from a crash...
Other notes / implementation ideas
Many database support issuing a command to flush everything to disk and prepare for catastrophe. You have
FLUSH TABLES WITH READ LOCK in MySQL/MariaDB, which needs to be followed by a
UNLOCK TABLES when finished with the backup. There’s no equivalent that I could find on the other side, but there is
CHECKPOINT in PostgreSQL which flushes all WAL files and allow for a shorter recovery. PostgreSQL would require filesystem snapshots for the backups to be consistent, however.
I know that both of those could be implemented with simple shell scripts, but I’m wondering if you’d be interested in merging this directly in the Python code, which might be easier (although I haven’t looked).
Worst case, this issue can track the shell script implementation too. :)