Embedded→canonical pgserve upgrade (main→dev) breaks; omni doctor --fix can't complete on a clean host

[vasconceloscezar]

Summary

Upgrading an embedded-Postgres install from @latest (main, 2.260410.1) to @next (dev, 2.260617.3) leaves omni-api in a permanent crash loop, and the documented remediation — omni doctor --fix (embedded → canonical pgserve migration) — cannot complete on a clean host. Data is not lost, but the service is down with no working automated path forward.

Found via an isolated sandbox upgrade test (rootful Podman, ubuntu:24.04, unprivileged user, real installer flow). The production VM (already on canonical autopg-server@2.6.10) is not an embedded install and is likely unaffected; this bites embedded/older installs upgrading across the backbone change.

Environment

• Clean ubuntu:24.04, install via the published flow: bun add -g @automagik/omni@<channel> → omni install --non-interactive → omni start.
• main 2.260410.1 installs an embedded, in-process Postgres (PG18) on :8432.
• dev 2.260617.3 expects a standalone canonical pgserve/autopg on :5432.

Repro

1. Install + boot main (@latest): healthy — embedded PG18 on :8432, 18 migrations, NATS connected. ✅
2. bun add -g @automagik/omni@next then omni stop && omni start.
3. omni-api crash-loops: ERROR api:startup "Failed to start API server" error="Database not ready after 30 attempts". omni start reports services "online" while the API silently restarts.
4. omni doctor correctly flags it: pgserve-canonical … embedded … DEPRECATED. Run omni doctor --fix (idempotent; pg_dump → pgserve install → restore → relaunch).
5. omni doctor --fix fails and cannot recover (details below).

Root causes (each independently blocks the migration)

1. Opaque failure on the bare upgrade

After upgrade, omni-api only logs Database not ready after 30 attempts. Nothing tells the operator the DB-backbone model changed (embedded → canonical) or to run omni doctor --fix. omni start should fail-fast on a deprecated-embedded + dev-binary combination with an actionable hint, not enter a silent restart loop.

2. No pg_dump ships with any bundled Postgres distribution

omni doctor --fix needs a PG18 pg_dump, but:

• @embedded-postgres bundles only initdb / pg_ctl / postgres — no pg_dump/pg_restore/psql.
• Canonical autopg/pgserve (~/.autopg/bin/...) — same: only initdb / pg_ctl / postgres.
• find / -name pg_dump → empty on a fresh install.

3. The remediation hint installs the wrong major version

doctor --fix suggests apt install postgresql-client. On Ubuntu 24.04 that's PG16, which refuses to dump a PG18 server (pg_dump: server version 18 … is newer). The hint is actively misleading; it should point at a PG≥server client (e.g. PGDG postgresql-client-18) or use a bundled one.

4. Ordering chicken-and-egg, and the source server isn't running under dev

Even with a real PG18 pg_dump supplied:

• doctor --fix tries to pg_dump the embedded DB on :8432, but the dev binary never starts the embedded server, so the source is down → connection to server … port 8432 failed: Connection refused.
• Installing canonical pgserve does a soft-rename of ~/.pgserve → ~/.autopg (documented, reversible — leaves MIGRATED-FROM-PGSERVE.md), which removes the embedded binaries from ~/.pgserve/bin, so there's no obvious supported way to bring the embedded source up for the dump.

Net: completing the migration required (a) an externally-installed PG18 client, and (b) manually starting the embedded server that dev no longer manages — neither of which omni doctor --fix does. The cascade of manual workarounds is the real blocker.

Impact

• Embedded installs upgrading main→dev get a down API with no working automated recovery.
• Data is intact (embedded PGDATA preserved, 15M PG18) but unreachable by the dev service.

Suggested fixes

• omni start/startup: detect deprecated-embedded under a canonical-only binary and fail fast with the omni doctor --fix hint instead of a 30-retry crash loop.
• Make omni doctor --fix self-sufficient: locate/ship a PG≥server-version pg_dump, and start the embedded server itself (it knows the embedded binary + PGDATA) for the dump before relaunching on canonical.
• Fix the remediation hint: never suggest a pg_dump older than the server; prefer the bundled/canonical PG18 client or PGDG postgresql-client-18.
• Consider bundling pg_dump/pg_restore with @embedded-postgres/autopg so migrations don't depend on host tooling.

Notes

Found via sandbox upgrade test. Not observed to affect the production deployment (already canonical autopg-server@2.6.10).

[vasconceloscezar]

Partial fix merged to dev in #723: fail-fast on omni start for legacy embedded installs (no more opaque 30× crash loop) + major-aware client hint in omni doctor --fix (stops recommending the too-old postgresql-client). Both validated end-to-end in an isolated sandbox.

Still open (the core data-path fix): omni doctor --fix migration still shells to host pg_dump/psql. Making it host-tooling-free needs Bun.SQL, but Bun 1.3.14's SQL has no COPY API — so it's a careful per-column-type SELECT→INSERT rewrite (jsonb/bytea/array fidelity), to be done as its own reviewed PR. Leaving this issue open for that piece.

[vasconceloscezar]

Update: the migration data copy is now host-tooling-free (#724, merged to dev). It streams COPY … TO STDOUT → COPY … FROM STDIN via postgres.js (no psql/pg_dump); the matching-major Postgres server reader is already auto-fetched. Verified byte-for-byte fidelity (jsonb/bytea/int[]/nulls) in a sandbox.

This makes migrateUnmountedEmbeddedToCanonical / the orphaned-data recovery path fully automatic. Remaining: wire the primary fixPgserveCanonical path (schema-via-migrations + this copier) so a fresh omni doctor --fix is automatic end-to-end — today it still uses the pg_dump/psql snapshot path. Now low-risk (the tool-free copier is proven); it reworks ~6 rollback tests so it's its own focused PR.

[vasconceloscezar]

✅ Resolved on dev (pending dev → main promotion via #589).

omni doctor --fix now migrates an embedded install to canonical pgserve fully automatically with zero host Postgres client tools:

• fix(cli): fail-fast on legacy embedded pgserve + major-aware migration hint (#722) #723 — omni start fails fast on legacy embedded (no more opaque 30× crash loop) + major-aware client hint.
• refactor(cli): host-tooling-free embedded→canonical migration via postgres.js (#722) #724 — embedded→canonical data copy rewritten to postgres.js COPY (no psql/pg_dump); byte-for-byte fidelity proven.
• fix(cli): fully automatic, host-tooling-free omni doctor --fix migration (#722) #725 — wired the primary doctor --fix path to the tool-free copier (schema via omni-api's own migrations) and converted the last two psql shell-outs in canonical provisioning (CREATE DATABASE, role/grants) to postgres.js.

Clean-room e2e (fresh container): @latest embedded + seeded canary → upgrade → omni doctor --fix → 13 OK / 0 FAIL, omni-api healthy on canonical, 30 tables copied (855ms), canary preserved with identical UUID, row counts match. No psql/pg_dump on the host.

Minor follow-up (non-blocking): the matching-major reader resolver only checks ~/.local/share/autopg but autopg ≥2.6 installs to ~/.autopg — when only autopg is present (and no npm for the embedded-postgres fetch fallback) the copy gracefully reports skipped and the idempotent embedded-data-orphaned check recovers once a reader resolves. Teaching the resolver autopg's real path would make even that edge fully hands-off.

[vasconceloscezar]

Closing — fully resolved across #723 / #724 / #725 (on dev, riding to production via the #589 dev → main promotion). omni doctor --fix now performs the embedded→canonical migration automatically with zero host Postgres client tools; verified end-to-end in a clean container (13 OK / 0 FAIL, 30 tables copied, canary preserved with identical UUID).