How to Use pgbadger for AWS RDS Logs

This tutorial describe step-by-step how to get all AWS RDS for PostgreSQL logs and analyze the logs. We will use AWS CLI v1 and pgbadger in this exercise.

Overview  

In this tutorial we learn how to get all AWS RDS For PostgreSQL logs and analyze the logs using pgbadger.

Prerequisites  

This tutorial assume you already have the following tools installed.

• AWS CLI version 1
• pgbadgepgbadger
• Required AWS credential and IAM permission to download AWS RDS Log file.
• AWS RDS for PostgreSQL not published to CloudWatch Logs.

Step 1 – Get All AWS RDS For PostgreSQL Logs File Name  

To get list of log file names from a RDS for PostgreSQL instance you can use command below. Change DB_INSTANCE_IDENTIFIER below with your RDS instance identifier.

aws rds describe-db-log-files \
 --db-instance-identifier DB_INSTANCE_IDENTIFIER \
  | grep LogFileName | cut -d\" -f 4 > logfilename.txt

The command above will put all log file names to file named logfilename.txt

Step 2 – Download All RDS For PostgreSQL Log Files  

We will loop through logfilename.txt file that created on previous step to download all the file name. Change DB_INSTANCE_IDENTIFIER below with your RDS instance identifier.

The command aws rds download-db-log-file-portion is paginated. The key to the command below is --starting-token 0 that will download file from the beginning and try to download all of the contents of the file page by page.

There will be white space on the file when aws cli download page by page but this will not impact log analysis.

The output of the command below is file named rds-postgres.log

for logfilename in `cat logfilename.txt`; do \
aws rds download-db-log-file-portion \
    --db-instance-identifier DB_INSTANCE_IDENTIFIER \
    --log-file-name $logfilename \
    --starting-token 0 \
    --output text >> rds-postgres.log \
; done

This might take some time depend on how many logs you retain on your RDS for PostgreSQL instance, what log options you enable and how verbose the logging level.

Step 3 – Analyze RDS for PostgreSQL using pgbadger  

We can use command below to let pgbadger analyze the logs and provide report in html form for us.

pgbadger rds-postgres.log

pgbadger by default will create report in html formate called output.html in the current directory.

Summary  

In this tutorial we learn how to get all AWS for PostgreSQL logs using AWS CLI and analyze the downloaded logs using pgbadger. pgbadger is an excellent tools to analyze your RDS for PostgreSQL logs.