cbcopy session get stuck

[adnanhamdussalam]

Hi,

I have successfully migrated from GP to CB by using cbcopy (version 1.1.4).
CB has 6 segments and now we have decided to move it to 4 segments.

So I have created another cb instance with 4 segments on same db server but with different port 65432.
Then I used cbcopy to transfer the data from 6 segments instance to 4 segments instance.

First I have imported the roles,permissions and metadata from 55432 to 65432 using pg_dump and psql.

So I have started it with cbcopy data only option and with copy-jobs 4 as it started normally but after sometimes the session got stuck on 65432. while inserting the data the session against it at 55432 copy command completed successfully and released the session but at 65432 side session remain there for hours when checked size of table it is same as on 55432 side but when I counted rows at 65432 it shows zero. Due to sessions stuck on 65432 no next table started as sessions was not released.

I have first tried with cbcopy 1.1.4 and then I try with latest binary of cbcopy 1.1.5 but same issue faced again.

Can you guide/support me on this issue?

PFB the cbcopy logs and sessions information:

$ psql mygp -p 65432
psql (14.4, server 14.4)
Type "help" for help.

mygp=# SELECT
  usename,
  datname,
  pid,
  state,
  now() - query_start AS query_time,
  substring(query, 1, 100) AS query_snippet,wait_event,wait_event_type,sess_id
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_time DESC;
 usename |  datname  |   pid   | state  |   query_time    |                                            query_snippet                                             | wait_event | wait_event_type | sess
_id
---------+-----------+---------+--------+-----------------+------------------------------------------------------------------------------------------------------+------------+-----------------+-----
----
 gpadmin | mygp | 2764944 | active | 00:23:17.879515 | INSERT INTO mycds.edc_detalle SELECT * FROM cbcopy_ext_1ac1fe84d7ea43438316149c45a10ca8        |            |                 |
133
                                                                                 |            |                 |
(5 rows)

mygp=# \dt+  mycds.edc_detalle
                                                 List of relations
   Schema    |    Name     | Type  |      Owner       | Storage | Persistence | Access method | Size  | Description
-------------+-------------+-------+------------------+---------+-------------+---------------+-------+-------------
 mycds | edc_detalle | table | u_mycds | heap    | permanent   | heap          | 71 GB |
(1 row)

mygp=# select count(*) from mycds.edc_detalle;
 count
-------
     0
(1 row)


mygp=# \q
$ psql mygp -p 55432
psql (14.4, server 14.4)
Type "help" for help.

mygp=# \dt+  mycds.edc_detalle
                                                 List of relations
   Schema    |    Name     | Type  |      Owner       | Storage | Persistence | Access method | Size  | Description
-------------+-------------+-------+------------------+---------+-------------+---------------+-------+-------------
 mycds | edc_detalle | table | u_mycds | heap    | permanent   | heap          | 71 GB |
(1 row)

mygp=# SELECT
  usename,
  datname,
  pid,
  state,
  now() - query_start AS query_time,
  substring(query, 1, 100) AS query_snippet,wait_event,wait_event_type,sess_id
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_time DESC;
 usename |  datname  |   pid   | state  |   query_time    |                                            query_snippet                                             | wait_event | wait_event_type | sess
_id
---------+-----------+---------+--------+-----------------+------------------------------------------------------------------------------------------------------+------------+-----------------+-----
----
 gpadmin | mygp | 2764991 | active | 00:23:20.310746 | COPY myapplied.contratos_1_prt_62 TO PROGRAM 'cbcopy_helper --compress-type gzip --seg-id < |            |                 |
330
 gpadmin | mygp | 2764989 | active | 00:09:17.737197 | COPY myapplied.contratos_1_prt_53 TO PROGRAM 'cbcopy_helper --compress-type gzip --seg-id < |            |                 |
329
 gpadmin | mygp | 2782473 | active | 00:00:00        | SELECT                                                                                              +|            |                 |    2
438
         |           |         |        |                 |   usename,                                                                                          +|            |                 |
         |           |         |        |                 |   datname,                                                                                          +|            |                 |
         |           |         |        |                 |   pid,                                                                                              +|            |                 |
         |           |         |        |                 |   state,                                                                                            +|            |                 |
         |           |         |        |                 |   now() - query_start AS query_time,                                                                +|            |                 |
         |           |         |        |                 |   substring(query,                                                                                   |            |                 |
(3 rows)

mygp=#

cbcopy log :

20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Committing transaction on destination database
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Successfully copied table "ws_16.routing_scope_stats"
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Executing isEmptyTable "mycds.edc_detalle" on source database
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] isEmptyTable, query is select 1 from mycds.edc_detalle limit 1
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Source table "mycds.edc_detalle" is not empty
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] There are 313933408 rows in the source table "mycds.edc_detalle"
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Executing setup query:
SET application_name TO 'cbcopy_20260419162735';
SET search_path TO pg_catalog;
SET statement_timeout = 0;
SET DATESTYLE = ISO;
SET standard_conforming_strings = 1;
SET enable_mergejoin TO off;
SET gp_autostats_mode = NONE;
SELECT set_config('extra_float_digits', (SELECT max_val FROM pg_settings WHERE name = 'extra_float_digits'), false);
SET synchronize_seqscans TO off;
SET INTERVALSTYLE = POSTGRES;

SET client_encoding = 'UTF8';
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Starting transaction on destination database
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Truncating table "mycds.edc_detalle"
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Finished truncating table "mycds.edc_detalle"
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] External web table command of receiving data: CREATE EXTERNAL WEB TEMP TABLE cbcopy_ext_1ac1fe84d7ea43438316149c45a10ca8 (like mycds.edc_detalle) EXECUTE 'cbcopy_helper --compress-type gzip --listen --seg-id $GP_SEGMENT_ID --cmd-id 2223b5fc-816d-4a89-8d2d-e7aa3139a68e --client-numbers 2,2,1,1 --data-port-range 1024-65535 --direction receive' FORMAT 'csv'
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Finished creating external web table cbcopy_ext_1ac1fe84d7ea43438316149c45a10ca8
20260419:16:29:00 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 3] Finished executing query
20260419:16:29:01 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Retrieving ports of helper programs:
        SELECT segID AS content, port AS port
        FROM public.cbcopy_ports_temp_onall_20260419162735
        WHERE cmdID = '2223b5fc-816d-4a89-8d2d-e7aa3139a68e'
20260419:16:29:01 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] Retried 1 times to get segment helpers' ports, got 4 items
20260419:16:29:01 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 1] COPY command of sending data: COPY mycds.edc_detalle TO PROGRAM 'cbcopy_helper --compress-type gzip --seg-id <SEGID> --host 10.10.247.112,10.10.247.112,10.10.247.112,10.10.247.112,10.10.247.112,10.10.247.112 --port 1030,1024,1028,1029,1030,1024 --direction send' ON SEGMENT CSV IGNORE EXTERNAL PARTITIONS
20260419:16:29:04 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 3] Dropping external web table cbcopy_ext_22a7f7ce49734217be1296a9e2e7f242
20260419:16:29:04 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 3] Finished droping external web table cbcopy_ext_22a7f7ce49734217be1296a9e2e7f242
20260419:16:29:04 cbcopy:gpadmin:MY-CB:2764769-[DEBUG]:-[Worker 3] source "mydev"."tempdb_vaca_20220805" affected rows 111511, dest "mydev"."tempdb_vaca_20220805" affected rows

Thank you for your support

[talmacschen-arch]

Looking at this — initial triage below.

What the symptom is telling us

• Source-side COPY mycds.edc_detalle TO PROGRAM ... is no longer in pg_stat_activity — the source COPY for that table has already exited.
• Dest-side INSERT INTO mycds.edc_detalle SELECT * FROM cbcopy_ext_... is still running with empty wait_event — it's not waiting on a lock or LWLock, it's blocked reading from the external web table.
• 71GB heap with count(*)=0 from another session is expected: the dest INSERT hasn't committed yet, MVCC hides the tuples, the relfile is just extended.
• --client-numbers 2,2,1,1 means each of your 4 dest segments expects N inbound TCP connections (2/2/1/1) from source-side send helpers and needs all of them to deliver EOF before the receive helper exits. If any one of those inbound connections doesn't cleanly close, the dest INSERT hangs forever.

Most likely root cause

The receive-side helper has no read deadline on accepted connections. In helper/concurrent_server.go (processCsvRecord):

for {
    record, er := cr.Read()       // blocking read, no SetReadDeadline
    ...
    if er == io.EOF { break }
}

The 600s deadline in helper/server.go is on listener.Accept, not on established connections. WaitForFinished() only returns when numFinished == numConn. So if any one of the 6 source-segment send helpers terminates without cleanly closing its socket (process killed, OOM, kernel didn't push FIN, half-close, etc.), the corresponding read goroutine on dest blocks forever, os.Stdout is never closed, and the external web table on dest never sees EOF.

That matches the observed state precisely: source-side COPY for the stuck table is gone, dest INSERT is alive but making no progress, and there's no wait event because it's a clean pipe read with nothing on the other end.

Diagnostic info that would confirm it

While the session is stuck:

# 1) Helper processes alive on both sides
ps -ef | grep cbcopy_helper | grep -v grep
# Expect 4 receive-side helpers on dest. Compare send-side count to what should be there.

# 2) Kernel stack of the stuck dest backend (pid 2764944 in your dump)
sudo cat /proc/2764944/stack
sudo cat /proc/2764944/wchan

# 3) Find the receive helper attached to that backend, check its TCP state
sudo lsof -p <recv-helper-pid> | grep -E 'TCP|pipe'
sudo cat /proc/<recv-helper-pid>/stack
# sk_wait_data / tcp_recvmsg + peer process already gone on the source side = confirmed.

# 4) TCP state of the data ports
ss -anp | grep -E ':(1024|1028|1029|1030)\b'
# Look for ESTABLISHED where one side has no owning process, or CLOSE_WAIT residue.

# 5) Did anything kill a helper recently?
dmesg -T | grep -iE 'oom|killed|cbcopy_helper'

Workarounds worth trying

1. --data-port-range 40000-65000. The default 1024-65535 walks the range from the low bound and takes the first free port (helper/server.go:53-59), so your listeners ended up on 1024/1028/1029/1030 — that's the system/service port region. Linux ephemeral default is 32768–60999. On a single-host two-cluster setup with multiple workers, the low end is more likely to overlap with stale state or non-ephemeral usage.
2. --copy-jobs 1 or 2 for the run that includes the 71GB table. Removes worker contention as a variable and gives a cleaner repro signal.
3. Single-host 6→4 reshape pushes all helper traffic over loopback and shares CPU/IO between source and dest segments. If the box is tight on resources, helpers are more likely to be OOM-killed mid-stream — dmesg will show it.

Underlying robustness gap (for maintainers)

Even after the first cause that killed a sender is identified, the receive side has no timeout safety net: once a connection is accepted, a peer that never delivers EOF hangs the INSERT indefinitely. A per-connection read deadline / heartbeat / total-time bound in processCsvRecord would convert "hung for hours, no error" into "fails with a clear error and unblocks the worker". Happy to send a PR if there's interest.