Backup and Restore¶

Purpose¶

Preserve the Postgres database and all three Team telemetry tables, then verify recovery without overwriting serving state. The database holds governance, identity, budget and audit records together with application content: Agent instructions and private Chat transcripts. Encrypted Connector credentials in the database still require their separate keyring.

Prerequisites¶

An operator who can stop every writer and reach the database at DATABASE_URL as its owning role. Restoring into a new database also needs the Postgres superuser, for example through sudo -u postgres on the database host.
pg_dump, pg_restore and psql of the database server's major version on the machine that takes or restores the backup. pg_dump refuses a server newer than itself, and pg_restore cannot read a dump written by a newer pg_dump. The deployment helper and scripts/rollback_vps.sh refuse client tools of another major version than the server's.
A private destination with encrypted storage or access controls. pg_dump output is not encrypted.
Matching release, configuration and encryption keys retained with each backup.

Backups the deployment helper takes¶

Every existing-VPS deployment stops the four services and then writes a backup directory under DEPLOY_STATE_ROOT/backups before it migrates. The directory is named pre-<first 7 characters of the deployed commit>-<UTC timestamp>, has mode 700 and holds files only the service account can read. The deployment prints its path as Backup:. It contains:

mission-control.dump: pg_dump --format=custom --no-owner of the whole database. pg_dump waits at most 60 seconds for a table lock (--lock-wait-timeout=60s), so a session that holds a lock longer fails the backup instead of blocking the deployment. The helper reads the whole dump back and stops the deployment if it is truncated or unreadable.
mission-control.dump.toc: the dump's catalog from pg_restore --list, which must be non-empty.
catalog/: a copy of the YAML catalog.
Artifact deployments also copy runtime-contract.json, the restricted environment files (environment/*.env, mode 600) and the service units (units/), and record the SHA-256 of every file, the dump included, in checksums.json.
Source deployments record the dump's SHA-256 in mission-control.dump.sha256.

The helper passes the database password to pg_dump through the PGPASSWORD environment variable, never on a command line. Retention is unchanged: the helper never deletes a backup directory, so remove old ones yourself once you no longer need them. environment/shared.env in an artifact backup contains the database password; protect the backup like shared.env itself.

To return the serving database to such a backup on the existing VPS, use scripts/rollback_vps.sh --restore-database <backup directory>; see Restore the serving database.

Procedure¶

Take a manual backup, for example before a maintenance task outside a deployment.

Stop Console, gateway, runtime workers, Collector, external writers and cleanup jobs. Quiescence must cover the database and ClickHouse. The database dump and the ClickHouse backup are separate operations and are not a cross-database atomic snapshot.
From any working directory, with DATABASE_URL loaded from shared.env (set -a; . "$HOME/.config/mission-control/shared.env"; set +a), write a custom-format dump and its catalog to a new private directory:
```
backup=/srv/mission-control/backups/manual-20260924
mkdir -m 700 "$backup"
pg_dump --format=custom --no-owner --file "$backup/mission-control.dump" "$DATABASE_URL"
pg_restore --list "$backup/mission-control.dump" > "$backup/mission-control.dump.toc"
pg_restore --file=/dev/null "$backup/mission-control.dump"
sha256sum "$backup/mission-control.dump" > "$backup/mission-control.dump.sha256"
```
The catalog is read from the start of the dump only; pg_restore --file=/dev/null reads every byte and fails on a truncated dump. Passing the URL on the command line shows its password in the process list while the command runs. On a shared host, keep the password in ~/.pgpass (mode 600) and use a URL without it for manual commands.
Back up team_span_payloads, team_span_heads and team_span_deletions together through the encrypted ClickHouse backup disk and separate operator credentials. Use TeamObservationMaintenance.backup(id) and restoreIsolated(id) for the current whole-three-table maintenance contract; it is not a public scoped signed-manifest API.
Retain the dump's SHA-256 independently, outside the backup directory, together with the release commit and configuration hash. An adjacent checksum detects accidental corruption, not someone changing both files.
Restart the services.

Expected result¶

You retain a verified custom-format dump with its catalog and checksum, the matching encrypted telemetry backup, configuration and commit metadata, and separately stored encryption keys. Backups contain protected application state and must never be treated as public artifacts.

Verification¶

Restore the dump into a new, empty database, never over the serving one, and check it. On the database host, from any working directory, as a user who can run sudo:

dump=/srv/mission-control/backups/manual-20260924/mission-control.dump
sudo -v
sudo -u postgres psql -X -c "create database mission_control_restore_check owner mission_control template template0 locale_provider libc lc_collate 'C' lc_ctype 'C'"
sudo cat "$dump" | sudo -u postgres pg_restore --no-owner --role=mission_control --exit-on-error --dbname mission_control_restore_check

sudo -v asks for your password once, before the pipe. pg_restore reads the dump from standard input, so the postgres account needs no access to the backup directory, and --role=mission_control makes the owning role own every restored object.

Expected result: CREATE DATABASE, and pg_restore prints nothing and exits 0. Then, from platform/ of the release that took the backup, with DATABASE_URL pointing at the restored database, run pnpm --filter @mission-control/database db:migrate: it must report Applied 0 migration(s), which proves the restored schema is current for that release. Compare a few row counts (for example select count(*) from organization) with the serving database. Verify all three restored telemetry tables, retention, tombstones and one permitted and one forbidden read on isolated ClickHouse names. Drop the check database afterwards with sudo -u postgres psql -X -c 'drop database mission_control_restore_check'.

Restore the serving database¶

A restore replaces the serving database with the dump: every record written after the dump was taken is lost. Choose the procedure by where the dump comes from.

Restore a deployment backup on the existing VPS¶

scripts/rollback_vps.sh --restore-database restores a deployment backup (pre-<commit>-<timestamp>) or its own safety dump (pre-restore-<timestamp>) under DEPLOY_STATE_ROOT/backups, together with a rollback to a release that matches it. Before stopping anything, it checks the dump's checksum, that it reads completely, and that it records every migration the target release bundles. It then takes a safety dump of the current database, waiting at most 60 seconds for table locks, and replaces the database in one transaction. After a failure it reports whether that transaction was rolled back, committed or has an unknown outcome. See Restore the database with the rollback. It accepts no other directory, such as a manual backup; restore those into a new database.

The one transaction sets two limits on the database server:

Disk: until the restore commits, PostgreSQL holds a second copy of the data plus its write-ahead log on the disk of its data directory. Leave free space of about twice the database size there.
Locks: the restore holds about 6,700 locks, against a default lock table of about 7,800. Raising max_locks_per_transaction to 256 gives headroom and needs a PostgreSQL restart; Migrate from SQLite shows the statement.

If the lock table or the disk runs out, the restore fails and its transaction is rolled back, and restoring into a new database, below, is the fallback. That procedure restores object by object, so it needs neither the enlarged lock table nor the second copy.

Restore into a new database¶

Use this procedure for a dump that the rollback script does not accept, or when scripts/rollback_vps.sh --restore-database fails and names this page. The Postgres superuser drops the database, creates it again with the same statement as Bootstrap and first sign-in, and restores the dump into it on behalf of the owning role. Nothing of the replaced database survives, so keep a copy of it first: after a failed --restore-database, the safety dump it printed; otherwise a manual backup taken as in Procedure.

Stop every service and writer. On the existing VPS, as the service account:

systemctl --user stop mission-control-console.service mission-control-agent-runtime.service \
  mission-control-collector.service mission-control-gateway.service

After a failed --restore-database, they are already stopped.

Check the dump. On the database host, from any working directory, as a user who can read it:
```
dump=/home/mission-control/.local/share/mission-control/backups/pre-ccccccc-20260924T120000Z/mission-control.dump
sha256sum "$dump"
pg_restore --file=/dev/null "$dump"
```
Expected result: the printed hash equals the one recorded beside the dump: mission-control.dump.sha256 for manual backups, source deployments and safety dumps, or the mission-control.dump entry of checksums.json for artifact deployments. pg_restore prints nothing and exits 0.

Recreate the database and restore the dump. On the database host, as a user who can run sudo, set dump to the path of step 2 and run:

sudo -v
sudo -u postgres psql -X -v ON_ERROR_STOP=1 -c 'drop database mission_control' \
  -c "create database mission_control owner mission_control template template0 locale_provider libc lc_collate 'C' lc_ctype 'C'"
sudo cat "$dump" | sudo -u postgres pg_restore --no-owner --role=mission_control --exit-on-error --dbname mission_control
sudo -u postgres psql -X -d mission_control -c 'analyze'

Expected result: DROP DATABASE, CREATE DATABASE, no output from pg_restore, and ANALYZE. The restored tables are owned by mission_control, as the migrations created them. If drop database fails, the database is untouched and pg_restore stops at its first statement; see Failure recovery.

Start the release whose migrations the dump records. On the existing VPS, run a code-only rollback to that release from the repository root of your checkout, as described in Roll back to an earlier release: it points the links at the release, starts the services and verifies them. Elsewhere, start the services as usual.

Expected result: the services start and private /readyz is ready. A service that stops with STORAGE_MIGRATION_REQUIRED belongs to a release newer than the dump: start the release the dump was taken for, or run db:migrate from the newer release.

Failure recovery¶

pg_restore --list or pg_restore --file=/dev/null fails, or the catalog is empty: the dump is incomplete. Take a new one; never restore it.
A checksum mismatch: the dump changed after it was written. Use another backup.
A failed restore into the check database: drop it, create it again and repeat; the serving database is untouched.
scripts/rollback_vps.sh --restore-database fails: its message states what happened to the database (rolled back or never started, committed, or unknown with the query that settles it); see Restore the database with the rollback.
drop database fails with database "mission_control" is being accessed by other users: a session is still connected. List it with sudo -u postgres psql -X -c "select pid, application_name, client_addr from pg_stat_activity where datname = 'mission_control'", stop that process and repeat step 3.
pg_restore fails during step 3: keep the services stopped, correct the cause it names, then repeat step 3, which drops the partial database first. Or repeat it with an earlier verified dump.
Do not start services on a partial restore, and do not copy rows between databases by hand.

Connector OAuth credentials¶

The database includes encrypted OAuth client material, dynamically registered client records, authorization-code tokens and PKCE verifiers. Back up MCP_CREDENTIAL_ENCRYPTION_KEYS separately in protected secret storage; the database alone cannot recover them. Restore matching keys before reconnecting traffic. Machine-token caches are memory-only. Pending consent expires ten minutes after creation; restoring an old backup does not extend it.

Rotate keys as the gateway operator with database/environment access. From platform/:

Add a new 32-byte key under a new ID while retaining old keys. Set MCP_CREDENTIAL_ACTIVE_KEY_ID to the new ID and restart the gateway with that keyring. No new consent or YAML change is needed.
Run against the existing, migrated database at DATABASE_URL:

pnpm --filter @mission-control/gateway connector:rotate-credentials

Expect Re-encrypted N connector credential records. The command rewrites envelopes atomically without changing generations, token versions or grants. It prints no keys/tokens and refuses a database that is not migrated.
Rerun with the same active key; expect Re-encrypted 0 connector credential records. Retain previous keys until this succeeds and backups requiring them expire.
Remove retired keys and restart the gateway. Startup automatically loads invocation configuration; restart does not undo encryption rotation.

On failure, retain both keys, correct database/keyring access and retry. Do not delete unreadable rows or reset storage as a workaround. A lost key cannot be reconstructed from ciphertext; restore protected keys or establish new grants with a working deployment.