How do I use Amazon RDS to turn on query logging for PostgreSQL?

5 minute read
0

I want to turn on query logging for my Amazon Relational Database Service (Amazon RDS) for PostgreSQL DB instances.

Short description

To turn on query logging on PostgreSQL, modify the following parameters that are associated with the DB instance for a customized parameter group:

When you modify log parameters, the DB instance's volume might require more space. If the volume's storage is full, then the DB instance is unavailable. It's a best practice to modify the rds.log_retention_period parameter to clean up unnecessary logs. It's also a best practice to use the Amazon CloudWatch metric FreeStorageSpace to continually monitor your storage consumption. When you see that the storage space is almost full, you can increase the storage space so that the DB instance is always available.

Resolution

Depending on the queries you want to log, turn on log_statement or log_min_duration_statement. You don't need to modify both parameters to turn on logging.

Modify log_min_duration_statement to set a threshold in milliseconds. This allows you to log all queries that take longer than the set parameter value. For example, suppose you set the log_min_duration_statement value to 500. When this happens, Amazon RDS logs all queries that take longer than half of a second to complete. If you set the value for this parameter to 2000, then Amazon RDS logs all queries that take longer than two seconds to complete. If you set the parameter value to -1, then the parameter is turned off. Amazon RDS doesn't 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 doesn't depend on or interfere with the log_statement parameter.

Modify the log_statement to control the SQL statements that are logged. The default value is none. You can modify the following values for this 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 execution time).

Note: Regardless of the value that you set for log_statement and log_min_duration_statement, the queries are written to the log once.

Turn on query logging on PostgreSQL

Complete the following steps to turn on query logging on PostgreSQL:

Note: The following example parameter modifications logs all queries that take longer than one second and also logs all schema changes.

  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 the parameter group 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 can't 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.
  4. Choose Parameter group from the Instance configuration pane, and then 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 and equals one second).
  7. Choose Save changes.
    Note: These parameters are dynamic. If your DB instance has a custom parameter group, then you don't need to reboot the DB instance for these parameters to take effect.

Confirm that logging is turned on

After you save the parameter group associated with your DB instance, the status is set to applying. After the parameter group is set in your DB instance, the status is set to in-sync. To confirm that logging is turned on, connect to the DB instance. In this example, confirm that the log includes all DDL statements and all queries that take longer than one second. 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

Complete the following steps to view the logs:

1.    Open the Amazon RDS console.

2.    In the navigation pane, choose Databases.

3.    Choose your DB instance, and then choose the Logs view.

4.    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;

Note: Make sure that you don't set the previous parameters at values that generate extensive logging. For example, setting log_statement to all or setting log_min_duration_statement to 0 generates a large amount of logging information. This affects your storage consumption. To set the parameters to these values, make sure you do so only for a short time for troubleshooting purposes. Closely monitor the storage space throughout when you're troubleshooting.

Related information

RDS for PostgreSQL database log files