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

Installation

Run:

gem install pghero_logs

It can take a few minutes to compile the query parser 🕑

Getting Started

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.

History

View the changelog

Contributing

Everyone is encouraged to help improve this project. Here are a few ways you can help:

Report bugs
Fix bugs and submit pull requests
Write, clarify, or fix documentation
Suggest or add new features

To get started with development:

git clone https://github.com/ankane/pghero_logs.git
cd pghero_logs
bundle install