Load AWS metrics to PostgreSQL
AWS provide lot of metrics about resource utilization of ec2/rds instance which is useful to analyze the system utilization. There are different ways to read the metrics like aws command line or using python boto3 library etc.
To know the resource utilization trend of the instance we need to maintain this metrics for longer term. As it is costly to store this metrics in Cloudwatch for longer term it would be good to copy the data to some database which will help to find the trend of resource utilization and useful for capacity planning.
Below is a simple way to load aws metrics of rds instance to PostgreSQL. PostgreSQL has bulk loading command COPY which can be used to load metrics directly from other programs. Below is the snippet of code useful to load metrics from aws to PostgreSQL.
Here I am creating three tables for read iops, write iops and cpu utilization and loading the respective metrics into the tables.
!/bin/bash
export PGPASSWORD='**********'
psql -h 10.12.34.56 -U viswamitra -d postgres -c "create table if not exists write_iops(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"
psql -h 10.12.34.56 -U viswamitra -d postgres -c "create table if not exists read_iops(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"
psql -h 10.12.34.56 -U viswamitra -d postgres -c "create table if not exists cpu_utilization(datapoint varchar,value decimal,gather_time timestamp,unit varchar)" -c "\copy sandbox_write from PROGRAM 'aws cloudwatch get-metric-statistics --metric-name WriteIOPS --start-time date +%Y-%m-%d --date="$1 days ago"T00:00:00Z --end-time date +%Y-%m-%d --date="$1 days ago"T23:59:59Z --period 60 --namespace AWS/RDS --statistics Average --dimensions Name=DBInstanceIdentifier,Value=testinstance1 --output text | tail -n +2';"
Viswamitra's blog 