How do I enable query logging using Amazon RDS for PostgreSQL?

Last updated: 2019-03-19

How can I enable query logging for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL DB instances?

Short Description

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, more space might be required from the DB instance's volume. If the volume's storage is full, then the instance will become unavailable. It's a best practice to use the rds.log_retention_period parameter to clean up old logs. Also, it's a best practice to continually monitor the storage consumption, and to consider increasing the storage size for heavy workloads.

Resolution

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 be completed. If you set the parameter value to -1, the parameter is disabled, and no queries are logged based on the time to complete. If the parameter value is set to 0, all queries are logged.
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 log_statement and log_min_duration_statement, the queries aren't written to the log twice.

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).

  1. Open the Amazon RDS console, and choose Databases from the navigation pane.
  2. Choose the DB instance that you want to log queries for.
  3. Choose the Configuration view to see the details of the DB instance and to see which parameter group is associated with the instance.
    Note: When a DB instance is created, the instance is associated with the default DB parameter group. Because this group can't be modified, create a custom parameter group to modify the parameter group. When you change the instance parameter group (from default to a custom group), you must reboot the DB instance.
  4. Choose Parameter group from the Instance configuration pane, and choose the parameter group that you want to modify.
  5. Choose Edit Parameter.
  6. 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).
  7. 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.

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;

To view the logs, follow these steps:

  1. Open the Amazon RDS console, and then choose Databases from navigation pane.
  2. Choose your DB instance, and choose the Logs view.
  3. 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:[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;

Did this article help you?

Anything we could improve?


Need more help?