Tips&Tricks: Get long running queries from PostgreSQL

Was looking for a method of getting queries that are running longer than 5 minutes out of a PostgreSQL. This solution needs stats_command_string enabled in the postgresql.conf, though. The query you can run:

echo "select procpid,datname,current_query,now() - pg_stat_activity.query_start as duration from pg_stat_activity where pg_stat_activity.current_query <> ''::text and now() - pg_stat_activity.query_start > interval '5 minutes'" | sudo -u postgres psql

 

Hope this helps someone!

Tags: , , ,

This entry was posted on Monday, June 7th, 2010 at 14:24. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.


4 Responses to “Tips&Tricks: Get long running queries from PostgreSQL”

  1. Brad M says:
    August 20, 2012 at 22:35

    or…

    select pg_sleep(300);

  2. Brad M says:
    August 20, 2012 at 22:15

    Does this still work? It returns immediately on 9.1.4, even after turning on the equivalent options:

    track_activities = true
    track_counts = true

  3. Ramu Valleti says:
    June 8, 2012 at 10:39

    Excellent, very useful..!

  4. thomas says:
    November 8, 2010 at 16:42

    very useful to me! thanks!

Kumina designs, builds, operates and supports Kubernetes solutions that help companies thrive online. As Certified Kubernetes Service Partner, we know how to build real solutions.

Learn more about us Get in touch