How do I turn on query logging for my Amazon RDS for PostgreSQL DB instances?

Short description

Depending on the queries that you want to log, turn on log_statement or log_min_duration_statement. For more information, see log_min_duration_statement and log_statement on the PostgreSQL website. You don't need to modify both parameters to turn on logging in Amazon RDS for PostgreSQL. 

To view logs of queries that take a certain amount of time to complete, modify the log_min_duration_statement parameter in a threshold of milliseconds. For example, if you set the parameter value to 500, then Amazon RDS logs all queries that take longer than half of a second to complete. If you set the parameter value to -1, then you turn off the parameter. Amazon RDS doesn't log queries based on the time to complete. If you set the parameter value to 0, then Amazon RDS logs all queries.

To control the SQL statements that you want to log, you can modify the following values for the log_statement parameter:

• 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 runtime.

Note: The default value for the log_statement parameter is none.

Regardless of the value that you set for either log_min_duration_statement or log_statement, Amazon RDS writes the queries to the log once. However, when you modify the parameters, the DB instance's volume might require more space. To minimize storage usage, don't set the parameters to values that generate extensive logging, such as log_statement to all or log_min_duration_statement to 0. 

Because the DB instance is unavailable when the volume storage is full, it's a best practice to modify the rds.log_retention_period parameter to remove unnecessary logs. It's also a best practice to use the FreeStorageSpace Amazon CloudWatch metric to continually monitor your storage usage. If the storage space is almost full, then you can increase the storage space so that the DB instance is always available.

Resolution

Modify the custom parameter group to turn on logging

Complete the following steps:

1. Open the Amazon RDS console.
2. Create a custom parameter group, and then associate it with your DB instance.
   Note: If you choose Apply immediately, then the DB instance becomes temporarily unavailable.
3. In the navigation pane, choose Parameter group.
4. Choose your custom parameter group.
5. Choose Edit Parameter.
6. In the Filter parameters field, enter either log_statement or log_min_duration_statement.
7. Select the parameter, and then modify it. For example, you can change log_statement to ddl or log_min_duration_statement to 1000 (1 second).
8. Choose Save changes.
   Note: Because the parameters are dynamic, you don't need to reboot the DB instance for the parameter changes to take effect.

The parameter group status changes to Applying. After the parameter group modifications are complete, the status changes to In-sync.

Confirm that you turned on logging

Complete the following steps:

1. Connect to the DB instance. 
2. Run the following commands:

   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;

   Note: Replace the example values with your values.

The output of the preceding example commands shows all DDL statements and queries that take longer than 1 second.

View query logs

Complete the following steps:

1. Open the Amazon RDS console.
2. In the navigation pane, choose Databases.
3. Select your DB instance. 
4. Choose the Logs & Events tab.
5. In the Logs section, select the most recent log, and then choose View.
   Example log:

   2018-12-19 11:05:32 UTC:172.31.xx.yyy(35708):user@awspostgres:[27174]: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:[27174]:LOG: duration: 2010.247 ms statement: select pg_sleep(2);  
   2018-12-19 11:11:25 UTC:172.31.xx.yyy(35708):user@awspostgres:[27174]:LOG: duration: 2159.838 ms statement: SELECT generate_series(1,10000000) as test;

Related information

RDS for PostgreSQL database log files