learning-website-django1-12 Exercise 2: Back Up the Database, and Prove You Can Restore It ========================================================================================== What needs backing up? The pages are in the content folder and can be rebuilt from it. But accounts, reading progress and redirect decisions exist ONLY in the database, and so does the search index (which takes a minute to rebuild). SQLite has a proper online backup that is safe while the site runs; copying the .sqlite3 file with cp is NOT (a write in the middle gives a damaged copy). Save as apps/core/backup.py: """Backing up the one thing that is not in the files: the database. The pages can always be rebuilt from the content folder (import_content), but accounts, reading progress, redirect decisions and the search index are only in the database, and rebuilding the index takes a minute. SQLite has a proper online backup, which is safe while the site is running; copying the .sqlite3 file with cp is NOT (a write in the middle gives a damaged copy).""" import re import sqlite3 from datetime import datetime from pathlib import Path NAME = re.compile(r"^lw-\d{8}-\d{6}\.sqlite3$") def backup_sqlite(database, folder, keep=7, now=None): """Copy the database into folder as lw-YYYYMMDD-HHMMSS.sqlite3, check the copy, delete all but the newest `keep`. Returns a dict describing the new backup. Raises if the copy is damaged (and deletes it): a backup nobody has ever opened is a hope, not a backup.""" folder = Path(folder) folder.mkdir(parents=True, exist_ok=True) stamp = (now or datetime.now()).strftime("%Y%m%d-%H%M%S") target = folder / f"lw-{stamp}.sqlite3" source = sqlite3.connect(f"file:{Path(database).as_posix()}?mode=ro", uri=True) try: copy = sqlite3.connect(target) try: source.backup(copy) finally: copy.close() finally: source.close() report = verify_backup(target) if not report["ok"]: target.unlink() raise RuntimeError(f"the backup failed its check and was removed: {report['problem']}") removed = prune(folder, keep) report.update(path=str(target), removed=removed) return report def verify_backup(path): """Open a backup the way a restore would: integrity check, and the tables that matter are present and not empty.""" conn = sqlite3.connect(f"file:{Path(path).as_posix()}?mode=ro", uri=True) try: try: result = conn.execute("PRAGMA integrity_check").fetchone()[0] except sqlite3.DatabaseError as error: # "file is not a database": not a backup return {"ok": False, "problem": str(error)} if result != "ok": return {"ok": False, "problem": f"integrity_check says {result}"} counts = {} for table in ("content_page", "page_fts"): try: counts[table] = conn.execute(f"SELECT count(*) FROM {table}").fetchone()[0] except sqlite3.DatabaseError as error: return {"ok": False, "problem": f"{table}: {error}"} if counts["content_page"] == 0: return {"ok": False, "problem": "content_page is empty"} users = conn.execute("SELECT count(*) FROM auth_user").fetchone()[0] return {"ok": True, "pages": counts["content_page"], "indexed": counts["page_fts"], "users": users, "bytes": Path(path).stat().st_size} finally: conn.close() def prune(folder, keep): """Delete the oldest backups beyond `keep`. Only files named exactly like our own are ever touched.""" ours = sorted((p for p in Path(folder).iterdir() if NAME.match(p.name)), reverse=True) old = ours[keep:] for path in old: path.unlink() return [p.name for p in old] Save as apps/core/management/commands/backup_db.py: from django.conf import settings from django.core.management.base import BaseCommand, CommandError from apps.core.backup import backup_sqlite, verify_backup class Command(BaseCommand): help = "Back up the SQLite database safely (online), check the copy, and keep only the newest few." def add_arguments(self, parser): parser.add_argument("folder", nargs="?", help="where to put backups (default: LW_BACKUP_DIR)") parser.add_argument("--keep", type=int, default=7) parser.add_argument("--verify", metavar="FILE", help="only check an existing backup file") def handle(self, folder=None, keep=7, verify=None, **options): if verify: report = verify_backup(verify) self.stdout.write(str(report)) if not report["ok"]: raise CommandError(report["problem"]) return folder = folder or getattr(settings, "BACKUP_DIR", None) if not folder: raise CommandError("give a folder, or set LW_BACKUP_DIR") database = settings.DATABASES["default"]["NAME"] try: report = backup_sqlite(database, folder, keep=keep) except RuntimeError as error: raise CommandError(str(error)) self.stdout.write(f"backup {report['path']}: {report['pages']} pages, {report['indexed']} indexed, " f"{report['users']} users, {report['bytes'] // 1024} KB; removed {len(report['removed'])} old") Every backup is opened the way a restore would: integrity check, content_page and page_fts present, pages not zero. A copy that fails is deleted and reported. Only files named exactly lw-YYYYMMDD-HHMMSS.sqlite3 are ever deleted by the cleanup, so a file you put in the folder yourself is safe (a test checks it). A bug the tests found: a damaged file made verify_backup RAISE (sqlite: "file is not a database") instead of returning "not ok". It now returns not-ok with the reason. The cron entry (deploy/lw-backup.cron): every night at 03:15, keep 14: # /etc/cron.d/lw-backup: a database backup every night at 03:15, keeping the newest 14, then a check of the newest one. # The service user owns the database, so it makes the backup. A failure writes to the system mail/journal. 15 3 * * * lw /srv/lw/venv/bin/python /srv/lw/current/manage.py backup_db /srv/lw/backups --keep 14 Setting: LW_BACKUP_DIR (or give the folder on the command line). The database file itself is chosen with LW_DATABASE, so in production it can live outside the release folders (/srv/lw/data/db.sqlite3). Run on the real database: python manage.py backup_db C:\lwdj\backups --keep 3 backup lw-20261007-140945.sqlite3: 4410 pages, 4410 indexed, 0 users, 159068 KB; removed 0 old (12 seconds) Restore, proved (copy the backup in, point LW_DATABASE at it, search it): LW_DATABASE=restored.sqlite3 python rest12.py database in use: restored.sqlite3 pages 4410 | search szeretnek on languages: 18 | ありがとう: 25 Rebuild from nothing, proved (an EMPTY database and the content folder, which is the recovery if every backup is lost and only the pages matter): migrate, then import_content: created 4411, updated 0, unchanged 0, errors 0 (39.8s) (42 s in all) search szeretnek on languages: 18, ありがとう: 25 (the same answers as the live database) So pages and search recover in about a minute from the files; accounts, progress and redirects recover only from a backup. Tests (tests/test_operations.py, shown in Exercise 3) cover: the copy is checked, the original is untouched, only the newest few are kept, only our own file names are deleted, a backup missing the search table or with no pages is rejected and removed, a damaged file does not verify, and the command works. What was not verified: the cron entry (no cron here), restoring while the site is running (stop the service first, see the rollback in Exercise 3), and backups kept off the server (a backup on the same disk does not survive the disk: copy them elsewhere, for example with rsync, and CHECK the copy). WHY THIS WORKS AS AN ANSWER --------------------------- A backup is only worth the restore you have tried. This one is checked when it is made, was restored and searched, and the second recovery route (rebuild from files) was timed.