Render Postgres migration + rollback plan (Fly → Render)

:::note Contenido en inglés Esta página del wiki se sincroniza desde la base de conocimiento en inglés y todavía no está traducida. :::

reference · devops · updated 2026-09-07 · source

Design/documentation only — sub-issue #1616 of effort #1604 (Render + Cloudflare migration). Nothing in this doc has been executed. Fly stays the live production backend, untouched. Sub #1622 (Phase 2, "backend database migration execution") is closed: it confirmed the live topology read-only and rehearsed the cross-version dump/restore on disposable containers; the real production schema+data load is deferred to sub #1623 (final DNS cutover, José 2026-09-06) and needs its own go-ahead per the effort's Phase 2 checkpoint model. This doc is that sub's starting design — it does not replace the live verification #1623 must run first.

Renamed 2026-09-07 (sub #1721): tedos-product-db below is now tedos-production-db, and tedos-api-blueprint-poc is now tedos-api-production — the historical narrative below keeps the names as written at the time.

Scope: the two databases

Per ADR-006 (dev-workflow vs. product split) and ADR-007 (schema-per-client), tedos runs two logically separate Postgres databases, both on the same Fly Managed Postgres (MPG) cluster today:

Env var	Contents	Owner package	Migration journal
DATABASE_URL	Plan Engine graph (plan schema) + pg-boss job queue (pgboss schema, library-managed, not Drizzle)	@tedos/db	apps/api/drizzle.config.ts has no schemaFilter (unused today — apps/api/src/db/schema/index.ts is an empty barrel since Mission Control was removed, #717); packages/db/drizzle.config.ts scopes its own journal to the plan schema
PRODUCT_DATABASE_URL	Product System of Record: one product control schema (global project registry) + one client_<shortid> schema per tenant	@tedos/domain	packages/domain/drizzle.config.ts, schemaFilter: ['product'] — the control schema only; per-client schemas are provisioned at runtime (provisionClientSchema), not migrated by drizzle-kit

Both are read from apps/api/fly.toml's release_command = pnpm run db:migrate:all, which chains three independent Drizzle migration histories in order: apps/api → @tedos/db (plan) → @tedos/domain (product control schema). A Render cutover has to satisfy all three cleanly.

Per-client tables actually shipped today (read from packages/domain/src/provisioning.ts's templateDdl(), not from the older packages/domain/README.md, which still lists only the original three): person, person_identity, person_merge, offering, offering_tag, tag, enrollment, payment, stripe_product_mapping, certificate, points_config, points_ledger, activity_event, memory_chunk (pgvector, EMBEDDING_DIM = 1536), notification, email_event, email_reminder_log, email_send_log, page, page_version, advisor, lesson, student_account, student_credential, student_person_bind, student_session, student_user, student_verification. Two Postgres extensions are required by this template: CREATE EXTENSION IF NOT EXISTS vector and CREATE EXTENSION IF NOT EXISTS pg_trgm (provisioning.ts).

UNVERIFIED — flagging up front (do not assume these before #1622 executes)

• Exact cluster/database topology. apps/api/fly.toml's primary_region comment says the app is "co-located with the MPG cluster (tedos-pg2, gru)" — ONE named cluster. knowledge/plan-engine.md §7 says the plan graph and product SoR "currently share one Fly MPG cluster, isolated by Postgres schema" (a documented compromise against ADR-006's intent, kept only because "no real tenant data exists yet," with an explicit upgrade-path note to split before real tenant-PII volume lands). apps/api/DEPLOY-FLY.md's own runbook, by contrast, shows example connection strings with different database names (tedos_graph vs tedos_product) on the same cluster. These are consistent with each other (one MPG cluster hosting two Postgres databases) but I have no DB access in this design-only task to confirm which is actually true — and it changes the exact pg_dump invocation (whole-database dump vs. schema-filtered dump). §1 below gives commands that are correct either way (schema-scoped), but #1622 should run a read-only fly mpg list / psql -c '\l' / psql -c '\dn' pass first to confirm.
• Data size / row counts. I was not authorized to connect to the live databases for this design-only sub — every duration estimate in §1 is inferred from context (the product SoR's first real tenant is very recent, per the "Tenant lifecycle from the console" work), not measured. #1622 must measure real sizes before committing to a maintenance-window length.
• api.tuempresa.digital's Cloudflare proxy status (orange-cloud vs. grey-cloud/DNS-only) — not stated as a decided fact anywhere I read; DEPLOY-FLY.md's own DNS section presents both as options. This materially changes the DNS-cutover risk profile (§2).
• Render Postgres support for incoming logical replication as a subscriber, and Render's actual backup/PITR retention window — not confirmed against Render's current docs in this pass; needed only if the dual-sync path (§3) is chosen over a maintenance window.
• Fly Postgres's current wal_level (must be logical for logical replication) — not checked.

1. pg_dump / pg_restore approach

Recommendation: schema-scoped custom-format dumps, restored with --no-owner --no-privileges. Scoping by schema (rather than trusting "these are separate databases") is a defensive move that is correct under either topology in the UNVERIFIED note above.

# Confirm PG major-version parity first (Fly source vs. Render target) — pg_dump -Fc restores
# forward across newer major versions but is not guaranteed backward.
psql "$DATABASE_URL" -c 'SHOW server_version;'
psql "$RENDER_DATABASE_URL" -c 'SHOW server_version;'   # after Render's Postgres is provisioned

# Graph DB (DATABASE_URL) — plan schema only. Deliberately EXCLUDES the pgboss schema (see below).
pg_dump "$DATABASE_URL" -Fc --schema=plan -f graph.dump

# Product DB (PRODUCT_DATABASE_URL) — control schema + every per-client schema.
# pg_dump's -n/--schema accepts glob patterns (same rules as psql's \d), so 'client_*' matches
# every tenant schema without enumerating them.
pg_dump "$PRODUCT_DATABASE_URL" -Fc --schema=product --schema='client_*' -f product.dump

# On the Render target: extensions must exist BEFORE restore (pg_dump does not install them).
psql "$RENDER_PRODUCT_DATABASE_URL" -c 'CREATE EXTENSION IF NOT EXISTS vector;'
psql "$RENDER_PRODUCT_DATABASE_URL" -c 'CREATE EXTENSION IF NOT EXISTS pg_trgm;'

pg_restore --no-owner --no-privileges -d "$RENDER_DATABASE_URL" graph.dump
pg_restore --no-owner --no-privileges -d "$RENDER_PRODUCT_DATABASE_URL" product.dump

• --no-owner --no-privileges: Fly's default connection role and Render's provisioned role will not be named identically — restoring owners/grants verbatim throws errors; the app connects with its own role either way, so ownership is re-established by the connecting user at restore time.
• pg-boss (pgboss schema) is deliberately NOT dumped. Recommendation: let the queue drain instead of migrating it. Reasoning: pg-boss's own tables are library-managed (not a Drizzle migration this repo owns), and every scheduled job type running today — class-reminders, payment-reminders, reconcile-sweep (apps/api/src/email/reminders-worker.ts, apps/api/src/reconciliation/sweep-worker.ts) — is an idempotent sweep, not a unique irreplaceable record. The safe cutover sequence is: stop enqueuing new jobs on Fly → let in-flight jobs finish → let pg-boss create its schema fresh on Render on first boot. This avoids fighting two independently-versioned pg-boss schema migrations across Fly's and Render's library versions. Flagging this as a recommendation for #1622 to confirm, not a decision this design-only sub can make on its own.
  • plan.mirror (apps/api/src/queue/index.ts's PLAN_MIRROR queue, apps/api/src/sync/ mirror-worker.ts) is NOT covered by the "idempotent sweep" reasoning above and needs its own drain gate. Unlike the three sweeps, a plan.mirror job is enqueued once per graph write (create/update/status-change/link) and, per its own docstring, nothing else re-enqueues it later — there is no periodic full reconciliation across every plan_node that would catch a job lost to a dropped pgboss schema. If cutover drops the queue while a plan.mirror job is still pending, that specific graph node silently stops mirroring to GitHub until something else happens to touch it again. Required before discarding pgboss on cutover: confirm the plan.mirror queue is at zero — no pending AND no active jobs (SELECT count(*) FROM pgboss.job WHERE name = 'plan.mirror' AND state IN ('created','retry','active')) — separately from just "the sweeps have drained"; if this fails to reach zero within the cutover window, wait or reconcile before proceeding.
• Post-restore correctness check, not a live migration: run each of the three db:migrate* scripts (apps/api/package.json: db:migrate, db:migrate:plan, db:migrate:product) against Render immediately after restore. All three should report zero pending migrations — that is the signal the restored schema exactly matches what develop's migration files describe. If any reports pending SQL, the restore is incomplete or Render's schema drifted from what was dumped — do not proceed to cutover.
• Expected duration — estimate, not measured (see UNVERIFIED): the product SoR's first real tenant is very recent and the per-client dataset is a handful of provisioned tables with normal row counts (not bulk media/blob storage) — a dump+restore round-trip at this stage is very likely a low-single-digit-minutes operation, dominated by connection/extension setup rather than data volume. This must be re-measured with real numbers before #1622 sizes a maintenance window — SELECT pg_size_pretty(pg_database_size(current_database())); and a per-schema pg_total_relation_size sweep are the first two commands #1622 should run, live, before deciding anything else.
• Dry-run venue: sub #1619 (apps/api on Render, staging) is the natural place to rehearse this entire dump → restore → migration-check sequence against a copy of the data (or seed/fixture data) before doing it for real in #1622.

2. DNS TTL lowering timeline

The database migration itself needs no public DNS change — DATABASE_URL / PRODUCT_DATABASE_URL are connection-string secrets swapped on the app at cutover time, not resolved by the app via a public hostname lookup. TTL only matters for the application's own DNS record, api.tuempresa.digital (and the portal/console records, out of this sub's scope), and only because the DB cutover has to happen in lockstep with the app cutover (#1623) — the dual-sync window in §3 has to outlast whatever fraction of clients are still resolving the OLD record after the new one is published.

• api.tuempresa.digital is already on Cloudflare (apps/api/DEPLOY-FLY.md § DNS). Whether it's proxied (orange cloud) or DNS-only (grey cloud) is not confirmed in this pass — the two cases have very different cutover risk:
  • Proxied: the client-visible A/AAAA record is Cloudflare's own anycast IP and does not change at cutover — only Cloudflare's internal origin pointer changes, which propagates across Cloudflare's edge in seconds, not by the record's TTL. In this case the DNS side of cutover is a "flip the origin, confirm both directions serve traffic" operation on the order of minutes, and the TTL choreography below is largely moot.
  • DNS-only: classic TTL-bound propagation applies in full — clients (and resolvers that cached the old value) keep hitting Fly until their cached TTL expires.
• Recommended timeline (standard practice, not something José has pre-approved — flag before #1623 runs it):
  1. T-72h: confirm current TTL + proxy status for api.tuempresa.digital (dig, Cloudflare dashboard).
  2. T-48h: lower TTL to 300s if it's currently higher.
  3. T-24h: lower again to 60s.
  4. Cutover window: flip the record once Render is confirmed healthy and the DB verification checklist (§5) has passed.
  5. T+24h after the cutover is confirmed stable: restore a normal TTL (e.g. 3600s) to reduce resolver load.
• If Cloudflare proxying is adopted for the app cutover (a natural fit, since Cloudflare is already this effort's DNS/domain-automation provider — sub #1615/#1621), this whole TTL choreography collapses to "flip after the DB migration is verified." This is a recommendation for sub #1623 to confirm, not a decision this DB-focused sub can make unilaterally.

3. Dual-sync window strategy (only if near-zero downtime is required)

Recommendation: given today's scale (one real tenant, MVP stage), prefer a short maintenance window over dual-write sync. A 15–30 minute read-only-or-fully-down window is dramatically lower risk than logical replication for a workload this small, and removes an entire class of failure mode (replication lag, slot exhaustion, schema-change-mid-sync conflicts). Flag this recommendation to José before #1622 executes — it trades a small, scheduled downtime window for a much simpler and safer migration, and is exactly the kind of "prefer the free/simple fix" call cost-decisions.md asks for even outside a strict $-cost context.

The maintenance window's actual steps (the part the paragraph above doesn't spell out) — an old-writer shutdown gate, not just "a window exists":

1. Stop Fly from accepting writes first. Scale the Fly apps/api machine(s) to zero (or an app-level maintenance/read-only mode, if one exists by then — none does today, see step 4 of the dual-sync path below). Confirm no write traffic is landing (pg_stat_activity shows no active write queries against either database) before proceeding — running §1's pg_dump while Fly is still accepting writes produces an inconsistent snapshot, and any write that lands after the dump starts is silently lost at cutover.
2. Run §1's pg_dump / pg_restore sequence against the now-quiescent Fly source.
3. Run §5's verification checklist against Render.
4. Flip DATABASE_URL/PRODUCT_DATABASE_URL + the DNS record (§2) to Render together.
5. Resume traffic on Render (scale its service up / lift the read-only mode).

If step 1 cannot actually stop Fly from accepting writes for the maintenance-window's full duration (e.g. a way to reach the app bypasses the scale-to-zero, or an app-level read-only mode is added but has gaps), this simple path is not safe and the dual-sync approach below (with true logical replication) is required instead — don't run a "maintenance window" that isn't actually enforced at the database-write level.

If near-zero downtime is required (e.g. once real paying-tenant traffic exists and a maintenance window is no longer acceptable), the dual-sync approach:

First, resolve the exact-topology UNVERIFIED item above — current_database() alone is not enough (two different clusters can have same-named databases); compare BOTH current_database() and pg_control_system().system_identifier (a genuine per-cluster unique id) against DATABASE_URL and PRODUCT_DATABASE_URL to confirm whether they are the same physical database (schema-separated) or two separate databases/clusters. This changes the shape of steps 1-2 below:

• Same database: ONE replication slot, ONE snapshot, ONE publication (covering plan + product + every client_* schema together), ONE subscription.
• Separate databases: run steps 1-2 independently, twice — one slot/snapshot/publication/ subscription pair per database — and require both subscriptions to reach zero lag (step 3) before cutover, not just one.

1. Seed, pinned to the exact LSN the subscription will resume from — not a plain, unpinned pg_dump. A separate pg_dump and CREATE SUBSCRIPTION (each picking its own snapshot) either double-copies data (if the subscription's default copy_data = true re-copies everything the dump already restored) or, worse, loses every write between the dump's snapshot and the subscription's start LSN (if copy_data is disabled naively). The safe handoff — note this needs the replication protocol, not the plain pg_create_logical_replication_slot() SQL function, which has no snapshot-export mode: over a replication connection (e.g. pg_recvlogical, or a client speaking the replication protocol), issue CREATE_REPLICATION_SLOT <name> LOGICAL pgoutput (SNAPSHOT 'export'), keep that connection open, run pg_dump --snapshot=<the exported snapshot id> against the still-open session, and restore the dump to Render. Stop here — do NOT create the subscription yet (it needs a publication to reference first, step 2 below).
2. Replicate: create the Postgres native logical replication PUBLICATION on the Fly source FIRST, then the SUBSCRIPTION on the Render target referencing the pre-created slot from step 1 (CREATE SUBSCRIPTION ... WITH (slot_name = '<name>', create_slot = false, copy_data = false)) — replication resumes from precisely where the dump snapshot ended, with no gap and no duplicate copy. (If the subscription is ever created before its publication for any reason, ALTER SUBSCRIPTION ... REFRESH PUBLICATION afterward — but the ordering above avoids needing that.) FOR TABLES IN SCHEMA does not accept a glob (unlike pg_dump's -n 'client_*', per §1) — every schema in the publication must be named explicitly: plan on the graph DB, and product + the literal, enumerated list of every client_* schema that exists at the time (never pgboss, per §1). This means: freeze new-tenant schema provisioning for the duration of the migration window (a new client_<shortid> schema created after the publication is defined would silently NOT replicate), enumerate the schema list immediately before creating the publication, and re-verify that list is still complete right before cutover. This needs (a) Fly's source Postgres running with wal_level = logical and (b) Render Postgres accepting an incoming logical-replication subscription — both UNVERIFIED, confirm before relying on this path. Sequences are NOT replicated (a native REFRESH SEQUENCES only ships in PG19+, unconfirmed whether Render/Fly are on it) — product.drizzle.__drizzle_migrations.id (SERIAL) replicates its ROWS but not the underlying sequence counter, so if a migration runs on Fly after the seed snapshot, Render's sequence can be behind and a post-cutover migration could collide on id. Immediately after freezing writes (step 4) and before resuming on Render, manually sync every replicated sequence's value from the source (setval to the source's current value, or ALTER SUBSCRIPTION ... REFRESH SEQUENCES if PG19+ is confirmed available) — validate this in sub #1619's rehearsal alongside the rest of this procedure.
3. Drain lag: monitor pg_stat_subscription (specifically latest_end_lsn vs. the source's current LSN) until replication lag is at or near zero and holds there.
4. Freeze writes on Fly: put the app into a brief read-only/maintenance mode. Note: the app already degrades gracefully when the DB env vars are entirely unset (isDbConfigured() guards in apps/api/src/db/index.ts / packages/domain/src/db.ts, returning 503s rather than crashing) but a true "DB is configured but read-only" mode does not exist today — a small, explicit addition (not something this design-only sub should build) would be needed to freeze writes without also freezing reads.
5. Confirm zero lag, then flip DATABASE_URL/PRODUCT_DATABASE_URL secrets + the app's DNS (§2) to Render together.
6. Resume writes on Render; tear down the now-one-directional subscription.

The pgboss/queue schema is deliberately excluded from replication for the same reason it's excluded from the dump in §1 (idempotent sweeps, safe to recreate fresh).

4. Rollback steps

Failure point	Detection	Rollback	Data-loss risk
db:migrate:all (or Render's equivalent release step) aborts	Render deploy fails closed — mirrors Fly's existing fail-closed contract (apps/api/DEPLOY-FLY.md)	Nothing to undo — traffic never routed to Render, Fly keeps serving	None
pg_dump/restore completes but §5's verification fails	Row-count/checksum mismatch, or a Drizzle journal still reports pending migrations	Do not flip secrets or DNS; discard the Render database and re-run from §1	None — the dump/restore never mutates the Fly source
Secrets/DNS already flipped, then a post-cutover smoke test (§5) fails	/healthz fails, a critical read 5xxs, or a spot-check surfaces corrupted/missing data	Immediately flip DATABASE_URL/PRODUCT_DATABASE_URL and the DNS record back to the Fly values; keep the Fly app + Fly Postgres running, untouched, until Render is re-proven	Real risk — any write that landed on Render during the flipped window and was not replayed back to Fly can be lost on revert

• The real-risk row is exactly what §3's dual-sync exists to bound. If logical replication was running, the same subscription can be re-pointed Render→Fly to catch Fly back up before or immediately after reverting. If a plain maintenance window (no dual-sync) was used instead, any write during the flipped-to-Render period needs a manual diff-and-replay (compare updated_at/equivalent timestamps on both sides for the affected tables) — this is the concrete cost of skipping dual-sync, to weigh against §3's recommendation.
• Never decommission Fly until (a) the full §5 verification passes on Render AND (b) a full stable production window (José sets the length) has run clean with zero rollback triggers. The effort's own dependency graph already encodes this: sub #1624 (decommission Fly) depends on #1623 (final cutover), which depends on this migration succeeding first.
• Render Postgres also carries its own backup/point-in-time-recovery mechanism as a second safety net on the target side — retention window not confirmed in this pass, verify before relying on it.

5. Post-migration verification checklist

Run all of these before decommissioning any Fly database, and before considering the migration "done" for the purposes of sub #1624:

• Row counts — compare table-by-table between Fly and Render for every table in plan, product, and each client_* schema (SELECT count(*) FROM <table>, or SELECT relname, n_live_tup FROM pg_stat_user_tables for a fast approximate pass first). Run this either immediately after pg_restore (before any writes resume anywhere) or as the final gate of a dual-sync's zero-lag check — never with writes landing on only one side mid-comparison.
• Checksums / DDL diff — pg_dump --schema-only on both sides, diffed, to catch a missed column/index/constraint; for a handful of high-value tables, an ordered aggregate hash (SELECT md5(string_agg(t::text, '' ORDER BY id)) FROM tablename t) catches row-content drift a count alone would miss.
• Critical-table spot checks (a human reads a small sample — never paste PII into an issue/PR, per the repo's PII-handling precedent):
  • project (control schema) — every tenant present, data_schema correct, clerk_org_id intact.
  • person, person_identity, student_account/student_credential (per-client) — identity fields (email/phone/RFC) intact for a handful of real records per tenant.
  • payment, stripe_product_mapping, points_ledger — monetary values match exactly (pg_dump/ restore is lossless for numeric/decimal types by design; this is a sanity check, not an expected failure mode).
  • plan_node/plan_link (plan schema) — a few rows resolve correctly, and the GitHub sync (apps/api/src/sync/*) can read/write against Render on its first sync tick without erroring.
  • memory_chunk — confirm the vector extension round-tripped (a SELECT embedding <-> embedding FROM memory_chunk LIMIT 1 style self-distance query returning 0 is a cheap sanity check that the column type survived the dump/restore correctly).
• Migration-journal sanity — all three Drizzle journals (apps/api public, packages/db plan, packages/domain product) report zero pending migrations against Render.
• Application smoke test — /healthz returns 200; the GitHub webhook round-trips; pg-boss on Render registers and completes at least one full sweep cycle (class-reminders/payment-reminders/reconcile-sweep) over a multi-day window with no errors — this mirrors the effort's own Phase-1 verification bullet for sub #1620 (pg-boss reliability on Render's paid Starter tier) and should be satisfied jointly with it, not treated as a second separate wait.
• Only once every item above is green, and a full stable production window has run clean on Render, does sub #1624 (decommission Fly's databases) become safe to run.

Region note (non-blocking, flagged for the app-hosting subs)

apps/api/fly.toml's own comment flags the current region choice as a known compromise: gru (São Paulo) was chosen only to co-locate with the existing MPG cluster, with an explicit TODO to "relocate app+DB to a MX-near region together later." This migration is the natural occasion to revisit that — Render's region list doesn't include a South America or native-Mexico option either, so the actual choice (likely a US region) is a call for whoever executes sub #1617/#1619 (app hosting on Render), made jointly with this DB migration's target region so the app and its database stay co-located. Not a decision this design-only sub makes.

For agents

• apps/api/fly.toml — current Fly deploy config (single web+worker process, scale-to-zero, gru region, the two-migration release_command).
• apps/api/DEPLOY-FLY.md — the Fly runbook this plan mirrors the structure of; its "Two databases (ADR-006)" section and DNS section are the direct precedent for §§1–2 above.
• packages/domain/README.md, packages/domain/src/provisioning.ts, packages/domain/src/schema/client-tables.ts — the authoritative current per-client table list (provisioning.ts, not the README, which is stale on table names) and the vector/pg_trgm extension requirements.
• packages/db/drizzle.config.ts, packages/domain/drizzle.config.ts, apps/api/drizzle.config.ts — the three independent migration journals a Render cutover must satisfy.
• apps/api/src/queue/index.ts, apps/api/src/email/reminders-worker.ts, apps/api/src/reconciliation/sweep-worker.ts — pg-boss + the three scheduled job types behind the "let it drain, don't migrate it" recommendation in §1.
• knowledge/plan-engine.md §7 ("DB-separation decision + upgrade path") — the existing, separately recorded decision that the two databases share one Fly MPG cluster today as a documented compromise, with its own upgrade-path note; this migration is a candidate moment to also resolve that TODO (split the two onto genuinely separate Render Postgres instances) rather than recreate the same shared-cluster compromise on the new host — a decision for #1622/#1619, not this sub.
• .claude/rules/render-conventions.md — the Render CLI access convention (no resource names filled in yet; this migration is what will fill in the Postgres rows).

Dry-run results (sub #1622 prep)

Rehearsed 2026-09-06 (sub-issue of effort #1604) against local, disposable Docker Postgres — zero Fly/Render access used, zero production data touched, zero cost (containers removed after the run). Goal: prove the mechanical parts of §1 (schema-creation-from-migrations, dump/restore, post-restore migration-journal check) work, so #1622 executes a rehearsed script instead of improvising live. Verdict: the mechanical dump/restore/migrate sequence is rehearsed and correct — no blocker found.

What was rehearsed

1. Postgres image / extension compatibility (a real finding, not assumed). Tested a plain postgres:16 image first: CREATE EXTENSION IF NOT EXISTS pg_trgm succeeds (pgtrgm ships in Postgres's own contrib, bundled by the official image), but CREATE EXTENSION IF NOT EXISTS vector fails — extension "vector" is not available (no control file). Switched to pgvector/pgvector:pg16 (server version 16.15): both extensions install cleanly (vector 0.8.6, pg_trgm 1.6). Action for #1622/#1619: whichever Render Postgres offering is provisioned must ship pgvector pre-installed (Render's managed Postgres does — this is a note for the _dry-run/rehearsal tooling choice, not a finding about Render itself, which this sub had no access to check).
2. Fresh schema-creation path — all three journals, from empty. Ran db:migrate (@tedos/api, effectively a no-op — empty barrel schema per #717), db:migrate (@tedos/db, plan schema), db:migrate (@tedos/domain, product control schema) against two brand-new scratch databases (tedos_graph / tedos_product, one Postgres server, mirroring DEPLOY-FLY.md's example naming — the exact one-server-two-databases-vs-one-database-two-schemas topology stays UNVERIFIED per the note above; this rehearsal's schema-scoped commands are correct either way). All three applied cleanly with no errors.
3. Real client-schema provisioning path, not reinvented SQL. Inserted one synthetic product.project row (clerk_org_id = 'org_dryrun_synthetic') and re-ran @tedos/domain's db:migrate — this is the actual production migration script, which (per its own code) calls migrateAllClientSchemas() after the control-schema migration. It provisioned a fresh client_<shortid> schema end-to-end: CREATE EXTENSION vector + CREATE EXTENSION pg_trgm + all ~27 template tables + FKs + indexes, then ran the public_id backfill and the tag backfill — zero errors, zero manual DDL written for this test.
4. Synthetic data + vector round-trip. Seeded 2 synthetic person rows and 1 memory_chunk row with a real 1536-dim vector (0.1 repeated) in the fresh client schema. SELECT embedding <-> embedding (self-distance) returned 0 — the pgvector column type and its data both round-trip correctly through normal INSERT/SELECT (this was checked pre- and post-restore, see below).
5. §1's exact pg_dump/pg_restore commands, byte-for-byte, source → target. Ran the literal commands from §1 (pg_dump -Fc --schema=plan, pg_dump -Fc --schema=product --schema='client_*', extensions pre-created on target, pg_restore --no-owner --no-privileges) between two separate scratch Postgres containers (simulating cross-host restore, not same-server). Restore completed with no errors, no warnings.
6. Post-restore verification (§5's checklist, executed for real):
   • Row counts — person (2/2), product.project (1/1) matched source exactly.
   • Vector round-trip — embedding <-> embedding self-distance still 0 on the restored target (the vector column type + its data survive pg_dump -Fc/pg_restore intact).
   • Schema-only diff — pg_dump --schema-only on source vs. target, diffed: byte-identical except for pg_dump's own per-invocation \restrict/\unrestrict guard tokens (a newer-pg_dump client feature — cosmetic, unrelated to schema content; a real gotcha for #1622's automated diff step: strip ^\\(un)?restrict lines before diffing, or the diff will always show 2 spurious lines even on a perfect restore).
   • Migration-journal sanity — the signal §1 calls out explicitly. Re-ran all three db:migrate* scripts against the restored target. Zero pending migrations reported on all three — no SQL applied, and critically, the @tedos/domain migrate script's own isRelationExists/"reconciling journal" self-heal path (see packages/domain/src/migrate/ migrate.ts) never triggered — meaning the restored __drizzle_migrations journal rows matched the present migration files exactly, byte for byte, with no drift. Re-running db:migrate (@tedos/domain) also re-ran migrateAllClientSchemas() against the restored target and reported 1 migrated (0 newly created) — the idempotent re-provisioning pass is a confirmed no-op on an already-current schema, exactly as designed.

What's still genuinely unverified (needs a live pass at #1622/#1619)

• PG major-version parity, Fly vs. Render. This rehearsal used pg16 as a reasonable modern default — neither Fly's actual MPG version nor Render's actual provisioned version were checked (no access). §1's own SHOW server_version step on both sides, live, is still required before relying on pg_dump -Fc's forward-compat guarantee.
• Real data size / duration. This rehearsal's dataset was 2 rows + 1 vector row — dump/restore completed in low single-digit seconds, confirming the mechanism works but saying nothing about duration at real scale. §1's own call to measure pg_database_size/pg_total_relation_size live before sizing a maintenance window stands unchanged.
• pg-boss / pgboss schema exclusion — not exercised here (no pg-boss process was booted against the scratch DBs; this rehearsal was schema-migration mechanics only, not a full app boot). The "let it drain, don't migrate it" recommendation in §1 is unchanged but unverified by this sub.
• Cross-host role/ownership mismatch — both scratch containers happened to use the same postgres superuser role name, so the --no-owner --no-privileges flags were exercised mechanically (present in the command, no errors) but the actual "Fly's role name differs from Render's" scenario this flag exists for was not reproduced.

New finding — Render Postgres logical replication + PITR (resolves 2 of the 4 original UNVERIFIED items)

Pulled directly from Render's own docs (render.com/docs/postgresql-logical-replication, render.com/docs/postgresql-backups) — public docs, no live-system check, but a real answer where the original doc had none:

• Render Postgres CAN be a logical-replication subscriber, confirming §3's dual-sync path is viable in principle: "Render Postgres databases do not enable logical replication by default… enable logical replication for (both publishers and subscribers)" — a Render DB can be either side. But it is not self-service: enabling it requires contacting Render support via the Dashboard (not an API/CLI call), providing the service ID(s), the publisher-side role name, and the schema list; CREATE PUBLICATION ... FOR ALL TABLES specifically requires Render support to run it (superuser-only) — a customer can only self-run CREATE PUBLICATION ... FOR TABLE .... Two more requirements that change §3's calculus: the workspace must be on Pro plan or higher, and the database needs at least 10 GB storage — both are cost actions (cost-decisions.md) relative to today's free-tier PoC Render resources (render-conventions.md), and the support-ticket step adds unknown lead time to any dual-sync timeline. This strengthens §3's existing recommendation to prefer a maintenance window over dual-sync at today's one-tenant scale — the dual-sync path now has a paid-plan floor AND a support-ticket dependency, not just the technical complexity already documented.
• Render's PITR/backup retention: Hobby plan = past 3 days, Pro or higher = past 7 days; the Free compute plan has NO recovery/PITR capability at all — a production Render Postgres must be on at least a paid (Hobby) plan to have any backup safety net, which is itself a cost action to flag before #1622/#1619 provisions the real Postgres instance (still _TBD_ per render-conventions.md's Resources table).

Verdict

The mechanical dump/restore/migrate sequence is rehearsed and correct. No blocker found in the schema-creation path, the dump/restore path, or the post-restore migration-journal check. The remaining unknowns (PG version parity, real data size/timing, pg-boss exclusion in a live app boot, role-name mismatch) all require live Fly/Render access that this design-only, no-prod-touch sub correctly did not have — they are the right things left for #1622 to confirm, not gaps in this rehearsal's method.

Live findings — first real pass (sub #1622, 2026-09-06)

José confirmed live go-ahead to start #1622. Real production topology now confirmed (read-only, via a fly mpg proxy tunnel — no writes to Fly):

• One database, not two — now confirmed via system_identifier, not just current_database(). current_database() is fly-db for both DATABASE_URL and PRODUCT_DATABASE_URL, and their secret digests on tedos-api are identical (confirmed independently twice: once by the dry-run sub, once by fly secrets list) — but neither check alone was airtight (two different clusters can have same-named databases; identical secrets only prove the app was configured with one URL). Closed the gap 2026-09-06 via a read-only fly mpg proxy tunnel to the cluster (1zqyxr7leexowp8m): fly mpg databases list 1zqyxr7leexowp8m shows exactly one database in the entire cluster (fly-db) — there is no second database for PRODUCT_DATABASE_URL to possibly point at even in theory — and SELECT current_database(), (pg_control_system()).system_identifier returned fly-db / 7653759862728859694. Combined, this is no longer one-sided: schema separation only, no cross-database complexity, genuinely settled.
• Postgres 16.14 (Percona Distribution), wal_level = logical — the dual-sync path (§3) is technically available if ever needed, but at 18MB total it's overkill; the maintenance-window approach stays the right call.
• Real size: 18MB total. plan 4048 kB / 7 tables, client_e867da589b1149a68ad0073ec7291c2a (the one real tenant) 2392 kB / 25 tables, product 208 kB / 6 tables, plus a drizzle schema (32 kB, 1 table — apps/api's own migration-tracking table, empty journal, safe to let a fresh db:migrate recreate rather than dump). This confirms the plan's own estimate: a dump/restore round-trip here is a seconds-not-minutes operation.
• Target Postgres created: tedos-product-db (dpg-daeivagn74is73e2ndsg-a), region oregon (co-located with tedos-api-blueprint-poc), version 18, plan 0.1c-256mb ($6/mo) — downsized from an initial 1c-2g ($40/mo) guess once real pricing was checked; José's call, flagged as a real risk (this DB serves both plan and product + pgvector on 256MB RAM) but accepted given today's 18MB scale.
• Cross-version gap (source Postgres 16.14, target tedos-product-db version 18) — rehearsed 2026-09-06, clean. The original dry-run only exercised a same-version local Docker Postgres; this closes that gap with the actual version jump on a disposable local pair (pgvector/pgvector: pg16 16.15 source ↔ pgvector/pgvector:pg18 18.6 target, no production data): seeded a plan/product/client_*-shaped schema with a pgvector column and real vector data, ran §1's exact commands (pg_dump -Fc --schema=plan, pg_dump -Fc --schema=product --schema='client_*', vector extension pre-created on target, pg_restore --no-owner --no-privileges) — zero errors on dump or restore. Row counts matched exactly; embedding <-> embedding self-distance was still 0 post-restore (pgvector data survives the version jump intact); a pg_dump --schema-only diff of source vs. target was identical except for the expected cosmetic -- Dumped from database version 16.15 vs 18.6 header comment — no structural drift. Not re-run in this pass: the full db:migrate/db:migrate:plan/db:migrate:product journal-sanity check against this specific rehearsal pair (that mechanism was already thoroughly proven same-version in the earlier dry-run, and drizzle's migration runner has no Postgres-major-version-dependent logic — the actual version-sensitive risk was the dump/restore binary compatibility + extension survival, both now confirmed clean). If a real anomaly ever surfaces at the actual #1623 load, re-check this first.

New blocker found — deferred to #1623, not a gap in this sub: actually loading the schema + data onto tedos-product-db needs a process running inside Render's network (its connection is internal-only from outside from a local machine) or a public URL — and both of Render's paths for that turned out to be paid features not yet in place:

• External Database URL requires a paid feature/plan (confirmed live in the Dashboard, 2026-09-06) — a local pg_dump/pg_restore round-trip like the one rehearsed in this doc's dry-run section isn't possible for free.
• Render's pre-deploy-command (the release_command/db:migrate:all equivalent) is also paid-plan-only (already flagged in .claude/rules/render-conventions.md's Resources table).

Decision (José, 2026-09-06): defer schema + data population entirely to #1623. Rather than find a free workaround now (a Render Shell session or a one-off Job, both untested), tedos-product-db stays empty until the actual cutover deploy.

Migrations must be run explicitly at #1623 — there is no Fly release_command on Render. Fly ran pnpm run db:migrate:all (the three independent histories — db:migrate → db:migrate:plan against DATABASE_URL → db:migrate:product against PRODUCT_DATABASE_URL) automatically before any new machine served traffic. Render has no equivalent on the plan tier assumed here: its pre-deploy-command is a paid feature, and the app's normal boot does not run migrations. So #1623's cutover runbook must carry an explicit, named migration step — Render's paid pre-deploy-command or a one-off Render Shell / Job running pnpm run db:migrate:all — that executes after the data restore (this plan's §1 dump/restore) and before the DNS cutover. It is migrations only: it applies schema changes on top of the restored data and is never a substitute for the restore. apps/api/docs/render-final-dns-cutover-runbook.md §3.2 step 4 is where this lands (sub-steps 4 → 5 → 6: restore → migrate → verify). Confirm at execution time which of the two mechanisms the real production compute plan actually supports.

Related

• Effort #1604 — parent effort (Render + Cloudflare migration).
• #1619 — apps/api on Render (staging); the natural dry-run venue for §1.
• #1620 — pg-boss reliability on Render's paid Starter tier; shares the application-smoke-test gate in §5.
• #1622 — executes this plan against production (deferred, needs a fresh go-ahead).
• #1623 — final DNS cutover; consumes §2.
• #1624 — decommission Fly; gated on §5's checklist passing.