How to log all or slow PostgreSQL queries?

Better Stack Team
Updated on August 25, 2023

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.

    # 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):

    sudo systemctl restart postgresql

    On macOS (using Homebrew):

    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
Licensed under CC-BY-NC-SA

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

Make your mark

Join the writer's program

Are you a developer and love writing and sharing your knowledge with the world? Join our guest writing program and get paid for writing amazing technical guides. We'll get them to the right readers that will appreciate them.

Write for us
Writer of the month
Marin Bezhanov
Marin is a software engineer and architect with a broad range of experience working...
Build on top of Better Stack

Write a script, app or project on top of Better Stack and share it with the world. Make a public repository and share it with us at our email.

or submit a pull request and help us build better products for everyone.

See the full list of amazing projects on github