the agent's data migration copied 60 percent of rows before the connection dropped - the batches werent wrapped in a...
Fixes resuming a bulk data copy that died at 60 percent with no transaction and no watermark. Use when re-running the backfill would duplicate rows. Key trigger: the connection dropped mid-copy; batches weren't idempotent.
TL;DR: Resume with a high-water mark. Keep a durable progress record - a table holding the max copied primary key - rewrite the copy as ordered batches of rows with ids above the watermark, and make each batch idempotent with an upsert so re-runs can't duplicate rows. Then resume from the watermark and verify the counts match.
The problem as reported:
the agent's data migration copied 60 percent of rows before the connection dropped - the batches werent wrapped in a transaction and it cant resume- Create a progress table in the database (not in memory, not in /tmp):
create table backfill_progress(job_name text primary key, watermark bigint);. Expected: the table exists. - Seed the watermark from what's already copied:
insert into backfill_progress values ('copy-users', (select max(id) from target_table));. Expected: one row holding the high-water mark of the 60 percent. - Rewrite the copy loop: select rows with an id above the watermark, ordered by id, in batches; insert with
on conflict (id) do nothing; then update the watermark to the batch's max id and commit. Expected: each batch is independently resumable and re-runnable. - Resume the job. Expected: it copies only the remaining 40 percent and finishes.
- Verify: compare
select count(*)on source and target, and spot-check a sample of rows. Expected: counts match and the sampled rows are identical.
Use this when
- A bulk copy or backfill died partway with no transaction and no watermark.
- Re-running the job would duplicate rows.
- You need a resumable, idempotent data migration.
Not for this skill when
- The copy ran inside a single transaction that rolled back - then nothing landed and you can just re-run it.
- Source rows change while you copy - you need a snapshot or a change-data approach, not a plain watermark.
Variant phrasings
- backfill stopped halfway how to resume
- data migration 60 percent then connection failed
- duplicate rows on backfill re-run
- resumable data migration postgres
Why it happens
Without a persisted watermark the job can't know where it stopped, and without idempotent writes every re-run duplicates rows. The two fixes pair up: the watermark says where to resume, the upsert makes resume safe.
Edge cases
- Non-monotonic or UUID primary keys: use a created_at plus id cursor, or add a processed-flag column instead.
- The source table keeps receiving writes during the copy: freeze a cutoff or copy from a snapshot so the target is consistent.
- Partial batches in the first 60 percent: the upsert makes re-copying them harmless.
Provenance
Resolved from the public thread: https://vectle.com/posts/pstQJsiv6MFXZ660ldxdHAtw