PostgreSQL integration with KloudMate

12min

The PostgreSQL integration with KloudMate enables you to monitor the health and performance of your PostgreSQL databases in near real-time. With the integration of OpenTelemetry, you can collect vital metrics, track slow queries, and set up automated alerts to keep your team informed about potential performance issues. This document outlines how to set up the integration, monitor key metrics, and leverage additional features like slow query logging for comprehensive database monitoring.
Key Features
Real-Time Monitoring: Gain insights into your PostgreSQL database's health and performance with near real-time metrics.
OpenTelemetry Integration: Leverage OpenTelemetry to collect and visualize metrics, traces, and logs from PostgreSQL.
Slow Query Logging: Identify slow queries affecting your database performance.
Dashboards: Visualize PostgreSQL performance metrics and set thresholds for key indicators like query response time, CPU usage, disk I/O, and more.
Alerts: Create alerts to notify your team about performance issues such as high CPU usage, long-running queries, and storage bottlenecks.
Pre-requisites:
PostgreSQL installed and running
Install the OpenTelemetry collector on the specific server that requires monitoring. See Installing and Configuring OpenTelemetry Collector. 
PostgreSQL username and password for authentication.
The postgreSQL logs must be enabled from configurations.

Step 1: Configure the Receivers to scrape metrics as well as Logs.
To start monitoring PostgreSQL with Otel Collector, you need to configure PostgreSQL receiver 
Linux Users: Open the file located at /etc/otelcol-contrib/config.yaml using your preferred text editor.
Windows Users: Create a new file called config.yaml in the C:\Program Files\OpenTelemetry Collector folder. You can use Notepad or any text editor to do this.
Add the suitable extensions : 
YAML
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true
extensions:
  health_check:
  pprof:
    endpoint: 0.0.0.0:1777
  zpages:
    endpoint: 0.0.0.0:55679
  file_storage:
    create_directory: true
﻿
In this step, two receivers are integrated to retrieve telemetry data from PostgreSQL.
PostgreSQL Receiver: To retrieve metrics(health and performance)
File Log Receiver: To retrieve logs(general logs, error logs, and slow query logs)
To get the metrics use only the postgresql receiver
YAML
receivers:
  postgresql:
    endpoint: localhost:5432  #update the endpoint
    transport: tcp
    username: <postgres>  #update postgres username
    password: <password>  #Update postgres password
    databases: []
    collection_interval: 30s
    tls:
      insecure: true
    metrics:
      postgresql.deadlocks:
        enabled: true
      postgresql.database.locks:
        enabled: true

  filelog/pgsql_json:    #use this file log if postgres version >= 15 
    include: [/var/lib/postgresql/*/main/log/postgresql*]
    storage: file_storage
    retry_on_failure:
      enabled: true
    operators:
      - type: json_parser
        parse_to: body
        severity:
          parse_from: body.error_severity
          mapping:
            info: LOG

  filelog/pgsql_log:  #use this file log if postgres version < 15 
    include: [/var/lib/postgresql/*/main/log/postgresql*]
    storage: file_storage
    multiline:
      line_start_pattern: ^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '^(?P<timestamp>[0-9\-]+\s[0-9:\.]+\s\w+)\s+\[(?P<pid>\d+)\](?:\s+(?P<database>\w+)@(?P<user>\w+))?\s+(?P<severity>\w+):(?:\s+duration:\s+(?P<duration>\d+\.\d+)\s+[^ ]*\s+(statement:\s+)?)?\s+(?P<query>.+)$'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp
receivers:
  postgresql:
    endpoint: localhost:5432  #update the endpoint
    transport: tcp
    username: <postgres>  #update postgres username
    password: <password>  #Update postgres password
    databases: []
    collection_interval: 30s
    tls:
      insecure: true
    metrics:
      postgresql.deadlocks:
        enabled: true
      postgresql.database.locks:
        enabled: true

  filelog/pgsql_json:    #use this file log if postgres version >= 15 
    include: [/var/lib/postgresql/*/main/log/postgresql*]
    storage: file_storage
    retry_on_failure:
      enabled: true
    operators:
      - type: json_parser
        parse_to: body
        severity:
          parse_from: body.error_severity
          mapping:
            info: LOG

  filelog/pgsql_log:  #use this file log if postgres version < 15 
    include: [/var/lib/postgresql/*/main/log/postgresql*]
    storage: file_storage
    multiline:
      line_start_pattern: ^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}
    retry_on_failure:
      enabled: true
    operators:
      - type: regex_parser
        parse_to: body
        regex: '^(?P<timestamp>[0-9\-]+\s[0-9:\.]+\s\w+)\s+\[(?P<pid>\d+)\](?:\s+(?P<database>\w+)@(?P<user>\w+))?\s+(?P<severity>\w+):(?:\s+duration:\s+(?P<duration>\d+\.\d+)\s+[^ ]*\s+(statement:\s+)?)?\s+(?P<query>.+)$'
        timestamp:
          parse_from: body.timestamp
          layout: '%Y-%m-%d %H:%M:%S.%f %Z'
        severity:
          parse_from: body.severity
          mapping:
            info:
              - STATEMENT
              - LOG
      - type: remove
        field: body.severity
      - type: remove
        field: body.timestamp
﻿
Step 2: Set up the processor component to identify resource information from the host and either append or replace the resource values in the telemetry data with this information.
Please choose one of the following configuration options based on your provider (AWS EC2, Azure VM, or on-premises server).
Server(Can be on-premise, non-cloud, or cloud)
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection:
    detectors: [system]
    system:
      resource_attributes:
        host.name:
          enabled: true
        host.id:
          enabled: true
        os.type:
          enabled: false
  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: host.name
﻿
AWS EC2:
(Optional)
To retrieve AWS EC2 instance tags along with logs and metrics, users need to associate an IAM role with the EC2 instance that includes the EC2:DescribeTags policy. 
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/ec2:
    detectors:  ["ec2"]
    ec2:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$ 
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: ec2.tag.Name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Azure Virtual Machines:
YAML
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
processors:
  batch:
    send_batch_size: 5000
    timeout: 10s

  resourcedetection/azure:
    detectors: ["azure"]
    azure:
      tags:
        - ^tag1$
        - ^tag2$
        - ^label.*$
        - ^Name$

  resource:
    attributes:
      - key: service.name
        action: insert
        from_attribute: azure.vm.name
      - key: service.instance.id
        action: insert
        from_attribute: host.id
﻿
Step 3: Configure the exporter, and extension and save the configuration
Set up the KloudMate Backend on the exporter part of the Open Telemetry configuration file and configure the pipeline.
YAML
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:

    metrics:
      receivers: [postgresql]
      processors: [batch, resourcedetection, resource]  # Apply the correct resourcedetection
      exporters: [otlphttp]

    logs:
      receivers: [pgsql_log]    #use the correct filelog receiver as per your pg version
      processors: [batch, resourcedetection, resource]  # Apply the correct resourcedetection
      exporters: [otlphttp]

  extensions: [health_check, pprof, zpages, file_storage]
exporters:
  debug:
    verbosity: detailed
  otlphttp:
    endpoint: 'https://otel.kloudmate.com:4318'
    headers:
      Authorization: xxxxxxxx  # Use the auth key

service:
  pipelines:

    metrics:
      receivers: [postgresql]
      processors: [batch, resourcedetection, resource]  # Apply the correct resourcedetection
      exporters: [otlphttp]

    logs:
      receivers: [pgsql_log]    #use the correct filelog receiver as per your pg version
      processors: [batch, resourcedetection, resource]  # Apply the correct resourcedetection
      exporters: [otlphttp]

  extensions: [health_check, pprof, zpages, file_storage]
﻿
Step 4:  To restart and verify the status of the OpenTelemetry (Otel) Collector, follow these steps:
For Linux:
Execute the following commands:
Text
sudo systemctl restart otelcol-contrib 
sudo systemctl status otelcol-contrib
sudo systemctl restart otelcol-contrib 
sudo systemctl status otelcol-contrib
﻿
These commands will restart the Otel Collector and display its current status.
For Windows:
Open the Services window:
Press Win + R, type services.msc, and press OK.
Alternatively, search for "Services" in the Windows Start menu.
In the Services window, locate the "OpenTelemetry Collector" service.
Right-click the service and select "Restart."
Subsequently, monitor the metrics on the KloudMate dashboard and set up an alarm to receive notifications if the potential metrics for a specific application rise. 
Default Metrics for PostgreSQL
Name
Description
Type
Unit
postgresql.backends
The number of backends
SUM
NONE
postgresql.connection.max
Configured the maximum number of client connections allowed
GAUGE
NONE
postgresql.database.count
Number of user databases
SUM
NONE
postgresql.db_size
The database disk usage
SUM
Bytes
postgresql.index.scans
The number of index scans on a table
SUM
NONE
postgresql.index.scans
The number of index scans on a table
SUM
NONE
postgresql.index.size
The size of the index on the disk
GAUGE
Bytes
postgresql.operations
The number of db row operations
SUM
NONE
postgresql.replication.data_delay
The amount of data delayed in replication
GAUGE
Bytes
postgresql.rollbacks
The number of rollbacks
SUM
NONE
postgresql.rows
The number of rows in the database
SUM
NONE
postgresql.table.count
Number of user tables in a database
SUM
NONE
postgresql.table.size
Disk space used by a table
SUM
Bytes
postgresql.table.vacuum.count
Number of times a table has manually been vacuumed
SUM
NONE
postgresql.wal.age
Age of the oldest WAL file. This metric requires WAL to be enabled with at least one replica.
GAUGE
seconds
﻿
The time between flushing the recent WAL locally and receiving notification that the standby server has completed an operation on it.
﻿
﻿
GAUGE
postgresql.wal.lag
INT
N/A
postgresql.bgwriter.buffers.allocated
Number of buffers allocated
SUM
NONE
postgresql.bgwriter.buffers.writes
Number of buffers written
SUM
NONE
postgresql.bgwriter.checkpoint.count
The number of checkpoints performed
SUM
NONE
postgresql.bgwriter.duration
Total time spent writing and syncing files to disk by checkpoints
SUM
Milliseconds
postgresql.bgwriter.maxwritten
Number of times the background writer stopped a cleaning scan because it had written too many buffers
SUM
NONE
postgresql.blocks_read
The number of blocks read
SUM
NONE
postgresql.commits
The number of commits
SUM
NONE
postgresql.deadlocks
The number of deadlocks.
SUM
NONE
postgresql.database.locks
The number of database locks.
GAUGE
COUNT
﻿

Name	Description	Type	Unit
postgresql.backends	The number of backends	SUM	NONE
postgresql.connection.max	Configured the maximum number of client connections allowed	GAUGE	NONE
postgresql.database.count	Number of user databases	SUM	NONE
postgresql.db_size	The database disk usage	SUM	Bytes
postgresql.index.scans	The number of index scans on a table	SUM	NONE
postgresql.index.scans	The number of index scans on a table	SUM	NONE
postgresql.index.size	The size of the index on the disk	GAUGE	Bytes
postgresql.operations	The number of db row operations	SUM	NONE
postgresql.replication.data_delay	The amount of data delayed in replication	GAUGE	Bytes
postgresql.rollbacks	The number of rollbacks	SUM	NONE
postgresql.rows	The number of rows in the database	SUM	NONE
postgresql.table.count	Number of user tables in a database	SUM	NONE
postgresql.table.size	Disk space used by a table	SUM	Bytes
postgresql.table.vacuum.count	Number of times a table has manually been vacuumed	SUM	NONE
postgresql.wal.age	Age of the oldest WAL file. This metric requires WAL to be enabled with at least one replica.	GAUGE	seconds
	The time between flushing the recent WAL locally and receiving notification that the standby server has completed an operation on it.
GAUGE	postgresql.wal.lag	INT	N/A
postgresql.bgwriter.buffers.allocated	Number of buffers allocated	SUM	NONE
postgresql.bgwriter.buffers.writes	Number of buffers written	SUM	NONE
postgresql.bgwriter.checkpoint.count	The number of checkpoints performed	SUM	NONE
postgresql.bgwriter.duration	Total time spent writing and syncing files to disk by checkpoints	SUM	Milliseconds
postgresql.bgwriter.maxwritten	Number of times the background writer stopped a cleaning scan because it had written too many buffers	SUM	NONE
postgresql.blocks_read	The number of blocks read	SUM	NONE
postgresql.commits	The number of commits	SUM	NONE
postgresql.deadlocks	The number of deadlocks.	SUM	NONE
postgresql.database.locks	The number of database locks.	GAUGE	COUNT

Did this page help you?

Postgres Monitoring

Enable logging in PostgreSQL