Migrate the data

Once the schema is in place, the data step copies rows table by table. The source is read-only throughout; nothing is written back to it.

Order tables by foreign keys

Rows must land in an order that respects every foreign key. A row that references a parent that hasn't been inserted yet fails with:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(...) is not present in table "users".

The FK graph is topologically sorted so parents (users, products) are inserted before children (orders, order_items). Self-referencing tables and cycles are handled by inserting rows first and deferring the constraint. See foreign-key-violation if a specific constraint still fails.

Insert in batches

Rows are read from the source and inserted in batches of about 1000. Batching keeps memory bounded and stays under statement size limits on large tables. Each batch is a single multi-row insert:

INSERT INTO public.orders (id, user_id, total, created_at)
VALUES
  ('...', '...', 42.00, '2026-01-01T00:00:00Z'),
  ('...', '...', 17.50, '2026-01-02T00:00:00Z')
  -- up to ~1000 rows per statement
ON CONFLICT DO NOTHING;

ON CONFLICT DO NOTHING

Every insert uses ON CONFLICT DO NOTHING. This makes the step idempotent: if a batch is retried after a network drop, rows already present are skipped instead of raising:

ERROR:  duplicate key value violates unique constraint "orders_pkey"

Existing rows on the destination are never overwritten. If you need to re-run a table cleanly, truncate it first.

Resync sequences

Inserting id values directly does not advance the table's sequence. Without a fix, the next INSERT without an explicit id collides with a migrated row. After all rows land, each serial/identity column is corrected:

SELECT setval(
  pg_get_serial_sequence('public.orders', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM public.orders)
);

Read sequence-resync for why this runs last and what it covers.

Verify counts

After the step, compare row counts per table between source and destination:

SELECT count(*) FROM public.orders;

Numbers should match. A shortfall usually means a batch hit an FK violation that was logged and skipped.

Next: migrate auth users so their bcrypt hashes and IDs carry over.