report_pg_stat_statements.sh

report_pg_stat_statements.sh

#!/bin/bash

# variables
database="database"
to="email@email.com"
cc="email2@rmail.com"

# export top 100 statements to temporary file
psql -U postgres $database -c "copy (select query,min_time,mean_time,max_time,total_time,calls from pg_stat_statements where calls>100 and mean_time > 30 and min_time >10order by mean_time desc limit 100) to '/tmp/pg_stat_statements_top100.csv' delimiter ';'"

# format csv and email to receivers
cat /tmp/pg_stat_statements_top100.csv | awk -F ';' '{printf("query: %s \n   min_time: %s \n   mean_time: %s \n   max_time: %s \n   total_time: %s \n   calls: %s \n\n",$1,$2,$3,$4,$5,$6)}' | tr -d \\r |  mail -B -s "Postgres tracking execution statistics on $(hostname) for $(date +%F)" -c $cc $to

# clean up & reset stats
rm /tmp/pg_stat_statements_top100.csv
psql -U postgres catalog_microservice -c "select pg_stat_statements_reset();"