How do I enable query logging using Amazon RDS for PostgreSQL?
Last updated: 2020-06-24
How can I enable query logging for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL DB instances?
To enable query logging on PostgreSQL, change the values of the following parameters by modifying a customized parameter group that is associated with the DB instance:
When you modify log parameters, you may require more space from the DB instance's volume. If the volume's storage is full, then the DB instance becomes unavailable. It's a best practice to modify rds.log_retention_period parameter, accordingly, to clean up old logs. It's also a best practice to continually monitor your storage consumption using the Amazon CloudWatch metric FreeStorageSpace, and increase it if needed.
Depending on which queries you want to log, you can enable log_statement or log_min_duration_statement. You don't need to modify both parameters to enable logging.
Modify log_min_duration_statement to set a threshold in milliseconds so that you can log all queries that take longer than the set parameter value. For example, if you set the log_min_duration_statement value to 500, Amazon RDS logs all queries (regardless of the query type) that take longer than half of a second to be completed. Similarly, if you set the value for this parameter to 2000, Amazon RDS logs all queries that take longer than two seconds to complete. If you set the parameter value to -1, this disables the parameter, and Amazon RDS does not log any queries based on the time to complete. If you set the parameter value to 0, Amazon RDS logs all queries.
Note: The log_min_duration_statement parameter does not depend on or interfere with the log_statement parameter.
Modify the log_statement to control which SQL statements are logged. The default value is none, and you can modify the values for this parameter as follows:
- ddl logs all data definition language (DDL) statements such as CREATE, ALTER, and DROP.
- mod logs all DDL and data modification language (DML) statements such as INSERT, UPDATE, and DELETE.
- all logs all queries (regardless of the execution time).
Note: Regardless of the value that you set for log_statement and log_min_duration_statement, the queries aren't written to the log twice.
Enable query logging on PostreSQL
To enable query logging on PostgreSQL, follow these steps:
Note: The following example parameter modifications logs the following: all queries that take longer than one second (regardless of the query type) and all schema changes (DDL statements regardless of completion time).
- Open the Amazon RDS console, and choose Databases from the navigation pane.
- Choose the DB instance that you want to log queries for.
- Choose the Configuration view to see the details of the DB instance and to see which parameter group is associated with the DB instance.
Note: When you create a DB instance, the DB instance is associated with the default DB parameter group. Because you cannot modify this group, create a custom parameter group to modify the parameter group. When you change the DB instance parameter group (from default to a custom group), you must reboot the DB instance.
- Choose Parameter group from the Instance configuration pane, and then choose the parameter group that you want to modify.
- Choose Edit Parameter.
- In the Filter parameters field, select the parameter that you want to change. For example:
Enter log_statement and change the value to ddl.
Enter log_min_duration_statement and change the value to 1000. (This value is in milliseconds, so 1000 equals one second).
- Choose Save changes.
Note: These parameters are dynamic. If your DB instance already has a custom parameter group, you don't 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. To confirm that logging is enabled (in this example, all DDL statements and all queries that take longer than one second), connect to the DB instance. Then, run commands similar to the following:
CREATE TABLE article(article_code bigint, created_at timestamp with time zone, summary text, content text) ; SELECT pg_sleep(2); SELECT generate_series(1,10000000) as test;
View query logs
To view the logs, follow these steps:
- Open the Amazon RDS console, and then choose Databases from navigation pane.
- Choose your DB instance, and then choose the Logs view.
- In the Logs & Events tab, choose the most recent log, and then choose View log to see the content of logs. For example:
2018-12-19 11:05:32 UTC:172.31.xx.yyy(35708):user@awspostgres::LOG: statement: CREATE TABLE article(article_code bigint, created_at timestamp with time zone, summary text, content text) ; 2018-12-19 11:10:36 UTC:172.31.xx.yyy(35708):user@awspostgres::LOG: duration: 2010.247 ms statement: select pg_sleep(2); 2018-12-19 11:11:25 UTC:172.31.xx.yyy(35708):user@awspostgres::LOG: duration: 2159.838 ms statement: SELECT generate_series(1,10000000) as test;
Note: Ensure that you do not set the above parameters at values that generate extensive logging. For example, setting log_statement to all or setting log_min_duration_statement to 0 or a very small number can generate a huge amount of logging information. This impacts your storage consumption. If you need to set the parameters to these values, make sure you are only doing so for a short period of time for troubleshooting purposes, and closely monitor the storage space, throughout.