How to get a status of a running query in postgresql database

Question

I have a select query running very long. How will I get a status of that query, like how long will it be running? Whether it is accessing a data from the tables or not.

Note : As per pg_stat_activity the query state is shown as active and not in a waiting state. Like in Oracle, we can see the source/target and processing status of a query - is there something like this in postgresql?

I need a completion time of a query in the above question. pg_stat_activity view gives only the active queries, database, user detail. pg_stat_activity do not contains the completion time of a running query. — Arun Padule, Oct 01 '12 at 06:06

score 57 · Answer 1 · edited Jan 17 '18 at 13:25

57

Based on @Anshu answer I am using:

SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age 
FROM pg_stat_activity
WHERE state <> 'idle' 
    AND query NOT LIKE '% FROM pg_stat_activity %' 
ORDER BY age;

edited Jan 17 '18 at 13:25

Jonathan

8,453
9
51
74

answered May 27 '17 at 00:34

Mircea Vutcovici

1,894
19
27

1

`AND <> 'idle in transaction'` may also be of interest – Ciro Santilli OurBigBook.com Jun 27 '23 at 06:25

score 9 · Answer 2 · edited Jun 10 '14 at 12:56

9

This can't be done yet, but is on the TODO.

edited Jun 10 '14 at 12:56

tshepang

12,111
21
91
136

answered Sep 19 '13 at 20:25

supyo

3,017
2
20
35

score 9 · Answer 3 · answered Sep 05 '17 at 13:54

9

we can find the query log with respect to the database in postgres .

select *
from pg_stat_activity
where datname = 'yourdatabasename'

This will give active query log of database .

answered Sep 05 '17 at 13:54

Himanshu sharma

7,487
4
42
75

How to get a status of a running query in postgresql database

3 Answers3