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. thomas says:

    very useful to me! thanks!

  2. Ramu Valleti says:

    Excellent, very useful..!

  3. 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

  4. Brad M says:

    or…

    select pg_sleep(300);

Leave a Reply

Kumina helps companies innovate with the power of open source software. As specialists in managed IT operations since 2007, our mission is simple: building and managing the perfectly tailored technical infrastructures that allow our clients to thrive.

With fully managed solutions, we help our customers unlock the full potential of the cloud and Kubernetes. Our team also supports organisations with IT consulting and Kubernetes training courses. Learn more about our services or get in touch, we would love to hear about your business and projects.