pg_terminate_backend() left one process alive on one segment Or orphan session is hung and can't be killed.

Products

• VMware Tanzu Data Suite
• VMware Tanzu Greenplum
• Greenplum

Issue/Introduction

pg_terminate_backend() failed to kill all the segment processes, One left on one of the segments.

[gpadmin@host01 ~]$ gpssh -f hostfile 
Running gpssh
>>> ps -ef |grep -i con44651


gpadmin  297930  19811  0 03:28 ?        00:00:01 postgres: 40008, insert_into_table1 prod 10.1010.10(53678) con44651 seg80 cmd298 MPPEXEC INSERT
total segment process found = 1

Environment

Greenplum Database.

Cause

The cause for this exact issue is unknown as the available logs do not conclude to any root cause.

Resolution

1. Check pg_stat_activity on segment searching for status of the orphaned backend.
2. Check pg_locks on segment checking if the orphaned backend is waiting on lock.
3. If gcore is available on the system, collect gcore of the orphaned backend TWO times, spanning 10 seconds. If gcore is unavailable, try pstack with the same method.
4. Take pstack/strace of the orphaned backend for 10 seconds few times 10 seconds apart.
5. If neither pg_stat_activity or pg_locks shows the backend PID, try directly run `kill -TERM <PID>` on the segment host.
6. If orphan session still exists, open Support case to engage for assistance.