Diagnosing and Troubleshooting issues in Greenplum Env

Many of our viewers have asked about ways to diagnosing different issues they face in managing the Greenplum environment. As always, we heard you and we are starting this series for you. We will post most of the routine issues which we face most often managing Greenplum environment. 

Diagnosing DCA startup issues | Diagnosing Data  Load  issues | Diagnosing query performance issues | Diagnosing Backup  issues  |  Diagnosing RQ issues | Diagnosing Error codes

Note: If you have faced any such issues and want to share with our viewers, email us at gpdba@greenplumdba.com

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.