[devops#4.2] Migrate data from bare-metal Postgres to CNPG cluster #11

New issue

Open

opened 2026-06-02 02:49:28 +00:00 by agent-cos · 1 comment

agent-cos commented

2026-06-02 02:49:28 +00:00

Problem

Parent #4 step 2: move the data after the cluster is ready.

Scope

Run the existing migrate-db.sh script. Verify data integrity post-migration. No cutover yet.

Acceptance Criteria

Set passwords in postgres-users Secret (kubectl edit secret postgres-users -n database)
Run migration: ./scripts/migrate-db.sh exits 0
Table counts match: forgejo, umami, any custom DB
Test connection from a debug pod: psql to CNPG works
Rollback path verified: dump file at /tmp/pg-migration-dump.sql exists and is non-empty

OUT OF SCOPE (deferred to devops#4.3)

Switching Forgejo/Umami to point at CNPG
Disabling bare-metal Postgres

Why this exists

Migrate is its own bounded step. A subagent can run the script + verify in 600s. Cutover is a separate risk class.

Depends on

devops#4.1 (cluster ready)

## Problem Parent #4 step 2: move the data after the cluster is ready. ## Scope Run the existing `migrate-db.sh` script. Verify data integrity post-migration. No cutover yet. ## Acceptance Criteria - [ ] Set passwords in `postgres-users` Secret (`kubectl edit secret postgres-users -n database`) - [ ] Run migration: `./scripts/migrate-db.sh` exits 0 - [ ] Table counts match: forgejo, umami, any custom DB - [ ] Test connection from a debug pod: `psql` to CNPG works - [ ] Rollback path verified: dump file at `/tmp/pg-migration-dump.sql` exists and is non-empty ## OUT OF SCOPE (deferred to devops#4.3) - Switching Forgejo/Umami to point at CNPG - Disabling bare-metal Postgres ## Why this exists Migrate is its own bounded step. A subagent can run the script + verify in 600s. Cutover is a separate risk class. ## Depends on - devops#4.1 (cluster ready)

sscoble commented

2026-06-02 04:41:29 +00:00

Owner

CoS Discovery (2026-06-02 21:36 PT) — SCOPE INCONSISTENCY, BLOCKED

Status: Cannot dispatch this cycle. Surfacing scope mismatch for Scott's decision.

What I found when investigating devops#11:

Forgejo is on SQLite, not Postgres. cat /var/lib/forgejo/custom/conf/app.ini shows:
```
[database]
DB_TYPE = sqlite3
PATH = /var/lib/forgejo/data/forgejo.db
```
The parent #4 spec says "Move Forgejo + Umami to k3s" but Forgejo's database is SQLite at /var/lib/forgejo/data/forgejo.db. The CNPG migration does not affect Forgejo's data.
Bare-metal Postgres actually has 6 user databases, not 2. psql -l on bare metal shows:
- homeschool (~9 MB)
- openbrain (~15 MB)
- pool_manager (~8 MB)
- poolq_test (~8 MB)
- style_prefs (~8 MB)
- umami (~9 MB)
- (plus postgres system DB)
The migrate-db.sh script's "DB_COUNT" check looks for ('forgejo','umami') which expects exactly 2 DBs. It will FAIL because:
- forgejo doesn't exist on bare-metal Postgres (it's on SQLite)
- The 6 actual DBs aren't all in the expected list
AC #3 in the ticket body says "Table counts match: forgejo, umami, any custom DB" — this is impossible to satisfy literally because forgejo is not a Postgres database.

What this means:

The migration as-written would still work mechanically: pg_dumpall → psql restore works regardless of DB count. The DB_COUNT=2 check at the end is the only blocker.
If we just remove the forgejo check and keep umami only, the script works but only verifies 1 of 6 DBs migrated.
If we update the check to look for all 6 DBs, we verify everything but acknowledge that "Forgejo" in the AC was a stale write.

Decision needed from Scott:

Option A: Migrate ALL 6 bare-metal Postgres DBs to CNPG. Update migrate-db.sh to check for all 6. Update devops#11 AC #3 to remove forgejo and add the actual DB names.
Option B: Migrate only umami (the one the parent actually specifies). Drop the other 5 from bare metal first, or leave them on bare metal and never migrate them.
Option C: Update parent #4 to clarify: "Migrate Umami (the only Forgejo-stack service on Postgres); Forgejo itself is on SQLite and is not part of this migration."

CoS recommendation: Option A + Option C combined. Migrate all 6 (simplest, no data loss), and update the parent ticket to reflect that "Forgejo" in the title was a stale assumption.

What I did this cycle:

devops#10 (Create CNPG cluster): DIRECT CLOSED. Cluster has been running 2d13h, all 5 ACs verified (kubectl get cluster shows 1 instance, 1/1 Ready, "Cluster in healthy state", Forgejo on bare metal still 200 OK, bare-metal Postgres still active). Updated body to check off all boxes, posted closure comment, closed ticket.
devops#11 (Data migration): NOT DISPATCHED. Posting this comment for human decision.
eng#27 (polylux-html MVP): DIRECT CLOSED in this cycle. Was 5/5 ACs checked off but ticket was still open. Closed + unassigned.

Files NOT modified:

/home/sscoble/devops/scripts/migrate-db.sh (will need update for whichever option)
/var/lib/forgejo/custom/conf/app.ini (Forgejo config is correct as-is)
No kubectl operations, no data migration executed.

CoS will await Scott's call before any further work on devops#11.

## CoS Discovery (2026-06-02 21:36 PT) — SCOPE INCONSISTENCY, BLOCKED **Status: Cannot dispatch this cycle. Surfacing scope mismatch for Scott's decision.** ### What I found when investigating devops#11: 1. **Forgejo is on SQLite, not Postgres.** `cat /var/lib/forgejo/custom/conf/app.ini` shows: ``` [database] DB_TYPE = sqlite3 PATH = /var/lib/forgejo/data/forgejo.db ``` The parent #4 spec says "Move Forgejo + Umami to k3s" but Forgejo's database is SQLite at `/var/lib/forgejo/data/forgejo.db`. The CNPG migration does not affect Forgejo's data. 2. **Bare-metal Postgres actually has 6 user databases, not 2.** `psql -l` on bare metal shows: - `homeschool` (~9 MB) - `openbrain` (~15 MB) - `pool_manager` (~8 MB) - `poolq_test` (~8 MB) - `style_prefs` (~8 MB) - `umami` (~9 MB) - (plus `postgres` system DB) 3. **The migrate-db.sh script's "DB_COUNT" check looks for `('forgejo','umami')`** which expects exactly 2 DBs. It will FAIL because: - `forgejo` doesn't exist on bare-metal Postgres (it's on SQLite) - The 6 actual DBs aren't all in the expected list 4. **AC #3 in the ticket body says "Table counts match: forgejo, umami, any custom DB"** — this is impossible to satisfy literally because `forgejo` is not a Postgres database. ### What this means: - The migration as-written would still work mechanically: `pg_dumpall` → `psql restore` works regardless of DB count. The `DB_COUNT=2` check at the end is the only blocker. - **If we just remove the `forgejo` check and keep `umami` only, the script works but only verifies 1 of 6 DBs migrated.** - **If we update the check to look for all 6 DBs, we verify everything but acknowledge that "Forgejo" in the AC was a stale write.** ### Decision needed from Scott: - **Option A:** Migrate ALL 6 bare-metal Postgres DBs to CNPG. Update migrate-db.sh to check for all 6. Update devops#11 AC #3 to remove `forgejo` and add the actual DB names. - **Option B:** Migrate only `umami` (the one the parent actually specifies). Drop the other 5 from bare metal first, or leave them on bare metal and never migrate them. - **Option C:** Update parent #4 to clarify: "Migrate Umami (the only Forgejo-stack service on Postgres); Forgejo itself is on SQLite and is not part of this migration." **CoS recommendation:** Option A + Option C combined. Migrate all 6 (simplest, no data loss), and update the parent ticket to reflect that "Forgejo" in the title was a stale assumption. ### What I did this cycle: - **devops#10 (Create CNPG cluster):** DIRECT CLOSED. Cluster has been running 2d13h, all 5 ACs verified (`kubectl get cluster` shows 1 instance, 1/1 Ready, "Cluster in healthy state", Forgejo on bare metal still 200 OK, bare-metal Postgres still active). Updated body to check off all boxes, posted closure comment, closed ticket. - **devops#11 (Data migration):** NOT DISPATCHED. Posting this comment for human decision. - **eng#27 (polylux-html MVP):** DIRECT CLOSED in this cycle. Was 5/5 ACs checked off but ticket was still open. Closed + unassigned. ### Files NOT modified: - /home/sscoble/devops/scripts/migrate-db.sh (will need update for whichever option) - /var/lib/forgejo/custom/conf/app.ini (Forgejo config is correct as-is) - No kubectl operations, no data migration executed. CoS will await Scott's call before any further work on devops#11.