tencent cloud

Overview

When using PostgreSQL, you need to monitor its running status to know whether it runs normally and troubleshoot its faults. TMP provides an exporter to monitor PostgreSQL and offers an out-of-the-box Grafana monitoring dashboard for it. This document describes how to deploy the PostgreSQL exporter and integrate it with the alert feature.

Note：

For easier export installation and management, we recommend you use TKE for unified management.

Prerequisites

• You have created a TKE cluster in the region and VPC of your TMP instance.
• You have located and integrated the target TKE cluster in the Integrate with TKE section of the target TMP instance in the TMP console. For more information, please see Agent Management.

Directions

Deploying exporter

1. Log in to the TKE console.
2. Click the ID/name of the cluster whose access credential you want to get to enter the cluster management page.
3. Perform the following steps to deploy an exporter: Using Secret to manage PostgreSQL password > Deploying PostgreSQL exporter > Getting metric.

Using Secret to manage PostgreSQL password

1. On the left sidebar, select Workload > Deployment to enter the Deployment page.
2. In the top-right corner of the page, click Create via YAML to create a YAML configuration as detailed below:
   You can use Kubernetes Secrets to manage and encrypt passwords. When starting the PostgreSQL exporter, you can directly use the Secret key but need to adjust the corresponding password. Below is a sample YAML configuration:
   apiVersion: v1
   kind: Secret
   metadata:
    name: postgres-test
   type: Opaque
   stringData:
    username: postgres
    password: you-guess # Corresponding PostgreSQL password

Deploying PostgreSQL exporter

On the Deployment management page, click Create and select the target namespace to deploy the service. You can create in the console. Here, YAML is used to deploy the exporter. Below is a sample YAML configuration (please directly copy the following content and adjust the corresponding parameters based on your actual business needs):

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-test
  namespace: postgres-test
  labels:
    app: postgres
    app.kubernetes.io/name: postgresql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
      app.kubernetes.io/name: postgresql
  template:
    metadata:
      labels:
        app: postgres
        app.kubernetes.io/name: postgresql
    spec:
      containers:
      - name: postgres-exporter
        image: wrouesnel/postgres_exporter:latest
        args:
          - "--web.listen-address=:9187"
          - "--log.level=debug"
        env:
          - name: DATA_SOURCE_USER
            valueFrom:
              secretKeyRef:
                name: postgres-test
                key: username
          - name: DATA_SOURCE_PASS
            valueFrom:
              secretKeyRef:
                name: postgres-test
                key: password
          - name: DATA_SOURCE_URI
            value: "x.x.x.x:5432/postgres?sslmode=disable"
        ports:
        - name: http-metrics
          containerPort: 9187

Note：

In the above sample, the username and password in Secret are passed in to the environment variables DATA_SOURCE_USER and DATA_SOURCE_PASS, so the username and password cannot be viewed in plaintext. You can also use DATA_SOURCE_USER_FILE/DATA_SOURCE_PASS_FILE to read the username and password from the file, or use DATA_SOURCE_NAME to put them in the connection string, such as postgresql://login:password@hostname:port/dbname.

Parameter description

The query part (after ?) in the DATA_SOURCE_URI/DATA_SOURCE_NAME connection string supports the following parameters (the latest supported parameters listed in Connection String Parameters shall prevail):

Other supported exporter parameters are as detailed below (for more information, please see PostgreSQL Server Exporter):

Getting metric

You cannot get the PostgreSQL instance operation time through curl http://exporter:9187/metrics. You can define a queries.yaml file to get this metric:

1. Create a ConfigMap containing queries.yaml.
2. Mount the ConfigMap to a directory in the exporter as a volume.
3. Use the ConfigMap through --extend.query-path to aggregate the information of the aforementioned Secret and Deployment. The YAML file after aggregation is as shown below:

# Note: the following document sample code creates a namespace named `postgres-test`, which is for reference only
apiVersion: v1
kind: Namespace
metadata:
  name: postgres-test

# The following document sample code creates a Secret containing a username and password
---
apiVersion: v1
kind: Secret
metadata:
  name: postgres-test-secret
  namespace: postgres-test
type: Opaque
stringData:
  username: postgres
  password: you-guess

# The following document sample code creates a `queries.yaml` file containing custom metrics
---
apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-test-configmap
  namespace: postgres-test
data:
  queries.yaml: |
    pg_postmaster:
      query: "SELECT pg_postmaster_start_time as start_time_seconds from pg_postmaster_start_time()"
      master: true
      metrics:
        - start_time_seconds:
            usage: "GAUGE"
            description: "Time at which postmaster started"

# The following document sample code mounts the Secret and ConfigMap and defines exporter deployment-related parameters such as image
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-test
  namespace: postgres-test
  labels:
    app: postgres
    app.kubernetes.io/name: postgresql
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
      app.kubernetes.io/name: postgresql
  template:
    metadata:
      labels:
        app: postgres
        app.kubernetes.io/name: postgresql
    spec:
      containers:
        - name: postgres-exporter
          image: wrouesnel/postgres_exporter:latest
          args:
            - "--web.listen-address=:9187"
            - "--extend.query-path=/etc/config/queries.yaml"
            - "--log.level=debug"
          env:
            - name: DATA_SOURCE_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-test-secret
                  key: username
            - name: DATA_SOURCE_PASS
              valueFrom:
                secretKeyRef:
                  name: postgres-test-secret
                  key: password
            - name: DATA_SOURCE_URI
              value: "x.x.x.x:5432/postgres?sslmode=disable"
          ports:
            - name: http-metrics
              containerPort: 9187
          volumeMounts:
            - name: config-volume
              mountPath: /etc/config
      volumes:
        - name: config-volume
          configMap:
            name: postgres-test-configmap

4. Run curl http://exporter:9187/metrics, and you can use the custom queries.yaml to query the PostgreSQL instance start time as follows:

# HELP pg_postmaster_start_time_seconds Time at which postmaster started
# TYPE pg_postmaster_start_time_seconds gauge
pg_postmaster_start_time_seconds{server="x.x.x.x:5432"} 1.605061592e+09

Adding scrape task

After the exporter runs, you need to configure TMP to discover and collect the monitoring metrics in the following steps:

1. Log in to the TMP console and select the target TMP instance to enter the management page.
2. Click a cluster ID in the TKE cluster list to enter the Integrate with TKE page.
3. In Scrape Configuration, add Pod Monitor to define a Prometheus scrape task. Below is a sample YAML configuration:

apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: postgres-exporter
  namespace: cm-prometheus
spec:
  namespaceSelector:
    matchNames:
    - postgres-test
  podMetricsEndpoints:
  - interval: 30s
    path: /metrics
    port: http-metrics # Port name of the aforementioned exporter container
    relabelings:
    - action: labeldrop
      regex: __meta_kubernetes_pod_label_(pod_|statefulset_|deployment_|controller_)(.+)
    - action: replace
      regex: (.*)
      replacement: postgres-xxxxxx
      sourceLabels:
      - instance
      targetLabel: instance
  selector:
    matchLabels:
      app: postgres

Note：

For more advanced usage, please see ServiceMonitor and PodMonitor.

Visualizing Grafana dashboard

Note：

You need to use the configuration in Getting metric to get the PostgreSQL instance start time.

1. In the TMP instance list, find the corresponding TMP instance, click on the right of the instance ID to open your Grafana page, and enter your account and password to access the Grafana visual dashboard operation section.
2. Enter Grafana, click the icon to expand the monitoring dashboard, and click the name of the corresponding monitoring chart to view the monitoring data.
   

Integrating with alert feature

1. Log in to the TMP console and select the target TMP instance to enter the management page.
2. Click Alerting Rule and add the corresponding alerting rules. For more information, please see Creating Alerting Rule.

   Note：

   TMP will provide more PostgreSQL alerting templates in the near future.