PgHero Docker

Installation

PgHero is available on Docker Hub.

docker pull ankane/pghero

Start the dashboard:

docker run -ti -e DATABASE_URL=postgres://user:password@hostname:5432/dbname -p 8080:8080 ankane/pghero

Use URL-encoding for any special characters in the username or password. For databases on the host machine, use host.docker.internal as the hostname (on Linux, this requires --add-host=host.docker.internal:host-gateway).

Then visit http://localhost:8080.

Authentication

Add basic authentication with:

docker run -e PGHERO_USERNAME=link -e PGHERO_PASSWORD=hyrule ...

Or use a reverse proxy like OAuth2 Proxy, Amazon’s ALB Authentication, or Google’s Identity-Aware Proxy.

Query Stats

Query stats can be enabled from the dashboard. If you run into issues, view the guide.

Historical Query Stats

To track query stats over time, create a table to store them.

sql

CREATE TABLE pghero_queries (
    id bigserial PRIMARY KEY,
    query text
);
CREATE INDEX ON pghero_queries USING hash (query);

CREATE TABLE pghero_query_stats (
    id bigserial PRIMARY KEY,
    database text,
    "user" text,
    query_id bigint,
    query_hash bigint,
    total_time float,
    calls bigint,
    captured_at timestamp
);
CREATE INDEX ON pghero_query_stats (database, captured_at);

This table can be in the current database or another database. If another database, pass the PGHERO_STATS_DATABASE_URL environment variable with commands.

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.

Remove old stats with:

docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:clean_query_stats KEEP_DAYS=14

Historical Space Stats

To track space stats over time, create a table to store them.

sql

CREATE TABLE pghero_space_stats (
    id bigserial 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

Remove old stats with:

docker run -ti -e DATABASE_URL=... ankane/pghero bin/rake pghero:clean_space_stats KEEP_DAYS=90

System Stats

CPU usage, IOPS, and other stats are available for:

Amazon RDS
Google Cloud SQL

Amazon RDS

Set these variables:

AWS_ACCESS_KEY_ID=my-access-key
AWS_SECRET_ACCESS_KEY=my-secret
AWS_REGION=us-east-1
PGHERO_DB_INSTANCE_IDENTIFIER=my-instance

This requires the following IAM policy:

json

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "cloudwatch:GetMetricStatistics",
            "Resource": "*"
        }
    ]
}

Google Cloud SQL

Set these variables:

GOOGLE_APPLICATION_CREDENTIALS=path/to/credentials.json
PGHERO_GCP_DATABASE_ID=my-project:my-instance

This requires the Monitoring Viewer role.

Customization & Multiple Databases

Create a pghero.yml file with:

yml

databases:
  main:
    url: <%= ENV["DATABASE_URL"] %>

    # System stats
    # aws_db_instance_identifier: my-instance
    # gcp_database_id: my-project:my-instance

  # Add more databases
  # other:
  #   url: <%= ENV["OTHER_DATABASE_URL"] %>

# Minimum time for long running queries
# long_running_query_sec: 60

# Minimum average time for slow queries
# slow_query_ms: 20

# Minimum calls for slow queries
# slow_query_calls: 100

# Minimum connections for high connections warning
# total_connections_threshold: 500

# Explain functionality
# explain: true / false / analyze

# Statement timeout for explain
# explain_timeout_sec: 10

# Visualize URL for explain
# visualize_url: https://...

# Time zone
# time_zone: "Pacific Time (US & Canada)"

Create a Dockerfile with:

Dockerfile

FROM ankane/pghero

COPY pghero.yml /app/config/pghero.yml

And build your image:

docker build -t my-pghero .

Deployment

Health Checks

Use the /health endpoint for health checks. Status code 200 indicates healthy.

Kubernetes

If you are planning to run on Kubernetes with a config file, you don’t need to create a new image. You can make use of ConfigMaps to mount the config file. Create a ConfigMap like this:

yaml

---
apiVersion: v1
kind: ConfigMap
metadata:
  name: pghero
data:
  pghero.yml: |-
    databases:
      main:
        url: <%= ENV["DATABASE_URL"] %>

      # Add more databases
      # other:
      #   url: <%= ENV["OTHER_DATABASE_URL"] %>

    # Minimum time for long running queries
    # long_running_query_sec: 60

    # Minimum average time for slow queries
    # slow_query_ms: 20

    # Minimum calls for slow queries
    # slow_query_calls: 100

    # Minimum connections for high connections warning
    # total_connections_threshold: 500

    # Explain functionality
    # explain: true / false / analyze

    # Statement timeout for explain
    # explain_timeout_sec: 10

    # Visualize URL for explain
    # visualize_url: https://...

    # Time zone
    # time_zone: "Pacific Time (US & Canada)"

Then launch the pod with the following config:

yaml

---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pghero
  labels:
    app: pghero
spec:
  selector:
    matchLabels:
      app: pghero
  replicas: 1
  template:
    metadata:
      labels:
        app: pghero
    spec:
      containers:
      - name: pghero
        image: ankane/pghero
        imagePullPolicy: Always
        volumeMounts:
        - name: pghero-configmap
          mountPath: /app/config/pghero.yml
          readOnly: true
          subPath: pghero.yml
      volumes:
      - name: pghero-configmap
        configMap:
          defaultMode: 0644
          name: pghero

Permissions

We recommend setting up a dedicated user for PgHero.

Upgrading

4.0

If historical query stats are enabled, run:

sql

CREATE TABLE pghero_queries (
    id bigserial PRIMARY KEY,
    query text
);
CREATE INDEX ON pghero_queries USING hash (query);

ALTER TABLE pghero_query_stats ADD COLUMN query_id bigint;

INSERT INTO pghero_queries (query)
    SELECT DISTINCT query FROM pghero_query_stats WHERE query IS NOT NULL;

UPDATE pghero_query_stats SET query_id = pghero_queries.id, query = NULL
    FROM pghero_queries WHERE pghero_queries.query = pghero_query_stats.query;

ALTER TABLE pghero_query_stats DROP COLUMN query;

VACUUM (FULL, ANALYZE) pghero_query_stats;

Credits

Thanks to Brian Morton for the original Docker image.