rig db: bring dump/restore on-box (D-278) + close DB/backup interaction gaps #15

New issue

Closed

opened 2026-07-16 11:50:08 +00:00 by dan-claude-bot · 0 comments

dan-claude-bot commented

2026-07-16 11:50:08 +00:00

(Migrated from github.com)

Goal

Bring ad-hoc DB dump/restore on-box as rig db subcommands, and close the DB/backup interaction gaps the prod-migration Task 9 drill surfaced. Decided in D-278 (heavy-duty/incubator brain): imperative on-box bash = rig; declarative Coolify-state convergence = cast.

Why

A restore is a one-shot docker exec … psql against containers that live on a box — running it on the box (rig) drops cast's ssh-hop + workstation file-transport. cast's off-box restore-db.sh is being retired; the drill proved the exact in-container primitive already (heavy-duty/cast#71, merged — the reference implementation to port).

Scope

1. `rig db dump` / `rig db restore` (port from cast)

rig db dump <container> [outfile] → docker exec <c> sh -c 'pg_dump -U "$POSTGRES_USER" --clean --if-exists --no-owner --no-acl "$POSTGRES_DB"' | gzip
- --no-owner --no-acl is mandatory for cross-instance restores (D-277 — source/target superusers differ; a plain dump aborts on the missing role under ON_ERROR_STOP=1).
rig db restore <artifact> <container> [db] → gunzip -c <a> | docker exec -i <c> sh -c 'psql -U "$POSTGRES_USER" -d "${db:-$POSTGRES_DB}" -v ON_ERROR_STOP=1'
- connects as the container's own superuser ($POSTGRES_USER), never hardcoded postgres (Coolify randomizes it — the bug cast#71 fixed).
Keep an explicit confirm gate on restore (overwrites the target DB).
Retire cast's scripts/restore-db.sh once this lands; update the infra runbooks/restore-drill.md to call rig db.

2. DB/backup interaction gaps (surfaced by the drill)

ServiceDatabase blindness: Coolify's backup API (and cast) are StandaloneDatabase-only, so a one-click service's bundled Postgres (e.g. umami) can't be backed up declaratively — only a UI click-op (coollabsio/coolify#7529). Tracked separately for umami in heavy-duty/infra#26 (make umami a first-class app on the shared Postgres). If rig grows any backup-adjacent verbs, keep this asymmetry in mind.
Multi-DB restore target: once umami shares the incubator Postgres as a separate umami database (#26), rig db restore must target a named db (-d umami) in a shared container — the [db] arg above already covers it.

References

D-278 / D-279 (heavy-duty/incubator docs/brain/DECISIONS.md)
Drill: heavy-duty/infra#25 · umami re-provision: heavy-duty/infra#26
Reference impl: heavy-duty/cast#71

## Goal Bring ad-hoc DB **dump/restore** on-box as `rig db` subcommands, and close the **DB/backup interaction** gaps the prod-migration Task 9 drill surfaced. Decided in **D-278** (heavy-duty/incubator brain): imperative on-box bash = rig; declarative Coolify-state convergence = cast. ## Why A restore is a one-shot `docker exec … psql` against containers that live **on a box** — running it *on* the box (rig) drops cast's ssh-hop + workstation file-transport. cast's off-box `restore-db.sh` is being **retired**; the drill proved the exact in-container primitive already ([heavy-duty/cast#71](https://github.com/heavy-duty/cast/pull/71), merged — the **reference implementation** to port). ## Scope ### 1. `rig db dump` / `rig db restore` (port from cast) - `rig db dump <container> [outfile]` → `docker exec <c> sh -c 'pg_dump -U "$POSTGRES_USER" --clean --if-exists --no-owner --no-acl "$POSTGRES_DB"' | gzip` - `--no-owner --no-acl` is **mandatory** for cross-instance restores (D-277 — source/target superusers differ; a plain dump aborts on the missing role under `ON_ERROR_STOP=1`). - `rig db restore <artifact> <container> [db]` → `gunzip -c <a> | docker exec -i <c> sh -c 'psql -U "$POSTGRES_USER" -d "${db:-$POSTGRES_DB}" -v ON_ERROR_STOP=1'` - connects as the container's **own** superuser (`$POSTGRES_USER`), never hardcoded `postgres` (Coolify randomizes it — the bug cast#71 fixed). - Keep an explicit confirm gate on restore (overwrites the target DB). - Retire cast's `scripts/restore-db.sh` once this lands; update the infra `runbooks/restore-drill.md` to call `rig db`. ### 2. DB/backup interaction gaps (surfaced by the drill) - **ServiceDatabase blindness:** Coolify's backup API (and cast) are StandaloneDatabase-only, so a one-click service's bundled Postgres (e.g. umami) can't be backed up declaratively — only a UI click-op (coollabsio/coolify#7529). Tracked separately for umami in [heavy-duty/infra#26](https://github.com/heavy-duty/infra/issues/26) (make umami a first-class app on the shared Postgres). If rig grows any backup-adjacent verbs, keep this asymmetry in mind. - **Multi-DB restore target:** once umami shares the incubator Postgres as a separate `umami` database (#26), `rig db restore` must target a **named** db (`-d umami`) in a shared container — the `[db]` arg above already covers it. ## References - D-278 / D-279 (heavy-duty/incubator `docs/brain/DECISIONS.md`) - Drill: heavy-duty/infra#25 · umami re-provision: heavy-duty/infra#26 - Reference impl: heavy-duty/cast#71

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference: heavy-duty/rig#15

No description provided.