killing a hung query/process in greenplum

Sometimes a long running query in Greenplum can get stuck and you need to kill it. Here are the steps to do so. 

1. Get a list of all currently active connections on the Postgres server. Run the query below on the server:

SELECT * FROM pg_stat_activity ORDER BY client_addr ASC, query_start ASC

2. Each row in the results table corresponds to a postgres process. Find the row for the process you want to kill by looking at the ‘current_query’ column.

3. Record the process id. You can find it in the ‘procpid’ column from the row that contains the query you want to kill.

4. Kill the process by running the following query (replace ‘<pid>’ with the process id you found in step 3):

SELECT pg_cancel_backend(<pid>)

5. Reload the query from step 1 to confirm that the process is gone.