Move the database to Postgres #2

New Issue

2026-09-20T05:58:35Z

niklas commented

2026-09-20 05:58:35 +00:00

Part of #1. This comes first: the teams work adds a team column to nearly every table, so doing Postgres first means those migrations are written once, for one database.

Status: the server-side half is merged in #8 (21f0eec). What is left is the wrapper chart and the cutover of the running install, both outside this repo. Reopened because the auto-close from "Closes #2" ran ahead of the "Done when" below — the production database is still SQLite.

Today

SQLite (modernc.org/sqlite) on a PVC, SetMaxOpenConns(1) and WAL (internal/db/db.go).
Raw database/sql, no ORM.
Embedded migrations internal/db/migrations/001..010_*.sql, tracked in schema_migrations.
A python backup sidecar streams a SQLite dump for k8up.

Scope

TERDUT_DB_DSN replaces TERDUT_DB_PATH in internal/config/config.go.
internal/db/db.go opens Postgres (pgx) with a sane pool, dropping the single-connection limit.
Port the migrations. Ten SQLite files became one Postgres baseline, 001_baseline.sql; timestamps stay BIGINT epochs, the JSON columns became jsonb.
Sweep the queries in internal/api/ for ? placeholders (become $1), RETURNING support and any LIMIT/OFFSET differences. 170 placeholders, plus four things a search and replace would not have caught — see the commit message.
Tests run against Postgres: a schema per test on a shared server, TERDUT_TEST_DSN says where, make test-db locally and a service container in ci.yaml. An unset DSN fails rather than skips.
A one-off migration script in scripts/ that reads the SQLite file and writes Postgres, run by hand once and then deleted. The binary gets no subcommand.
App chart: drop pvc.yaml, drop the backup sidecar from values.yaml, take a DSN (with the password from a Secret).
Wrapper chart in Ryuvia/charts: an acid.zalan.do/v1 postgresql CR with a k8up.io/backupcommand: pg_dump annotation, following gitea/templates/postgres.yaml and immich, rework the k8up schedule off the SQLite dump, and open the network policy to Postgres.
Cut over the running install: stop, run the script against the PVC, start on the new version.
README: the new env var, and the migration procedure.

Notes

The cutover is ordered: stop the old version, run the script, start the new one. Plan it as its own step rather than folding it into a normal release.
The SQLite driver has to stay in go.mod until the migration script is deleted. Remove both once this install is migrated, so it doesn't become permanent.
The migration script was verified end to end against a SQLite database built at the old schema and seeded in every table: ids survive, so incidents keep their numbers and every foreign key still points where it did, and the identity sequences are moved past the copied ids.

Done when

A fresh install comes up against Postgres ✅, the existing SQLite data is migrated with no loss, make fmt lint test helm-lint is green ✅, and a k8up backup produces a restorable pg_dump.

Part of #1. **This comes first:** the teams work adds a team column to nearly every table, so doing Postgres first means those migrations are written once, for one database. > **Status:** the server-side half is merged in #8 (`21f0eec`). What is left is the wrapper chart and the cutover of the running install, both outside this repo. Reopened because the auto-close from "Closes #2" ran ahead of the "Done when" below — the production database is still SQLite. ### Today - SQLite (`modernc.org/sqlite`) on a PVC, `SetMaxOpenConns(1)` and WAL (`internal/db/db.go`). - Raw `database/sql`, no ORM. - Embedded migrations `internal/db/migrations/001..010_*.sql`, tracked in `schema_migrations`. - A python backup sidecar streams a SQLite dump for k8up. ### Scope - [x] `TERDUT_DB_DSN` replaces `TERDUT_DB_PATH` in `internal/config/config.go`. - [x] `internal/db/db.go` opens Postgres (pgx) with a sane pool, dropping the single-connection limit. - [x] Port the migrations. Ten SQLite files became one Postgres baseline, `001_baseline.sql`; timestamps stay BIGINT epochs, the JSON columns became `jsonb`. - [x] Sweep the queries in `internal/api/` for `?` placeholders (become `$1`), `RETURNING` support and any `LIMIT`/`OFFSET` differences. 170 placeholders, plus four things a search and replace would not have caught — see the commit message. - [x] Tests run against Postgres: a schema per test on a shared server, `TERDUT_TEST_DSN` says where, `make test-db` locally and a service container in `ci.yaml`. An unset DSN fails rather than skips. - [x] A one-off migration script in `scripts/` that reads the SQLite file and writes Postgres, run by hand once and then deleted. The binary gets no subcommand. - [x] App chart: drop `pvc.yaml`, drop the backup sidecar from `values.yaml`, take a DSN (with the password from a Secret). - [ ] Wrapper chart in `Ryuvia/charts`: an `acid.zalan.do/v1 postgresql` CR with a `k8up.io/backupcommand: pg_dump` annotation, following `gitea/templates/postgres.yaml` and `immich`, rework the k8up schedule off the SQLite dump, and open the network policy to Postgres. - [ ] Cut over the running install: stop, run the script against the PVC, start on the new version. - [x] README: the new env var, and the migration procedure. ### Notes - The cutover is ordered: stop the old version, run the script, start the new one. Plan it as its own step rather than folding it into a normal release. - The SQLite driver has to stay in `go.mod` until the migration script is deleted. Remove both once this install is migrated, so it doesn't become permanent. - The migration script was verified end to end against a SQLite database built at the old schema and seeded in every table: ids survive, so incidents keep their numbers and every foreign key still points where it did, and the identity sequences are moved past the copied ids. ### Done when A fresh install comes up against Postgres ✅, the existing SQLite data is migrated with no loss, `make fmt lint test helm-lint` is green ✅, and a k8up backup produces a restorable pg_dump.

niklas referenced this issue

2026-09-20 05:59:55 +00:00

Epic: teams, escalation chains, self-service onboarding and an admin page #1

niklas added the database helm labels 2026-09-20 06:12:59 +00:00

niklas referenced a pull request that will close this issue

2026-09-20 08:48:13 +00:00

Move the database to Postgres #8

niklas closed this issue

2026-09-20 08:54:24 +00:00

niklas reopened this issue

2026-09-20 08:56:29 +00:00

niklas referenced this issue

2026-09-20 09:06:46 +00:00

Take the database password from PGPASSWORD, not the DSN #9

niklas commented

2026-09-20 11:13:29 +00:00

Deployed and verified in the cluster, 2026-09-20.

Ryuvia/charts#231 merged, Flux reconciled, and the namespace is on Postgres:

terdut-server-7dbfff86d6-c6df8   1/1   Running
terdut-server-postgres-0         1/1   Running
terdut-server-postgres-1         1/1   Running
terdut-server-postgres  terdut-server  17  2  5Gi  Running

Verified, each from a command that ran:

Schema: 15 tables in public, schema_migrations holds 001_baseline.sql.
The alert path works end to end — Alertmanager POSTed /api/alertmanager/webhook and got 200, and the row landed: 1 alert, 0 incidents, which is correct because the alert is Watchdog and Watchdog is a dead man's switch.
The bootstrap Job ran (201) and created the first user, so a fresh install comes up usable.
Backup: a one-off k8up Backup selected terdut-server-postgres-0, ran the pg_dump --clean backupcommand from its annotation and streamed it to restic as /terdut-server-postgres.sql — new files: 1, errors: 0, bytes processed: 58932. Only the database pod contributed; the app pod carries no annotation and holds no data.

The data migration was deliberately skipped. This install is not production, and the decision was to let the server bootstrap a fresh database rather than carry the old rows across. scripts/sqlite-to-postgres.go was still verified end to end before the release, against a SQLite database built at the old schema and seeded in every table.

Two things left behind, neither harmful:

PV pvc-c5ef5aa7-017d-4550-ab93-c17f640e0c1f (1Gi, Released, Retain) still holds the old SQLite database. Helm deleted the terdut-server-data PVC when pvc.yaml left the chart — a template removal deletes the live object, which is worth knowing for any future chart that drops a volume. The data survived only because the storage class retains. Delete it when you are sure you want it gone.
The migration script and the modernc.org/sqlite dependency are still in the tree. They exist for an upgrade that is now not going to happen here, so they can be deleted whenever — that is the follow-up this issue's note asked for.

Two dead tags for the record: v0.11.0 published nothing (release.yaml's test job had no database; fixed in a8ee742), alongside v0.10.0 and v0.10.1. The shipped version is v0.11.1.

Deployed and verified in the cluster, 2026-09-20. `Ryuvia/charts#231` merged, Flux reconciled, and the namespace is on Postgres: ``` terdut-server-7dbfff86d6-c6df8 1/1 Running terdut-server-postgres-0 1/1 Running terdut-server-postgres-1 1/1 Running terdut-server-postgres terdut-server 17 2 5Gi Running ``` **Verified, each from a command that ran:** - Schema: 15 tables in `public`, `schema_migrations` holds `001_baseline.sql`. - The alert path works end to end — Alertmanager POSTed `/api/alertmanager/webhook` and got 200, and the row landed: 1 alert, 0 incidents, which is correct because the alert is Watchdog and Watchdog is a dead man's switch. - The bootstrap Job ran (201) and created the first user, so a fresh install comes up usable. - **Backup:** a one-off k8up `Backup` selected `terdut-server-postgres-0`, ran the `pg_dump --clean` backupcommand from its annotation and streamed it to restic as `/terdut-server-postgres.sql` — `new files: 1, errors: 0, bytes processed: 58932`. Only the database pod contributed; the app pod carries no annotation and holds no data. **The data migration was deliberately skipped.** This install is not production, and the decision was to let the server bootstrap a fresh database rather than carry the old rows across. `scripts/sqlite-to-postgres.go` was still verified end to end before the release, against a SQLite database built at the old schema and seeded in every table. **Two things left behind, neither harmful:** - PV `pvc-c5ef5aa7-017d-4550-ab93-c17f640e0c1f` (1Gi, `Released`, `Retain`) still holds the old SQLite database. Helm deleted the `terdut-server-data` PVC when `pvc.yaml` left the chart — a template removal deletes the live object, which is worth knowing for any future chart that drops a volume. The data survived only because the storage class retains. Delete it when you are sure you want it gone. - The migration script and the `modernc.org/sqlite` dependency are still in the tree. They exist for an upgrade that is now not going to happen here, so they can be deleted whenever — that is the follow-up this issue's note asked for. Two dead tags for the record: **v0.11.0** published nothing (release.yaml's test job had no database; fixed in `a8ee742`), alongside v0.10.0 and v0.10.1. The shipped version is **v0.11.1**.

niklas closed this issue

2026-09-20 11:13:33 +00:00

niklas referenced this issue

2026-09-20 13:29:36 +00:00

Teams: scope data per team, per-team integration keys, per-team dead-man switches #4

niklas referenced this issue

2026-09-20 16:11:48 +00:00

Remove the unauthenticated webhook and the SQLite migration script #14

Sign in to join this conversation.