canonical-ubuntu-qa team mailing list archive

[Tim Andersson <mp+460043@xxxxxxxxxxxxxxxxxx>]

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()