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:
- Open the PostgreSQL Configuration File:
The configuration file is typically named
postgresql.confand is located in the PostgreSQL data directory. You'll need to modify this file to adjust the logging settings.
Configure Query Logging:
- To log all queries:
- To log only slow queries:
'none'(this is the default) and adjust the
log_min_duration_statementparameter to specify the threshold for slow queries.
postgresql.conffile 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_statementvalue to set the threshold in milliseconds for what you consider a "slow" query.
- To log all queries: Set the
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
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:
- On macOS (using Homebrew):
You can open the log file with a text editor or use command-line tools to search for queries.
- On Linux:
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.
Where are PostgreSQL logs stored?
PostgreSQL stores logs in the log files. Depending on your system, you can find the logs at the following locations: Ubuntu On Ubuntu and Ubuntu-like systems, the PostgreSQL log is stored in the /v...Questions
How to log PostgreSQL queries?
If you would like to log all PostgreSQL queries into a file, you can do that by changing few settings int the configuration file Open the data/postgresql.conf file and change the following settings...Questions
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
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@example.com
or submit a pull request and help us build better products for everyone.
See the full list of amazing projects on github