PostgreSQL how to see particular query have run or not

ppb 0
Viewed 20

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

postgresql
2 Answers

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

richyen0

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.

Cosmin Gruian0
Related