# Looking for libraries to monitor postgres queries

**URL:** <https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240>\
**Category:** Ruby\
**Created:** [February 6, 2008, 4:02am UTC](https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240 "2008-02-06T04:02:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![glacius](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/dec6dc/32.png) [@glacius](https://www.ruby-forum.com/u/glacius)\
**Post date:** [February 6, 2008, 4:02am UTC](https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240/1 "2008-02-06T04:02:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![glacius](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/dec6dc/32.png) [@glacius](https://www.ruby-forum.com/u/glacius)\
**Post date:** [February 6, 2008, 9:00am UTC](https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240/2 "2008-02-06T09:00:23Z")

</div>

Gian H. 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’

1. 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 \<\> ‘’;

(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

---

<div class="post-metadata">

**Author:** ![glacius](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/dec6dc/32.png) [@glacius](https://www.ruby-forum.com/u/glacius)\
**Post date:** [February 6, 2008, 1:05pm UTC](https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240/3 "2008-02-06T13:05:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![glacius](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/dec6dc/32.png) [@glacius](https://www.ruby-forum.com/u/glacius)\
**Post date:** [February 6, 2008, 5:35pm UTC](https://www.ruby-forum.com/t/looking-for-libraries-to-monitor-postgres-queries/128240/4 "2008-02-06T17:35:31Z")

</div>

Gian H. wrote:

> 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/](http://sqlrelay.sourceforge.net/) or  
[http://pgpool.projects.postgresql.org/](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