Merge ~juliank/autopkgtest-cloud:sqlite3-backup-api into autopkgtest-cloud:master

[Julian Andres Klode]

Code not run yet, needs testing on staging to make sure it works. Twice as slow as before, but this should take care of proper locking.

[Julian Andres Klode (juliank)]

Updated to run in steps of 16 pages, such that the lock can be released in between. The database currently has 17k iterations this way, giving plenty of time for other threads, maybe too wasteful (2/17k seconds per lock).

[Julian Andres Klode (juliank)]

Updated for 1024 pages per iteration (267 iterations, so 2/267 or 7ms per iteration followed by 250ms sleep in case someone else wants to write)

[Julian Andres Klode (juliank)]

Readjusted to move the database copying into init_db(), because we need to copy the db before updating the schema. Verified it works on staging now.

[Dave Jones (waveform)]

Looks good to me, particularly with the bigger pages value (so there's no more than a ~25% premium on an otherwise uninterrupted copy time). +1

review: Approve

diff --git a/charms/focal/autopkgtest-web/webcontrol/publish-db b/charms/focal/autopkgtest-web/webcontrol/publish-db
index 666ff81..6d6caae 100755
--- a/charms/focal/autopkgtest-web/webcontrol/publish-db
+++ b/charms/focal/autopkgtest-web/webcontrol/publish-db
@@ -22,11 +22,20 @@ archive_url = "http://archive.ubuntu.com/ubuntu"
 components = ["main", "restricted", "universe", "multiverse"]
 
 
-def init_db(path, path_current):
+def init_db(path, path_current, path_rw):
     """Create DB if it does not exist, and connect to it"""
 
     db = sqlite3.connect(path)
     db_current = sqlite3.connect(path_current)
+    db_rw = sqlite3.connect("file:%s?mode=ro" % path_rw, uri=True)
+
+    # Copy r/w database over
+    with db:
+        # Specifying a number of pages allows Python to let sqlite sleep,
+        # in turn allowing other clients to write to the db again.
+        db_rw.backup(db, pages=128 * 1024)
+    db_rw.close()
+
     c = db.cursor()
     current_c = db_current.cursor()
     try:
@@ -181,14 +190,13 @@ if __name__ == "__main__":
     target = config["web"]["database_ro"]
     target_new = "{}.new".format(target)
 
-    with open(config["web"]["database"], "rb") as src:
-        with open(target_new, "wb") as dest:
-            fcntl.flock(dest, fcntl.LOCK_EX | fcntl.LOCK_NB)
-            shutil.copyfileobj(src, dest)
-            dest.flush()
-            os.fsync(dest.fileno())
-
-    db_con = init_db(target_new, target)
+    try:
+        # systemd makes sure to not run us in parallel, so we can safely
+        # delete any leftover file.
+        os.unlink(target_new)
+    except FileNotFoundError:
+        pass
+    db_con = init_db(target_new, target, config["web"]["database"])
 
     for row in db_con.execute("SELECT DISTINCT release FROM test"):
         get_sources(db_con, row[0])