Postgres Dashboard to Grafonnet

February 25, 2023

Update: While writing this out I realized the process really needed to be scripted to maintain sanity. Soon I discovered Grafana is actively building a tool to convert Grafana JSON into Grafonnet. I decided to post these unedited notes, in case they’re helpful in the meantime.

Converting a Grafana dashboard to use grafonnet

Grafana dashboards are extremely useful, although maintaining changes can be a pain when dealing only with JSON blobs. In this article I explain how I converted the Postgres Overview dashboard to use grafonnet-lib.

First we must clone the git repo and change to the postgres_mixin directory:

$ git clone https://github.com/prometheus-community/postgres_exporter
$ cd postgres_exporter/postgres_mixin

Next we create a new file postgres-overview.jsonnet for our grafonnet dashboard:

$ echo <<EOF > postgres-overview.jsonnet

local grafana = import 'grafonnet/grafana.libsonnet';
local dashboard = grafana.dashboard;
local template = grafana.template;
local row = grafana.row;
local panel = grafana.panel;
local prometheus = grafana.prometheus;
local graphPanel = grafana.graphPanel;

grafana.dashboard.new(
  title='Postgres Overview',
)
EOF

Now we’re ready to start populating attributes from the legacy dashboard. We begin by listing top-level dashboard attributes:

$ jq '. | del(.targets, .templating, .panels, .timepicker)' \
  dashboards/postgres-overview.json

{
  "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": 455,
  "graphTooltip": 0,
  "id": 1,
  "iteration": 1603191461722,
  "links": [],
  "refresh": false,
  "schemaVersion": 26,
  "style": "dark",
  "tags": [
    "postgres"
  ],
  "time": {
    "from": "now-1h",
    "to": "now"
  },
  "timezone": "browser",
  "title": "Postgres Overview",
  "uid": "wGgaPlciz",
  "version": 5
}

Referring to dashboard.libsonnet, we can set all top-level attributes with non-default values:

grafana.dashboard.new(
  title='Postgres Overview',
  tags=['postgres'],
  time_from='now-1h',
  description='Performance metrics for Postgres',
)

Next we will add templating (variables), panels, and targets.

Templating

The template library is used to add datasources and template variables.

Template source variable

First we view the datasource attributes:

$ jq '.templating.list[] | select(.type=="datasource")' \
  dashboards/postgres-overview.json

{
  "hide": 0,
  "includeAll": false,
  "label": "Data Source",
  "multi": false,
  "name": "datasource",
  "options": [],
  "query": "prometheus",
  "refresh": 1,
  "regex": "",
  "skipUrlSync": false,
  "type": "datasource"
}

To add this using grafonnet, we use addTemplate() and pass in a template.datasource object. This function is appended to the dashboard.new object we started earlier. Other than the mandatory attributes (w/o a default listed), only non-default values need to be specified.

grafana.dashboard.new(
  title='Postgres Overview',
  tags=['postgres'],
  time_from='now-1h',
  description='Performance metrics for Postgres',
)
.addTemplate(
  template.datasource(
    name='datasource',
    query='prometheus',
    current='Prometheus',
    label='Data Source',
  )
)

Generating the dashboard now should succeed, but it will be empty. First we need to update the dashboard library to use this new file, and we also need to install the grafonnet library using jsonnet-bundler:

$ sed -i \
  "s/import 'postgres-overview.json'/import 'postgres-overview.jsonnet'/" \
  dashboards/dashboards.libsonnet

$ jb init
$ jb install https://github.com/grafana/grafonnet-lib/grafonnet

Then we can run mixtool to build the new dashboard:

$ mixtool generate dashboards mixin.libsonnet --directory dashboards_out

This should succeed (with no output) and you can view the resulting dashboards_out/postgres-overview.json in a text editor and/or load it into Grafana.

Template variables

To view the remaining template variables we repeat the same jq query as before, but change the == to !=:

jq '.templating.list[] | select(.type!="datasource")' \
  dashboards/postgres-overview.json

{
  "allValue": ".+",
  "datasource": "$datasource",
  "definition": "label_values(pg_up, job)",
  "hide": 0,
  "includeAll": true,
  "label": "job",
  "multi": true,
  "name": "job",
  "options": [],
  "query": "label_values(pg_up, job)",
  "refresh": 0,
  "regex": "",
  "skipUrlSync": false,
  "sort": 0,
  "tagValuesQuery": "",
  "tags": [],
  "tagsQuery": "",
  "type": "query",
  "useTags": false
}
{
  "allValue": ".+",
  "datasource": "$datasource",
  "definition": "",
  "hide": 0,
  "includeAll": true,
  "label": "instance",
  "multi": true,
  "name": "instance",
  "options": [],
  "query": "label_values(up{job=~\"$job\"},instance)",
  "refresh": 1,
  "regex": "",
  "skipUrlSync": false,
  "sort": 0,
  "tagValuesQuery": "",
  "tags": [],
  "tagsQuery": "",
  "type": "query",
  "useTags": false
}
{
  "allValue": ".+",
  "datasource": "$datasource",
  "definition":
"label_values(pg_stat_database_tup_fetched{instance=~\"$instance\",datname!~\"template.*|postgres\"},datname)",
  "hide": 0,
  "includeAll": true,
  "label": "db",
  "multi": false,
  "name": "db",
  "options": [],
  "query":
"label_values(pg_stat_database_tup_fetched{instance=~\"$instance\",datname!~\"template.*|postgres\"},datname)",
  "refresh": 1,
  "regex": "",
  "skipUrlSync": false,
  "sort": 0,
  "tagValuesQuery": "",
  "tags": [],
  "tagsQuery": "",
  "type": "query",
  "useTags": false
}

This may look like a lot, although all are type: query and most values are defaults. We define these using template.new(), passed to addTemplate(), which are appended to the previous addTemplate() that we used for the datasource.

.addTemplate(
  template.datasource(
    name='datasource',
    query='prometheus',
    current='Prometheus',
    label='Data Source',
  )
)
.addTemplate(
  template.new(
    name='job',
    datasource='$datasource',
    query='label_values(pg_up, job)',
    label='job',
    allValues='.+',
    includeAll=true,
    multi=true,
  )
)
.addTemplate(
  template.new(
    name='instance',
    datasource='$datasource',
    query='label_values(up{job=~"$job"},instance)',
    label='instance',
    allValues='.+',
    refresh='load',
    includeAll=true,
    multi=true,
  )
)
.addTemplate(
  template.new(
    name='db',
    datasource='$datasource',
    query='label_values(pg_stat_database_tup_fetched{instance=~\"$instance\",datname!~\"template.*|postgres\"},datname)',
    label='db',
    allValues='.+',
    refresh='load',
    includeAll=true,
  )
)

To validate our jsonnet, run mixtool again:

mixtool generate dashboards mixin.libsonnet --directory dashboards_out

Panels

With templating complete, we’re ready to add the panels from our JSON dashboard. Many dashboards use rows to organize panels, which also adds the ability to collapse them. This dashboard does not, so they aren’t covered here. The concept is the same as adding the panels, but using row instead.

From looking at our dashboard we see six panels. Each contains many attributes, so we start by listing the title and type of each panel:

$ jq '.panels | keys[] as $k | "\(.[$k].title), \(.[$k].type)"' \
  dashboards/postgres-overview.json

"Rows, graph"
"QPS, singlestat"
"Buffers, graph"
"Conflicts/Deadlocks, graph"
"Cache hit ratio, graph"
"Number of active connections, graph"

One is singlestat, the other five are graph. Each will be added by passing either graph_panel or stat_panel to the addPanel(), function. We will look at the first two panels in detail (last 4 follow the same process as the first).

Rows Graph Panel

Display the complete Rows panel JSON, except targets:

$ jq '.panels[] | select(.title=="Rows") | del(.targets)' \
  dashboards/postgres-overview.json

{
  "aliasColors": {},
  "bars": false,
  "dashLength": 10,
  "dashes": false,
  "datasource": "$datasource",
  "editable": true,
  "error": false,
  "fieldConfig": {
    "defaults": {
      "custom": {}
    },
    "overrides": []
  },
  "fill": 1,
  "fillGradient": 0,
  "grid": {},
  "gridPos": {
    "h": 7,
    "w": 20,
    "x": 0,
    "y": 0
  },
  "hiddenSeries": false,
  "id": 1,
  "isNew": true,
  "legend": {
    "alignAsTable": true,
    "avg": true,
    "current": false,
    "max": true,
    "min": true,
    "rightSide": true,
    "show": true,
    "total": false,
    "values": true
  },
  "lines": true,
  "linewidth": 1,
  "links": [],
  "nullPointMode": "connected",
  "options": {
    "alertThreshold": true
  },
  "percentage": false,
  "pluginVersion": "7.2.1",
  "pointradius": 5,
  "points": false,
  "renderer": "flot",
  "seriesOverrides": [],
  "spaceLength": 10,
  "stack": false,
  "steppedLine": false,
  "thresholds": [],
  "timeFrom": null,
  "timeRegions": [],
  "timeShift": null,
  "title": "Rows",
  "tooltip": {
    "msResolution": true,
    "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": null,
      "show": true
    },
    {
      "format": "short",
      "label": null,
      "logBase": 1,
      "max": null,
      "min": null,
      "show": true
    }
  ],
  "yaxis": {
    "align": false,
    "alignLevel": null
  }
}

Start appending the contents using addPanel(graphPanel.new()). Since jq sorts the keys, making them easy to locate, I traverse the list in graph_panel.libsonnet. For each item, compare the value from the JSON dashboard, and add any that are required or non-default.

The JSON dashboard has some nested objects, such as the axes and grid position. The list in graph_panel is a flat representation, so on the first pass you can skip items that say “_ of the _ axis” or similar. Add those on a second pass, and add gridPos at the end.

.addTemplate(
  template.new(
    name='db',
    ...
  )
)
.addPanel(
  graphPanel.new(
    title="Rows",
    datasource="$datasource",
  ), gridPos={h=7, w=20, x=0, y=0}
)

To complete this panel, we need to add targets that contain the queries for each panel. First let’s list the datasource type and expression for each:

$ jq '.panels[] | select(.title=="Rows") | .targets[] | .dsType, .expr' \
  dashboards/postgres-overview.json

"prometheus"
"sum(irate(pg_stat_database_tup_fetched{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))"
"prometheus"
"sum(irate(pg_stat_database_tup_returned{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))"
"prometheus"
"sum(irate(pg_stat_database_tup_inserted{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))"
"prometheus"
"sum(irate(pg_stat_database_tup_updated{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))"
"prometheus"
"sum(irate(pg_stat_database_tup_deleted{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))"

In this case all are of type prometheus, so we use [prometheus.target])(https://github.com/grafana/grafonnet-lib/blob/master/grafonnet/prometheus.libsonnet).

Each target can be inspected individually for non-default values, by specifying the array index in targets[]. For example, element 0:

$ jq '.panels[] | select(.title=="Rows") | .targets[0] | .dsType, .expr' \
  dashboards/postgres-overview.json

{
  "alias": "fetched",
  "dsType": "prometheus",
  "expr": "sum(irate(pg_stat_database_tup_fetched{datname=~\"$db\",job=~\"$job\",instance=~\"$instance\"}[$__rate_interval]))",
  "format": "time_series",
  "groupBy": [
    {
      "params": [
        "$interval"
      ],
      "type": "time"
    },
    {
      "params": [
        "null"
      ],
      "type": "fill"
    }
  ],
  "intervalFactor": 2,
  "legendFormat": "fetched",
  "measurement": "postgresql",
  "policy": "default",
  "refId": "A",
  "resultFormat": "time_series",
  "select": [
    [
      {
        "params": [
          "tup_fetched"
        ],
        "type": "field"
      },
      {
        "params": [],
        "type": "mean"
      },
      {
        "params": [
          "10s"
        ],
        "type": "non_negative_derivative"
      }
    ]
  ],
  "step": 120,
  "tags": [
    {
      "key": "instance",
      "operator": "=~",
      "value": "/^$instance$/"
    }
  ]
}

Use the addTarget() function with a prometheus.new() parameter for each.

These are appended to the graphPanel.new() object, within the top-level addPanel() object. Be sure to keep gridPos in the expected location, as the second argument to addPanel(), as indicated below.

.addTemplate(
  template.new(
    name='db',
    ...
  )
)
.addPanel(
  graphPanel.new(
    title="Rows",
    datasource="$datasource",
  )
  .addTarget(
    prometheus.target(
      expr='sum(irate(pg_stat_database_tup_fetched{datname=~"$db",job=~"$job",instance=~"$instance"}[$__rate_interval]))',
    )
  )
  .addTarget(
    prometheus.target(
      expr='sum(irate(pg_stat_database_tup_returned{datname=~"$db",job=~"$job",instance=~"$instance"}[$__rate_interval]))',
    )
  )
  .addTarget(
    prometheus.target(
      expr='sum(irate(pg_stat_database_tup_inserted{datname=~"$db",job=~"$job",instance=~"$instance"}[$__rate_interval]))',
    )
  )
  .addTarget(
    prometheus.target(
      expr='sum(irate(pg_stat_database_tup_updated{datname=~"$db",job=~"$job",instance=~"$instance"}[$__rate_interval]))',
    )
  )
  .addTarget(
    prometheus.target(
      expr='sum(irate(pg_stat_database_tup_deleted{datname=~"$db",job=~"$job",instance=~"$instance"}[$__rate_interval]))',
    )
  ), gridPos={h: 7, w: 20, x: 0, y: 0}
)

To validate our jsonnet, run mixtool again:

mixtool generate dashboards mixin.libsonnet --directory dashboards_out

QPS Stat Panel

Work in progress, check back soon.

Remaining Graph Panels

Repeat the process used for adding the Rows panel for each remaining graph panel:

Requests
Conflicts/Deadlocks
Cache hit ratio
Number of active connections

Building the dashboard

mixtool generate dashboards mixin.libsonnet --directory dashboards_out

mixtool lint --grafana dashboards/dashboards.libsonnet

mixtool mixin.libsonnet

Comparing grafonnet output to the original

# Run from the postgres_mixin directory
cd ..

vimdiff \
  <(jq . dashboards/postgres-overview.json) \
  <(jq . dashboards_out/postgres-overview.json)