Janady13/missionmatch / docs/RUNBOOK_RESTORE.md

1# RUNBOOK: RESTORING missionMATCH FROM BACKUP 2 3Written 28 July 2026 from a drill that actually ran, not from theory. Every step 4below was executed and timed. Where the drill found a prerequisite nobody knew 5about, it says so, because that is the step a real outage would have discovered 6at the worst possible moment. 7 8Last successful drill: 28 July 2026. Restore time 7 seconds for the 95 MB tenant 9database. Schema v80. All ten reconciled tables matched, including 6,676 10opportunity records and 56,470 audit rows. 11 12-------------------------------------------------------------------------------- 130. THE PREREQUISITE THE DRILL FOUND 14-------------------------------------------------------------------------------- 15 16RESTORING REQUIRES A SUPERUSER TO CREATE THE pgvector EXTENSION FIRST. 17 18The first drill produced 60 pg_restore errors and silently lost the two most 19important tables in the platform, candidates and opportunity_records, because: 20 21 ERROR: permission denied to create extension "vector" 22 ERROR: extension "vector" does not exist 23 ERROR: type "public.vector" does not exist 24 25The dump contains CREATE EXTENSION vector, but creating an extension requires 26superuser and the migrator role deliberately is not one. So a restore run as the 27migrator into a fresh database SILENTLY produces a database that looks restored, 28reports the right schema version, and is missing every candidate and every 29opportunity. 30 31 ALWAYS, BEFORE pg_restore, AS A SUPERUSER: 32 CREATE EXTENSION IF NOT EXISTS vector; 33 34 ALWAYS, AFTER pg_restore: reconcile row counts. A restore that reports 35 success is not a restore that worked. 36 37-------------------------------------------------------------------------------- 381. WHAT EXISTS AND WHERE 39-------------------------------------------------------------------------------- 40 41 Backup sets Bubbles /var/backups/missionmatch-db/ 42 daily/YYYYMMDD/ 14 retained 43 weekly/YYYYMMDD/ 8 retained, promoted on Sundays 44 Falls back here because /mnt/storage is NOT mounted (the off 45 disk drive is dead). This survives accidental deletion and a bad 46 deploy. It does NOT survive a Bubbles root disk failure. Mount a 47 replacement drive at /mnt/storage and the job uses it 48 automatically with no change. 49 50 Per set control-plane-*.dump.gpg identity, users, sessions, the 51 tenant directory. From 52 megamind-agi:5433. 53 tenant-missionmatch-*.dump.gpg all business data. From M4:5432. 54 staging-*.dump.gpg the staging environment. 55 MANIFEST.sha256 sha256 of every artifact. 56 57 Encryption AES256 symmetric, passphrase at /etc/missionmatch/backup.key, 58 root owned 0600, present identically on Bubbles AND on 59 megamind-agi (sha 02aa72b7e8c58818) so either host can restore 60 either dump. IF THAT FILE IS LOST, EVERY BACKUP IS UNREADABLE. 61 It exists on exactly two machines and nowhere else. 62 63 Schedule 02:45 megamind-agi dumps the control plane and pushes it 64 03:05 Bubbles dumps tenant and staging, ingests the pushed 65 control plane dump, writes the manifest, prunes 66 Both are systemd timers with OnFailure notification. 67 68-------------------------------------------------------------------------------- 692. VERIFY A BACKUP SET BEFORE TRUSTING IT 70-------------------------------------------------------------------------------- 71 72 cd /var/backups/missionmatch-db/daily/YYYYMMDD 73 sha256sum -c MANIFEST.sha256 74 ls -la # THREE artifacts expected, one per database 75 76A set with only two artifacts is missing the control plane and is NOT a 77complete backup. The nightly job fails loudly in that case; if it did not, the 78push from megamind-agi is broken. 79 80-------------------------------------------------------------------------------- 813. RESTORE, THE PROCEDURE THAT WAS DRILLED 82-------------------------------------------------------------------------------- 83 84 # 1. Decrypt. --yes is REQUIRED if the output file already exists, or gpg 85 # fails with "handle plaintext failed" and the backup looks corrupt when 86 # it is fine. This cost one drill run to discover. 87 gpg --batch --yes --quiet --decrypt \ 88 --passphrase-file /etc/missionmatch/backup.key \ 89 --output /tmp/restore.dump \ 90 tenant-missionmatch-YYYYMMDD-HHMMSS.dump.gpg 91 92 # 2. AS A SUPERUSER on the target host, prepare the database. 93 psql -U postgres -d postgres -c "CREATE DATABASE missionmatch_new OWNER missionmatch_migrator" 94 psql -U postgres -d missionmatch_new -c "CREATE EXTENSION IF NOT EXISTS vector" 95 96 # 3. Restore. 97 pg_restore --clean --if-exists --no-owner --no-privileges \ 98 --dbname="postgres://missionmatch_migrator:PW@HOST:5432/missionmatch_new" \ 99 /tmp/restore.dump 100 101 # 4. VERIFY. Not optional. A restore is not done until it reconciles. 102 # Schema version must equal the source. 103 psql ... -c "select max(version_id) from goose_db_version" 104 # Row counts must equal the source, table by table. 105 for t in candidates requirements users organizations opportunity_records \ 106 audit_logs tracked_opportunities clients requisitions applications; do 107 echo "$t: $(psql ... -tAc "select count(*) from $t")" 108 done 109 110 # 5. Shred the cleartext dump. It carries candidate PII and is CUI adjacent. 111 shred -u /tmp/restore.dump 112 113-------------------------------------------------------------------------------- 1144. THE DRILL, RUN AT EVERY PHASE CLOSE 115-------------------------------------------------------------------------------- 116 117 sudo /usr/local/sbin/mm-restore-drill.sh [YYYYMMDD] 118 119It verifies the manifest, decrypts, restores into missionmatch_restore_drill on 120the staging host, TIMES the restore, compares the schema version, reconciles ten 121tables, and fails on any mismatch. The drill database is pre created and owned by 122the migrator; each run replaces its contents with --clean, so nothing lingers. 123 124TWO DELIBERATE DEVIATIONS from the protocol wording, both recorded in 125BUILD_STATE: the drill restores onto the staging HOST into a scratch database 126rather than over staging itself, because destroying the verification environment 127at every phase close cannot be the intent; and it reuses a pre created database 128because the migrator deliberately lacks CREATEDB and weakening it to save a 129setup step would trade a real separation of duties for convenience. 130 131-------------------------------------------------------------------------------- 1325. WHAT IS STILL NOT PROTECTED 133-------------------------------------------------------------------------------- 134 135 A Bubbles root disk failure destroys every backup, because /mnt/storage is not 136 mounted and the fallback is local. Mounting a replacement drive fixes this with 137 no code change. THIS IS THE REMAINING GAP IN THE LARGEST RISK. 138 139 There is no off site copy. Everything is in one building. 140 141 The passphrase exists on two machines in the same building. Losing both loses 142 every backup irreversibly. 143 144 Restore has been drilled for the TENANT database. The control plane dump is 145 taken, pushed, integrity checked, and decrypt verified, but a full control 146 plane restore has not yet been timed. 147