Backup and restore

Deckgauge's durable state lives across two Postgres databases and a set of ClickHouse tables, all inside Docker named volumes — a volume loss (docker compose down -v, or disk failure) loses all of it. One command takes a full backup; another restores it.

deckgauge · pnpm backup

postgres.pgdump cockpit database — boards, sync state, users

keycloak.pgdump realm, clients, and user accounts

clickhouse/*.native.gz per-table, deduplicated with FINAL

Redis not included — BullMQ queue state is transient and regenerable

What ends up in the tarball, and the one store it deliberately skips.

Public snapshot noteThe published open-source snapshot doesn't include scripts/backup.sh or scripts/restore.sh — only scripts/apply-clickhouse-schemas.sh is allowlisted so far, pending an audit of the rest of scripts/. Everything on this page assumes a full clone of the private repo; from the public snapshot, these commands won't be there to run.

What a backup covers

./scripts/backup.sh takes a consistent snapshot of everything that would otherwise be lost for good:

Postgres — a pg_dump of the cockpit database, custom format.
Keycloak's database — a separate pg_dump of the keycloak database (its own Postgres instance).
ClickHouse — every table in clickhouse/schemas/ (Jira, GitHub, GitLab merge requests/commits/reviews/issues, ADO work items/transitions/PRs/commits/reviews, the identity map, the timesheet CapEx/OpEx classification mirror, and jira_flow_efficiency_state — the only materialized-view state table that currently exists) exported table-by-table over HTTP with FINAL, so duplicate rows are resolved before export.
Uploads volume — file attachments from the api container.

Everything is written to one timestamped tarball in ./backups/, alongside a MANIFEST.txt — the only authoritative record of which tables a given archive actually contains. Read it before you trust an archive, especially an older one taken before a schema change.

Older archives may be missing three tablesgitlab_reviews, gitlab_issues, and board_item_classification (the CapEx/OpEx classification mirror) were added to the exported table list after earlier releases; an archive taken with an older backup.sh silently lacks all three, and restoring it can show empty GitLab review/issue history or empty CapEx/OpEx history with no error. The classification mirror in particular has no backup-driven rebuild path outside this table — recovering it from an archive that predates this fix means re-running its one-off backfill script by hand. Check MANIFEST.txt against clickhouse/schemas/ before trusting an archive taken before this fix.

What is NOT backed up

Redis — BullMQ queue state is transient; it regenerates as sync jobs re-run.
.env and config/jira.yaml — these live on the host filesystem, not in a Docker volume, so they're covered by your normal file backups instead.

How to back up and restore

Take a backup — ./scripts/backup.sh (or --dest/--tag to customize the destination or label).
Restore from it — ./scripts/restore.sh ./backups/<file>.tar.gz. It starts Postgres, Redis, ClickHouse, and Keycloak's own database (if they aren't already up), stops api, web, worker, and keycloak, drops and recreates both Postgres databases from the dumps, truncates and reloads each ClickHouse table, clears the uploads volume and extracts the backup over it, then reruns migrate:deploy in case the schema advanced since the backup was taken.
Confirm — you're prompted with y/N before anything is overwritten (skip it with --yes); restore ends with health checks against the API, web, and ClickHouse.

Two flags narrow what a restore touches: --skip-clickhouse and --skip-uploads.

Under the hood

Restore reapplies the ClickHouse DDL from clickhouse/schemas/ before loading data — every schema file uses IF NOT EXISTS, so this is safe even against a completely fresh ClickHouse instance with no tables yet.

Before you run down -vdocker compose down -v destroys the named volumes irreversibly — Postgres, Keycloak's database, and ClickHouse all revert to empty. Open the backup's MANIFEST.txt first and confirm it lists every table you need: the current scripts capture gitlab_reviews, gitlab_issues, and board_item_classification along with everything else in clickhouse/schemas/, but an archive taken with an older version of backup.sh (see above) may still be missing them — and MANIFEST.txt is the only way to know before the volumes are already gone. Only proceed once you've confirmed the archive actually has what you need, or captured the gap separately.

For a full volume loss, the disaster-recovery sequence is: take a baseline backup, docker compose down -v, docker compose up -d for fresh volumes, migrate:deploy to recreate the schema, then ./scripts/restore.sh to bring the data back.

If it looks wrong

Symptom	Cause	Fix
`backup.sh` refuses to run	Postgres, Keycloak's DB, the API container, or ClickHouse isn't running	Bring the stack up (`docker compose ps` to confirm) before backing up
Restore's final health checks fail	A container didn't come back up in time after restore	Check `docker compose logs api` / `web` / `clickhouse` — the restore itself already completed
Board data comes back but dashboards don't	ClickHouse restore was skipped, or a table wasn't in the backup	Re-run without `--skip-clickhouse`, or check the archive's `MANIFEST.txt` for which tables it actually contains

Upgrades — schema migrations, which a restore also reapplies.
Services & architecture — what Postgres, Keycloak's DB, and ClickHouse each hold.
Troubleshooting — what a disk or memory failure looks like before it gets this far.

Last updated 17 August 2026