Friday, July 11, 2025

PostgreSQL: how to tail the alert log of the day with filtering for long-running statements

I have set the parameter log_min_duration_statement to 2 seconds and reloaded the config:
postgres=# alter system set log_min_duration_statement='2s';
ALTER SYSTEM

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 2s
(1 row)

I expect every statement that lasts longer than 2 seconds to be logged in the postgreSQL server main log.

From a different session, I execute the following in any database in the cluster:
postgres=# SELECT pg_sleep(3);
 pg_sleep
----------

(1 row)
From another session, tail the log from today:
tail -F /var/log/postgres/postgresql-2025-07-11_000000.log | grep --line-buffered "duration:.*statement:"
Output
2025-07-11 09:45:42 CEST [173349]: [5-1] user=postgres,db=postgres,app=psql,client=[local] LOG:  duration: 3003.698 ms  statement: SELECT pg_sleep(3);

No comments:

Post a Comment