Build psql report RDS using pgbadger

export_log.sh

# sh export_log.sh db_identifier

# If any of this commands fail, stop script.
set -e

db_identifier=$1
date=$(date -v-7H +'%Y-%m-%d-%H')
log_file=~/Desktop/postgresql.log.$date
report_file=~/Desktop/out.$date.html
page_size=100000

aws rds download-db-log-file-portion --db-instance-identifier $db_identifier --log-file-name error/postgresql.log.$date --page-size $page_size --output text > $log_file

~/Downloads/pgbadger-master/pgbadger -f rds $log_file -o $report_file -p '%t:%r:%u@%d:[%p]:' -f stderr -j 8

echo $report_file

Filter write queries

cat ~/Desktop/postgresql.log.2021-09-20-14 | grep -A 1 "INSERT\|DELETE\|UPDATE" > ~/Desktop/postgresql.log.2021-09-20-14.write

DATE=2023-11-06-07
PROFILE=sample
DB_INSTANCE_IDENTIFIER=sample
LOG_FILE_NAME=error/postgresql.log.$DATE
LOG_FILE=~/Desktop/postgresql.log.$DATE

aws rds download-db-log-file-portion \
    --profile $PROFILE \
    --db-instance-identifier $DB_INSTANCE_IDENTIFIER \
    --log-file-name $LOG_FILE_NAME \
    --starting-token 0 \
    --output text > $LOG_FILE