Working with RDS and Aurora PostgreSQL logs: Part 2
July 2023: This post was reviewed for accuracy.
The first post in this series, Working with RDS and Aurora PostgreSQL Logs: Part 1, discussed the importance of PostgreSQL logs and how to tune various parameters to capture more database activity details. PostgreSQL logs provide useful information when troubleshooting database issues. This post focuses on different methods to access PostgreSQL logs.
The PostgreSQL logs are generated and stored on each of the instances in the cluster. There are multiple ways to access these log files. The following sections provide some of these methods.
Accessing via the AWS Management Console
The most direct method to access the PostgreSQL log files is through the AWS Management Console. Complete the following steps:
- Open the Amazon RDS
- Choose your RDS/Aurora instance.
- Choose Logs and events.
- In Logs, choose the required log file.
- Choose either View, Watch, or Download.
The following screenshot is a preview of the Logs section.
This is the most basic method to view or download the log files.
Publishing log files to CloudWatch Logs
When the logs are in CloudWatch Logs, you can create alarms and perform real-time analysis. For example, if you set
ddl, you can set up an alarm to alert whenever a DDL statement is executed.
CloudWatch Logs provide searching and filtering options that can help with the log analysis. For more information, see Filter and Pattern Syntax.
For example, the following screenshot shows all Aurora PostgreSQL log messages generated in the last 5 minutes that contain the words
Downloading log files using AWS CLI
You can download RDS and Aurora PostgreSQL log files using the AWS CLI. Before running any AWS CLI command, either configure your IAM user credentials or use an Amazon EC2 instance with the appropriate IAM role assigned. For more information, see Installing the AWS CLI and Configuring the AWS CLI.
The IAM user or role used for downloading the log files should have the permission to execute operations rds:DescribeDBLogFiles and rds:DownloadDBLogFilePortion. For more information, see Manage IAM Permissions.
The following AWS CLI command lists the available log files for the given instance:
You can choose the required file name from this list and download the required files using the following command:
You can automate the steps to download the log files generated during a given time window using a script. For a sample script, see get_rds_log_files.sh on the GitHub repo. This script assumes that you already ran
aws configure to set up the AWS CLI credentials, or you are running from an EC2 instance with a role assigned that allows downloading PostgreSQL log files. The script expects the RDS or Aurora PostgreSQL instance name and the date range as an argument, and it downloads the log files generated for that instance during the given time window.
By default, this script downloads the default standard format (
stderr) files. If you are using CSV format log files, you can download them by using the “
-c 1” argument.
If you don’t provide a date range, this script downloads all logs generated on the current day.
The following is the help output of the script:
The following is a sample output of the script from an EC2 jump server:
You need to download the log files to analyze them using some custom scripts or PostgreSQL log file analysis tools such as pgBadger.
Using log_fdw to view log data via a foreign table
To access PostgreSQL logs using the SQL interface, use the
log_fdw extension. It creates a foreign table on top of the log file and makes it available for SQL queries. The foreign table provides a mechanism to access data that resides outside this PostgreSQL server. With a few steps, you can link to the required log file and start a troubleshooting or monitoring activity. For more information, see Foreign Data in the PostgreSQL documentation.
The CSV format is useful when using the
log_fdw extension. The foreign table created on
stderr log files contains a single column with log event data. For the CSV format log file, the foreign table has multiple columns, which makes analysis easy.
The following sections provide details about how to use this extension, and a custom function to load available log files with a single function call.
Basic usage steps
To configure and use
log_fdw, complete the following steps:
- Connect to the PostgreSQL database using any client, such as
- Create the
log_fdwextension using the following SQL statement:
- Create the foreign server using log_fdw with the following SQL statement:
- Get the list of the most recent log files with the following SQL statement:
- Load any of these files as a foreign table using the following SQL statement:
- After the preceding statement is successful, the foreign table is ready for your analysis. Use the following code:
- When you are done using the foreign table, drop it using the following SQL statement:
Load log files as a single table
The preceding steps are useful if you are interested in a small number of log files; you can create foreign tables for your analysis easily. However, if you need to look at all the available log files, loading each file individually can be time-consuming.
To address this problem, use the custom function
load_postgres_log_files() available in the GitHub repository. This function simplifies the process for loading the available log files as a single table. Complete the following steps:
- Download the SQL file for function
load_postgres_log_filesfrom the GitHub repository.
- Create the function
- Run the following statement to load the log files:
- Start looking at the logs with the following code:
- If necessary, repeat Step 2 to pick up new log files that were generated since the last load.
- When you are done with the log analysis, drop the tables with the following code:
The following are the key features of this function:
- By default, a table named
postgres_logsis created in the schema logs.
- You can change the schema name and table name via arguments.
- Existing tables are dropped.
- If the schema
logsdoes not exist, it is created.
- Each log file is loaded as a foreign table and made the child of table
- By default, the function looks for CSV file format logs. If it can’t find CSV files, it falls back to the standard error format log file. To change this behavior, set the argument
- The following daily-, hourly-, and minute-based log file name formats are supported for CSV and non-CSV output files:
- Using CSV format adds a check constraint to the child table created for each log file.
To use your own schema and table names, run the command in Step 3 as follows:
If you need the latest logs to always be available in this table without manual reload, you can write a cron job or scheduled Lambda function to call this function every hour. For more information, see Schedule Expressions Using Rate or Cron.
RDS and Aurora PostgreSQL logs provide useful information about database activity, which can help in performance tuning and troubleshooting. This post explained how you can use the logs; you can now choose the methods that suit your needs.
If you have any questions or comments, please post your thoughts in the comments section.
About the Author
Yaser Raja is a Senior Consultant with Professional Services team at Amazon Web Services. He works with customers to build scalable, highly available and secure solutions in AWS cloud. His focus area is homogenous and heterogeneous migrations of on-premise databases to AWS RDS and Aurora PostgreSQL.