PostgreSQL

PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.

Prerequisites

1. Create a read-only user with proper access to your PostgreSQL server and start psql on your PostgreSQL database.
   • For PostgreSQL version 10 and above, run the following:

   create user '<Username>' with password '<PASSWORD>';
   grant pg_monitor to <Username>;
   

   • For older versions of PostgreSQL, run the following:

   create user '<Username>' with password '<PASSWORD>';
   grant SELECT ON pg_stat_database to '<Username>';
   

2. To get the metrics of WAL files, grant the folllowing permissions:

REVOKE ALL ON FUNCTION pg_ls_dir(dirname text ) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pg_ls_dir(dirname text ) TO '<Username>';

3. To get the metric of Function:

vi /etc/postgresql/12/main/postgresql.conf
track_functions = all

Note: Do not restart the server for this setting.

4. For Virtual Machines, install the Linux Agent.

Configuring the credentials

Configure the credentials in the directory /opt/opsramp/agent/conf/app.d/creds.yaml

postgresql:
- name: postgresql
  user: <username>
  pwd: <Password>
  encoding-type: plain
  labels:
    key1: val1
    key2: val2

Configuring the application

Virtual machine

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-detection.yaml

- name: postgresql
  instance-checks:
    service-check:
      - postgresql
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgresSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and provide a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Docker environment

Configure the application in the directory /opt/opsramp/agent/conf/app/discovery/auto-container-detection.yaml

- name: postgresql
  container-checks:
    image-check:
      - postgres
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgresSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and provide a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Kubernetes environment

Configure the application in config.yaml

- name: postgresql
  container-checks:
    image-check:
      - postgres
    port-check:
      - 5432
#  monitoring-configs :
#    #   The name of the PostgresSQL database to monitor.
#    #   Note: If omitted, the default system Postgres database is queried.
#    dbname: $DBNAME
#    #   ssl type could be any of these : disable , allow, prefer, require, verify-ca, verify-full
#    ssl: disable
#    #   The list of relations/tables must be specified here to track per-relation (table) metrics.
#    #   If enabled, `dbname` should be specified to collect database-specific relations metrics.
#    #   You can either specify a single relation by its exact name in 'relation_name' or use a regex to track metrics in relation_regex
#    #   By default all schemas are included. To track relations from specific schemas only,
#    #   you can specify the `schemas` attribute and provide a list of schemas to use for filtering.
#    relations:
#      - relation_name: $TABLE_NAME1
#        schemas:
#          - $SCHEMA_NAME1
#      - relation_name: $TABLE_NAME2
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2
#      - relation_regex: $TABLE_PATTERN1
#        schemas:
#          - $SCHEMA_NAME1
#          - $SCHEMA_NAME2

Validate

Go to Resources under the Infrastructure tab to check if your resources are onboarded and the metrics are collected.

Metrics