Using pg_stat_activity in pgadmin to check query status on the CSS server

Often you may want to check the activity on the postgres server to see specifically what is running on it. A good way to do this is with the following query which can be run from the pgadmin query window:

Select * from pg_stat_activity;

No search_path variable is needed as this is a command which is run directly on the server.

A more interesting modification is to run the following which will just check for active queries, normally removing inactive pgadmin and CAST-MS sessions:

select * from pg_stat_activity where state != 'idle';

By checking the query activity, you can see if the same query remains hanging for a long period of time. Often as well, when you cancel processing in CAST-MS when a process is hung on a long query, the query will still be active on the postgres server and then would need to be canceled and possibly terminated.

To cancel or terminate a query, you first need the process id (pid) from the pg_stat_activity query above which is in the 3rd column.

You should also take care with this process and make sure the process id you are looking at is the correct one. If you have multiple CAST operations going on multiple databases simultaneously, then there may be multiple similar queries being run.

To cancel the back end query, you would then run:

select pg_cancel_backend (<pid from above);

A cancel is more graceful way for the query to end, but sometimes you may need to just have it stop, so then need to run the following to terminate the back end query:

select pg_terminate_backend(<pid from above>);