Scripting

pgmetrics was designed to facilitate machine-processing of the information that it collects. It can save the collected information in JSON format, which you can then read from a script written in your favorite programming language.

Generating JSON Output

To make pgmetrics generate the output in JSON and save it to a file, use:

$ pgmetrics -h myhost -f json -o out.json mydb1 mydb2

You can then read and process the file out.json. The output can also be directly piped to a script, like so:

$ pgmetrics -h myhost -f json --no-password mydb1 mydb2 | check_and_warn_bloat

Note that the --no-password option is required in the case, otherwise the password prompt also goes off into the stdout.

JSON Schema

The canonical reference for the schema of the generated JSON is the Go-language struct called “Model” living at https://github.com/rapidloop/pgmetrics/blob/master/model.go. pgmetrics is written in Go, and the package “github.com/rapidloop/pgmetrics” serves as the versioned, typed schema for the JSON.

Working With The JSON Output

Here are some examples in different languages showing how you can work with the generated JSON.

Go

You can direcly unmarshal the JSON into a typed structure, like this:

import (
    "encoding/json"
    "os"

    "github.com/rapidloop/pgmetrics"
)

func loadFile(filename string) (model pgmetrics.Model, err error) {
    f, err := os.Open(filename)
    if err != nil {
        return
    }
    defer f.Close()

    err = json.NewDecoder(f).Decode(&model)
    return
}

Python

You can unmarshal the JSON into dicts and arrays with the standard library module json (available in both Python 2 and 3).

import json

model = json.loads(open('out.json').read())

locks = 0
for b in model['backends']:
	if b['wait_event_type'] == 'Lock':
		locks += 1

print('There are %d backends waiting for locks.' % locks)

Shell Scripts

You may find the tools jq, jp and gron helpful in extracting specific values from the JSON.

Generating CSV Output

To make pgmetrics generate the output in CSV and save it to a file, use:

$ pgmetrics -h myhost -f csv -o out.csv mydb1 mydb2

The CSV format is mainly intended for easier integration with (legacy) monitoring systems. It does not contain all the information that is present in the JSON format. Where possible, you should consider writing a script that will parse the JSON format and use or convert it to the format you desire.