[Feature] Monitoring PostgreSQL Server

[yswdqz]

Search before asking

• I had searched in the issues and found no similar feature requirement.

Description

Right now, we have a MySQL Monitoring, and I want to add the PostgreSQL Server Monitoring to enhance SkyWalking's database monitoring.
The dataflow would be PostgreSQL Server -> postgres_exporter -> OpenTelemetry Collector -> SkyWalking OpenCensus Receiver -> MAL engine.

Steps

1. Discuss which metrics we should do for the PostgreSQL server.
2. Add PostgreSQL layer to OAP, and begin to set up dashboards.
3. Add PostgreSQL as a sub of the Database layer.

Related issues

No response

Are you willing to submit a PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[yswdqz]

@wu-sheng
I have listed the metrics:
metrics doc

[wu-sheng]

It is better you don't provide private env, it is dangerous for your env(cyber attach), and it is not safe for others reading this page, and feel should access another IP. 😀

[yswdqz]

I understand, I have edited it.

[wu-sheng]

Reviewed the doc, generally good for me.

[yswdqz]

I get it.

[yswdqz]

I have a metric like this:

pg_locks_count{datname="postgres",mode="accessexclusivelock",server="postgres:5432"} 0

I want delete the datname and mode
And add a mode label = datname + mode, how can I do that?
I have tested this way: pg_locks_count.tag({tags -> tags.mode = tags.datname + tags.mode}).sum(['mode' , 'server']), but OAP will receive nothing.

[wu-sheng]

@wankai123 ☝️ Could you take a look?

[yswdqz]

There are two metrics that I haven't set up yet (Because of the problem which I said above ).
Others is following:


Is that ok?

[wu-sheng]

The preview seems good.

[wankai123]

pg_locks_count.tag({tags -> tags.mode = tags.datname + tags.mode}).sum(['mode' , 'server'])
This expression should be correct, I have tried it in https://github.com/apache/skywalking/blob/master/oap-server/analyzer/meter-analyzer/src/test/java/org/apache/skywalking/oap/meter/analyzer/dsl/AggregationTest.java.

            {
                "sum-by",
                of("http_success_request", SampleFamilyBuilder.newBuilder(
                    Sample.builder().labels(of("idc", "t1")).value(50).name("http_success_request").build(),
                    Sample.builder().labels(of("idc", "t3", "region", "cn", "svc", "catalog")).value(50).name("http_success_request").build(),
                    Sample.builder().labels(of("idc", "t1", "region", "us", "svc", "product")).value(50).name("http_success_request").build(),
                    Sample.builder().labels(of("idc", "t1", "region", "us", "instance", "10.0.0.1")).name("http_success_request").value(50).build(),
                    Sample.builder().labels(of("idc", "t3", "region", "cn", "instance", "10.0.0.1")).name("http_success_request").value(3).build()
                ).build()),
                "http_success_request.tag({tags -> tags.idc = tags.idc + tags.region }).sum(['region', 'idc'])",
                Result.success(SampleFamilyBuilder.newBuilder(
                    Sample.builder().labels(of("idc", "t1", "region", "")).value(50).name("http_success_request").build(),
                    Sample.builder().labels(of("idc", "t1", "region", "us")).value(100).name("http_success_request").build(),
                    Sample.builder().labels(of("idc", "t3", "region", "cn")).value(53).name("http_success_request").build()
                ).build()),
                false,
            }

the result is

<Result(success=true, isThrowable=false, error=null, data=SampleFamily(samples=[Sample(name=http_success_request, labels={region=us, idc=t1us}, value=100.0, timestamp=0), Sample(name=http_success_request, labels={region=, idc=t1null}, value=50.0, timestamp=0), Sample(name=http_success_request, labels={region=cn, idc=t3cn}, value=53.0, timestamp=0)], context=SampleFamily.RunningContext(meterSamples=null, histogramType=CUMULATIVE, defaultHistogramBucketUnit=SECONDS)))>

you could try it by yourself.
I think if the OAP receives nothing, you might need to check the other place. @yswdqz
By the way, the UT is a good place to test your expressions :D

[yswdqz]

Thank you , I understand !

[yswdqz]

I had a problem about e2e.
I write a case to verify metric:

    - query: swctl --display yaml --base-url=http://${oap_host}:${oap_12800}/graphql metrics multiple-linear --name=meter_pg_fetched_rows_rate --labels=postgres --service-name=postgresql::postgres:5432 |yq e 'to_entries | with(.[] ; .value=(.value | to_entries))' -
      expected:  expected/metrics-has-value-label.yml  

When I run the e2e , I get the error:

WARNING verify case failure, will continue retry, failed to execute the query: swctl --display yaml --base-url=http://${oap_host}:${oap_12800}/graphql metrics multiple-linear --name=meter_pg_fetched_rows_rate --labels=postgres --service-name=postgresql::postgres:5432 |yq e 'to_entries | with(.[] ; .value=(.value | to_entries))' -, output: , error: Error: Parsing expression: Lexer error: could not match text starting at 1:14 failing at 1:19.
        unmatched text: "with(" 

But when I run the query without e2e, it will success.

[root@VM-0-8-centos ~]#  swctl --display yaml --base-url=http://localhost:49155/graphql metrics multiple-linear --name=meter_pg_fetched_rows_rate --labels=postgres --service-name=postgresql::postgres:5432 |yq e 'to_entries | with(.[] ; .value=(.value | to_entries))' -
- key: postgres
  value:
    - key: 2022-08-20 0635
      value: 1
    - key: 2022-08-20 0636
      value: 1
    - key: 2022-08-20 0637
      value: 1
    - key: 2022-08-20 0638
      value: 1
    - key: 2022-08-20 0639
      value: 1
    - key: 2022-08-20 0640
      value: 1
    - key: 2022-08-20 0641
      value: 1
    - key: 2022-08-20 0642
      value: 1
    - key: 2022-08-20 0643
      value: 1
    - key: 2022-08-20 0644
      value: 1
    - key: 2022-08-20 0645
      value: 1
    - key: 2022-08-20 0646
      value: 1
    - key: 2022-08-20 0647
      value: 1
    - key: 2022-08-20 0648
      value: 1
    - key: 2022-08-20 0649
      value: 1
    - key: 2022-08-20 0650
      value: 1
    - key: 2022-08-20 0651
      value: 1
    - key: 2022-08-20 0652
      value: 1
    - key: 2022-08-20 0653
      value: 1
    - key: 2022-08-20 0654
      value: 1
    - key: 2022-08-20 0655
      value: 1
    - key: 2022-08-20 0656
      value: 1
    - key: 2022-08-20 0657
      value: 1
    - key: 2022-08-20 0658
      value: 1
    - key: 2022-08-20 0659
      value: 1
    - key: 2022-08-20 0700
      value: 1
    - key: 2022-08-20 0701
      value: 1
    - key: 2022-08-20 0702
      value: 1
    - key: 2022-08-20 0703
      value: 1
    - key: 2022-08-20 0704
      value: 1
    - key: 2022-08-20 0705
      value: 1

So I don't know why it will fail in e2e test.