# RUNBOOK: RESTORING missionMATCH FROM BACKUP Written 28 July 2026 from a drill that actually ran, not from theory. Every step below was executed and timed. Where the drill found a prerequisite nobody knew about, it says so, because that is the step a real outage would have discovered at the worst possible moment. Last successful drill: 28 July 2026. Restore time 7 seconds for the 95 MB tenant database. Schema v80. All ten reconciled tables matched, including 6,676 opportunity records and 56,470 audit rows. -------------------------------------------------------------------------------- 0. THE PREREQUISITE THE DRILL FOUND -------------------------------------------------------------------------------- RESTORING REQUIRES A SUPERUSER TO CREATE THE pgvector EXTENSION FIRST. The first drill produced 60 pg_restore errors and silently lost the two most important tables in the platform, candidates and opportunity_records, because: ERROR: permission denied to create extension "vector" ERROR: extension "vector" does not exist ERROR: type "public.vector" does not exist The dump contains CREATE EXTENSION vector, but creating an extension requires superuser and the migrator role deliberately is not one. So a restore run as the migrator into a fresh database SILENTLY produces a database that looks restored, reports the right schema version, and is missing every candidate and every opportunity. ALWAYS, BEFORE pg_restore, AS A SUPERUSER: CREATE EXTENSION IF NOT EXISTS vector; ALWAYS, AFTER pg_restore: reconcile row counts. A restore that reports success is not a restore that worked. -------------------------------------------------------------------------------- 1. WHAT EXISTS AND WHERE -------------------------------------------------------------------------------- Backup sets Bubbles /var/backups/missionmatch-db/ daily/YYYYMMDD/ 14 retained weekly/YYYYMMDD/ 8 retained, promoted on Sundays Falls back here because /mnt/storage is NOT mounted (the off disk drive is dead). This survives accidental deletion and a bad deploy. It does NOT survive a Bubbles root disk failure. Mount a replacement drive at /mnt/storage and the job uses it automatically with no change. Per set control-plane-*.dump.gpg identity, users, sessions, the tenant directory. From megamind-agi:5433. tenant-missionmatch-*.dump.gpg all business data. From M4:5432. staging-*.dump.gpg the staging environment. MANIFEST.sha256 sha256 of every artifact. Encryption AES256 symmetric, passphrase at /etc/missionmatch/backup.key, root owned 0600, present identically on Bubbles AND on megamind-agi (sha 02aa72b7e8c58818) so either host can restore either dump. IF THAT FILE IS LOST, EVERY BACKUP IS UNREADABLE. It exists on exactly two machines and nowhere else. Schedule 02:45 megamind-agi dumps the control plane and pushes it 03:05 Bubbles dumps tenant and staging, ingests the pushed control plane dump, writes the manifest, prunes Both are systemd timers with OnFailure notification. -------------------------------------------------------------------------------- 2. VERIFY A BACKUP SET BEFORE TRUSTING IT -------------------------------------------------------------------------------- cd /var/backups/missionmatch-db/daily/YYYYMMDD sha256sum -c MANIFEST.sha256 ls -la # THREE artifacts expected, one per database A set with only two artifacts is missing the control plane and is NOT a complete backup. The nightly job fails loudly in that case; if it did not, the push from megamind-agi is broken. -------------------------------------------------------------------------------- 3. RESTORE, THE PROCEDURE THAT WAS DRILLED -------------------------------------------------------------------------------- # 1. Decrypt. --yes is REQUIRED if the output file already exists, or gpg # fails with "handle plaintext failed" and the backup looks corrupt when # it is fine. This cost one drill run to discover. gpg --batch --yes --quiet --decrypt \ --passphrase-file /etc/missionmatch/backup.key \ --output /tmp/restore.dump \ tenant-missionmatch-YYYYMMDD-HHMMSS.dump.gpg # 2. AS A SUPERUSER on the target host, prepare the database. psql -U postgres -d postgres -c "CREATE DATABASE missionmatch_new OWNER missionmatch_migrator" psql -U postgres -d missionmatch_new -c "CREATE EXTENSION IF NOT EXISTS vector" # 3. Restore. pg_restore --clean --if-exists --no-owner --no-privileges \ --dbname="postgres://missionmatch_migrator:PW@HOST:5432/missionmatch_new" \ /tmp/restore.dump # 4. VERIFY. Not optional. A restore is not done until it reconciles. # Schema version must equal the source. psql ... -c "select max(version_id) from goose_db_version" # Row counts must equal the source, table by table. for t in candidates requirements users organizations opportunity_records \ audit_logs tracked_opportunities clients requisitions applications; do echo "$t: $(psql ... -tAc "select count(*) from $t")" done # 5. Shred the cleartext dump. It carries candidate PII and is CUI adjacent. shred -u /tmp/restore.dump -------------------------------------------------------------------------------- 4. THE DRILL, RUN AT EVERY PHASE CLOSE -------------------------------------------------------------------------------- sudo /usr/local/sbin/mm-restore-drill.sh [YYYYMMDD] It verifies the manifest, decrypts, restores into missionmatch_restore_drill on the staging host, TIMES the restore, compares the schema version, reconciles ten tables, and fails on any mismatch. The drill database is pre created and owned by the migrator; each run replaces its contents with --clean, so nothing lingers. TWO DELIBERATE DEVIATIONS from the protocol wording, both recorded in BUILD_STATE: the drill restores onto the staging HOST into a scratch database rather than over staging itself, because destroying the verification environment at every phase close cannot be the intent; and it reuses a pre created database because the migrator deliberately lacks CREATEDB and weakening it to save a setup step would trade a real separation of duties for convenience. -------------------------------------------------------------------------------- 5. WHAT IS STILL NOT PROTECTED -------------------------------------------------------------------------------- A Bubbles root disk failure destroys every backup, because /mnt/storage is not mounted and the fallback is local. Mounting a replacement drive fixes this with no code change. THIS IS THE REMAINING GAP IN THE LARGEST RISK. There is no off site copy. Everything is in one building. The passphrase exists on two machines in the same building. Losing both loses every backup irreversibly. Restore has been drilled for the TENANT database. The control plane dump is taken, pushed, integrity checked, and decrypt verified, but a full control plane restore has not yet been timed.