PostgreSQL how to see particular query have run or not-CodePudding

I have Postgres db. My NodeJs application connects it and runs a job every 30 minutes. In that job it runs a query.

I want to check, that query is actually running or not after every 30 minutes. How can I check a particular query runs or not in Postgres DB

CodePudding user response：

If you use the log_min_duration_statement = 0 parameter, the PostgreSQL log will indicate when the query has finished running. Also, if you wish to print the query when it is about to start running, you can use log_statement = 'all'

The way to set these parameters would be simply by calling:

> ALTER SYSTEM SET log_min_duration_statement to 0;
ALTER SYSTEM
> ALTER SYSTEM SET log_statement to 'all';
ALTER SYSTEM
> SELECT pg_reload_conf();

Note that it is possible for these two parameters to produce a lot more log traffic and take up disk space, so use at your own risk.

More information about logging can be found in the documentation

CodePudding user response：

Use the following:

SELECT * FROM pg_stat_activity WHERE state='active' AND query='{your query}';

You can see query_start, state_change or even if it has a LOCK on wa Additional, you can filter by other fields like "username", "client_hostname","client_addr", etc...

More info: PostgreSQL doc

You can put the statement in a function in which you can decide what to do with the queries that are running for longer than 30 min.