Looking for libraries to monitor postgres queries

[Gian Holland]

[Note: parts of this message were removed to make it a legal post.]

My goal is to display postgres queries as they happen or close to as they
happen

 

[Dan Fitzpatrick]

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

 

[Gian Holland]

[Note: parts of this message were removed to make it a legal post.]

Problem is I am not the system admin and he wont turn on logging, claims
there is a performance hit

 

[Dan Fitzpatrick]

Problem is I am not the system admin and he wont turn on logging, claims
there is a performance hit

Yeah there is definitely a performance hit on logging every query. What
are you trying to accomplish? I assume you are using Ruby for your app.
If so, you can othe query execution method of your db lib and capture
the query at that point and log it to a file before it hits the DB
server. If you are trying to log queries from other apps (that you don't
control), then I think you will have to use db logging or use a db proxy
like http://sqlrelay.sourceforge.net/ or
http://pgpool.projects.postgresql.org/. If all the apps use the proxy,
then the proxy can do the logging. (of course you may need that same
admin to set this up.)

Dan