Backup SQLite database to AWS S3

You probably using SQLite as database or for caching in your application. SQLite is a lightweight file-based database.SQLite only supports one writer at a time per database file. It supports medium traffic website where concurrent writes less and reads are more. We will see how to take backup of full database and store in aws S3 using python.

Versions

Python supports SQLite natively. We will use inbuilt backup method which was introduced in Python 3.7.  

SQLite - 3.30.1
Python - 3.7
Boto3 - 1.13.3

Backup strategy

We will use below strategy to save database backup to aws S3 instead of directly saving into S3.

  • We will take a full database backup in the local system. You will be backing up all data.
  • After taking the database backup, we will copy to AWS S3 bucket.

SQLite full backup

SQLite backup method copies database from source connection to target connection. SQLite database is a single file which split into pages based on page size. When pages is set to 0, full database is being copied to target. This is full database backup.

def backup_sqlite(source, target):
"""
copies source database to target database
:param source: db file to be copied
:param target: target location
:return: returns status of the task
"""

status = True
backup_connection = None
source_connection = None
try:
source_connection = sqlite3.connect(source)
backup_connection = sqlite3.connect(target)
with backup_connection:
source_connection.backup(backup_connection, progress=progress)
print(f"Backup successful to {target}.!")
except sqlite3.Error as error:
print("Error while taking backup to {target}: ", error)
status = False
finally:
if backup_connection:
backup_connection.close()

if source_connection:
source_connection.close()
return status

Progress parameter accepts callable which can have simple print statement to complex logic.

def progress(status, remaining, total):
print(f'Copied {total - remaining} of {total} pages...')

Output#: Copied 989 of 989 pages...

Restoring full backup

Restoring full backup of SQLite is pretty simple as copying the backup to the desired location. This type of restoring SQLite database backup requires that application needs to be stop before restoring to avoid data corruption.

AWS S3 Configuration for backup

I have written a detailed about create S3 bucket and configuration here. This post will explain about creating IAM user, new S3 bucket with new policy for S3 access and backup related configuration.

If you have already user created and S3 bucket is configured, you can skip this step.

Script to upload

We need aws key id, aws secret key of aws user, aws s3 bucket name and prefix to upload. The below function will copy the file to aws s3 using given configuration.

def copy_file_to_s3(file_path, file_name, aws_params, s3_params):
"""
copies file from local disk to AWS S3 bucket.

:param file_path: source file path
:param file_name: name of the file to create in s3
:param aws_params: aws dict with key id, secret key
:param s3_params: s3 dict with bucket name and prefix
:return:
"""
status = True
key = aws_params['key']
secret_key = aws_params['secret_key']
bucket = s3_params['bucket']
prefix = s3_params.get('prefix', None)

s3_target_file_path = os.path.join(prefix, file_name) if prefix else file_name
s3_client = boto3.client('s3',
aws_access_key_id=key,
aws_secret_access_key=secret_key)

try:
s3_client.upload_file(file_path, bucket, s3_target_file_path)
print(f"Backup completed to s3.!")
except ClientError as e:
print(f"{e}")
status = False
return status

You can call the above function as below and use anywhere.

backup_file_path = "/hobby/db/db.sqlite3"
backup_file = "db.sqlite3"
aws_params = {
'key': 'your_aws_key_id',
'secret_key': 'your_aws_key_id',
}
s3_params = {
'bucket': 'backup-sample123',
'prefix': 'database'
}
copy_file_to_s3(file_path=backup_file_path,
file_name=backup_file,
aws_params=aws_params,
s3_params=s3_params)

Github gist

I have created GitHub git as a simple script to use. You can check that out.


Happy reading. Please comment if you are stuck or you have any questions.

Related posts
Archiving directory in python

Archiving directory in python

Durai Pandian May 09, 2020

Archiving multiple files or folder and subfolders as zip recursively in python using native python zipfile library and a...
Continue reading...
AWS S3 configuration for backup

AWS S3 configuration for backup

Durai Pandian May 07, 2020

Amazon Simple Storage Service (AWS S3) configuration for backup with expiration, maintaining versions and configuration ...
Continue reading...
Backup SQLite database to AWS S3

Backup SQLite database to AWS S3

Durai Pandian May 07, 2020

Taking SQLite database backup in python to AWS S3 with expiration and maintaining versioning using s3 lifecycle in manag...
Continue reading...

Comments
We'll never share your email with anyone else.