Postgresql exporter in docker. Monitroing postgres with prometheus.
Create user for exporter in postgres
CREATE USER postgres_exporter PASSWORD 'kiokfhubhlundsfjbdsufdsf'; alter user postgres_exporter with password 'kiokfhubhlundsfjbdsufdsf'; ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_exporter,pg_catalog; CREATE SCHEMA postgres_exporter AUTHORIZATION postgres_exporter; CREATE VIEW postgres_exporter.pg_stat_activity AS SELECT * from pg_catalog.pg_stat_activity; GRANT SELECT ON postgres_exporter.pg_stat_activity TO postgres_exporter; CREATE VIEW postgres_exporter.pg_stat_replication AS SELECT * from pg_catalog.pg_stat_replication; GRANT SELECT ON postgres_exporter.pg_stat_replication TO postgres_exporter;
Build docker postgresql exporter.
FROM alpine:3.7
LABEL maintainer="a.v.galushko86@gmail.com"
ENV POSTGRES_EXPORTER 0.4.6
RUN set -xe \
&& apk add --update --no-cache \
ca-certificates \
curl \
tar \
wget \
supervisor
RUN mkdir -p /opt \
&& mkdir -p /etc/supervisor/conf.d \
&& cd /opt \
&& mkdir -p /etc/prometheus/exporters/postgres/ \
&& wget https://github.com/wrouesnel/postgres_exporter/releases/download/v${POSTGRES_EXPORTER}/postgres_exporter_v${POSTGRES_EXPORTER}_linux-amd64.tar.gz \
&& tar -xvf postgres_exporter_v${POSTGRES_EXPORTER}_linux-amd64.tar.gz \
&& mv /opt/postgres_exporter_v${POSTGRES_EXPORTER}_linux-amd64/postgres_exporter /opt/postgres_exporter
COPY postgres-querys/ /etc/prometheus/exporters/postgres/
RUN echo 'Create supervisord conf' \
&& { \
echo '[supervisord]'; \
echo 'nodaemon=true'; \
echo 'environment = DATA_SOURCE_NAME="postgresql://postgres_exporter:kiokfhubhlundsfjbdsufdsf@postgres:5432/postgres?sslmode=disable"'; \
echo '[program:postgres_exporter]'; \
echo 'command=/opt/postgres_exporter --extend.query-path="/etc/prometheus/exporters/postgres/postgres-queries.yml" --web.listen-address=":9187"'; \
echo 'stdout_logfile=/dev/stdout'; \
echo 'stdout_logfile_maxbytes=0'; \
echo 'stderr_logfile=/dev/stdout'; \
echo 'redirect_stderr=true'; \
echo 'stderr_logfile_maxbytes=0'; \
echo 'exitcodes=0'; \
echo '[include]'; \
echo 'files = /etc/supervisor/conf.d/*.conf'; \
} | tee /etc/supervisor/conf.d/supervisord.conf
WORKDIR /
EXPOSE 9187
ENTRYPOINT /usr/bin/supervisord -c /etc/supervisor/conf.d/supervisord.conf
And add docker exporter to you docker network with postgresql. As example with docker-swarm
exporter-postgres:
image: "registry-git.it-uk.ru:4567/deploy/docker-stack/exporter_postgres:latest"
extra_hosts:
- "postgres:postgres_ip"
ports:
- 9187:9187
environment:
- "TZ=Asia/Yekaterinburg"
networks:
asujs-network:
aliases:
- exporter-postgres
deploy:
replicas: 1
resources:
limits:
cpus: '1'
memory: 256MB
restart_policy:
condition: on-failure
delay: 5s
max_attempts: 3
window: 20s
update_config:
parallelism: 1
delay: 10s
failure_action: continue
monitor: 1s
max_failure_ratio: 0.3
placement:
constraints: [node.role == manager]
networks:
asujs-network:
driver: overlay
And create config in prometheus server.
- targets:
- "10.16.0.3:9187"
labels:
job: dbexporter
env: prod
Import grafana dashboard.
{
"annotations": {
"list": [
{
"builtIn": 1,
"datasource": "-- Grafana --",
"enable": true,
"hide": true,
"iconColor": "rgba(0, 211, 255, 1)",
"name": "Annotations & Alerts",
"type": "dashboard"
}
]
},
"description": "Performance metrics for Postgres",
"editable": true,
"gnetId": 3300,
"graphTooltip": 0,
"id": 36,
"iteration": 1531902887193,
"links": [],
"panels": [
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 0,
"y": 0
},
"id": 17,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "pg_replication_lag{instance=\"$host\"}",
"format": "time_series",
"intervalFactor": 2,
"metric": "pg_replication_lag",
"refId": "A",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Replication Lag",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "s",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": false
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"editable": true,
"error": false,
"fill": 1,
"grid": {},
"gridPos": {
"h": 7,
"w": 8,
"x": 8,
"y": 0
},
"id": 3,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 2,
"links": [],
"nullPointMode": "connected",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"alias": "conflicts",
"dsType": "prometheus",
"expr": "sum(rate(pg_stat_database_deadlocks{datname=~\"$db\",instance=\"$host\"}[5m]))",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "deadlocks",
"measurement": "postgresql",
"policy": "default",
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"conflicts"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
},
{
"alias": "deadlocks",
"dsType": "prometheus",
"expr": "sum(rate(pg_stat_database_conflicts{datname=~\"$db\",instance=\"$host\"}[5m]))",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "conflicts",
"measurement": "postgresql",
"policy": "default",
"refId": "B",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"deadlocks"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Repl. Conflicts/Deadlocks",
"tooltip": {
"msResolution": false,
"shared": true,
"sort": 0,
"value_type": "cumulative"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": 0,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 16,
"y": 0
},
"id": 23,
"legend": {
"alignAsTable": true,
"avg": true,
"current": true,
"hideZero": true,
"max": true,
"min": false,
"show": true,
"total": false,
"values": true
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "pg_runtime_variable_max_connections{instance=\"$host\"}",
"interval": "",
"intervalFactor": 2,
"legendFormat": "max_connections",
"metric": "pg_runtime_variable_max_connections",
"refId": "A",
"step": 2
},
{
"expr": "sum(pg_stat_activity_count{instance=\"$host\"})",
"intervalFactor": 2,
"legendFormat": "total connections",
"metric": "pg",
"refId": "B",
"step": 2
},
{
"expr": "sum(pg_stat_activity_count{instance=\"$host\",state=\"active\"})",
"intervalFactor": 2,
"legendFormat": "active sessions",
"refId": "D",
"step": 2
},
{
"expr": "sum(pg_stat_activity_count{instance=\"$host\",state=\"idle in transaction\"})",
"intervalFactor": 2,
"legendFormat": "idle in transaction sessions",
"refId": "C",
"step": 2
}
],
"thresholds": [
{
"colorMode": "critical",
"fill": true,
"line": true,
"op": "gt"
}
],
"timeFrom": null,
"timeShift": null,
"title": "PG Connections",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"decimals": 1,
"editable": true,
"error": false,
"fill": 1,
"grid": {},
"gridPos": {
"h": 5,
"w": 8,
"x": 0,
"y": 7
},
"id": 2,
"legend": {
"alignAsTable": true,
"avg": true,
"current": true,
"hideZero": true,
"max": true,
"min": false,
"rightSide": false,
"show": true,
"total": false,
"values": true
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "connected",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"alias": "Buffers Allocated",
"dsType": "prometheus",
"expr": "irate(pg_stat_bgwriter_buffers_alloc{instance='$host'}[5m])",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "buffers_alloc",
"measurement": "postgresql",
"policy": "default",
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffers_alloc"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
},
{
"alias": "Buffers Allocated",
"dsType": "prometheus",
"expr": "irate(pg_stat_bgwriter_buffers_backend_fsync{instance='$host'}[5m])",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "buffers_backend_fsync",
"measurement": "postgresql",
"policy": "default",
"refId": "B",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffers_alloc"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
},
{
"alias": "Buffers Allocated",
"dsType": "prometheus",
"expr": "irate(pg_stat_bgwriter_buffers_backend{instance='$host'}[5m])",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "buffers_backend",
"measurement": "postgresql",
"policy": "default",
"refId": "C",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffers_alloc"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
},
{
"alias": "Buffers Allocated",
"dsType": "prometheus",
"expr": "irate(pg_stat_bgwriter_buffers_clean{instance='$host'}[5m])",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "buffers_clean",
"measurement": "postgresql",
"policy": "default",
"refId": "D",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffers_alloc"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
},
{
"alias": "Buffers Allocated",
"dsType": "prometheus",
"expr": "irate(pg_stat_bgwriter_buffers_checkpoint{instance='$host'}[5m])",
"groupBy": [
{
"params": [
"$interval"
],
"type": "time"
},
{
"params": [
"null"
],
"type": "fill"
}
],
"intervalFactor": 2,
"legendFormat": "buffers_checkpoint",
"measurement": "postgresql",
"policy": "default",
"refId": "E",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"buffers_alloc"
],
"type": "field"
},
{
"params": [],
"type": "mean"
},
{
"params": [],
"type": "difference"
}
]
],
"step": 2,
"tags": [
{
"key": "host",
"operator": "=~",
"value": "/^$host$/"
}
]
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Buffers",
"tooltip": {
"msResolution": false,
"shared": true,
"sort": 0,
"value_type": "cumulative"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": 0,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 5,
"w": 8,
"x": 8,
"y": 7
},
"id": 18,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "max(pg_stat_activity_max_tx_duration{instance='$host'})",
"intervalFactor": 2,
"legendFormat": "max_tx_duration",
"metric": "pg_stat_activity_max_tx_duration",
"refId": "A",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Longest Transaction, s",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "s",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "s",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"editable": true,
"error": false,
"fill": 1,
"grid": {},
"gridPos": {
"h": 5,
"w": 8,
"x": 16,
"y": 7
},
"id": 14,
"legend": {
"avg": true,
"current": true,
"max": false,
"min": true,
"show": true,
"total": false,
"values": true
},
"lines": true,
"linewidth": 2,
"links": [],
"minSpan": 4,
"nullPointMode": "connected",
"percentage": true,
"pointradius": 1,
"points": false,
"renderer": "flot",
"repeat": null,
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "round(100*sum(pg_stat_database_blks_hit{datname=~\"$db\",instance=\"$host\"}) / (sum(pg_stat_database_blks_hit{datname=~\"$db\",instance=\"$host\"}) + sum(pg_stat_database_blks_read{datname=~\"$db\",instance=\"$host\"})),0.1)",
"hide": false,
"interval": "",
"intervalFactor": 2,
"legendFormat": "pg cache hit rate",
"refId": "A",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "PG buffer cache hit rate",
"tooltip": {
"msResolution": false,
"shared": true,
"sort": 0,
"value_type": "cumulative"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "percent",
"label": null,
"logBase": 1,
"max": "100",
"min": null,
"show": true
},
{
"format": "percent",
"label": "",
"logBase": 1,
"max": "100",
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 0,
"y": 12
},
"id": 24,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "sum(rate(pg_stat_database_tup_returned{instance=\"$host\"}[1m]))",
"hide": false,
"intervalFactor": 2,
"legendFormat": "tup_returned (seq scan)",
"refId": "A",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_tup_fetched{instance=\"$host\"}[1m]))",
"hide": false,
"intervalFactor": 2,
"legendFormat": "tup_fetched (idx access)",
"refId": "B",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Read tuple activity",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 8,
"y": 12
},
"id": 25,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "sum(rate(pg_stat_database_tup_inserted{instance=\"$host\"}[1m]))",
"format": "time_series",
"intervalFactor": 2,
"legendFormat": "tup_inserted",
"metric": "pg_stat_database_tup",
"refId": "C",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_tup_updated{instance=\"$host\"}[1m]))",
"format": "time_series",
"intervalFactor": 2,
"legendFormat": "tup_updated",
"metric": "pg_stat_database_tup",
"refId": "D",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_tup_deleted{instance=\"$host\"}[1m]))",
"format": "time_series",
"intervalFactor": 2,
"legendFormat": "tup_deleted",
"metric": "pg_stat_database_tup",
"refId": "E",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Tuples changed per minute",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 16,
"y": 12
},
"id": 26,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "sum(rate(pg_stat_database_xact_commit{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "commits/s",
"metric": "pg_stat_database_xact_commit",
"refId": "A",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_xact_rollback{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "rollbacks/s",
"metric": "pg_stat_database_xact_commit",
"refId": "B",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Transactions",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"description": "Based on pg_stat_database_conflicts view",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 0,
"y": 19
},
"id": 28,
"legend": {
"avg": false,
"current": false,
"max": false,
"min": false,
"show": true,
"total": false,
"values": false
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "sum(rate(pg_stat_database_conflicts_confl_bufferpin{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "confl_bufferpin",
"metric": "pg_stat_database_conflicts_confl_bufferpin",
"refId": "A",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_conflicts_confl_deadlock{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "confl_deadlock",
"metric": "pg_stat_database_conflicts_confl_bufferpin",
"refId": "B",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_conflicts_confl_lock{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "confl_lock",
"metric": "pg_stat_database_conflicts_confl_bufferpin",
"refId": "C",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_conflicts_confl_snapshot{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "confl_snapshot",
"metric": "pg_stat_database_conflicts_confl_bufferpin",
"refId": "D",
"step": 2
},
{
"expr": "sum(rate(pg_stat_database_conflicts_confl_tablespace{instance=\"$host\"}[1m]))",
"intervalFactor": 2,
"legendFormat": "confl_tablespace",
"metric": "pg_stat_database_conflicts_confl_bufferpin",
"refId": "E",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Conflicts",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "short",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
},
{
"aliasColors": {},
"bars": false,
"dashLength": 10,
"dashes": false,
"datasource": "prometheus",
"fill": 1,
"gridPos": {
"h": 7,
"w": 8,
"x": 8,
"y": 19
},
"id": 22,
"legend": {
"avg": true,
"current": false,
"hideEmpty": false,
"hideZero": false,
"max": true,
"min": false,
"show": true,
"total": false,
"values": true
},
"lines": true,
"linewidth": 1,
"links": [],
"nullPointMode": "null",
"percentage": false,
"pointradius": 5,
"points": false,
"renderer": "flot",
"seriesOverrides": [],
"spaceLength": 10,
"stack": false,
"steppedLine": false,
"targets": [
{
"expr": "rate(pg_stat_bgwriter_checkpoint_sync_time{instance='$host'}[1m])",
"hide": false,
"intervalFactor": 2,
"legendFormat": "checkpoint_sync_time, s/min",
"metric": "pg_stat_bgwriter_checkpoint_sync_time",
"refId": "A",
"step": 2
},
{
"expr": "rate(pg_stat_bgwriter_checkpoint_write_time{instance='$host'}[1m])",
"hide": false,
"intervalFactor": 2,
"legendFormat": "checkpoint_write_time, s/min",
"metric": "pg_stat_bgwriter_checkpoint_write_time",
"refId": "B",
"step": 2
}
],
"thresholds": [],
"timeFrom": null,
"timeShift": null,
"title": "Checkpoint stats",
"tooltip": {
"shared": true,
"sort": 0,
"value_type": "individual"
},
"type": "graph",
"xaxis": {
"buckets": null,
"mode": "time",
"name": null,
"show": true,
"values": []
},
"yaxes": [
{
"format": "ms",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
},
{
"format": "ms",
"label": null,
"logBase": 1,
"max": null,
"min": null,
"show": true
}
],
"yaxis": {
"align": false,
"alignLevel": null
}
}
],
"refresh": false,
"schemaVersion": 16,
"style": "dark",
"tags": [
"postgres"
],
"templating": {
"list": [
{
"allValue": null,
"current": {
"text": "10.16.0.3:9187",
"value": "10.16.0.3:9187"
},
"datasource": "prometheus",
"hide": 0,
"includeAll": false,
"label": null,
"multi": false,
"name": "host",
"options": [],
"query": "label_values(up{job=~\"dbexporter\",instance!~\".*.voleon.net:.*\"},instance)",
"refresh": 1,
"regex": "",
"sort": 1,
"tagValuesQuery": "",
"tags": [],
"tagsQuery": "",
"type": "query",
"useTags": false
},
{
"allValue": ".*",
"current": {
"text": "All",
"value": "$__all"
},
"datasource": "prometheus",
"hide": 0,
"includeAll": true,
"label": "db",
"multi": false,
"name": "db",
"options": [],
"query": "label_values(pg_stat_database_tup_fetched{job=~\"dbexporter\",instance=\"$host\",datname!~\"template.*|postgres\"},datname)",
"refresh": 1,
"regex": "",
"sort": 0,
"tagValuesQuery": "",
"tags": [],
"tagsQuery": "",
"type": "query",
"useTags": false
}
]
},
"time": {
"from": "now-15m",
"to": "now"
},
"timepicker": {
"refresh_intervals": [
"5s",
"10s",
"30s",
"1m",
"5m",
"15m",
"30m",
"1h",
"2h",
"1d"
],
"time_options": [
"5m",
"15m",
"1h",
"6h",
"12h",
"24h",
"2d",
"7d",
"30d"
]
},
"timezone": "browser",
"title": "Postgres Node3300",
"uid": "I_4r8UOik",
"version": 2
}