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: , , ,


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

  1. Brad M says:

    or…

    select pg_sleep(300);

  2. Brad M says:

    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:

    Excellent, very useful..!

  4. thomas says:

    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.