PgHero Logs
Slow query log parser for Postgres
Total Avg Count Query
(min) (ms)
20 4381 283 SELECT DISTINCT "orders"."id" AS t0_r0, "orders"
7 3574 120 SELECT "visits".* FROM "visits" WHERE ("visits".
4 12621 20 SELECT DISTINCT "order_deliveries"."id" AS t0_r0
Install
gem install pghero_logs
It can take a few minutes to compile the query parser :clock2:
Tell Postgres to log slow queries in postgresql.conf
log_min_duration_statement = 20 # ms
Analyze the logs
cat /usr/local/var/log/postgres.log | pghero_logs
Amazon RDS
First, download the logs. Create an IAM user with the policy below
{
"Version": "2012-10-17",
"Statement": [
{
"Action": [
"rds:DescribeDBLogFiles",
"rds:DownloadDBLogFilePortion"
],
"Effect": "Allow",
"Resource": "*"
}
]
}
And run
aws configure
pghero_logs download <instance-id>
Once logs are downloaded, run
cat postgresql.log* | pghero_logs
To analyze with PgBadger, install
brew install pgbadger
And run
pgbadger --prefix "%t:%r:%u@%d:[%p]:" --outfile pgbadger.html postgresql.log*
open pgbadger.html
Thanks to RDS PgBadger for the prefix.