Slow query log parser for Postgres
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
bin
lib
.gitignore
Gemfile
LICENSE.txt
README.md Not code Aug 28, 2017
Rakefile First commit Sep 15, 2014
pghero_logs.gemspec

README.md

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 10 minutes or more to compile the query parser 🕑

Tell Postgres to log slow queries in postgresql.conf

log_min_duration_statement = 20 # ms

Analyze the logs

cat /usr/local/var/postgres/server.log | pghero_logs

Amazon RDS

First, download the logs. Create an IAM user with the policy below

{
  "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.