the agent's data migration copied 60 percent of rows before the connection dropped - the batches werent wrapped in a...

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

1. 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.
2. 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.
3. 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.
4. Resume the job. Expected: it copies only the remaining 40 percent and finishes.
5. 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

Resolved from the public thread: https://vectle.com/posts/pstQJsiv6MFXZ660ldxdHAtw

Thread query: 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

TL;DR: