Intermittent duplicate key violation on retry after "Broken pipe" error during cbcopy migration Hi all, I'm migrating data from a Greenplum source cluster to a Cloudberry destination cluster using cbcopy. I'm running into a recurring issue with one of our larger partitioned tables (~900M rows, range-partitioned into ~21 partitions) and would appreciate any guidance. Environment: • Source: Greenplum cluster • Destination: Cloudberry cluster Background / sequence of events: 1. Initial attempt — I ran a full schema data copy in one go with --copy-jobs=8. This caused CPU utilization on the source to spike to 100%, and during this run I also got a "Broken pipe" error on our largest table (~900M rows):    ERROR: could not write to COPY program: Broken pipe (copy.c:607) (seg0 <source_ip>:<port> pid=...) (SQLSTATE XX000) 1. Revised strategy — To reduce load, I changed approach: • First ran a schema-only migration (metadata only, no data) — this completed successfully with no issues. • Then planned to migrate all smaller tables together (--data-only --truncate, excluding the two large tables), followed by the two large tables one at a time, each with reduced --copy-jobs. 2. Large table attempt #1 — partition by partition — For the ~900M row table, I split the copy into individual partitions (--include-table per partition, --copy-jobs=1 or 2) to further reduce load. During this, the same partition failed with "Broken pipe" mid-copy. On retry of that same partition (after --truncate), I got:    ERROR: duplicate key value violates unique constraint "<table>_<partition>_<column>_idx1" (SQLSTATE 23505) 1. Large table attempt #2 — whole table at once — I also tried running the same large table as a single --include-table job (not split by partition). This produced the same pattern: intermittent "Broken pipe" followed by a duplicate key violation on retry, on a different partition than before. 2. Full schema re-migration — To rule out leftover/stale data, I dropped the entire destination schema and re-migrated it from scratch. The schema and smaller tables migrated fine. However, when copying the large table again, the same duplicate-key error recurred on yet another partition — despite this being a completely fresh destination with no prior data. Troubleshooting steps I've taken to rule out other causes: 1. Verified no duplicate values exist on the source table for the column backing the unique index (checked specifically against the single-column unique index, not just the composite one). 2. Confirmed source and destination partition boundaries are identical. 3. Confirmed indexes on source and destination match exactly (same indexes, same definitions). 4. Confirmed no other cbcopy process was running concurrently (ps -ef | grep cbcopy). 5. Manually truncated (and vacuumed) the destination table/partition before each retry. 6. Reduced --copy-jobs to 1 to minimize concurrency and CPU/network load. 7. Repeated the entire migration on a freshly dropped and recreated destination schema — the same duplicate-key error still occurred, just on a different partition. My working theory: Since cbcopy streams data directly segment-to-segment (via cbcopy_helper) rather than through a single atomic transaction, I suspect that when "Broken pipe" occurs, some segments may have already committed their portion of data before the connection dropped on another segment. If cbcopy's retry logic then re-attempts the copy without re-truncating (or without full transactional rollback across all segments), it would re-send rows that other segments already committed — producing a duplicate key violation against data it just wrote itself. Questions: 1. Is this a known issue with how cbcopy handles partial failures / retries at the segment level? 2. Is there a recommended way to make the truncate-and-copy operation atomic per table/partition, so a broken pipe mid-copy doesn't leave partially committed data behind? 3. Are there recommended settings (timeouts, retry behavior, --connection-mode, concurrency limits, etc.) to reduce the frequency of the underlying "Broken pipe" errors in the first place — particularly for large tables where source-side CPU/network load seems to be a contributing factor? 4. Is per-partition copying or whole-table copying generally more reliable for very large tables (~900M+ rows) in your experience? Happy to share full debug logs (with sensitive details redacted) if that would help diagnose this further. Any guidance would be greatly appreciated. Thanks!

Hi, could you create an issue on Github? Then can help invite more community members to help with this issue.

Thanks for the detailed writeup — that's a lot of good elimination work already. One thing worth flagging up front: your own step 5 disproves your theory. You dropped and recreated the schema, so there was no leftover data for a retry to collide with, and you still got a duplicate key violation. That means the duplicates are being produced within a single copy run, not carried over from a failed one. Different problem than the one you're chasing. So I'd treat these as two separate issues and fix them in order. Broken pipe first. It's the primary fault and the duplicate key is downstream of it.

could not write to COPY program

is emitted on the source when the program behind COPY TO PROGRAM exits — so the destination-side helper went away. At

--copy-jobs=8

with the source pegged at 100% CPU, the likely chain is: source stalls → connection sits idle → something between the clusters (conntrack, NAT, LB) drops the flow → helper discovers it on the next write. Worth trying

--compression

to cut wire time, OS keepalives on both sides, and

--connection-mode=pull

if your firewall rules are asymmetric. On the duplicate key. GPDB 7.3.3 → CB 2.1.0 is a version mismatch, which means Copy On Segment is off the table — you'll be on Copy On External Table, which redistributes data on the way in. That makes distribution policy the thing I'd check first. Two specific things: 1. Compare

DISTRIBUTED BY

on source and destination for that table, exactly. Unique enforcement in GPDB/CBDB is per-segment, and it only equals global uniqueness because the unique columns must contain the distribution key. If the destination landed on a different key (or randomly distributed), rows that never collided on the source can land together on the destination and collide. This would reproduce on a fresh schema on a different partition every time, which matches exactly what you're seeing. 2. Confirm the copy set isn't picking up both the root partitioned table and its children. If both get copied, every row goes in twice. On atomicity — there isn't a cross-segment atomic retry. Per-partition is the right call at 900M rows: smaller blast radius, and you can resume from the

cbcopy_failed_$timestamp

list. Truncate the specific child partition, confirm it's at zero rows, then copy. If you can share two things I can probably narrow it further: the

DISTRIBUTED BY

clause from

\d+

on both sides, and the copy strategy line for that table from a

--debug

run. — Tushar, Synx Data Labs

@Dianjin Wang Yes sure i will create the issue thanks