Self-hosting the open-source stack? Prove your backup actually restores before you need it

The Stack

Steps

1. 1

   Run the backup -> destroy -> restore round-trip and assert the data survived

   Create a known-good dataset, dump it to a backup file, then delete the live database to simulate a disaster. Restore from the dump into a fresh database and compare the rows against the original. If a single row differs, the check fails, which is the whole point: you learn the backup is broken here, not when the server is already gone.

   python3 - <<'PY'
   import sqlite3, os, sys
   
   SRC = "crm.db"
   BACKUP = "crm-backup.sql"
   for f in (SRC, BACKUP):
       if os.path.exists(f):
           os.remove(f)
   
   # 1. the live business data (known-good state)
   con = sqlite3.connect(SRC)
   con.executescript("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, mrr INTEGER);")
   con.executemany("INSERT INTO customers VALUES (?,?,?)", [(1, "Acme", 500), (2, "Globex", 1200), (3, "Initech", 300)])
   con.commit()
   orig = list(con.execute("SELECT id, name, mrr FROM customers ORDER BY id"))
   
   # 2. back it up (a plain SQL dump)
   with open(BACKUP, "w") as f:
       for line in con.iterdump():
           print(line, file=f)
   con.close()
   
   # 3. disaster: the live database is gone
   os.remove(SRC)
   if os.path.exists(SRC):
       print("BAD: could not simulate data loss")
       sys.exit(1)
   
   # 4. restore from the backup into a fresh database
   con2 = sqlite3.connect(SRC)
   with open(BACKUP) as f:
       con2.executescript(f.read())
   rest = list(con2.execute("SELECT id, name, mrr FROM customers ORDER BY id"))
   con2.close()
   
   # 5. the assertion the whole recipe exists for
   if rest != orig:
       print("BAD: restore does not match the original; this backup would have silently lost data (had " + str(len(orig)) + " rows, got " + str(len(rest)) + ")")
       sys.exit(1)
   
   print("restore OK: backed up " + str(len(orig)) + " rows, destroyed the live database, restored from the dump, and the restored data matches the original exactly. A backup you have never restored is a hope, not a backup")
   PY

2. 2

   Point it at your real stack (the part CI cannot do for you)

   Schedule the dump for your actual database (pg_dump / mysqldump / the app's built-in backup, or a snapshot tool like restic), ship a copy offsite, and periodically run this same restore-and-compare against a real backup on a throwaway host. Whether last night's cron actually fired, and whether the offsite copy is current, are the fenced parts, this recipe proves the procedure, your ops proves it ran.