PgHero is available as a Docker image.
docker run -ti -e DATABASE_URL=postgres://user:password@hostname:5432/dbname -p 8080:8080 ankane/pghero
And visit http://localhost:8080.
Query stats can be enabled from the dashboard. If you run into issues, view the guide.
To track query stats over time, create a table to store them.
CREATE TABLE "pghero_query_stats" (
"id" serial primary key,
"database" text,
"user" text,
"query" text,
"query_hash" bigint,
"total_time" float,
"calls" bigint,
"captured_at" timestamp
);
CREATE INDEX ON "pghero_query_stats" ("database", "captured_at");
Schedule the task below to run every 5 minutes.
docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:capture_query_stats
After this, a time range slider will appear on the Queries tab.
To track space stats over time, create a table to store them.
CREATE TABLE "pghero_space_stats" (
"id" serial primary key,
"database" text,
"schema" text,
"relation" text,
"size" bigint,
"captured_at" timestamp
);
CREATE INDEX ON "pghero_space_stats" ("database", "captured_at");
Schedule the task below to run once a day.
docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:capture_space_stats
Create a file at /app/config/pghero.yml
with:
databases:
primary:
url: postgres://...
replica:
url: postgres://...
We recommend setting up a dedicated user for PgHero.
And basic authentication with:
docker run -e PGHERO_USERNAME=link -e PGHERO_PASSWORD=hyrule ...
Thanks to Brian Morton for the original Docker image.