SQLite is a popular database used in many applications, and is also widely used in the EC2 environment. Although perceived as a more lightweight database compared to MySQL or PostGreSQL, SQLite is broadly used for critical production applications. As SQLite supports transactions and concurrency, it is important to back it up in a consistent manner to avoid any corruptions during recovery.
On an EC2 instance, an SQLite database may be located either entirely in-memory, or as a single file on ephemeral storage or an EBS volume. In the latter case, where the SQLite file database resides on an EBS volume, the fastest way of backing up the database would be to acquire a shared lock before the snapshot of that EBS volume is taken. The lock would ensure that no process writes data to the file at the point-in-time of the snapshot (although read-only access will be allowed). Immediately after the snapshot is triggered, the lock can be released.
As this backup method is only applicable for SQLite databases located on EBS volumes, I chose to describe another method that can work no matter where the primary database is stored. By using the official backup mechanism of SQLite 3, a consistent copy of the file is created in another location on disk. Since SQLite databases are typically not extremely large, and the copy made locally, the time and resources consumed during this process should not interfere with the production environment. So, in this method, SQLLite will be making a consistent backup of the database on an EBS volume immediately before the snapshots are taken.
This backup process will work irrespective of the location of the source database: it may be on an EBS volume, but more importantly, the SQLite database may also be located in-memory or on ephemeral storage. The backup will be usable as long as the target backup is on an EBS volume that is being snapshotted. As an alternative to EBS snapshots, one may also consider the following backup approach: to simply copy the database aside to S3 or another remote location. However, using EBS snapshots is a better backup practice, as it will not only ensure that the database file is backed up, but will also allow recovering the complete EC2 instance, if needed.
Example: The backed-up instance in this example is a Linux instance. Although it doesn’t have to work this way, this example was written for Cloud Protection manager (CPM) as a “before” script (a pre-snapshot script). This means that the script actually resides on the backup server and uses SSH to connect to the SQLite instance to perform the backup. This is a very effective way to manage backup operations, as all the backup scripts of the different instances and applications reside and are managed from one central location. Here is the script (/cpmdata/script/before_<policy_name>):
#!/bin/bash remote=<ssh username>@<SQLIte Instance address> ssh -i <SSH private key> $remote <<EOFSSH bash <<EOFBASH /usr/bin/sqlite3 /<path on EBS volume>/mydb.backup <<ENDOFSQLITE3 .timeout 30000 .backup n2w_source.backup ENDOFSQLITE3 EOFBASH EOFSSH if [ $? -gt 0 ]; then
echo “Failed creating consistent copy of sqlite db” 1>&2
echo “created consistent copy of sqlite db at <location>” 1>&2
A short explanation: This script is executed right before the EBS snapshots are taken. It connects to the SQLite instance using SSH and issues an SQLite3 backup command. It gives the backup process a pre-defined timeout that should match the size of the database file (as the database gets bigger the copy takes longer). Also, if using with CPM, the timeout of the backup policy should be larger than the timeout in the script, to be used only if the script hangs and its own timeout doesn’t work for some reason.
With this simple process, you can make sure your EC2 backup contains a consistent copy of the SQLite database, even if the original database runs in-memory or on ephemeral storage.
N2WS is an enterprise-class backup solution for EC2 based on EBS & RDS snapshots. It supports consistent backup of applications on Linux servers as well as Windows servers. N2WS is sold on AWS Marketplace with prices starting at $0. See pricing or try it for free.