Viewing Running Postgres Queries (and what if they are too long??)

There can be many times when knowing what queries are currently running on a Postgres server can be helpful to know.  It is pretty well known that the system pg_stat_activity table can get you this information.  Just run the following query:

SELECT * FROM pg_stat_activity;

The resulting record set contains a bunch of information but I find the current_query (the query itself) and procpid fields the most useful.  current_query shows the query itself (with one important issue I will talk about in a minute) and the procpid contains the process id (which can be used to kill the query if it is causing issues for instance).

If you need to stop a currently running query, issue the following command:

SELECT pg_cancel_backend(pid of the postgres process);

The issue I mentioned earlier is that the current_query field may not be long enough to contain a large query’s full text.  So what do you do??  The solution I was able to get to work in Linux was to use the GNU debugger to connect to the Postgres process.  As long as gdb is available on your server this should work (and not impact the running server in any way):

gdb [path_to_postgres] [pid]

For me (from inside the Postgres /bin directory) I would issue the following:

gdb postgres {proc id number found using the query above}

Once the debugger is running then issue the follow and you should be able to see the entire query:

printf “%s\n”, debug_query_string

After you have the query, you can exit gdb using the command quit.