Page tree
Skip to end of metadata
Go to start of metadata

Put these lines into postgresql.conf to log the queries and their execution time if they run for longer than 500ms:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%F.log'
log_statement = 'all'
log_line_prefix = '%c %t '
log_min_duration_statement = 500

Run

$ sudo grep duration <path_to_log_file>|sort -k8 -g

to find the slowest queries. Then pick the session ids (this is the first part of the prefix of each line) you are interested in and grep for those.

The log files accumulate due to %F being part of log_filename and the log files become large rather quickly due to log_statement being set to all, so keep an eye on the size of the log files and delete them, either manually, or automatically in a cron job to avoid filling up your disk.

Only changing logging_collector and log_directory require a server restart. To activate any of the other parameters, a reload like pg_ctl reload is sufficient. So you can switch on log_statment and log_min_duration_statement only when needed and then switch it off again to control log size growth.

  • No labels