Pg-agent can gather metrics from Postgres servers starting from version 9.6.
It requires a database user with the assigned pg_monitor role.
You will also need to enable the pg_stat_statement extension.
create role <USER> with login password '<PASSWORD>';
grant pg_monitor to <USER>
create extension pg_stat_statements;
Then, run the agent:
docker run --detach --name coroot-pg-agent \
--env DSN="postgresql://<USER>:<PASSWORD>@<HOST>:5432/postgres?connect_timeout=1&statement_timeout=30000" \
ghcr.io/coroot/coroot-pg-agent
Flags:
usage: coroot-pg-agent [<flags>] <dsn>
Flags:
--listen="0.0.0.0:80" Listen address (env: LISTEN) - "<ip>:<port>" or ":<port>".
--scrape-interval=15s How often to snapshot system views (env: PG_SCRAPE_INTERVAL).
--version Show application version.
Args:
<dsn> Data source name (env: DSN) - "postgresql://<user>:<password>@<host>:5432/postgres?connect_timeout=1&statement_timeout=30000".
Zalando Operator for Postgres
If you run Postgres in a Kubernetes cluster, you can deploy pg-agent as a sidecar container.
Here is an example of the sidecars spec section
for the Postgres Operator by Zalando:
...
podAnnotations:
prometheus.io/scrape: 'true'
prometheus.io/port: '80'
sidecars:
- name: pg-agent
image: "ghcr.io/coroot/coroot-pg-agent:latest"
ports:
- containerPort: 80
protocol: TCP
resources:
requests:
cpu: 100m
memory: 100Mi
env:
- name: PGUSER
valueFrom:
secretKeyRef:
name: postgres.product-pg.credentials
key: username
- name: PGPASSWORD
valueFrom:
secretKeyRef:
name: postgres.product-pg.credentials
key: password
- name: DSN
value: postgresql://$(PGUSER):$(PGPASSWORD)@127.0.0.1:5432/postgres