How to log all or slow PostgreSQL queries?

To log all or slow queries in PostgreSQL, you can configure the PostgreSQL server's logging options. PostgreSQL provides various settings that allow you to control the level of detail for query logging, including logging all queries or only queries that exceed a specified execution time threshold (slow queries). Here's how you can achieve this:

1. Open the PostgreSQL Configuration File: The configuration file is typically named postgresql.conf and is located in the PostgreSQL data directory. You'll need to modify this file to adjust the logging settings.

2. Configure Query Logging:

   • To log all queries: Set the log_statement parameter to 'all'.
   • To log only slow queries: Set the log_statement parameter to 'none' (this is the default) and adjust the log_min_duration_statement parameter to specify the threshold for slow queries.

   Open the postgresql.conf file and look for these parameters. Uncomment them if necessary and set the desired values.

    

   Copied!

   # Log all queries
   log_statement = 'all'
   
   # Log only queries that exceed 1000 ms (1 second)
   log_min_duration_statement = 1000
   

   You can adjust the log_min_duration_statement value to set the threshold in milliseconds for what you consider a "slow" query.

3. Restart PostgreSQL: After making the changes, you need to restart the PostgreSQL server for the changes to take effect.

   On Linux (systemd-based systems):

    

   Copied!

   sudo systemctl restart postgresql
   

   On macOS (using Homebrew):

    

   Copied!

   brew services restart postgresql
   

4. Check the Log Files: By default, PostgreSQL logs queries to the server log file. The location of this file depends on your PostgreSQL installation and operating system.

   • On Linux: /var/log/postgresql/postgresql-X.X-main.log
   • On macOS (using Homebrew): /usr/local/var/log/postgresql.log

   You can open the log file with a text editor or use command-line tools to search for queries.

Please note that logging all queries can generate a large amount of log data, which may impact server performance and disk space. It's a good practice to enable query logging temporarily for troubleshooting or performance optimization purposes, and then revert to logging only slow queries once you've gathered the necessary information.

To learn more about logging, visit Better Stack Community.

Got an article suggestion? Let us know

Explore more

PostgreSQL Logging

Where are PostgreSQL logs stored?

Questions

How to log PostgreSQL queries?

Questions

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.