Setting up pgblame with self-hosted Postgres

For Postgres you run yourself — VM, bare-metal, Docker, Kubernetes, or a Postgres operator. You have full superuser access and can edit postgresql.conf directly.

1. Enable pg_stat_statements

Edit your Postgres config:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

# Optional but recommended:
pg_stat_statements.track = all          # capture nested statements too
pg_stat_statements.max = 10000          # default; raise if you have a wide query mix

For Docker: -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all on the postgres command line.

Restart Postgres, then create the extension:

-- Connect as a superuser
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Verify with SELECT 1 FROM pg_stat_statements LIMIT 1; — it should return without error.

2. Create a read-only role

CREATE ROLE pgblame_reader WITH LOGIN PASSWORD '<strong-password>';
GRANT pg_read_all_stats TO pgblame_reader;
GRANT CONNECT ON DATABASE postgres TO pgblame_reader;

-- Optional: belt-and-braces, lock it out of everything else
REVOKE ALL ON SCHEMA public FROM pgblame_reader;

pg_read_all_stats is a standard built-in role since Postgres 10. The agent needs nothing more.

3. Authentication and network

Make sure pg_hba.conf allows pgblame_reader to connect from wherever the agent runs:

# pg_hba.conf
# TYPE  DATABASE        USER                ADDRESS                 METHOD
host    postgres        pgblame_reader      <agent-cidr>            scram-sha-256

Reload Postgres after editing. If the agent runs on the same host as Postgres, use host.docker.internal (Docker Desktop) or the host's bridge IP from inside the agent container.

4. Get a token from pgblame

Sign up, create a project — the wizard tests the connection and issues a one-time token like pgb_xxxxxxxx.

5. Run the agent

Vanilla Docker:

docker run -d \
  --name pgblame-agent \
  --restart unless-stopped \
  -e PGBLAME_DATABASE_URL="postgresql://pgblame_reader:<password>@<host>:5432/postgres?sslmode=disable" \
  -e PGBLAME_TOKEN="pgb_xxxxxxxx" \
  -v pgblame-data:/var/lib/pgblame \
  ghcr.io/liberzon/pgblame-agent:latest

Set sslmode=require if your Postgres has TLS configured; sslmode=disable is fine on a private network you control.

Docker Compose

services:
  pgblame-agent:
    image: ghcr.io/liberzon/pgblame-agent:latest
    restart: unless-stopped
    environment:
      PGBLAME_DATABASE_URL: postgresql://pgblame_reader:${PGBLAME_DB_PASS}@postgres:5432/postgres
      PGBLAME_TOKEN: ${PGBLAME_TOKEN}
    volumes:
      - pgblame-data:/var/lib/pgblame
    networks:
      - postgres-net  # whichever network reaches your DB

volumes:
  pgblame-data:

Kubernetes

apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgblame-agent
spec:
  replicas: 1   # single-writer; do not scale
  selector:
    matchLabels:
      app: pgblame-agent
  template:
    metadata:
      labels:
        app: pgblame-agent
    spec:
      containers:
        - name: agent
          image: ghcr.io/liberzon/pgblame-agent:latest
          env:
            - name: PGBLAME_DATABASE_URL
              valueFrom:
                secretKeyRef: { name: pgblame, key: db-url }
            - name: PGBLAME_TOKEN
              valueFrom:
                secretKeyRef: { name: pgblame, key: token }
          volumeMounts:
            - { name: data, mountPath: /var/lib/pgblame }
      volumes:
        - name: data
          persistentVolumeClaim:
            claimName: pgblame-agent

Run a single replica per database. The agent stores deltas locally between ticks; running two would double-count.

6. Wire deploy webhooks

• Vercel webhook setup
• Railway webhook setup
• curl one-liner for any CI

Troubleshooting