Tim Andersson has proposed merging
~andersson123/autopkgtest-cloud:sqlite-db-backup into autopkgtest-cloud:master.
Requested reviews:
Canonical's Ubuntu QA (canonical-ubuntu-qa)
For more details, see:
https://code.launchpad.net/~andersson123/autopkgtest-cloud/+git/autopkgtest-cloud/+merge/460043
--
Your team Canonical's Ubuntu QA is requested to review the proposed merge of
~andersson123/autopkgtest-cloud:sqlite-db-backup into autopkgtest-cloud:master.
diff --git a/charms/focal/autopkgtest-web/units/db-backup.service b/charms/focal/autopkgtest-web/units/db-backup.service
new file mode 100644
index 0000000..9c3d038
--- /dev/null
+++ b/charms/focal/autopkgtest-web/units/db-backup.service
@@ -0,0 +1,8 @@
+[Unit]
+Description=Backup sql database
+
+[Service]
+Type=oneshot
+User=ubuntu
+EnvironmentFile=/home/ubuntu/public-swift-creds
+ExecStart=/home/ubuntu/webcontrol/db-backup
diff --git a/charms/focal/autopkgtest-web/units/db-backup.timer b/charms/focal/autopkgtest-web/units/db-backup.timer
new file mode 100644
index 0000000..222663a
--- /dev/null
+++ b/charms/focal/autopkgtest-web/units/db-backup.timer
@@ -0,0 +1,8 @@
+[Unit]
+Description=Backup sql database
+
+[Timer]
+OnCalendar=*-*-* 00:05:00
+
+[Install]
+WantedBy=autopkgtest-web.target
\ No newline at end of file
diff --git a/charms/focal/autopkgtest-web/webcontrol/db-backup b/charms/focal/autopkgtest-web/webcontrol/db-backup
new file mode 100755
index 0000000..60f9634
--- /dev/null
+++ b/charms/focal/autopkgtest-web/webcontrol/db-backup
@@ -0,0 +1,203 @@
+#!/usr/bin/python3
+"""
+This script periodically backs up the sqlite3 db to swift storage
+and clears up old backups
+"""
+
+import atexit
+import configparser
+import datetime
+import gzip
+import logging
+import os
+import shutil
+import sqlite3
+import sys
+import time
+
+import swiftclient
+from helpers.utils import init_db
+
+DB_PATH = ""
+DB_COPY_LOCATION = ""
+CONTAINER_NAME = "db-backups"
+MAX_DAYS = 7
+
+
+def db_connect() -> sqlite3.Connection:
+ """
+ Establish connection to sqlite3 db
+ """
+ global DB_PATH
+ cp = configparser.ConfigParser()
+ cp.read(os.path.expanduser("~ubuntu/autopkgtest-cloud.conf"))
+ DB_PATH = cp["web"]["database"]
+
+ db_con = init_db(cp["web"]["database"])
+
+ return db_con
+
+
+def is_db_locked(db_con: sqlite3.Connection) -> bool:
+ """
+ Check if sqlite3 db is locked, if not, continue with rest of script
+ """
+ c = db_con.cursor()
+ try:
+ c.execute("BEGIN EXECUTE IMMEDIATE")
+ except sqlite3.OperationalError as e:
+ if "database is locked" in str(e):
+ logging.info(
+ "Database is locked, full error: %s\nsleeping..." % str(e)
+ )
+ time.sleep(5)
+ return True
+ elif "database disk image is malformed" in str(e):
+ logging.info(
+ "Database is corrupted! Exiting! Full error: %s" % str(e)
+ )
+ sys.exit(1)
+ else:
+ logging.info("Locking db failed: %s" % str(e))
+ time.sleep(5)
+ return True
+ c.execute("END")
+ return False
+
+
+def copy_db():
+ """
+ Copy database to /tmp
+ """
+ global DB_PATH
+ global DB_COPY_LOCATION
+ db_name = DB_PATH.split("/")
+ DB_COPY_LOCATION = "/tmp/%s" % db_name
+ shutil.copyfile(DB_PATH, DB_COPY_LOCATION)
+
+
+def compress_db():
+ # use gzip to compress database
+ global DB_COPY_LOCATION
+ with open(DB_COPY_LOCATION, "rb") as f_in, gzip.open(
+ "%s.gz" % DB_COPY_LOCATION, "wb"
+ ) as f_out:
+ f_out.writelines(f_in)
+
+
+def init_swift_con() -> swiftclient.Connection:
+ """
+ Establish connection to swift storage
+ """
+ swift_creds = {
+ "authurl": os.environ["OS_AUTH_URL"],
+ "user": os.environ["OS_USERNAME"],
+ "key": os.environ["OS_PASSWORD"],
+ "os_options": {
+ "region_name": os.environ["OS_REGION_NAME"],
+ "project_domain_name": os.environ["OS_PROJECT_DOMAIN_NAME"],
+ "project_name": os.environ["OS_PROJECT_NAME"],
+ "user_domain_name": os.environ["OS_USER_DOMAIN_NAME"],
+ },
+ "auth_version": 3,
+ }
+ swift_conn = swiftclient.Connection(**swift_creds)
+ return swift_conn
+
+
+def create_container_if_it_doesnt_exist(swift_conn: swiftclient.Connection):
+ """
+ create db-backups container if it doesn't already exist
+ """
+ global CONTAINER_NAME
+ try:
+ swift_conn.get_container(CONTAINER_NAME)
+ except swiftclient.exceptions.ClientException:
+ swift_conn.put_container(
+ CONTAINER_NAME,
+ )
+
+
+def upload_backup_to_db(
+ swift_conn: swiftclient.Connection,
+) -> swiftclient.Connection:
+ """
+ Upload compressed database to swift storage under container db-backups
+ """
+ now = datetime.datetime.now().strftime("%Y/%m/%d/%H_%M_%S")
+ object_path = "%s/%s" % (now, DB_PATH.split("/")[-1] + ".gz")
+ for _ in range(5):
+ try:
+ swift_conn.put_object(
+ CONTAINER_NAME,
+ object_path,
+ "%s.gz" % DB_COPY_LOCATION,
+ content_type="text/plain; charset=UTF-8",
+ headers={"Content-Encoding": "gzip"},
+ )
+ break
+ except swiftclient.exceptions.ClientException as e:
+ print("exception: %s" % str(e))
+ swift_conn = init_swift_con()
+ return swift_conn
+
+
+def delete_old_backups(
+ swift_conn: swiftclient.Connection,
+) -> swiftclient.Connection:
+ """
+ Delete objects in db-backups container that are older than 7 days
+ """
+ print("Removing old db backups...")
+ _, objects = swift_conn.get_container(CONTAINER_NAME)
+ now = datetime.datetime.now()
+
+ for obj in objects:
+ last_modified = obj["last_modified"].split(".")[0]
+ timestamp = datetime.datetime.strptime(
+ last_modified, "%Y-%m-%dT%H:%M:%S"
+ )
+ diff = now - timestamp
+ if diff > datetime.timedelta(days=MAX_DAYS):
+ print("Deleting %s" % obj["name"])
+ for _ in range(5):
+ try:
+ swift_conn.delete_object(CONTAINER_NAME, obj["name"])
+ break
+ except swiftclient.exceptions.ClientException as _:
+ swift_conn = init_swift_con()
+ return swift_conn
+
+
+def cleanup():
+ """
+ Delete db and compressed db under /tmp
+ """
+ if os.path.isfile(DB_COPY_LOCATION):
+ os.remove(DB_COPY_LOCATION)
+ if os.path.isfile("%s.gz" % DB_COPY_LOCATION):
+ os.remove("%s.gz" % DB_COPY_LOCATION)
+
+
+if __name__ == "__main__":
+ # connect to db
+ db_con = db_connect()
+ # check to see if database is locked
+ while is_db_locked(db_con):
+ pass
+ # if it's not locked, copy it to tmp location
+ copy_db()
+ # compress it
+ compress_db()
+ # register cleanup if anything fails
+ atexit.register(cleanup)
+ # initialise swift conn
+ swift_conn = init_swift_con()
+ # create container if it doesn't exist
+ create_container_if_it_doesnt_exist(swift_conn)
+ # upload to swift container
+ swift_conn = upload_backup_to_db(swift_conn)
+ # Remove old results
+ swift_conn = delete_old_backups(swift_conn)
+ # run cleanup
+ cleanup()
--
Mailing list: https://launchpad.net/~canonical-ubuntu-qa
Post to : canonical-ubuntu-qa@lists.launchpad.net
Unsubscribe : https://launchpad.net/~canonical-ubuntu-qa
More help : https://help.launchpad.net/ListHelp
