fix(automation): reset test-db schema before restore (avoid P3009 on unreleased migrations) #95

Merged

shad0w merged 1 commit from fix/refresh-test-db-clean-schema into master

2026-06-23 14:53:18 +00:00

shad0w commented

2026-06-23 14:49:59 +00:00

Owner

Fixes the daily pelagia_test refresh so unreleased migrations apply cleanly.

Problem. refresh-test-db.sh mirrored prod with pg_dump --clean, which only drops objects that exist in prod. Objects created on the test DB by a previously-applied unreleased migration (e.g. the crewing tables/types, before crewing was on prod) survived as leftovers, then collided with the migrate deploy replay — 42710 type already exists / P3009 — which blocked every later migration and 500-ed the staging app.

Fix. Drop+recreate public before the restore, so the test DB starts from exactly prod's objects and the unreleased migrations apply from a clean base. Restore no longer needs --clean. Adds a guard that refuses to run unless the derived target DB name is the expected test DB (it performs a DROP SCHEMA).

Already deployed to the pms1 copy (~/issue-watcher/refresh-test-db.sh, backup kept) so tonight's cron is safe; this PR tracks it in the repo.

🤖 Generated with Claude Code

Fixes the daily `pelagia_test` refresh so unreleased migrations apply cleanly. **Problem.** `refresh-test-db.sh` mirrored prod with `pg_dump --clean`, which only drops objects that exist in prod. Objects created on the test DB by a previously-applied **unreleased** migration (e.g. the crewing tables/types, before crewing was on prod) survived as leftovers, then collided with the `migrate deploy` replay — `42710 type already exists` / **P3009** — which blocked every later migration and 500-ed the staging app. **Fix.** Drop+recreate `public` before the restore, so the test DB starts from exactly prod's objects and the unreleased migrations apply from a clean base. Restore no longer needs `--clean`. Adds a guard that refuses to run unless the derived target DB name is the expected test DB (it performs a `DROP SCHEMA`). Already deployed to the pms1 copy (`~/issue-watcher/refresh-test-db.sh`, backup kept) so tonight's cron is safe; this PR tracks it in the repo. 🤖 Generated with [Claude Code](https://claude.com/claude-code)

shad0w added 1 commit 2026-06-23 14:50:00 +00:00

fix(automation): reset test-db schema before restore to avoid P3009

PR checks / checks (pull_request) Successful in 41s

Details

PR checks / integration (pull_request) Successful in 30s

Details

81744d1fa8

refresh-test-db.sh mirrored prod with `pg_dump --clean`, which only drops objects
that exist in prod. Objects created on pelagia_test by a previously-applied
UNRELEASED migration (e.g. crewing tables/types not yet released to prod) survived
as leftovers, then collided with the `migrate deploy` replay ("type already
exists", P3009) — blocking every later migration and 500-ing the staging app.

Drop+recreate `public` before the restore so the test DB starts from exactly
prod's objects and unreleased migrations apply cleanly. Adds a guard that refuses
to run unless the derived target is the expected test DB.

Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>