^
--result-file=/tmp/binlog.txt
```
Amazon RDS normally purges a binary log as soon as possible. However, the binary log must still be available on the instance to be accessed by mysqlbinlog. To specify the number of hours for RDS to retain binary logs, use the `mysql.rds_set_configuration` stored procedure. Specify a period with enough time for you to download the logs. After you set the retention period, monitor storage usage for the DB instance to ensure that the retained binary logs don't take up too much storage.
The following example sets the retention period to 1 day.
```
call mysql.rds_set_configuration('binlog retention hours', 24);
```
To display the current setting, use the `mysql.rds_show_configuration` stored procedure.
```
call mysql.rds_show_configuration;
```
# Enabling MariaDB binary log annotation
In a MariaDB DB instance, you can use the `Annotate_rows` event to annotate a row event with a copy of the SQL query that caused the row event. This approach provides similar functionality to enabling the `binlog_rows_query_log_events` parameter on an RDS for MySQL DB instance.
You can enable binary log annotations globally by creating a custom parameter group and setting the `binlog_annotate_row_events` parameter to **1**. You can also enable annotations at the session level, by calling `SET SESSION binlog_annotate_row_events = 1`. Use the `replicate_annotate_row_events` to replicate binary log annotations to the replica instance if binary logging is enabled on it. No special privileges are required to use these settings.
The following is an example of a row-based transaction in MariaDB. The use of row-based logging is triggered by setting the transaction isolation level to read-committed.
```
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE square(x INT PRIMARY KEY, y INT NOT NULL) ENGINE = InnoDB;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN
INSERT INTO square(x, y) VALUES(5, 5 * 5);
COMMIT;
```
Without annotations, the binary log entries for the transaction look like the following:
```
BEGIN
/*!*/;
# at 1163
# at 1209
#150922 7:55:57 server id 1855786460 end_log_pos 1209 Table_map: `test`.`square` mapped to number 76
#150922 7:55:57 server id 1855786460 end_log_pos 1247 Write_rows: table id 76 flags: STMT_END_F
### INSERT INTO `test`.`square`
### SET
### @1=5
### @2=25
# at 1247
#150922 7:56:01 server id 1855786460 end_log_pos 1274 Xid = 62
COMMIT/*!*/;
```
The following statement enables session-level annotations for this same transaction, and disables them after committing the transaction:
```
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE square(x INT PRIMARY KEY, y INT NOT NULL) ENGINE = InnoDB;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION binlog_annotate_row_events = 1;
BEGIN;
INSERT INTO square(x, y) VALUES(5, 5 * 5);
COMMIT;
SET SESSION binlog_annotate_row_events = 0;
```
With annotations, the binary log entries for the transaction look like the following:
```
BEGIN
/*!*/;
# at 423
# at 483
# at 529
#150922 8:04:24 server id 1855786460 end_log_pos 483 Annotate_rows:
#Q> INSERT INTO square(x, y) VALUES(5, 5 * 5)
#150922 8:04:24 server id 1855786460 end_log_pos 529 Table_map: `test`.`square` mapped to number 76
#150922 8:04:24 server id 1855786460 end_log_pos 567 Write_rows: table id 76 flags: STMT_END_F
### INSERT INTO `test`.`square`
### SET
### @1=5
### @2=25
# at 567
#150922 8:04:26 server id 1855786460 end_log_pos 594 Xid = 88
COMMIT/*!*/;
```
# Amazon RDS for Microsoft SQL Server database log files
You can access Microsoft SQL Server error logs, agent logs, trace files, and dump files by using the Amazon RDS console, AWS CLI, or RDS API. For more information about viewing, downloading, and watching file-based database logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
## Retention schedule
Log files are rotated each day and whenever your DB instance is restarted. The following is the retention schedule for Microsoft SQL Server logs on Amazon RDS.
****
| Log type | Retention schedule |
| --- | --- |
| Error logs | A maximum of 30 error logs are retained. Amazon RDS might delete error logs older than 7 days. |
| Agent logs | A maximum of 10 agent logs are retained. Amazon RDS might delete agent logs older than 7 days. |
| Trace files | Trace files are retained according to the trace file retention period of your DB instance. The default trace file retention period is 7 days. To modify the trace file retention period for your DB instance, see [Setting the retention period for trace and dump files](Appendix.SQLServer.CommonDBATasks.TraceFiles.md#Appendix.SQLServer.CommonDBATasks.TraceFiles.PurgeTraceFiles). |
| Dump files | Dump files are retained according to the dump file retention period of your DB instance. The default dump file retention period is 7 days. To modify the dump file retention period for your DB instance, see [Setting the retention period for trace and dump files](Appendix.SQLServer.CommonDBATasks.TraceFiles.md#Appendix.SQLServer.CommonDBATasks.TraceFiles.PurgeTraceFiles). |
## Viewing the SQL Server error log by using the rds\$1read\$1error\$1log procedure
You can use the Amazon RDS stored procedure `rds_read_error_log` to view error logs and agent logs. For more information, see [Viewing error and agent logs](Appendix.SQLServer.CommonDBATasks.Logs.md#Appendix.SQLServer.CommonDBATasks.Logs.SP).
## Publishing SQL Server logs to Amazon CloudWatch Logs
With Amazon RDS for SQL Server, you can publish error and agent log events directly to Amazon CloudWatch Logs. Analyze the log data with CloudWatch Logs, then use CloudWatch to create alarms and view metrics.
With CloudWatch Logs, you can do the following:
+ Store logs in highly durable storage space with a retention period that you define.
+ Search and filter log data.
+ Share log data between accounts.
+ Export logs to Amazon S3.
+ Stream data to Amazon OpenSearch Service.
+ Process log data in real time with Amazon Kinesis Data Streams. For more information, see [Working with Amazon CloudWatch Logs](https://docs.aws.amazon.com/kinesisanalytics/latest/dev/cloudwatch-logs.html) in the *Amazon Managed Service for Apache Flink for SQL Applications Developer Guide*.
Amazon RDS publishes each SQL Server database log as a separate database stream in the log group. For example, if you publish the agent logs and error logs, error data is stored in an error log stream in the `/aws/rds/instance/my_instance.node1/error` log group, and agent log data is stored in the `/aws/rds/instance/my_instance.node1/agent` log group.
For Multi-AZ DB instances, Amazon RDS publishes the database log as two separate streams in the log group. For example, if you publish the error logs, the error data is stored in the error log streams `/aws/rds/instance/my_instance.node1/error` and `/aws/rds/instance/my_instance.node2/error` respectively. The log streams don't change during a failover and the error log stream of each node can contain error logs from primary or secondary instance. With Multi-AZ, a log stream is automatically created for `/aws/rds/instance/my_instance/rds-events` to store event data such as DB instance failovers.
**Note**
Publishing SQL Server logs to CloudWatch Logs isn't enabled by default. Publishing trace and dump files isn't supported. Publishing SQL Server logs to CloudWatch Logs is supported in all regions.
### Console
**To publish SQL Server DB logs to CloudWatch Logs from the AWS Management Console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**, and then choose the DB instance that you want to modify.
1. Choose **Modify**.
1. In the **Log exports** section, choose the logs that you want to start publishing to CloudWatch Logs.
You can choose **Agent log**, **Error log**, or both.
1. Choose **Continue**, and then choose **Modify DB Instance** on the summary page.
### AWS CLI
To publish SQL Server logs, you can use the [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command with the following parameters:
+ `--db-instance-identifier`
+ `--cloudwatch-logs-export-configuration`
**Note**
A change to the `--cloudwatch-logs-export-configuration` option is always applied to the DB instance immediately. Therefore, the `--apply-immediately` and `--no-apply-immediately` options have no effect.
You can also publish SQL Server logs using the following commands:
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html)
**Example**
The following example creates an SQL Server DB instance with CloudWatch Logs publishing enabled. The `--enable-cloudwatch-logs-exports` value is a JSON array of strings that can include `error`, `agent`, or both.
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mydbinstance \
--enable-cloudwatch-logs-exports '["error","agent"]' \
--db-instance-class db.m4.large \
--engine sqlserver-se
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mydbinstance ^
--enable-cloudwatch-logs-exports "[\"error\",\"agent\"]" ^
--db-instance-class db.m4.large ^
--engine sqlserver-se
```
When using the Windows command prompt, you must escape double quotes (") in JSON code by prefixing them with a backslash (\$1).
**Example**
The following example modifies an existing SQL Server DB instance to publish log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `EnableLogTypes`, and its value is an array of strings that can include `error`, `agent`, or both.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--cloudwatch-logs-export-configuration '{"EnableLogTypes":["error","agent"]}'
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--cloudwatch-logs-export-configuration "{\"EnableLogTypes\":[\"error\",\"agent\"]}"
```
When using the Windows command prompt, you must escape double quotes (") in JSON code by prefixing them with a backslash (\$1).
**Example**
The following example modifies an existing SQL Server DB instance to disable publishing agent log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `DisableLogTypes`, and its value is an array of strings that can include `error`, `agent`, or both.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--cloudwatch-logs-export-configuration '{"DisableLogTypes":["agent"]}'
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--cloudwatch-logs-export-configuration "{\"DisableLogTypes\":[\"agent\"]}"
```
When using the Windows command prompt, you must escape double quotes (") in JSON code by prefixing them with a backslash (\$1).
# MySQL database log files
You can monitor the MySQL logs directly through the Amazon RDS console, Amazon RDS API, AWS CLI, or AWS SDKs. You can also access MySQL logs by directing the logs to a database table in the main database and querying that table. You can use the mysqlbinlog utility to download a binary log.
For more information about viewing, downloading, and watching file-based database logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
**Topics**
+ [
# Overview of RDS for MySQL database logs
](USER_LogAccess.MySQL.LogFileSize.md)
+ [
# Publishing MySQL logs to Amazon CloudWatch Logs
](USER_LogAccess.MySQLDB.PublishtoCloudWatchLogs.md)
+ [
# Sending MySQL log output to tables
](Appendix.MySQL.CommonDBATasks.Logs.md)
+ [
# Configuring RDS for MySQL binary logging for Single-AZ databases
](USER_LogAccess.MySQL.BinaryFormat.md)
+ [
# Configuring MySQL binary logging for Multi-AZ DB clusters
](USER_Binlog.MultiAZ.md)
+ [
# Accessing MySQL binary logs
](USER_LogAccess.MySQL.Binarylog.md)
# Overview of RDS for MySQL database logs
You can monitor the following types of RDS for MySQL log files:
+ Error log
+ Slow query log
+ General log
+ Audit log
+ Instance log
+ IAM database authentication error log
The RDS for MySQL error log is generated by default. You can generate the slow query and general logs by setting parameters in your DB parameter group.
**Topics**
+ [
## RDS for MySQL error logs
](#USER_LogAccess.MySQL.Errorlog)
+ [
## RDS for MySQL slow query and general logs
](#USER_LogAccess.MySQL.Generallog)
+ [
## MySQL audit log
](#USER_LogAccess.MySQL.Auditlog)
+ [
## Log rotation and retention for RDS for MySQL
](#USER_LogAccess.MySQL.LogFileSize.retention)
+ [
## Size limits on redo logs
](#USER_LogAccess.MySQL.LogFileSize.RedoLogs)
## RDS for MySQL error logs
RDS for MySQL writes errors in the `mysql-error.log` file. Each log file has the hour it was generated (in UTC) appended to its name. The log files also have a timestamp that helps you determine when the log entries were written.
RDS for MySQL writes to the error log only on startup, shutdown, and when it encounters errors. A DB instance can go hours or days without new entries being written to the error log. If you see no recent entries, it's because the server didn't encounter an error that would result in a log entry.
By design, the error logs are filtered so that only unexpected events such as errors are shown. However, the error logs also contain some additional database information, for example query progress, which isn't shown. Therefore, even without any actual errors the size of the error logs might increase because of ongoing database activities. And while you might see a certain size in bytes or kilobytes for the error logs in the AWS Management Console, they might have 0 bytes when you download them.
RDS for MySQL writes `mysql-error.log` to disk every 5 minutes. It appends the contents of the log to `mysql-error-running.log`.
RDS for MySQL rotates the `mysql-error-running.log` file every hour. It retains the logs generated during the last two weeks.
**Note**
The log retention period is different between Amazon RDS and Aurora.
## RDS for MySQL slow query and general logs
You can write the RDS for MySQL slow query log and the general log to a file or a database table. To do so, set parameters in your DB parameter group. For information about creating and modifying a DB parameter group, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md). You must set these parameters before you can view the slow query log or general log in the Amazon RDS console or by using the Amazon RDS API, Amazon RDS CLI, or AWS SDKs.
You can control RDS for MySQL logging by using the parameters in this list:
+ `slow_query_log`: To create the slow query log, set to 1. The default is 0.
+ `general_log`: To create the general log, set to 1. The default is 0.
+ `long_query_time`: To prevent fast-running queries from being logged in the slow query log, specify a value for the shortest query runtime to be logged, in seconds. The default is 10 seconds; the minimum is 0. If log\$1output = FILE, you can specify a floating point value that goes to microsecond resolution. If log\$1output = TABLE, you must specify an integer value with second resolution. Only queries whose runtime exceeds the `long_query_time` value are logged. For example, setting `long_query_time` to 0.1 prevents any query that runs for less than 100 milliseconds from being logged.
+ `log_queries_not_using_indexes`: To log all queries that do not use an index to the slow query log, set to 1. Queries that don't use an index are logged even if their runtime is less than the value of the `long_query_time` parameter. The default is 0.
+ `log_output option`: You can specify one of the following options for the `log_output` parameter.
+ **TABLE** (default) – Write general queries to the `mysql.general_log` table, and slow queries to the `mysql.slow_log` table.
+ **FILE** – Write both general and slow query logs to the file system.
+ **NONE** – Disable logging.
For slow query data to appear in Amazon CloudWatch Logs, the following conditions must be met:
+ CloudWatch Logs must be configured to include slow query logs.
+ `slow_query_log` must be enabled.
+ `log_output` must be set to `FILE`.
+ The query must take longer than the time configured for `long_query_time`.
For more information about the slow query and general logs, go to the following topics in the MySQL documentation:
+ [The slow query log](https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html)
+ [The general query log](https://dev.mysql.com/doc/refman/8.0/en/query-log.html)
## MySQL audit log
To access the audit log, the DB instance must use a custom option group with the `MARIADB_AUDIT_PLUGIN` option. For more information, see [MariaDB Audit Plugin support for MySQL](Appendix.MySQL.Options.AuditPlugin.md).
## Log rotation and retention for RDS for MySQL
When logging is enabled, Amazon RDS rotates table logs or deletes log files at regular intervals. This measure is a precaution to reduce the possibility of a large log file either blocking database use or affecting performance. RDS for MySQL handles rotation and deletion as follows:
+ The MySQL slow query log, error log, and the general log file sizes are constrained to no more than 2 percent of the allocated storage space for a DB instance. To maintain this threshold, logs are automatically rotated every hour. MySQL removes log files more than two weeks old. If the combined log file size exceeds the threshold after removing old log files, then the oldest log files are deleted until the log file size no longer exceeds the threshold.
+ When `FILE` logging is enabled, log files are examined every hour and log files more than two weeks old are deleted. In some cases, the remaining combined log file size after the deletion might exceed the threshold of 2 percent of a DB instance's allocated space. In these cases, the oldest log files are deleted until the log file size no longer exceeds the threshold.
+ When `TABLE` logging is enabled, in some cases log tables are rotated every 24 hours. This rotation occurs if the space used by the table logs is more than 20 percent of the allocated storage space. It also occurs if the size of all logs combined is greater than 10 GB. If the amount of space used for a DB instance is greater than 90 percent of the DB instance's allocated storage space, then the thresholds for log rotation are reduced. Log tables are then rotated if the space used by the table logs is more than 10 percent of the allocated storage space. They're also rotated if the size of all logs combined is greater than 5 GB. You can subscribe to the `low storage` event category to be notified when log tables are rotated to free up space. For more information, see [Working with Amazon RDS event notification](USER_Events.md).
When log tables are rotated, the current log table is first copied to a backup log table. Then the entries in the current log table are removed. If the backup log table already exists, then it is deleted before the current log table is copied to the backup. You can query the backup log table if needed. The backup log table for the `mysql.general_log` table is named `mysql.general_log_backup`. The backup log table for the `mysql.slow_log` table is named `mysql.slow_log_backup`.
You can rotate the `mysql.general_log` table by calling the `mysql.rds_rotate_general_log` procedure. You can rotate the `mysql.slow_log` table by calling the `mysql.rds_rotate_slow_log` procedure.
Table logs are rotated during a database version upgrade.
To work with the logs from the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs, set the `log_output` parameter to FILE. Like the MySQL error log, these log files are rotated hourly. The log files that were generated during the previous two weeks are retained. Note that the retention period is different between Amazon RDS and Aurora.
## Size limits on redo logs
For RDS for MySQL version 8.0.32 and lower, the default value of this parameter is 256 MB. This amount is derived by multiplying the default value of the `innodb_log_file_size` parameter (128 MB) by the default value of the `innodb_log_files_in_group` parameter (2). For more information, see [Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance](https://aws.amazon.com/blogs/database/best-practices-for-configuring-parameters-for-amazon-rds-for-mysql-part-1-parameters-related-to-performance/).
For RDS for MySQL version 8.0.33 and higher minor versions, Amazon RDS uses the `innodb_redo_log_capacity` parameter instead of the `innodb_log_file_size` parameter. The Amazon RDS default value of the `innodb_redo_log_capacity` parameter is 2 GB. For more information, see [ Changes in MySQL 8.0.30](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-30.html) in the MySQL documentation.
Starting with MySQL 8.4, Amazon RDS enables the `innodb_dedicated_server` parameter by default. With the `innodb_dedicated_server` parameter, the database engine calculates the `innodb_buffer_pool_size` and `innodb_redo_log_capacity` parameters. For more information, see [Configuring buffer pool size and redo log capacity in MySQL 8.4](Appendix.MySQL.CommonDBATasks.Config.Size.8.4.md).
# Publishing MySQL logs to Amazon CloudWatch Logs
You can configure your MySQL DB instance to publish log data to a log group in Amazon CloudWatch Logs. With CloudWatch Logs, you can perform real-time analysis of the log data, and use CloudWatch to create alarms and view metrics. You can use CloudWatch Logs to store your log records in highly durable storage.
Amazon RDS publishes each MySQL database log as a separate database stream in the log group. For example, if you configure the export function to include the slow query log, slow query data is stored in a slow query log stream in the `/aws/rds/instance/my_instance/slowquery` log group.
The error log is enabled by default. The following table summarizes the requirements for the other MySQL logs.
| Log | Requirement |
| --- | --- |
| Audit log | The DB instance must use a custom option group with the `MARIADB_AUDIT_PLUGIN` option. |
| General log | The DB instance must use a custom parameter group with the parameter setting `general_log = 1` to enable the general log. |
| Slow query log | The DB instance must use a custom parameter group with the parameter setting `slow_query_log = 1` to enable the slow query log. |
| IAM database authentication error log | You must enable the log type `iam-db-auth-error` for a DB instance by creating or modifying a DB instance. |
| Log output | The DB instance must use a custom parameter group with the parameter setting `log_output = FILE` to write logs to the file system and publish them to CloudWatch Logs. |
## Console
**To publish MySQL logs to CloudWatch Logs using the console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**, and then choose the DB instance that you want to modify.
1. Choose **Modify**.
1. In the **Log exports** section, choose the logs that you want to start publishing to CloudWatch Logs.
1. Choose **Continue**, and then choose **Modify DB Instance** on the summary page.
## AWS CLI
You can publish MySQL logs with the AWS CLI. You can call the [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command with the following parameters:
+ `--db-instance-identifier`
+ `--cloudwatch-logs-export-configuration`
**Note**
A change to the `--cloudwatch-logs-export-configuration` option is always applied to the DB instance immediately. Therefore, the `--apply-immediately` and `--no-apply-immediately` options have no effect.
You can also publish MySQL logs by calling the following AWS CLI commands:
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html)
Run one of these AWS CLI commands with the following options:
+ `--db-instance-identifier`
+ `--enable-cloudwatch-logs-exports`
+ `--db-instance-class`
+ `--engine`
Other options might be required depending on the AWS CLI command you run.
**Example**
The following example modifies an existing MySQL DB instance to publish log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `EnableLogTypes`, and its value is an array of strings with any combination of `audit`, `error`, `general`, and `slowquery`.
For Linux, macOS, or Unix:
```
1. aws rds modify-db-instance \
2. --db-instance-identifier mydbinstance \
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
```
For Windows:
```
1. aws rds modify-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["audit","error","general","slowquery"]}'
```
**Example**
The following example creates a MySQL DB instance and publishes log files to CloudWatch Logs. The `--enable-cloudwatch-logs-exports` value is a JSON array of strings. The strings can be any combination of `audit`, `error`, `general`, and `slowquery`.
For Linux, macOS, or Unix:
```
1. aws rds create-db-instance \
2. --db-instance-identifier mydbinstance \
3. --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' \
4. --db-instance-class db.m4.large \
5. --engine MySQL
```
For Windows:
```
1. aws rds create-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --enable-cloudwatch-logs-exports '["audit","error","general","slowquery"]' ^
4. --db-instance-class db.m4.large ^
5. --engine MySQL
```
## RDS API
You can publish MySQL logs with the RDS API. You can call the [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) action with the following parameters:
+ `DBInstanceIdentifier`
+ `CloudwatchLogsExportConfiguration`
**Note**
A change to the `CloudwatchLogsExportConfiguration` parameter is always applied to the DB instance immediately. Therefore, the `ApplyImmediately` parameter has no effect.
You can also publish MySQL logs by calling the following RDS API operations:
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html)
Run one of these RDS API operations with the following parameters:
+ `DBInstanceIdentifier`
+ `EnableCloudwatchLogsExports`
+ `Engine`
+ `DBInstanceClass`
Other parameters might be required depending on the AWS CLI command you run.
# Sending MySQL log output to tables
You can direct the general and slow query logs to tables on the DB instance by creating a DB parameter group and setting the `log_output` server parameter to `TABLE`. General queries are then logged to the `mysql.general_log` table, and slow queries are logged to the `mysql.slow_log` table. You can query the tables to access the log information. Enabling this logging increases the amount of data written to the database, which can degrade performance.
Both the general log and the slow query logs are disabled by default. In order to enable logging to tables, you must also set the `general_log` and `slow_query_log` server parameters to `1`.
Log tables keep growing until the respective logging activities are turned off by resetting the appropriate parameter to `0`. A large amount of data often accumulates over time, which can use up a considerable percentage of your allocated storage space. Amazon RDS doesn't allow you to truncate the log tables, but you can move their contents. Rotating a table saves its contents to a backup table and then creates a new empty log table. You can manually rotate the log tables with the following command line procedures, where the command prompt is indicated by `PROMPT>`:
```
PROMPT> CALL mysql.rds_rotate_slow_log;
PROMPT> CALL mysql.rds_rotate_general_log;
```
To completely remove the old data and reclaim the disk space, call the appropriate procedure twice in succession.
# Configuring RDS for MySQL binary logging for Single-AZ databases
The *binary log* is a set of log files that contain information about data modifications made to an MySQL server instance. The binary log contains information such as the following:
+ Events that describe database changes such as table creation or row modifications
+ Information about the duration of each statement that updated data
+ Events for statements that could have updated data but didn't
The binary log records statements that are sent during replication. It is also required for some recovery operations. For more information, see [The Binary Log](https://dev.mysql.com/doc/refman/8.0/en/binary-log.html) in the MySQL documentation.
The automated backups feature determines whether binary logging is turned on or off for MySQL. You have the following options:
Turn binary logging on
Set the backup retention period to a positive nonzero value.
Turn binary logging off
Set the backup retention period to zero.
For more information, see [Enabling automated backups](USER_WorkingWithAutomatedBackups.Enabling.md).
MySQL on Amazon RDS supports the *row-based*, *statement-based*, and *mixed* binary logging formats. We recommend mixed unless you need a specific binlog format. For details on the different MySQL binary log formats, see [Binary Logging Formats](https://dev.mysql.com/doc/refman/8.0/en/binary-log-formats.html) in the MySQL documentation.
If you plan to use replication, the binary logging format is important because it determines the record of data changes that is recorded in the source and sent to the replication targets. For information about the advantages and disadvantages of different binary logging formats for replication, see [Advantages and Disadvantages of Statement-Based and Row-Based Replication](https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html) in the MySQL documentation.
**Important**
With MySQL 8.0.34, MySQL deprecated the `binlog_format` parameter. In later MySQL versions, MySQL plans to remove the parameter and only support row-based replication. As a result, we recommend using row-based logging for new MySQL replication setups. For more information, see [binlog\$1format](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_format) in the MySQL documentation.
MySQL versions 8.0 and 8.4 accept the parameter `binlog_format`. When using this parameter, MySQL issues a deprecation warning. In a future major release, MySQL will remove the parameter `binlog_format`.
Statement-based replication can cause inconsistencies between the source DB instance and a read replica. For more information, see [Determination of Safe and Unsafe Statements in Binary Logging](https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-safe-unsafe.html) in the MySQL documentation.
Enabling binary logging increases the number of write disk I/O operations to the DB instance. You can monitor IOPS usage with the `WriteIOPS``` CloudWatch metric.
**To set the MySQL binary logging format**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Parameter groups**.
1. Choose the DB parameter group, associated with the DB instance, that you want to modify.
You can't modify a default parameter group. If the DB instance is using a default parameter group, create a new parameter group and associate it with the DB instance.
For more information on parameter groups, see [Parameter groups for Amazon RDS](USER_WorkingWithParamGroups.md).
1. From **Actions**, choose **Edit**.
1. Set the `binlog_format` parameter to the binary logging format of your choice (`ROW`, `STATEMENT`, or `MIXED`).
You can turn off binary logging by setting the backup retention period of a DB instance to zero, but this disables daily automated backups. Disabling automated backups turns off or disables the `log_bin` session variable. This disables binary logging on the RDS for MySQL DB instance, which in turn resets the `binlog_format` session variable to the default value of `ROW` in the database. We recommend that you don't disable backups. For more information about the **Backup retention period** setting, see [Settings for DB instances](USER_ModifyInstance.Settings.md).
1. Choose **Save changes** to save the updates to the DB parameter group.
Because the `binlog_format` parameter is dynamic in RDS for MySQL, you don't need to reboot the DB instance for the changes to apply. (Note that in Aurora MySQL, this parameter is static. For more information, see [Configuring Aurora MySQL binary logging](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.MySQL.BinaryFormat.html).)
**Important**
Changing a DB parameter group affects all DB instances that use that parameter group. If you want to specify different binary logging formats for different MySQL DB instances in an AWS Region, the DB instances must use different DB parameter groups. These parameter groups identify different logging formats. Assign the appropriate DB parameter group to the each DB instance.
# Configuring MySQL binary logging for Multi-AZ DB clusters
Binary logging in Amazon RDS for MySQL Multi-AZ DB clusters records all database changes to support replication, point-in-time recovery, and auditing. In Multi-AZ DB clusters, binary logs synchronize secondary nodes with the primary node, ensuring data consistency across Availability Zones and enabling seamless failovers.
To optimize binary logging, Amazon RDS supports binary log transaction compression, which reduces the storage requirements for binary logs and improves replication efficiency.
**Topics**
+ [
## Binary log transaction compression for Multi-AZ DB clusters
](#USER_Binlog.MultiAZ.compression)
+ [
## Configuring binary log transaction compression for Multi-AZ DB clusters
](#USER_Binlog.MultiAZ.configuring)
## Binary log transaction compression for Multi-AZ DB clusters
Binary log transaction compression uses the zstd algorithm to reduce the size of transaction data stored in binary logs. When enabled, the MySQL database engine compresses transaction payloads into a single event, minimizing I/O and storage overhead. This feature improves database performance, reduces binary log size, and optimizes resource use for managing and replicating logs in Multi-AZ DB clusters.
Amazon RDS provides binary log transaction compression for RDS for MySQL Multi-AZ DB clusters through the following parameters:
+ `binlog_transaction_compression` – When enabled (`1`), the database engine compresses transaction payloads and writes them to the binary log as a single event. This reduces storage usage and I/O overhead. The parameter is disabled by default.
+ `binlog_transaction_compression_level_zstd` – Configures the zstd compression level for binary log transactions. Higher values increase the compression ratio, reducing storage requirements further but increasing CPU and memory usage for compression. The default value is 3, with a range of 1-22.
These parameters let you fine-tune binary log compression based on workload characteristics and resource availability. For more information, see [Binary Log Transaction Compression](https://dev.mysql.com/doc/refman/8.4/en/binary-log-transaction-compression.html) in the MySQL documentation.
Binary log transaction compression has the following main benefits:
+ Compression decreases the size of binary logs, particularly for workloads with large transactions or high write volumes.
+ Smaller binary logs reduce network and I/O overhead, enhancing replication performance.
+ The `binlog_transaction_compression_level_zstd` parameter provides control over the trade-off between compression ratio and resource consumption.
## Configuring binary log transaction compression for Multi-AZ DB clusters
To configure binary log transaction compression for an RDS for MySQL Multi-AZ DB cluster, modify the relevant cluster parameter settings to match your workload requirements.
### Console
**To enable binary log transaction compression**
1. Modify the DB cluster parameter group to set the `binlog_transaction_compression` parameter to `1`.
1. (Optional) Adjust the value of the `binlog_transaction_compression_level_zstd` parameter based on your workload requirements and resource availability.
For more information, see [Modifying parameters in a DB cluster parameter group](USER_WorkingWithParamGroups.ModifyingCluster.md).
### AWS CLI
To configure binary log transaction compression using the AWS CLI, use the [modify-db-cluster-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-cluster-parameter-group.html) command.
**Example**
For Linux, macOS, or Unix:
```
aws rds modify-db-cluster-parameter-group \
--db-cluster-parameter-group-name your-cluster-parameter-group \
--parameters "ParameterName=binlog_transaction_compression,ParameterValue=1,ApplyMethod=pending-reboot"
```
For Windows:
```
aws rds modify-db-cluster-parameter-group ^
--db-cluster-parameter-group-name your-cluster-parameter-group ^
--parameters "ParameterName=binlog_transaction_compression,ParameterValue=1,ApplyMethod=pending-reboot"
```
### RDS API
To configure binary log transaction compression using the Amazon RDS API, use the [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBClusterParameterGroup.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBClusterParameterGroup.html) operation.
# Accessing MySQL binary logs
You can use the mysqlbinlog utility to download or stream binary logs from RDS for MySQL DB instances. The binary log is downloaded to your local computer, where you can perform actions such as replaying the log using the mysql utility. For more information about using the mysqlbinlog utility, see [Using mysqlbinlog to back up binary log files](https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html) in the MySQL documentation.
To run the mysqlbinlog utility against an Amazon RDS instance, use the following options:
+ `--read-from-remote-server` – Required.
+ `--host` – The DNS name from the endpoint of the instance.
+ `--port` – The port used by the instance.
+ `--user` – A MySQL user that has been granted the `REPLICATION SLAVE` permission.
+ `--password` – The password for the MySQL user, or omit a password value so that the utility prompts you for a password.
+ `--raw` – Download the file in binary format.
+ `--result-file` – The local file to receive the raw output.
+ `--stop-never` – Stream the binary log files.
+ `--verbose` – When you use the `ROW` binlog format, include this option to see the row events as pseudo-SQL statements. For more information on the `--verbose` option, see [mysqlbinlog row event display](https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-row-events.html) in the MySQL documentation.
+ Specify the names of one or more binary log files. To get a list of the available logs, use the SQL command `SHOW BINARY LOGS`.
For more information about mysqlbinlog options, see [mysqlbinlog — Utility for processing binary log files](https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog.html) in the MySQL documentation.
The following examples show how to use the mysqlbinlog utility.
For Linux, macOS, or Unix:
```
mysqlbinlog \
--read-from-remote-server \
--host=MySQLInstance1.cg034hpkmmjt.region.rds.amazonaws.com \
--port=3306 \
--user ReplUser \
--password \
--raw \
--verbose \
--result-file=/tmp/ \
binlog.00098
```
For Windows:
```
mysqlbinlog ^
--read-from-remote-server ^
--host=MySQLInstance1.cg034hpkmmjt.region.rds.amazonaws.com ^
--port=3306 ^
--user ReplUser ^
--password ^
--raw ^
--verbose ^
--result-file=/tmp/ ^
binlog.00098
```
Binary logs must remain available on the DB instance for the mysqlbinlog utility to access them. To ensure their availability, use the [mysql.rds\$1set\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_set_configuration) stored procedure and specify a period with enough time for you to download the logs. If this configuration isn't set, Amazon RDS purges the binary logs as soon as possible, leading to gaps in the binary logs that the mysqlbinlog utility retrieves.
The following example sets the retention period to 1 day.
```
call mysql.rds_set_configuration('binlog retention hours', 24);
```
To display the current setting, use the [mysql.rds\$1show\$1configuration](mysql-stored-proc-configuring.md#mysql_rds_show_configuration) stored procedure.
```
call mysql.rds_show_configuration;
```
# Amazon RDS for Oracle database log files
You can access Oracle alert logs, audit files, and trace files by using the Amazon RDS console or API. For more information about viewing, downloading, and watching file-based database logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md).
The Oracle audit files provided are the standard Oracle auditing files. Amazon RDS supports the Oracle fine-grained auditing (FGA) feature. However, log access doesn't provide access to FGA events that are stored in the `SYS.FGA_LOG$` table and that are accessible through the `DBA_FGA_AUDIT_TRAIL` view.
The [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBLogFiles.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DescribeDBLogFiles.html) API operation that lists the Oracle log files that are available for a DB instance ignores the `MaxRecords` parameter and returns up to 1,000 records. The call returns `LastWritten` as a POSIX date in milliseconds.
**Topics**
+ [
## Retention schedule
](#USER_LogAccess.Concepts.Oracle.Retention)
+ [
## Working with Oracle trace files
](#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles)
+ [
## Publishing Oracle logs to Amazon CloudWatch Logs
](#USER_LogAccess.Oracle.PublishtoCloudWatchLogs)
+ [
## Accessing alert logs and listener logs
](#USER_LogAccess.Concepts.Oracle.AlertLogAndListenerLog)
## Retention schedule
The Oracle database engine might rotate log files if they get very large. To retain audit or trace files, download them. If you store the files locally, you reduce your Amazon RDS storage costs and make more space available for your data.
The following table shows the retention schedule for Oracle alert logs, audit files, and trace files on Amazon RDS.
****
| Log type | Retention schedule |
| --- | --- |
| Alert logs | The text alert log is rotated daily with 30-day retention managed by Amazon RDS. The XML alert log is retained for at least seven days. You can access this log by using the `ALERTLOG` view. |
| Audit files | The default retention period for audit files is seven days. Amazon RDS might delete audit files older than seven days. |
| Trace files | The default retention period for trace files is seven days. Amazon RDS might delete trace files older than seven days. |
| Listener logs | The default retention period for the listener logs is seven days. Amazon RDS might delete listener logs older than seven days. |
**Note**
Audit files and trace files share the same retention configuration.
## Working with Oracle trace files
Following, you can find descriptions of Amazon RDS procedures to create, refresh, access, and delete trace files.
**Topics**
+ [
### Listing files
](#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles.ViewingBackgroundDumpDest)
+ [
### Generating trace files and tracing a session
](#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles.Generating)
+ [
### Retrieving trace files
](#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles.Retrieving)
+ [
### Purging trace files
](#USER_LogAccess.Concepts.Oracle.WorkingWithTracefiles.Purging)
### Listing files
You can use either of two procedures to allow access to any file in the `background_dump_dest` path. The first procedure refreshes a view containing a listing of all files currently in `background_dump_dest`.
```
1. EXEC rdsadmin.manage_tracefiles.refresh_tracefile_listing;
```
After the view is refreshed, query the following view to access the results.
```
1. SELECT * FROM rdsadmin.tracefile_listing;
```
An alternative to the previous process is to use `FROM table` to stream nonrelational data in a table-like format to list database directory contents.
```
1. SELECT * FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP'));
```
The following query shows the text of a log file.
```
1. SELECT text FROM TABLE(rdsadmin.rds_file_util.read_text_file('BDUMP','alert_dbname.log.date'));
```
On a read replica, get the name of the BDUMP directory by querying `V$DATABASE.DB_UNIQUE_NAME`. If the unique name is `DATABASE_B`, then the BDUMP directory is `BDUMP_B`. The following example queries the BDUMP name on a replica and then uses this name to query the contents of `alert_DATABASE.log.2020-06-23`.
```
1. SELECT 'BDUMP' || (SELECT regexp_replace(DB_UNIQUE_NAME,'.*(_[A-Z])', '\1') FROM V$DATABASE) AS BDUMP_VARIABLE FROM DUAL;
2.
3. BDUMP_VARIABLE
4. --------------
5. BDUMP_B
6.
7. SELECT TEXT FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP_B','alert_DATABASE.log.2020-06-23'));
```
### Generating trace files and tracing a session
Because there are no restrictions on `ALTER SESSION`, many standard methods to generate trace files in Oracle remain available to an Amazon RDS DB instance. The following procedures are provided for trace files that require greater access.
****
| Oracle method | Amazon RDS method |
| --- | --- |
| `oradebug hanganalyze 3 ` | `EXEC rdsadmin.manage_tracefiles.hanganalyze; ` |
| `oradebug dump systemstate 266 ` | `EXEC rdsadmin.manage_tracefiles.dump_systemstate;` |
You can use many standard methods to trace individual sessions connected to an Oracle DB instance in Amazon RDS. To enable tracing for a session, you can run subprograms in PL/SQL packages supplied by Oracle, such as `DBMS_SESSION` and `DBMS_MONITOR`. For more information, see [ Enabling tracing for a session](https://docs.oracle.com/database/121/TGSQL/tgsql_trace.htm#GUID-F872D6F9-E015-481F-80F6-8A7036A6AD29) in the Oracle documentation.
### Retrieving trace files
You can retrieve any trace file in `background_dump_dest` using a standard SQL query on an Amazon RDS–managed external table. To use this method, you must execute the procedure to set the location for this table to the specific trace file.
For example, you can use the `rdsadmin.tracefile_listing` view mentioned preceding to list all of the trace files on the system. You can then set the `tracefile_table` view to point to the intended trace file using the following procedure.
```
1. EXEC rdsadmin.manage_tracefiles.set_tracefile_table_location('CUST01_ora_3260_SYSTEMSTATE.trc');
```
The following example creates an external table in the current schema with the location set to the file provided. You can retrieve the contents into a local file using a SQL query.
```
1. SPOOL /tmp/tracefile.txt
2. SELECT * FROM tracefile_table;
3. SPOOL OFF;
```
### Purging trace files
Trace files can accumulate and consume disk space. Amazon RDS purges trace files by default and log files that are older than seven days. You can view and set the trace file retention period using the `show_configuration` procedure. You should run the command `SET SERVEROUTPUT ON` so that you can view the configuration results.
The following example shows the current trace file retention period, and then sets a new trace file retention period.
```
1. # Show the current tracefile retention
2. SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
3. NAME:tracefile retention
4. VALUE:10080
5. DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted.
6.
7. # Set the tracefile retention to 24 hours:
8. SQL> EXEC rdsadmin.rdsadmin_util.set_configuration('tracefile retention',1440);
9. SQL> commit;
10.
11. #show the new tracefile retention
12. SQL> EXEC rdsadmin.rdsadmin_util.show_configuration;
13. NAME:tracefile retention
14. VALUE:1440
15. DESCRIPTION:tracefile expiration specifies the duration in minutes before tracefiles in bdump are automatically deleted.
```
In addition to the periodic purge process, you can manually remove files from the `background_dump_dest`. The following example shows how to purge all files older than five minutes.
```
EXEC rdsadmin.manage_tracefiles.purge_tracefiles(5);
```
You can also purge all files that match a specific pattern (if you do, don't include the file extension, such as .trc). The following example shows how to purge all files that start with `SCHPOC1_ora_5935`.
```
1. EXEC rdsadmin.manage_tracefiles.purge_tracefiles('SCHPOC1_ora_5935');
```
## Publishing Oracle logs to Amazon CloudWatch Logs
You can configure your RDS for Oracle DB instance to publish log data to a log group in Amazon CloudWatch Logs. With CloudWatch Logs, you can analyze the log data, and use CloudWatch to create alarms and view metrics. You can use CloudWatch Logs to store your log records in highly durable storage.
Amazon RDS publishes each Oracle database log as a separate database stream in the log group. For example, if you configure the export function to include the audit log, audit data is stored in an audit log stream in the `/aws/rds/instance/my_instance/audit` log group. The following table summarizes the requirements for RDS for Oracle to publish logs to Amazon CloudWatch Logs.
| Log name | Requirement | Default |
| --- | --- | --- |
| Alert log | None. You can't disable this log. | Enabled |
| Trace log | Set the `trace_enabled` parameter to `TRUE` or leave it set at the default. | `TRUE` |
| Audit log | Set the `audit_trail` parameter to any of the following allowed values: { none | os | db [, extended] | xml [, extended] } | `none` |
| Listener log | None. You can't disable this log. | Enabled |
| Oracle Management Agent log | None. You can't disable this log. | Enabled |
This Oracle Management Agent log consists of the log groups shown in the following table.
****
| Log name | CloudWatch log group |
| --- | --- |
| emctl.log | oemagent-emctl |
| emdctlj.log | oemagent-emdctlj |
| gcagent.log | oemagent-gcagent |
| gcagent\$1errors.log | oemagent-gcagent-errors |
| emagent.nohup | oemagent-emagent-nohup |
| secure.log | oemagent-secure |
For more information, see [Locating Management Agent Log and Trace Files](https://docs.oracle.com/en/enterprise-manager/cloud-control/enterprise-manager-cloud-control/13.4/emadm/locating-management-agent-log-and-trace-files1.html#GUID-9C710D78-6AA4-42E4-83CD-47B5FF4892DF) in the Oracle documentation.
### Console
**To publish Oracle DB logs to CloudWatch Logs from the AWS Management Console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**, and then choose the DB instance that you want to modify.
1. Choose **Modify**.
1. In the **Log exports** section, choose the logs that you want to start publishing to CloudWatch Logs.
1. Choose **Continue**, and then choose **Modify DB Instance** on the summary page.
### AWS CLI
To publish Oracle logs, you can use the [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command with the following parameters:
+ `--db-instance-identifier`
+ `--cloudwatch-logs-export-configuration`
**Note**
A change to the `--cloudwatch-logs-export-configuration` option is always applied to the DB instance immediately. Therefore, the `--apply-immediately` and `--no-apply-immediately` options have no effect.
You can also publish Oracle logs using the following commands:
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-s3.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html)
**Example**
The following example creates an Oracle DB instance with CloudWatch Logs publishing enabled. The `--cloudwatch-logs-export-configuration` value is a JSON array of strings. The strings can be any combination of `alert`, `audit`, `listener`, and `trace`.
For Linux, macOS, or Unix:
```
aws rds create-db-instance \
--db-instance-identifier mydbinstance \
--cloudwatch-logs-export-configuration '["trace","audit","alert","listener","oemagent"]' \
--db-instance-class db.m5.large \
--allocated-storage 20 \
--engine oracle-ee \
--engine-version 19.0.0.0.ru-2024-04.rur-2024-04.r1 \
--license-model bring-your-own-license \
--master-username myadmin \
--manage-master-user-password
```
For Windows:
```
aws rds create-db-instance ^
--db-instance-identifier mydbinstance ^
--cloudwatch-logs-export-configuration trace alert audit listener oemagent ^
--db-instance-class db.m5.large ^
--allocated-storage 20 ^
--engine oracle-ee ^
--engine-version 19.0.0.0.ru-2024-04.rur-2024-04.r1 ^
--license-model bring-your-own-license ^
--master-username myadmin ^
--manage-master-user-password
```
**Example**
The following example modifies an existing Oracle DB instance to publish log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `EnableLogTypes`, and its value is an array of strings with any combination of `alert`, `audit`, `listener`, and `trace`.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--cloudwatch-logs-export-configuration '{"EnableLogTypes":["trace","alert","audit","listener","oemagent"]}'
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--cloudwatch-logs-export-configuration EnableLogTypes=\"trace\",\"alert\",\"audit\",\"listener\",\"oemagent\"
```
**Example**
The following example modifies an existing Oracle DB instance to disable publishing audit and listener log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `DisableLogTypes`, and its value is an array of strings with any combination of `alert`, `audit`, `listener`, and `trace`.
For Linux, macOS, or Unix:
```
aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--cloudwatch-logs-export-configuration '{"DisableLogTypes":["audit","listener"]}'
```
For Windows:
```
aws rds modify-db-instance ^
--db-instance-identifier mydbinstance ^
--cloudwatch-logs-export-configuration DisableLogTypes=\"audit\",\"listener\"
```
### RDS API
You can publish Oracle DB logs with the RDS API. You can call the [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) action with the following parameters:
+ `DBInstanceIdentifier`
+ `CloudwatchLogsExportConfiguration`
**Note**
A change to the `CloudwatchLogsExportConfiguration` parameter is always applied to the DB instance immediately. Therefore, the `ApplyImmediately` parameter has no effect.
You can also publish Oracle logs by calling the following RDS API operations:
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromS3.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html)
Run one of these RDS API operations with the following parameters:
+ `DBInstanceIdentifier`
+ `EnableCloudwatchLogsExports`
+ `Engine`
+ `DBInstanceClass`
Other parameters might be required depending on the RDS operation that you run.
## Accessing alert logs and listener logs
You can view the alert log using the Amazon RDS console. You can also use the following SQL statement.
```
1. SELECT message_text FROM alertlog;
```
Access the listener log using Amazon CloudWatch Logs.
**Note**
Oracle rotates the alert and listener logs when they exceed 10 MB, at which point they are unavailable from Amazon RDS views.
# RDS for PostgreSQL database log files
You can monitor the following types of log files:
+ PostgreSQL log
+ Upgrade log
+ IAM database authentication error log
**Note**
To enable IAM database authentication error logs, you must first enable IAM database authentication for your RDS for PostgreSQL DB instance. For more information about enabling IAM database authentication, see [Enabling and disabling IAM database authentication](UsingWithRDS.IAMDBAuth.Enabling.md).
RDS for PostgreSQL logs database activities to the default PostgreSQL log file. For an on-premises PostgreSQL DB instance, these messages are stored locally in `log/postgresql.log`. For an RDS for PostgreSQL DB instance, the log file is available on the Amazon RDS instance. These logs are also accessible via the AWS Management Console, where you can view or download them. The default logging level captures login failures, fatal server errors, deadlocks, and query failures.
For more information about how you can view, download, and watch file-based database logs, see [Monitoring Amazon RDS log files](USER_LogAccess.md). To learn more about PostgreSQL logs, see [Working with Amazon RDS and Aurora PostgreSQL logs: Part 1](https://aws.amazon.com/blogs/database/working-with-rds-and-aurora-postgresql-logs-part-1/) and [ Working with Amazon RDS and Aurora PostgreSQL logs: Part 2](https://aws.amazon.com/blogs/database/working-with-rds-and-aurora-postgresql-logs-part-2/).
In addition to the standard PostgreSQL logs discussed in this topic, RDS for PostgreSQL also supports the PostgreSQL Audit extension (`pgAudit`). Most regulated industries and government agencies need to maintain an audit log or audit trail of changes made to data to comply with legal requirements. For information about installing and using pgAudit, see [Using pgAudit to log database activity](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md).
**Topics**
+ [
# Parameters for logging in RDS for PostgreSQL
](USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.md)
+ [
# Turning on query logging for your RDS for PostgreSQL DB instance
](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md)
+ [
## Publishing PostgreSQL logs to Amazon CloudWatch Logs
](#USER_LogAccess.Concepts.PostgreSQL.PublishtoCloudWatchLogs)
# Parameters for logging in RDS for PostgreSQL
You can customize the logging behavior for your RDS for PostgreSQL DB instance by modifying various parameters. In the following table you can find the parameters that affect how long the logs are stored, when to rotate the log, and whether to output the log as a CSV (comma-separated value) format. You can also find the text output sent to STDERR, among other settings. To change settings for the parameters that are modifiable, use a custom DB parameter group for your RDS for PostgreSQL instance. For more information, see [DB parameter groups for Amazon RDS DB instances](USER_WorkingWithDBInstanceParamGroups.md).
| Parameter | Default | Description |
| --- | --- | --- |
| log\$1destination | stderr | Sets the output format for the log. The default is `stderr` but you can also specify comma-separated value (CSV) by adding `csvlog` to the setting. For more information, see [Setting the log destination (`stderr`, `csvlog`)](#USER_LogAccess.Concepts.PostgreSQL.Log_Format). |
| log\$1filename | postgresql.log.%Y-%m-%d-%H | Specifies the pattern for the log file name. In addition to the default, this parameter supports `postgresql.log.%Y-%m-%d` and `postgresql.log.%Y-%m-%d-%H%M` for the filename pattern. |
| log\$1line\$1prefix | %t:%r:%u@%d:[%p]: | Defines the prefix for each log line that gets written to `stderr`, to note the time (%t), remote host (%r), user (%u), database (%d), and process ID (%p). |
| log\$1rotation\$1age | 60 | Minutes after which log file is automatically rotated. You can change this value within the range of 1 and 1440 minutes. For more information, see [Setting log file rotation](#USER_LogAccess.Concepts.PostgreSQL.log_rotation). |
| log\$1rotation\$1size | – | The size (kB) at which the log is automatically rotated. By default, this parameter isn't used because logs are rotated based on the `log_rotation_age` parameter. To learn more, see [Setting log file rotation](#USER_LogAccess.Concepts.PostgreSQL.log_rotation). |
| rds.log\$1retention\$1period | 4320 | PostgreSQL logs that are older than the specified number of minutes are deleted. The default value of 4320 minutes deletes log files after 3 days. For more information, see [Setting the log retention period](#USER_LogAccess.Concepts.PostgreSQL.log_retention_period). |
To identify application issues, you can look for query failures, login failures, deadlocks, and fatal server errors in the log. For example, suppose that you converted a legacy application from Oracle to Amazon RDS PostgreSQL, but not all queries converted correctly. These incorrectly formatted queries generate error messages that you can find in the logs to help identify problems. For more information about logging queries, see [Turning on query logging for your RDS for PostgreSQL DB instance](USER_LogAccess.Concepts.PostgreSQL.Query_Logging.md).
In the following topics, you can find information about how to set various parameters that control the basic details for your PostgreSQL logs.
**Topics**
+ [
## Setting the log retention period
](#USER_LogAccess.Concepts.PostgreSQL.log_retention_period)
+ [
## Setting log file rotation
](#USER_LogAccess.Concepts.PostgreSQL.log_rotation)
+ [
## Setting the log destination (`stderr`, `csvlog`)
](#USER_LogAccess.Concepts.PostgreSQL.Log_Format)
+ [
## Understanding the log\$1line\$1prefix parameter
](#USER_LogAccess.Concepts.PostgreSQL.Log_Format.log-line-prefix)
## Setting the log retention period
The `rds.log_retention_period` parameter specifies how long your RDS for PostgreSQL DB instance keeps its log files. The default setting is 3 days (4,320 minutes), but you can set this value to anywhere from 1 day (1,440 minutes) to 7 days (10,080 minutes). Be sure that your RDS for PostgreSQL DB instance has sufficient storage to hold the log files for the period of time.
We recommend that you have your logs routinely published to Amazon CloudWatch Logs so that you can view and analyze system data long after the logs have been removed from your RDS for PostgreSQL DB instance. For more information, see [Publishing PostgreSQL logs to Amazon CloudWatch Logs](USER_LogAccess.Concepts.PostgreSQL.md#USER_LogAccess.Concepts.PostgreSQL.PublishtoCloudWatchLogs).
## Setting log file rotation
Amazon RDS creates new log files every hour by default. The timing is controlled by the `log_rotation_age` parameter. This parameter has a default value of 60 (minutes), but you can set it to anywhere from 1 minute to 24 hours (1,440 minutes). When it's time for rotation, a new distinct log file is created. The file is named according to the pattern specified by the `log_filename` parameter.
Log files can also be rotated according to their size, as specified in the `log_rotation_size` parameter. This parameter specifies that the log should be rotated when it reaches the specified size (in kilobytes). For an RDS for PostgreSQL DB instance, `log_rotation_size` is unset, that is, there is no value specified. However, you can set the parameter from 0-2097151 kB (kilobytes).
The log file names are based on the file name pattern specified in the `log_filename` parameter. The available settings for this parameter are as follows:
+ `postgresql.log.%Y-%m-%d` – Default format for the log file name. Includes the year, month, and date in the name of the log file.
+ `postgresql.log.%Y-%m-%d-%H` – Includes the hour in the log file name format.
For more information, see [https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-AGE](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-AGE) and [https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-ROTATION-SIZE) in the PostgreSQL documentation.
## Setting the log destination (`stderr`, `csvlog`)
By default, Amazon RDS PostgreSQL generates logs in standard error (stderr) format. This format is the default setting for the `log_destination` parameter. Each message is prefixed using the pattern specified in the `log_line_prefix` parameter. For more information, see [Understanding the log\$1line\$1prefix parameter](#USER_LogAccess.Concepts.PostgreSQL.Log_Format.log-line-prefix).
RDS for PostgreSQL can also generate the logs in `csvlog` format. The `csvlog` is useful for analyzing the log data as comma-separated values (CSV) data. For example, suppose that you use the `log_fdw` extension to work with your logs as foreign tables. The foreign table created on `stderr` log files contains a single column with log event data. By adding `csvlog` to the `log_destination` parameter, you get the log file in the CSV format with demarcations for the multiple columns of the foreign table. You can now sort and analyze your logs more easily. To learn how to use the `log_fdw` with `csvlog`, see [Using the log\$1fdw extension to access the DB log using SQL](CHAP_PostgreSQL.Extensions.log_fdw.md).
If you specify `csvlog` for this parameter, be aware that both `stderr` and `csvlog` files are generated. Be sure to monitor the storage consumed by the logs, taking into account the `rds.log_retention_period` and other settings that affect log storage and turnover. Using `stderr` and `csvlog` more than doubles the storage consumed by the logs.
If you add `csvlog` to `log_destination` and you want to revert to the `stderr` alone, you need to reset the parameter. To do so, open the Amazon RDS Console and then open the custom DB parameter group for your instance. Choose the `log_destination` parameter, choose **Edit parameter**, and then choose **Reset**.
For more information about configuring logging, see [ Working with Amazon RDS and Aurora PostgreSQL logs: Part 1](https://aws.amazon.com/blogs/database/working-with-rds-and-aurora-postgresql-logs-part-1/).
## Understanding the log\$1line\$1prefix parameter
The `stderr` log format prefixes each log message with the details specified by the `log_line_prefix` parameter. The default value is:
```
%t:%r:%u@%d:[%p]:t
```
Starting from Aurora PostgreSQL version 16, you can also choose:
```
%m:%r:%u@%d:[%p]:%l:%e:%s:%v:%x:%c:%q%a
```
Each log entry sent to stderr includes the following information based on the selected value:
+ `%t` – Time of log entry without milliseconds
+ `%m` – Time of log entry with milliseconds
+ `%r` – Remote host address
+ `%u@%d` – User name @ database name
+ `[%p]` – Process ID if available
+ `%l` – Log line number per session
+ `%e` – SQL error code
+ `%s` – Process start timestamp
+ `%v` – Virtual transaction id
+ `%x` – Transaction ID
+ `%c` – Session ID
+ `%q` – Non-session terminator
+ `%a` – Application name
# Turning on query logging for your RDS for PostgreSQL DB instance
You can collect more detailed information about your database activities, including queries, queries waiting for locks, checkpoints, and many other details by setting some of the parameters listed in the following table. This topic focuses on logging queries.
| Parameter | Default | Description |
| --- | --- | --- |
| log\$1connections | – | Logs each successful connection. |
| log\$1disconnections | – | Logs the end of each session and its duration. |
| log\$1checkpoints | 1 | Logs each checkpoint. |
| log\$1lock\$1waits | – | Logs long lock waits. By default, this parameter isn't set. |
| log\$1min\$1duration\$1sample | – | (ms) Sets the minimum execution time above which a sample of statements is logged. Sample size is set using the log\$1statement\$1sample\$1rate parameter. |
| log\$1min\$1duration\$1statement | – | Any SQL statement that runs atleast for the specified amount of time or longer gets logged. By default, this parameter isn't set. Turning on this parameter can help you find unoptimized queries. |
| log\$1statement | – | Sets the type of statements logged. By default, this parameter isn't set, but you can change it to `all`, `ddl`, or `mod` to specify the types of SQL statements that you want logged. If you specify anything other than `none` for this parameter, you should also take additional steps to prevent the exposure of passwords in the log files. For more information, see [Mitigating risk of password exposure when using query loggingMitigating password exposure risk](#USER_LogAccess.Concepts.PostgreSQL.Query_Logging.mitigate-risk). |
| log\$1statement\$1sample\$1rate | – | The percentage of statements exceeding the time specified in `log_min_duration_sample` to be logged, expressed as a floating point value between 0.0 and 1.0. |
| log\$1statement\$1stats | – | Writes cumulative performance statistics to the server log. |
## Using logging to find slow performing queries
You can log SQL statements and queries to help find slow performing queries. You turn on this capability by modifying the settings in the `log_statement` and `log_min_duration` parameters as outlined in this section. Before turning on query logging for your RDS for PostgreSQL DB instance, you should be aware of possible password exposure in the logs and how to mitigate the risks. For more information, see [Mitigating risk of password exposure when using query loggingMitigating password exposure risk](#USER_LogAccess.Concepts.PostgreSQL.Query_Logging.mitigate-risk).
Following, you can find reference information about the `log_statement` and `log_min_duration` parameters.log\$1statement
This parameter specifies the type of SQL statements that should get sent to the log. The default value is `none`. If you change this parameter to `all`, `ddl`, or `mod`, be sure to apply recommended actions to mitigate the risk of exposing passwords in the logs. For more information, see [Mitigating risk of password exposure when using query loggingMitigating password exposure risk](#USER_LogAccess.Concepts.PostgreSQL.Query_Logging.mitigate-risk).
**all**
Logs all statements. This setting is recommended for debugging purposes.
**ddl**
Logs all data definition language (DDL) statements, such as CREATE, ALTER, DROP, and so on.
**mod**
Logs all DDL statements and data manipulation language (DML) statements, such as INSERT, UPDATE, and DELETE, which modify the data.
**none**
No SQL statements get logged. We recommend this setting to avoid the risk of exposing passwords in the logs.log\$1min\$1duration\$1statement
Any SQL statement that runs atleast for the specified amount of time or longer gets logged. By default, this parameter isn't set. Turning on this parameter can help you find unoptimized queries.
**–1–2147483647**
The number of milliseconds (ms) of runtime over which a statement gets logged.
**To set up query logging**
These steps assume that your RDS for PostgreSQL DB instance uses a custom DB parameter group.
1. Set the `log_statement` parameter to `all`. The following example shows the information that is written to the `postgresql.log` file with this parameter setting.
```
2022-10-05 22:05:52 UTC:52.95.4.1(11335):postgres@labdb:[3639]:LOG: statement: SELECT feedback, s.sentiment,s.confidence
FROM support,aws_comprehend.detect_sentiment(feedback, 'en') s
ORDER BY s.confidence DESC;
2022-10-05 22:05:52 UTC:52.95.4.1(11335):postgres@labdb:[3639]:LOG: QUERY STATISTICS
2022-10-05 22:05:52 UTC:52.95.4.1(11335):postgres@labdb:[3639]:DETAIL: ! system usage stats:
! 0.017355 s user, 0.000000 s system, 0.168593 s elapsed
! [0.025146 s user, 0.000000 s system total]
! 36644 kB max resident size
! 0/8 [0/8] filesystem blocks in/out
! 0/733 [0/1364] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 19/0 [27/0] voluntary/involuntary context switches
2022-10-05 22:05:52 UTC:52.95.4.1(11335):postgres@labdb:[3639]:STATEMENT: SELECT feedback, s.sentiment,s.confidence
FROM support,aws_comprehend.detect_sentiment(feedback, 'en') s
ORDER BY s.confidence DESC;
2022-10-05 22:05:56 UTC:52.95.4.1(11335):postgres@labdb:[3639]:ERROR: syntax error at or near "ORDER" at character 1
2022-10-05 22:05:56 UTC:52.95.4.1(11335):postgres@labdb:[3639]:STATEMENT: ORDER BY s.confidence DESC;
----------------------- END OF LOG ----------------------
```
1. Set the `log_min_duration_statement` parameter. The following example shows the information that is written to the `postgresql.log` file when the parameter is set to `1`.
Queries that exceed the duration specified in the `log_min_duration_statement` parameter are logged. The following shows an example. You can view the log file for your RDS for PostgreSQL DB instance in the Amazon RDS Console.
```
2022-10-05 19:05:19 UTC:52.95.4.1(6461):postgres@labdb:[6144]:LOG: statement: DROP table comments;
2022-10-05 19:05:19 UTC:52.95.4.1(6461):postgres@labdb:[6144]:LOG: duration: 167.754 ms
2022-10-05 19:08:07 UTC::@:[355]:LOG: checkpoint starting: time
2022-10-05 19:08:08 UTC::@:[355]:LOG: checkpoint complete: wrote 11 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=1.013 s, sync=0.006 s, total=1.033 s; sync files=8, longest=0.004 s, average=0.001 s; distance=131028 kB, estimate=131028 kB
----------------------- END OF LOG ----------------------
```
### Mitigating risk of password exposure when using query logging
We recommend that you keep `log_statement` set to `none` to avoid exposing passwords. If you set `log_statement` to `all`, `ddl`, or `mod`, we recommend that you take one or more of the following steps.
+ For the client, encrypt sensitive information. For more information, see [Encryption Options](https://www.postgresql.org/docs/current/encryption-options.html) in the PostgreSQL documentation. Use the `ENCRYPTED` (and `UNENCRYPTED`) options of the `CREATE` and `ALTER` statements. For more information, see [CREATE USER](https://www.postgresql.org/docs/current/sql-createuser.html) in the PostgreSQL documentation.
+ For your RDS for PostgreSQL DB instance, set up and use the PostgreSQL Auditing (pgAudit) extension. This extension redacts sensitive information in CREATE and ALTER statements sent to the log. For more information, see [Using pgAudit to log database activity](Appendix.PostgreSQL.CommonDBATasks.pgaudit.md).
+ Restrict access to the CloudWatch logs.
+ Use stronger authentication mechanisms such as IAM.
## Publishing PostgreSQL logs to Amazon CloudWatch Logs
To store your PostgreSQL log records in highly durable storage, you can use Amazon CloudWatch Logs. With CloudWatch Logs, you can also perform real-time analysis of log data and use CloudWatch to view metrics and create alarms. For example, if you set `log_statement` to `ddl`, you can set up an alarm to alert you whenever a DDL statement is executed. You can choose to have your PostgreSQL logs uploaded to CloudWatch Logs during the process of creating your RDS for PostgreSQL DB instance. If you chose not to upload logs at that time, you can later modify your instance to start uploading logs from that point forward. In other words, existing logs aren't uploaded. Only new logs are uploaded as they're created on your modified RDS for PostgreSQL DB instance.
All currently available RDS for PostgreSQL versions support publishing log files to CloudWatch Logs. For more information, see [Amazon RDS for PostgreSQL updates](https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-versions.html) in the *Amazon RDS for PostgreSQL Release Notes.*.
To work with CloudWatch Logs, configure your RDS for PostgreSQL DB instance to publish log data to a log group.
You can publish the following log types to CloudWatch Logs for RDS for PostgreSQL:
+ PostgreSQL log
+ Upgrade log
+ IAM database authentication error log
After you complete the configuration, Amazon RDS publishes the log events to log streams within a CloudWatch log group. For example, the PostgreSQL log data is stored within the log group `/aws/rds/instance/my_instance/postgresql`. To view your logs, open the CloudWatch console at [https://console.aws.amazon.com/cloudwatch/](https://console.aws.amazon.com/cloudwatch/).
### Console
**To publish PostgreSQL logs to CloudWatch Logs using the console**
1. Open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/).
1. In the navigation pane, choose **Databases**.
1. Choose the DB instance that you want to modify, and then choose **Modify**.
1. In the **Log exports** section, choose the logs that you want to start publishing to CloudWatch Logs.
The **Log exports** section is available only for PostgreSQL versions that support publishing to CloudWatch Logs.
1. Choose **Continue**, and then choose **Modify DB Instance** on the summary page.
### AWS CLI
You can publish PostgreSQL logs with the AWS CLI. You can call the [https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-instance.html) command with the following parameters.
+ `--db-instance-identifier`
+ `--cloudwatch-logs-export-configuration`
**Note**
A change to the `--cloudwatch-logs-export-configuration` option is always applied to the DB instance immediately. Therefore, the `--apply-immediately` and `--no-apply-immediately` options have no effect.
You can also publish PostgreSQL logs by calling the following CLI commands:
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html](https://docs.aws.amazon.com/cli/latest/reference/rds/create-db-instance.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-from-db-snapshot.html)
+ [https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html](https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html)
Run one of these CLI commands with the following options:
+ `--db-instance-identifier`
+ `--enable-cloudwatch-logs-exports`
+ `--db-instance-class`
+ `--engine`
Other options might be required depending on the CLI command you run.
**Example Modify an instance to publish logs to CloudWatch Logs**
The following example modifies an existing PostgreSQL DB instance to publish log files to CloudWatch Logs. The `--cloudwatch-logs-export-configuration` value is a JSON object. The key for this object is `EnableLogTypes`, and its value is an array of strings with any combination of `postgresql` and `upgrade`.
For Linux, macOS, or Unix:
```
1. aws rds modify-db-instance \
2. --db-instance-identifier mydbinstance \
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql", "upgrade"]}'
```
For Windows:
```
1. aws rds modify-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql","upgrade"]}'
```
**Example Create an instance to publish logs to CloudWatch Logs**
The following example creates a PostgreSQL DB instance and publishes log files to CloudWatch Logs. The `--enable-cloudwatch-logs-exports` value is a JSON array of strings. The strings can be any combination of `postgresql` and `upgrade`.
For Linux, macOS, or Unix:
```
1. aws rds create-db-instance \
2. --db-instance-identifier mydbinstance \
3. --enable-cloudwatch-logs-exports '["postgresql","upgrade"]' \
4. --db-instance-class db.m4.large \
5. --engine postgres
```
For Windows:
```
1. aws rds create-db-instance ^
2. --db-instance-identifier mydbinstance ^
3. --enable-cloudwatch-logs-exports '["postgresql","upgrade"]' ^
4. --db-instance-class db.m4.large ^
5. --engine postgres
```
### RDS API
You can publish PostgreSQL logs with the RDS API. You can call the [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBInstance.html) action with the following parameters:
+ `DBInstanceIdentifier`
+ `CloudwatchLogsExportConfiguration`
**Note**
A change to the `CloudwatchLogsExportConfiguration` parameter is always applied to the DB instance immediately. Therefore, the `ApplyImmediately` parameter has no effect.
You can also publish PostgreSQL logs by calling the following RDS API operations:
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_CreateDBInstance.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceFromDBSnapshot.html)
+ [https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RestoreDBInstanceToPointInTime.html)
Run one of these RDS API operations with the following parameters:
+ `DBInstanceIdentifier`
+ `EnableCloudwatchLogsExports`
+ `Engine`
+ `DBInstanceClass`
Other parameters might be required depending on the operation that you run.