What is Required to Change?
To enable query logging on PostgreSQL, you need to change the following parameters by modifying a customized parameter group that is associated with the DB instance. It is better to create a new parameter group with the necessary modifications.
| Parameter Name | Value to Set |
| auto_explain.log_analyze | 1 |
| auto_explain.log_buffers | 1 |
| auto_explain.log_format | 100 |
| auto_explain.log_min_duration | 1 |
| auto_explain.log_timing | 1 |
| auto_explain.log_verbose | 1 |
| log_min_duration_statement | 100 |
| shared_preload_libraries | auto_explain, pgaudit, pg_stat_statements, pg_hint_plan |
Most Critical Parameter:
Changing the log_min_duration_statement to set a limit in milliseconds so that it can log all queries that take longer than the set parameter value. For example, if you set the log_min_duration_statement value to 100, Amazon RDS logs all queries (regardless of the query type) that take longer than 100 milliseconds (ms). Likewise, if you set the value for this parameter to 1000, Amazon RDS logs all queries that take longer than 1 second (i.e. 1000 ms) to complete. The default value of this parameter is -1, this disables the parameter, and Amazon RDS does not log any queries based on the time to complete. Please note that if you set the parameter value to 0, Amazon RDS logs all queries and eventually create huge log files.
What may Happen after Logging?
Please note that when you modify log parameters, you may require more space from the Database instance volume and if the total volume storage is full due to extensive logging, then the DB instance becomes unavailable. Thus, it is a best practice to modify the log_retention_period parameter, this would enable us to clean up old logs automatically. However, it is a best practice to frequently monitor your storage consumption.
What are the steps to be followed?
- Open your Amazon RDS console, and choose Databases from the navigation pane.
- Choose the DB instance that you want to log queries and to check which parameter group is associated with the DB instance.
- If your DB instance is associated with the default DB parameter group, then create a custom parameter group to make your changes.
- Please note that when you change the DB instance parameter group (from default to a custom group), you must reboot the DB instance to take effect. This will suspend your DB instance from usage for a moment usually.
- Click on the Create parameter group button to create a custom parameter group.
6. Please select your version of PostgreSQL followed by Name of Group and Description if required.
7. Further steps after you click “create” are self-explanatory except the parameters you need to set as specified above.
8. In the Filter parameters field, select the parameter that you want to change. For example:
Enter log_min_duration_statement and change the value to 100. (This value is in milliseconds).
9. Make changes to all the required parameters.
10. Finally, click Save changes.
11. Please note that these changes will reboot the DB instance accordingly.
Furthermore, these parameters are dynamic. If your DB instance already has a custom parameter group, you do not need to reboot the DB instance for these parameters to take effect.
Confirm Logging is Enabled
After you save the parameter group associated with your DB instance, the status is applying. After the parameter group is set in your DB instance, the status is in-sync. Once the new parameter group is set, you would see the logs getting filled as soon as the query executes.
Where to View the Query Logs?
To view the logs, follow these steps:
- If you are not already logged in then open the Amazon RDS console, and then choose Databases from the navigation pane.
- Choose your DB instance, and then click the Logs & Events tab.
- In the Logs & Events tab, choose the most recent log, and then choose to View/Download log to see the content of logs.
Hope you got the required information on enabling the logs, please do let us know if you are looking for any help.