Monitoring the PostgreSQL datastore for Watson Discovery

You can enable monitoring of the PostgreSQL datastore to receive updates on its usage and status by the Watson Discovery. The events can be consumed by Prometheus monitoring software or whatever application you use for monitoring.

Permissions you need for these tasks:: You must have login credentials for the cluster.; You must have the name of the project (the namespace) for your deployment, which you can obtain from your cluster administrator.

Monitoring the PostgreSQL datastore

By enabling monitoring for user-defined projects in addition to the default platform monitoring, you can monitor your own projects with the Red Hat® OpenShift® Container Platform monitoring stack.

Create a configuration map that defines a list of the queries that you want to monitor.

apiVersion: v1
kind: ConfigMap
metadata:
  labels:
    cnpg.io/reload: "true"
  name: {name-of-config-map}
data:
  {name-of-query}: |
  ...

where {name-of-config-map} is the name of the configuration map and {name-of-query} is the name of the query followed by a list of query examples.

For example, the following configuration map is named postgresql-wds-record-monitor and checks for queries that run for over 5 minutes.

apiVersion: v1
kind: ConfigMap
metadata:
  labels:
    cnpg.io/reload: "true"
  name: postgresql-wds-record-monitor
data:
  custom-queries: |
    wd_long_running_query:
      primary: true
      query: "select datname,count(*) as count from pg_stat_activity where (now() - pg_stat_activity.query_start)> interval '5 minutes' group by datname"
      metrics:
        - datname:
            usage: "LABEL"
            description: "Name of the database"
        - count:
            usage: "GAUGE"
            description: "Number of queries that have been running for over 5 minutes"

Patch the Discovery service custom resource to enable EDB monitoring by using the configuration map that you defined in the previous step.
```
oc patch wd wd --type=merge --patch '{"spec": {"postgres": {"monitoring": {\
"customQueriesConfigMap": [{"key": "{name-of-query}", "name": "{name-of-config-map}"}]}}}}'
```
This patch causes the Discovery operator to add the new configuration map to spec.monitoring.customQueriesConfigMap in the PostgreSQL custom resource.
Create the cluster-monitoring-config configuration map and set enableUserWorkload to true under data/config.yaml. (If the configmap already exists, this command overwrites it.) When set to true, the enableUserWorkload parameter enables monitoring for user-defined projects in a cluster.
```
cat << EOF | oc apply -f -
apiVersion: v1
kind: ConfigMap
metadata:
  name: cluster-monitoring-config
  namespace: openshift-monitoring
data:
  config.yaml: |
    enableUserWorkload: true
EOF
```
Set openshift.io/user-monitoring to true for the IBM Cloud Pak for Data project (namespace). This enables user monitoring when the Network Policy is created.
```
oc label namespace ${PROJECT_CPD_INST_OPERANDS} openshift.io/user-monitoring=true
```

Create a Network Policy to allow Prometheus to monitor the PodMonitor resource in the instance namespace. This allows the project to accept connections from the Red Hat OpenShift Container Platform monitoring stack.

cat << EOF | oc apply -f -
kind: NetworkPolicy
apiVersion: networking.k8s.io/v1
metadata:
  name: wd-discovery-cn-postgres-prometheus
  namespace: ${PROJECT_CPD_INST_OPERANDS}
spec:
  ingress:
  - from:
    - namespaceSelector:
       matchLabels:
         network.openshift.io/policy-group: monitoring
  podSelector:
    matchLabels:
      postgresql: wd-discovery-cn-postgres
  policyTypes:
  - Ingress
EOF

Create a PodMonitor configuration map to create a PodMonitor object that watches the PostgreSQL pods for metrics and makes them available to the Red Hat OpenShift Container Platform monitoring stack.

cat << EOF | oc apply -f -
apiVersion: monitoring.coreos.com/v1
kind: PodMonitor
metadata:
  name: wd-discovery-cn-postgres-podmonitor
spec:
  podMetricsEndpoints:
  - port: metrics
  selector:
    matchLabels:
      postgresql: wd-discovery-cn-postgres
EOF

Ensure that you can see the PostgreSQL metrics from your monitoring stack. For a list of predefined metrics that are exposed by PostgreSQL and for information about how to define your own metrics, see Monitoring in the EDB PostgreSQL for Kubernetes documentation.