We upgraded a self-hosted Central install across 12 releases in one step.
It worked in the end, but the event-stamping migration from v2026.1.0 would have kept us offline for 3+ hours.
Below are our numbers, the cause we found, the workaround we tested, and some suggestions.
Setup
| Install | Docker Compose, standard getodk/central checkout |
| Upgrade | v2025.4.2 → v2026.3.1 (12 releases, includes v2026.1.0) |
| Host | Ubuntu, 32 cores, 64 GB RAM, ext4 on LVM, local SSD/RAID |
| Docker / Compose | 29.9.3 / v5.7.1 |
| Postgres | Bundled postgres14 (14.24), stock settings at upgrade time |
| Database | 14 GB (pg_database_size), ~963k submissions, ~965k submission defs, ~1.17M audits, ~80k local blobs |
Timings
| Step | Time |
|---|---|
docker compose pull + build --pull |
~5 min |
Migrations up to the final COMMIT |
~2 min |
Final COMMIT (unpatched) |
48 min and still running when cancelled. One core at 100%, I/O idle (disk 10% busy, IO pressure <0.5%). Projected 3+ more hours. |
Cancel (pg_terminate_backend) and rollback |
A few seconds |
| Full migration batch with the workaround below, test copy | 82 s |
| Same, production | ~2 min |
During the long COMMIT the frontend only showed "error loading", and nginx logged 502s for API clients. There was no way to see progress: the work runs as deferred triggers inside one transaction, so counters and the current_event row are invisible to other sessions. We estimated progress from the growth of relation files on disk.
What we found
Within a single migration transaction:
20251127-01-submission-event-stampingaddssubmissions.event, numbers every row, and installs:blank_submissions_event_on_update(BEFORE UPDATE, setsNEW.event = NULLwhen old and new are both non-NULL)set_eventstamp_submissions_at_commit(deferred constraint trigger,WHEN (NEW.event IS NULL), callsget_event())
20260115-01-submission-event-stamping-unshared-eventsrenumbers every submission with a single bulkUPDATE. Because the triggers from step 1 are already installed, the BEFORE trigger blankseventon every row, and the deferred trigger queues one call per row (~963k).- At
COMMITthose calls run one by one. Each oneUPDATEs the single row incurrent_event. Inside one transaction, old versions of that row can't be pruned, so each call scans all the previous versions. The cost grows quadratically: about 100 calls/s early on and falling. The end result also simply renumbers the rows that step 2 had just numbered.
Workaround we used (tested on a restored copy first)
File: getodk/central-backend → lib/model/migrations/20260115-01-submission-event-stamping-unshared-events-01.up.sql
Existing:
TRUNCATE TABLE current_event;
UPDATE
submissions s1
SET
event = s2.rowno
...
s1.id = s2.id
;
INSERT INTO current_event (event) (SELECT coalesce(MAX(event), 0) FROM submissions);
Suggested:
TRUNCATE TABLE current_event;
ALTER TABLE submissions
DISABLE TRIGGER blank_submissions_event_on_update,
DISABLE TRIGGER set_eventstamp_submissions_at_commit;
UPDATE
submissions s1
SET
event = s2.rowno
...
s1.id = s2.id
;
ALTER TABLE submissions
ENABLE TRIGGER blank_submissions_event_on_update,
ENABLE TRIGGER set_eventstamp_submissions_at_commit;
INSERT INTO current_event (event) (SELECT coalesce(MAX(event), 0) FROM submissions);
Checks afterwards: every submission has a unique event number, current_event = max(event), all migrations applied, both triggers enabled, and updating a submission afterwards assigns max + 1 as expected. We mounted the patched file read-only into the service container through docker-compose.override.yml for one start, then removed the mount.
An earlier attempt that only added AND event IS NULL to eventstamp_submissions_triggerfunction did not help, because the BEFORE trigger had already nulled every row.
Suggestions
Migration performance
- Disable the event triggers around bulk backfills (as above), or set
eventbefore the triggers are created, so large installs don't queue a deferred trigger call for every row. get_event()updating one counter row once per row inside a single transaction is quadratic for bulk operations. A sequence, or a set-based assignment for bulk paths, would avoid that.- Give a time estimate in the release notes ("about X minutes per 100k submissions") and test the migration on a database with ~1M submissions before release.
- Show a "server is upgrading, please wait" page instead of "error loading" while migrations run, and log progress (for example "stamping submissions: 400k / 963k").
nginx keeps the service's old IP address
After docker compose up -d postgres14 service recreated service, the container got a new IP, but nginx kept proxying to the old one ("connect() failed (113: No route to host)"). The UI showed "error loading" and Collect formList calls returned 502 until we ran docker compose restart nginx. Using Docker's resolver (resolver 127.0.0.11 valid=10s; with a variable in proxy_pass) would make nginx look the address up again automatically.
Visibility
- Enabling
track_functions(or logging from the migration runner) would make long migrations easier to diagnose.
Happy to share more detail or test patches on a copy of our data.