Using pgo metrics with existing prometheus and grafana

[alrooney]

I'm using pgo v4.5.0 and have deployed my pg pods with the --metrics flag. If I already have grafana and prometheus running in my cluster how can I see the metrics coming from pg pods? How can I install the pg dashboards that you have in your grafana instance? Do you have a link to those? Thanks!

[jkatz]

• Prometheus: https://github.com/CrunchyData/pgmonitor/blob/master/prometheus/crunchy-prometheus.yml.containers
• Grafana: https://github.com/CrunchyData/pgmonitor/tree/master/grafana/containers
• Alertmanager: https://github.com/CrunchyData/pgmonitor/blob/master/prometheus/alert-rules.d/crunchy-alert-rules-pg.yml.containers.example

We should probably better document how to connect this to one's existing Prometheus / Grafana. If you are able to get this this working and would like to propose a patch (or a workflow that you used) I'd be happy to review it.

[philipgeraldtaylor]

Further to this, I'm trying to create a PodMonitor for an existing Prometheus installation and it requires a named port on the Collect container which I cannot seem to find how to create without editing the running Pod definition (!) - we have had it running by doing just that but it's obviously an anti-pattern. I suspect the non-Ansible kustomize deployer that @jkatz is working on may be the answer to this but please confirm.

[zposloncec]

Hi, I've created a working PodMonitor for an existing Prometheus.

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: crunchy-postgres-exporter
  labels:
    release: prometheus-operator
  namespace: cattle-monitoring-system
spec:
  namespaceSelector:
      matchNames:           
      - cluster-postgres  
  selector:
    matchLabels:
      crunchy_postgres_exporter: "true"
  podTargetLabels: 
    - deployment_name
    - role
    - pg_cluster
  podMetricsEndpoints: 
    - relabelings: 
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "5432"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "8009"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "2022"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "10000"
      - sourceLabels: 
        - "__meta_kubernetes_namespace"
        action: "replace"
        targetLabel: "kubernetes_namespace"
      - sourceLabels: 
        - "__meta_kubernetes_pod_name"
        targetLabel: "pod"
      - sourceLabels: 
        - "__meta_kubernetes_pod_ip"
        targetLabel: "ip"
        replacement: "$1"
      - sourceLabels: 
        - "dbname"
        targetLabel: "dbname"
        replacement: "$1"
      - sourceLabels: 
        - "relname"
        targetLabel: "relname"
        replacement: "$1"
      - sourceLabels: 
        - "schemaname"
        targetLabel: "schemaname"
        replacement: "$1"
      - targetLabel: "exp_type"
        replacement: "pg"

[captainjapeng]

@zposloncec when I used your PodMonitor the label clusters which is in form of {{ namespace }}:{{ pg_cluster }} is only translated to {{ pg_cluster }}. Do you have any suggestion on how to keep the original format? Thank you.

[mariusstaicu]

Hi,

I've created a service monitor from: https://github.com/CrunchyData/pgmonitor/blob/master/prometheus/crunchy-prometheus.yml.containers

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  name: postgres-servicemonitor
  namespace: monitoring
  labels:
    app: prometheus
spec:
  endpoints:
    - port: postgres-exporter
      path: /metrics
      honorLabels: true
      interval: 10s
      relabelings:
      - sourceLabels: [ __meta_kubernetes_pod_label_crunchy_postgres_exporter ]
        action: keep
        regex: "true"
      - sourceLabels: [ __meta_kubernetes_pod_container_port_number ]
        action: drop
        regex: "5432"
      - sourceLabels: [ __meta_kubernetes_pod_container_port_number ]
        action: drop
        regex: "10000"
      - sourceLabels: [ __meta_kubernetes_pod_container_port_number ]
        action: drop
        regex: "8009"
      - sourceLabels: [ __meta_kubernetes_pod_container_port_number ]
        action: drop
        regex: "2022"
      - sourceLabels: [ __meta_kubernetes_namespace ]
        action: replace
        targetLabel: kubernetes_namespace
      - sourceLabels: [ __meta_kubernetes_pod_name ]
        targetLabel: pod
      - sourceLabels: [ __meta_kubernetes_namespace,__meta_kubernetes_pod_label_pg_cluster ]
        targetLabel: pg_cluster
        separator: ':'
        replacement: '$1$2'
      - sourceLabels: [ __meta_kubernetes_pod_ip ]
        targetLabel: ip
        replacement: '$1'
      - sourceLabels: [ __meta_kubernetes_pod_label_deployment_name ]
        targetLabel: deployment
        replacement: '$1'
      - sourceLabels: [ __meta_kubernetes_pod_label_role ]
        targetLabel: role
        replacement: '$1'
      - sourceLabels: [ dbname ]
        targetLabel: dbname
        replacement: '$1'
      - sourceLabels: [ relname ]
        targetLabel: relname
        replacement: '$1'
      - sourceLabels: [ schemaname ]
        targetLabel: schemaname
        replacement: '$1'
      - targetLabel: exp_type
        replacement: 'pg'
  namespaceSelector:
    matchNames:
      - monitoring
  selector:
    matchLabels:
      vendor: crunchydata

[ludzzz]

Little input if it can help someone. (for the V5.0.1)

First, Thanks @mariusstaicu , I reused your ServiceMonitor.

Second, on my side, it was not totally enough.
I got the entry in the service discovery, but nothing on the target side.
To fix that, I needed to add a service with the exporter port and the selector aiming for the exporter:

   apiVersion: v1
kind: Service
metadata:
  creationTimestamp: "2021-08-23T04:25:23Z"
  labels:
    postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true"
  name: pgo-exporter
  namespace: pgo
spec:
  clusterIP: None
  selector:
    postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true"
  ports:
  - name: postgres
    port: 9187
    protocol: TCP
    targetPort: 9187
  sessionAffinity: None
  type: ClusterIP
status:
  loadBalancer: {}

Of course, you will need to change the selector.matchlabels of the service monitor.

It was the last piece to integrate the monitoring to my kube-prometheus stack.
bonus: the json service-monitor that you import in your main.jsonnet of kube-prometheus (based on the reply of @mariusstaicu):

{
    "apiVersion": "monitoring.coreos.com/v1",
    "kind": "ServiceMonitor",
    "metadata": {
        "name": "postgres-servicemonitor",
        "namespace": "monitoring",
        "labels": {
            "app": "prometheus"
        }
    },
    "spec": {
        "endpoints": [
            {
                "port": "postgres",
                "path": "/metrics",
                "honorLabels": true,
                "interval": "10s",
                "relabelings": [
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_crunchy_postgres_exporter"
                        ],
                        "action": "keep",
                        "regex": "true"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_container_port_number"
                        ],
                        "action": "drop",
                        "regex": "5432"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_container_port_number"
                        ],
                        "action": "drop",
                        "regex": "^$"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_namespace"
                        ],
                        "action": "replace",
                        "targetLabel": "kubernetes_namespace"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_name"
                        ],
                        "targetLabel": "pod"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_namespace",
                            "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_cluster"
                        ],
                        "targetLabel": "pg_cluster",
                        "separator": ":",
                        "replacement": "$1$2"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_ip"
                        ],
                        "targetLabel": "ip",
                        "replacement": "$1"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_instance"
                        ],
                        "targetLabel": "deployment",
                        "replacement": "$1"
                    },
                    {
                        "sourceLabels": [
                            "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_role"
                        ],
                        "targetLabel": "role",
                        "replacement": "$1"
                    },
                    {
                        "sourceLabels": [
                            "dbname"
                        ],
                        "targetLabel": "dbname",
                        "replacement": "$1"
                    },
                    {
                        "sourceLabels": [
                            "relname"
                        ],
                        "targetLabel": "relname",
                        "replacement": "$1"
                    },
                    {
                        "sourceLabels": [
                            "schemaname"
                        ],
                        "targetLabel": "schemaname",
                        "replacement": "$1"
                    }
                ]
            }
        ],
        "namespaceSelector": {
            "matchNames": [
                "pgo"
            ]
        },
        "selector": {
          "matchLabels": {
            "postgres-operator.crunchydata.com/crunchy-postgres-exporter": "true"
          }
        }
    }
}

[dimitrigraf]

@jkatz

* Prometheus: https://github.com/CrunchyData/pgmonitor/blob/master/prometheus/crunchy-prometheus.yml.containers

* Grafana: https://github.com/CrunchyData/pgmonitor/tree/master/grafana/containers

* Alertmanager: https://github.com/CrunchyData/pgmonitor/blob/master/prometheus/alert-rules.d/crunchy-alert-rules-pg.yml.containers.example

We should probably better document how to connect this to one's existing Prometheus / Grafana. If you are able to get this this working and would like to propose a patch (or a workflow that you used) I'd be happy to review it.

Hi there!

I hope it's okay to ask this question here. I do have the same "problem" as the person creating the issue in the first place.
We have our own monitoring stack (prometheus, alertmanager, grafana) and I would like to use Crunchy PGO together with our monitoring stack. I already checked out the exporter and confirmed that the metrics are available. So, once a cluster is up with the exporter sidecars running, what needs to be done to connect those apart from setting up a ServiceMonitor which picks up on the exporter?
Is this use case supported and just not documented or will have to bend things quite a bit in order to get meaningful metrics etc.?

Thanks in advance!

[bdols]

I don't know if a ServiceMonitor is necessary. This is an update of a PodMonitor spec that works based off of @zposloncec earlier post.

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: crunchy-postgres-exporter
  namespace: postgres-operator
spec:
  namespaceSelector:
    matchNames:           
    - postgres-operator
  selector:
    matchLabels:
      postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true"
  podTargetLabels: 
    - deployment
    - role
    - pg_cluster
  podMetricsEndpoints: 
    - port: exporter
      path: /metrics
      honorLabels: true
      interval: 10s
      relabelings: 
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "5432"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "8009"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "2022"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "10000"
      - sourceLabels: 
        - "__meta_kubernetes_namespace"
        action: "replace"
        targetLabel: "kubernetes_namespace"
      - sourceLabels: 
        - "__meta_kubernetes_pod_name"
        targetLabel: "pod"
      - sourceLabels:
        - "__meta_kubernetes_namespace"
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_cluster"
        targetLabel: "pg_cluster"
        separator: ':'
        replacement: '$1$2'
      - sourceLabels: 
        - "__meta_kubernetes_pod_ip"
        targetLabel: "ip"
        replacement: "$1"
      - sourceLabels:
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_instance"
        targetLabel: "deployment"
        replacement: '$1'
      - sourceLabels:
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_role"
        targetLabel: "role"
        replacement: '$1'
      - sourceLabels: 
        - "dbname"
        targetLabel: "dbname"
        replacement: "$1"
      - sourceLabels: 
        - "relname"
        targetLabel: "relname"
        replacement: "$1"
      - sourceLabels: 
        - "schemaname"
        targetLabel: "schemaname"
        replacement: "$1"
      - targetLabel: "exp_type"
        replacement: "pg"

This spec assumes that the postgres objects are stored in the prometheus-operator namespace and this has updates to match labels in the latest version of the operator, I believe. I used kustomize/monitoring/prometheus-config.yaml in the https://github.com/CrunchyData/postgres-operator-examples repo and prometheus/containers/crunchy-prometheus.yml.containers in pgmonitor as a basis for the labels. It appears to me that there's some labels in the prometheus configs that are used as sources for backwards compatibility (i.e. what's deployment_name?). I'm not sure that the podTargetLabels here is necessary as every target label specified in the relabelings appeared in the grafana metrics browser regardless of whether they were specified as a podTargetLabel.

I imported the dashboards in kustomize/monitoring/dashboards in the examples repo but needed to replace references to the "PROMETHEUS" datasource with "Prometheus" to match the default datasource that kube-prometheus-stack sets up.

You may need to store the PodMonitor in the namespace where your prometheus-stack is installed (i.e. monitoring instead of prometheus-operator at line 5) if you don't have target discovery configured to check all namespaces.

[githilman]

I don't know if a ServiceMonitor is necessary. This is an update of a PodMonitor spec that works based off of @zposloncec earlier post.

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: crunchy-postgres-exporter
  namespace: postgres-operator
spec:
  namespaceSelector:
    matchNames:           
    - postgres-operator
  selector:
    matchLabels:
      postgres-operator.crunchydata.com/crunchy-postgres-exporter: "true"
  podTargetLabels: 
    - deployment
    - role
    - pg_cluster
  podMetricsEndpoints: 
    - port: exporter
      path: /metrics
      honorLabels: true
      interval: 10s
      relabelings: 
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "5432"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "8009"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "2022"
      - sourceLabels: 
        - "__meta_kubernetes_pod_container_port_number"
        action: "drop"
        regex: "10000"
      - sourceLabels: 
        - "__meta_kubernetes_namespace"
        action: "replace"
        targetLabel: "kubernetes_namespace"
      - sourceLabels: 
        - "__meta_kubernetes_pod_name"
        targetLabel: "pod"
      - sourceLabels:
        - "__meta_kubernetes_namespace"
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_cluster"
        targetLabel: "pg_cluster"
        separator: ':'
        replacement: '$1$2'
      - sourceLabels: 
        - "__meta_kubernetes_pod_ip"
        targetLabel: "ip"
        replacement: "$1"
      - sourceLabels:
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_instance"
        targetLabel: "deployment"
        replacement: '$1'
      - sourceLabels:
        - "__meta_kubernetes_pod_label_postgres_operator_crunchydata_com_role"
        targetLabel: "role"
        replacement: '$1'
      - sourceLabels: 
        - "dbname"
        targetLabel: "dbname"
        replacement: "$1"
      - sourceLabels: 
        - "relname"
        targetLabel: "relname"
        replacement: "$1"
      - sourceLabels: 
        - "schemaname"
        targetLabel: "schemaname"
        replacement: "$1"
      - targetLabel: "exp_type"
        replacement: "pg"

This spec assumes that the postgres objects are stored in the prometheus-operator namespace and this has updates to match labels in the latest version of the operator, I believe. I used kustomize/monitoring/prometheus-config.yaml in the https://github.com/CrunchyData/postgres-operator-examples repo and prometheus/containers/crunchy-prometheus.yml.containers in pgmonitor as a basis for the labels. It appears to me that there's some labels in the prometheus configs that are used as sources for backwards compatibility (i.e. what's deployment_name?). I'm not sure that the podTargetLabels here is necessary as every target label specified in the relabelings appeared in the grafana metrics browser regardless of whether they were specified as a podTargetLabel.

I imported the dashboards in kustomize/monitoring/dashboards in the examples repo but needed to replace references to the "PROMETHEUS" datasource with "Prometheus" to match the default datasource that kube-prometheus-stack sets up.

You may need to store the PodMonitor in the namespace where your prometheus-stack is installed (i.e. monitoring instead of prometheus-operator at line 5) if you don't have target discovery configured to check all namespaces.

Thanks for the snippet @bdols . It works for me, been trying with the service monitor but keep fail to get the target available. Thanks!!

[ValClarkson]

Hi @alrooney,

With the release of PGO v5, we are now re-evaluating various GitHub issues, feature requests, etc. from previous PGO v4 releases. More specifically, in order to properly prioritize the features and fixes needed by our end-users for upcoming PGO v5 releases, we are now attempting to identify which PGO v4 issues, use cases, and enhancement requests are still valid (especially in the context of PGO v5).

Therefore, please let us know if you are still experiencing this issue in PGO v5.

If so, you can either respond to this issue directly to ensure it remains open, or you can close this issue and submit a new one for PGO v5 (this would also be a great opportunity to include any updated details, context, or any other information relevant to your issue). Otherwise, we will be closing this issue in the next 30 days.

If you are still running PGO v4, we recommend that you upgrade to PGO v5 as soon as possible to ensure you have the latest PGO features and functionality.