Skip to main content
Sumo Logic

Collect PostgreSQL Logs and Metrics for Kubernetes environments.

Collect PostgreSQL Logs and Metrics for Kubernetes environments

In a Kubernetes environment, we use the Telegraf Operator, which is packaged with our Kubernetes collection. You can learn more about it here.The diagram below illustrates how data is collected from PostgreSQL in Kubernetes environments. In the architecture shown below, there are four services that make up the metric collection pipeline: Telegraf, Prometheus, Fluentd and FluentBit.

The first service in the pipeline is Telegraf. Telegraf collects metrics from PostgreSQL. Note that we’re running Telegraf in each pod we want to collect metrics from as a sidecar deployment: i.e. Telegraf runs in the same pod as the containers it monitors. Telegraf uses the PostgreSQL Extensible input plugin to obtain metrics, (For simplicity, the diagram doesn’t show the input plugins). The injection of the Telegraf sidecar container is done by the Telegraf Operator. Prometheus scrapes the metrics from each of the Telegraf containers and sends it to FluentD.We also have Fluentbit that collects logs written to standard out and forwards them to FluentD, which in turn sends all the logs and metrics data to a Sumo Logic HTTP Source.


 

Follow the instructions below to set up the metric collection:

  1. Configure Metrics Collection

    1. Add annotations on your PostgreSQL pods
    2. (Optional)Collecting metrics from multiple databases
  2. Configure Logs Collection

    1. Configuring logging in PostgreSQL
    2. Add labels on your PostgreSQL pods to capture logs from standard output.
    3. (Optional) Collecting PostgreSQL logs from a file.

Prerequisites

  • Please ensure that you are monitoring your Kubernetes clusters with the Telegraf operator -  If you are not, then please follow these instructions to do so.

Step 1 Configure Metrics Collection

Follow the steps below to collect metrics from a Kubernetes environment:

  1. Add annotations on your PostgreSQL Pods

On your PostgreSQL Pods, add the following annotations mentioned in this file.

Please enter in values for the following parameters (marked with CHANGE_ME) in the downloaded file:

  • Annotations:

    • telegraf.influxdata.com/inputs - This contains the required configuration for the Telegraf Postgres Input plugin.As telegraf will be run as a sidecar the host should always be localhost.

      • In the input plugins section which is [[inputs.postgresql_extensible]]

        • address - Specify the db user, db name and password used for connecting to the database.Example "host=localhost user=postgres dbname=postgres password=mypassword sslmode=disable"

      • In the tags section, which is [inputs.postgresql_extensible.tags]

        • environment - This is the deployment environment where the postgresql cluster resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it. 
        • db_cluster - Enter a name to identify this PostgreSQL cluster. This cluster name will be shown in the Sumo Logic dashboards. For example:  analytics-dbcluster, webapp-dbcluster

Here’s an explanation for additional values set by this configuration that we request you please do not modify these values as they will cause the Sumo Logic apps to not function correctly.

  • telegraf.influxdata.com/class: sumologic-prometheus - This instructs the Telegraf operator what output to use. This should not be changed.

  • prometheus.io/scrape: "true" - This ensures our Prometheus plugin will scrape the metrics.

  • prometheus.io/port: "9273" - This tells Prometheus what ports to scrape metrics from. This should not be changed.

  • telegraf.influxdata.com/inputs

    • In the tags sections [inputs.postgresql_extensible.tags]

      • component= “database” - This value is used by Sumo Logic apps to identify application components. 

      • db_system= “postgresql” - This value identifies the database system.

For more information on configuring the PostgreSQL input plugin for Telegraf please see  this doc.

For more information on all other Telegraf related global parameters please see this doc.

Once this has been done, the Sumo Logic Kubernetes collection will automatically start collecting metrics from the pods having the annotations defined in the previous step. Verify metrics are flowing into Sumo Logic by running the following metrics query:

component="database" and db_system="postgresql"

2. Collecting metrics from multiple databases (Optional) 

If you want to monitor multiple databases then you can copy and paste the text from this file and create another [[inputs.postgresql_extensible]] section and add it in your annotations. This section contains only those queries which are meant to be run for each database.

          Here is an example sample_postgresql_annotations_kubernetes_multiple_db.txt.

Step 2 Configure Logs Collection

This section explains the steps to collect PostgreSQL logs from a Kubernetes environment.

  1. Configuring logging parameters in postgresql.conf
  2. Add labels on your PostgreSQL pods.
  3. (Recommended Method) Collecting Logs written to Standard output
  4. (Optional) Collect PostgreSQL logs written to log files.
  5. Add an FER to normalize fields in Kubernetes environments

 

  1. Configuring logging parameters in postgresql.conf

    1. Edit the postgresql.conf configuration file present in your pod.Under the ERROR REPORTING AND LOGGING section of the file, use the following config parameters. 

log_min_duration_statement = 250
log_connections = on
log_duration = on
log_hostname = on
log_timezone = 'UTC'
log_min_messages = 'WARNING'
log_line_prefix = '%m [%p] %q%u@%d '

For more information on the above parameters, please see the PostgreSQL documentation.

  1. Add labels on your PostgreSQL pods

  1. Apply the following labels to your PostgreSQL pods:

  labels:

     environment: "<environment name for example dev, qa>-CHANGE_ME"
     component: "database"
     db_system: "postgresql"
     db_cluster: "<cluster name for example analytics-dbcluster, webapp-dbcluster>-CHANGE_ME"

Please enter in values for the following parameters (marked in bold and CHANGE_ME above):

  • Labels:

    • environment - This is the deployment environment where the PostgreSQL cluster identified by the value of servers resides. For example dev, prod or qa. While this value is optional we highly recommend setting it. 

    • db_cluster - Enter a name to identify this PostgreSQL cluster. This cluster name will be shown in the Sumo Logic dashboards.For example analytics-dbcluster, webapp-dbcluster.

Here’s an explanation for additional values set by this configuration that we request you please do not modify as they will cause the Sumo Logic apps to not function correctly.

  • Labels:

    • component: “database” - This value is used by Sumo Logic apps to identify application components. 

    • db_system: “postgresql” - This value identifies the database system.

3. Collecting Logs written to Standard output (Recommended)

The Sumologic-Kubernetes-Collection will automatically capture the logs from stdout and will send the logs to Sumologic. For more information on deploying the Sumo Logic Kubernetes Collection, please see this page.

4. Collect PostgreSQL logs written to log files (Optional)

If your PostgreSQL service is writing its logs to log files, you can use a sidecar to send log files to stdout. To do this:

  1. Determine the location of the PostgreSQL log file on Kubernetes. 
  2. Install the Sumo Logic tailing sidecar operator.
  3. Add the following annotation in addition to the existing annotations.

annotations:
tailing-sidecar:sidecarconfig;container_name:<mount_volume>:/<path_of_postgresql_log_file_name>

Example:

annotations:
  tailing-sidecar: sidecarconfig;data:/pg_data/postgresql.log

  1. Make sure that the PostgreSQL pods are running and annotations and labels are applied.Verify by using the command: kubectl describe pod <PostgreSQL_pod_name>

  2. Sumo Logic Kubernetes collection will automatically start collecting logs from the pods having the annotations defined above. 

  1. Add an FER to normalize the fields in Kubernetes environments

Labels created in Kubernetes environments automatically are prefixed with pod_labels. To normalize these for our app to work, we need to create a Field Extraction Rule if not already created for Database Application Components. To do so:

  1. Go to Manage Data > Logs > Field Extraction Rules.
  2. Click the + Add button on the top right of the table.
  3. The following form appears:

  1. Enter the following options:

    • Rule Name. Enter the name as App Component Observability - Database.

    • Applied At. Choose Ingest Time

    • Scope. Select Specific Data

      • Scope: Enter the following keyword search expression:
        pod_labels_environment=* pod_labels_component=database pod_labels_db_system=* pod_labels_db_cluster=*

      • Parse Expression.Enter the following parse expression:
        | if (!isEmpty(pod_labels_environment), pod_labels_environment, "") as environment
        | pod_labels_component as component
        | pod_labels_db_system as db_system
        | pod_labels_db_cluster as db_cluster

  2. Click Save to create the rule.

  3. Verify logs are flowing into Sumo Logic by running the following logs query

Sample Log Messages

{ "timestamp":1615988485842, "log":"2021-04-01 08:30:20.002 UTC [11916] postgres@postgres LOG: connection authorized: user=postgres database=postgres ", "stream":"stdout", "time":"2021-03-17T13:41:19.103646109Z" }