PostgreSQL

Integrating PostgreSQL Metrics and Logs to KubeSense

KubeSense provides comprehensive monitoring for PostgreSQL through OpenTelemetry (OTEL) Collector integration, allowing you to collect key PostgreSQL metrics and logs and visualize them in the KubeSense platform.

Prerequisites

Before you begin, ensure you have:

A PostgreSQL server running version 9.6 or newer
- You can check the server version with the SQL statement: SELECT version();

A PostgreSQL user with required permissions for metrics collection

To create a monitoring user for PostgreSQL versions 10+, run:

CREATE USER monitoring WITH PASSWORD '<PASSWORD>';
GRANT pg_monitor TO monitoring;
GRANT SELECT ON pg_stat_database TO monitoring;

To create a monitoring user for PostgreSQL versions >= 9.6 and < 10, run:

CREATE USER monitoring WITH PASSWORD '<PASSWORD>';
GRANT SELECT ON pg_stat_database TO monitoring;

KubeSense deployed with OpenTelemetry (OTEL) Collector with access to the PostgreSQL server
- Install the OTEL Collector if not done already
- Ensure the OTEL collector has access to the PostgreSQL server as a client using the monitoring user
- For log collection, the collector must be able to read the PostgreSQL server log file
Access to modify the KubeSense Helm values file

Locating the KubeSense Sensor Service Endpoint

To find the endpoint for the Sensor service (referred to as {KUBESENSOR_HOST}), follow the instructions provided here.

Collecting PostgreSQL Metrics

Step 1: Update KubeSense Helm Values File

Add the PostgreSQL receiver configuration to your KubeSense Helm values file, under kubesensor section. Update the OpenTelemetry collector configuration section with the following:

otel-collector:
  config:
    receivers:
      postgresql:
        # The endpoint of the postgresql server. Whether using TCP or Unix sockets, this value should be host:port.
        endpoint: <POSTGRES_ENDPOINT>:5432
        # The username used to access the postgres instance
        username: monitoring
        # The password used to access the postgres instance
        password: <password>
        # The frequency at which to collect metrics from the Postgres instance.
        collection_interval: 10s
        # The list of databases for which the receiver will attempt to collect statistics. 
        # If an empty list is provided, the receiver will attempt to collect statistics for all non-template databases
        databases: []
        # Defines the network to use for connecting to the server. Valid Values are `tcp` or `unix`
        # transport: tcp
        tls:
          # set to false if SSL is enabled on the server
          insecure: true
        #   ca_file: /etc/ssl/certs/ca-certificates.crt
        #   cert_file: /etc/ssl/certs/postgres.crt
        #   key_file: /etc/ssl/certs/postgres.key
        metrics:
          postgresql.database.locks:
            enabled: true
          postgresql.deadlocks:
            enabled: true
          postgresql.sequential_scans:
            enabled: true
    appendToPipelines:
      metrics:
        receivers:
        - postgresql

Note: Replace the endpoint, username, and password values with your actual PostgreSQL server details. The endpoint should be reachable from the OTEL collector pod.

Step 2: Apply the Configuration

Upgrade your KubeSense deployment with the updated values file:

helm upgrade kubesense kubesense/kubesense \
  -f values.yaml \
  --namespace kubesense

Collecting PostgreSQL Logs

Step 1: Update KubeSense Helm Values File for Log Collection

Add the PostgreSQL log collection configuration to your KubeSense Helm values file:

otel-collector:
  config:
    receivers:
      filelog/postgresql:
        include: ["/var/log/postgresql/postgresql.log"]
        operators:
          # Parse default postgresql text log format.
          # `log_line_prefix` postgres setting defaults to '%m [%p] ' which logs the timestamp and the process ID
          # See https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX for more details
          - type: regex_parser
            if: body matches '^(?P<ts>\\d{4}-\\d{2}-\\d{2} \\d{2}:\\d{2}:\\d{2}.?[0-9]*? [A-Z]*) \\[(?P<pid>[0-9]+)\\] (?P<log_level>[A-Z]*). (?P<message>.*)$'
            parse_from: body
            regex: '^(?P<ts>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}.?[0-9]*? [A-Z]*) \[(?P<pid>[0-9]+)\] (?P<log_level>[A-Z]*). (?P<message>.*)$'
            timestamp:
              parse_from: attributes.ts
              layout: '%Y-%m-%d %H:%M:%S %Z'
            severity:
              parse_from: attributes.log_level
              mapping:
                debug:
                  - DEBUG1
                  - DEBUG2
                  - DEBUG3
                  - DEBUG4
                  - DEBUG5
                info:
                  - INFO
                  - LOG
                  - NOTICE
                  - DETAIL
                warn: WARNING
                error: ERROR
                fatal:
                  - FATAL
                  - PANIC
            on_error: send
          - type: move
            if: attributes.message != nil
            from: attributes.message
            to: body
          - type: remove
            if: attributes.log_level != nil
            field: attributes.log_level
          - type: remove
            if: attributes.ts != nil
            field: attributes.ts
          - type: add
            field: attributes.source
            value: postgres
    processors:
      batch/postgresql:
        send_batch_size: 10000
        send_batch_max_size: 11000
        timeout: 10s
    service:
      pipelines:
        logs/postgresql:
          receivers:
          - filelog/postgresql
          processors:
          - batch
          - resource
          exporters:
          - otlphttp/kubesense_logs

Note: Replace /var/log/postgresql/postgresql.log with the actual path to your PostgreSQL log file. The log file path can be found by running SELECT pg_current_logfile(); in PostgreSQL, or typically found in /usr/local/var/log/postgresql on macOS. The OTEL collector must have access to read this log file.

Step 2: Apply the Configuration

Upgrade your KubeSense deployment with the updated values file:

helm upgrade kubesense ./kubesense-chart \
  -f values.yaml \
  --namespace kubesense

PostgreSQL Metrics Collected

KubeSense automatically collects and enriches the following key PostgreSQL metrics:

Metric Name	Type	Unit	Description
postgresql_backends	sum	number	The number of backends.
postgresql_bgwriter_buffers_allocated	sum	number	Number of buffers allocated.
postgresql_bgwriter_buffers_writes	sum	number	Number of buffers written.
postgresql_bgwriter_checkpoint_count	sum	number	The number of checkpoints performed.
postgresql_bgwriter_duration	sum	milliseconds	Total time spent writing and syncing files to disk by checkpoints.
postgresql_bgwriter_maxwritten	sum	number	Number of times the background writer stopped a cleaning scan because it had written too many buffers.
postgresql_blocks_read	sum	number	The number of blocks read.
postgresql_commits	sum	number	The number of commits.
postgresql_connection_max	gauge	number	Configured maximum number of client connections allowed
postgresql_database_count	sum	number	Number of user databases.
postgresql_database_locks	gauge	number	The number of database locks.
postgresql_db_size	sum	Bytes	The database disk usage.
postgresql_deadlocks	sum	number	The number of deadlocks.
postgresql_index_scans	sum	number	The number of index scans on a table.
postgresql_index_size	gauge	Bytes	The size of the index on disk.
postgresql_operations	sum	number	The number of db row operations.
postgresql_replication_data_delay	gauge	Bytes	The amount of data delayed in replication.
postgresql_rollbacks	sum	number	The number of rollbacks.
postgresql_rows	sum	number	The number of rows in the database.
postgresql_sequential_scans	sum	number	The number of sequential scans.
postgresql_table_count	sum	number	Number of user tables in a database.
postgresql_table_size	sum	Bytes	Disk space used by a table.
postgresql_table_vacuum_count	sum	number	Number of times a table has manually been vacuumed.
postgresql_temp_files	sum	number	The number of temp files.
postgresql_wal_age	gauge	seconds	Age of the oldest WAL file.
postgresql_wal_delay	gauge	seconds	Time between flushing recent WAL locally and receiving notification that the standby server has completed an operation with it.
postgresql_wal_lag	gauge	seconds	Time between flushing recent WAL locally and receiving notification that the standby server has completed an operation with it.

PostgreSQL Log Attributes

When collecting PostgreSQL logs, the following attributes are available:

Name	Path	Type	Description
Process ID	attributes.pid	string	The PostgreSQL process ID
Timestamp	timestamp	timestamp	The log entry timestamp
Severity Text	severity_text	string	The log severity level (DEBUG, INFO, WARNING, ERROR, FATAL, PANIC)
Severity Number	severity_number	number	Numeric representation of the severity level
Source	attributes.source	string	Source identifier (set to "postgres")

Visualization in KubeSense

Once configured, PostgreSQL metrics and logs will appear in the KubeSense dashboard, allowing you to:

Monitor PostgreSQL performance in real-time
Track database connections and query patterns
Analyze deadlocks and sequential scans
Monitor replication lag and WAL status
View and query PostgreSQL logs with parsed attributes
Set up alerts for key PostgreSQL metrics

Configuration Parameters

Here's a detailed breakdown of the PostgreSQL receiver configuration:

Parameter	Type	Required	Description
`endpoint`	string	Yes	PostgreSQL server endpoint (host:port)
`username`	string	Yes	Username for PostgreSQL authentication
`password`	string	Yes	Password for PostgreSQL authentication
`collection_interval`	string	No	Frequency of metrics collection (default: 60s)
`databases`	array	No	List of databases to monitor (empty = all non-template databases)
`transport`	string	No	Network transport type: `tcp` or `unix` (default: `tcp`)
`tls.insecure`	boolean	No	Set to `true` if SSL is disabled on the server
`tls.ca_file`	string	No	Path to CA certificate file (if SSL enabled)
`tls.cert_file`	string	No	Path to client certificate file (if SSL enabled)
`tls.key_file`	string	No	Path to client key file (if SSL enabled)
`metrics.*.enabled`	boolean	No	Enable/disable specific metrics

Troubleshooting

Issue: Cannot connect to PostgreSQL server

Solution: Verify the endpoint is reachable from the OTEL collector pod.

Issue: Authentication failed

Solution: Verify the monitoring user has the required permissions:

SELECT * FROM pg_user WHERE usename = 'monitoring';

Issue: Logs not being collected

Solution: Verify the log file path is correct and accessible:

kubectl exec -n kubesense <otel-collector-pod> -- ls -la /var/log/postgresql/postgresql.log

Issue: Metrics not appearing in dashboard

Solution: Check the OTEL collector logs for errors:

kubectl logs -n kubesense <kubesense-otel-collector-pod> -f

Best Practices

Security: Use dedicated monitoring users with minimal required permissions
Network: Ensure OTEL collector pods have network access to PostgreSQL servers
Log Rotation: Configure PostgreSQL log rotation to prevent disk space issues
Monitoring: Set up alerts for critical metrics like deadlocks and connection limits
Performance: Adjust collection_interval based on your monitoring needs and PostgreSQL load

Conclusion

KubeSense provides seamless PostgreSQL monitoring through OpenTelemetry integration, enabling you to track the health and performance of your PostgreSQL instances alongside your other infrastructure and applications in a unified observability platform.

PostgreSQL

ON THIS PAGE