From: Dan Fitzpatrick <dan@...>
Date: 2008-02-06T16:59:52+09:00
Subject: Re: Looking for libraries to monitor postgres queries

Gian Holland wrote:
> My goal is to display postgres queries as they happen or close to as they
> happen
>
I don't know a library to do it but here are a few options.

1. You can setup logging and tail the logs. In postgresql.conf (ERROR 
REPORTING AND LOGGING section). Mine looks like this (on development box):

log_destination = 'stderr'
redirect_stderr = on
log_directory = '/var/log/pgsql'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
silent_mode = on
log_duration = on
log_statement = 'all'


2. You can query the pg_stat_activity table at your convenience. You 
need to be a super user to see all the queries. Otherwise it will only 
show the ones you own I think.

SELECT procpid, current_query, waiting
FROM   pg_stat_activity
WHERE  datname = 'your_database_name'
AND    current_query <> '<IDLE>';

(last line is optional)

These other fields are also available from the pg_stat_activity view:

    Column     |           Type           | Modifiers
---------------+--------------------------+-----------
 datid         | oid                      |
 datname       | name                     |
 procpid       | integer                  |
 usesysid      | oid                      |
 usename       | name                     |
 current_query | text                     |
 waiting       | boolean                  |
 query_start   | timestamp with time zone |
 backend_start | timestamp with time zone |
 client_addr   | inet                     |
 client_port   | integer                  |

If you need to kill an out of control query, you can use the procpid to 
get the system process ID of the query and run:

Slow kill: kill -TERM procpid
Fast kill: kill -INT procpid
Immediate: kill -QUIT procpid

(Do not use kill -9)

Hope that helps.

Dan