PostgreSQL - OpenTelemetry Collector

PostgreSQL is an open source object-relational database that extends the robustness SQL language to safely store and scale extensive data workloads.

The Sumo Logic app for PostgreSQL includes predefined searches and dashboards that allow you to monitor logs and metrics for the database. The logs enable you to monitor database activity, user activity, incoming connections, query execution time, and errors. The metrics allow you to monitor database resource utilization and throughput performance.

This app supports PostgreSQL version 9.6+.

We use the OpenTelemetry collector for PostgreSQL metric collection and for collecting PostgreSQL logs.

The diagram below illustrates the components of the PostgreSQL collection for each database server. OpenTelemetry collector runs on the same host as PostgreSQL, and uses the PostgreSQL receiver to obtain PostgreSQL metrics, and the Sumo Logic OpenTelemetry Exporter to send the metrics to Sumo Logic. MySQL logs are sent to Sumo Logic through a filelog receiver.

Fields creation in Sumo Logic for PostgreSQL

Following are the tags that will be created as part of PostgreSQL App install if not already present:

db.cluster.name. User configured. Enter a name to identify this PostgreSQL cluster. This cluster name will be shown in the Sumo Logic dashboards.
db.system. Has a fixed value of postgresql.
sumo.datasource. Has a fixed value of postgresql.
db.node.name. Has a value of the host name of the machine which is being monitored.

Prerequisites

For metrics collection

This collection queries the PostgreSQL statistics collector.
This receiver supports PostgreSQL version 9.6+.
The monitoring user must be granted SELECT on the pg_stat_database.

For logs collection

Configure logging in PostgreSQL:

Locate your local PostgreSQL postgresql.conf configuration file in the database data_directory. For more information, see the PostgreSQL File Locations documentation. By default it's located in /var/lib/pgsql/<version>/data/postgresql.conf. You can run SHOW config_file command inside your server's psql shell to get the location. After determining the location of conf file, modify the PostgreSQL postgresql.conf configuration file logging parameters
Connect to the database server (using SSH) in a terminal window.
Open postgresql.conf configuration file.
Under the ERROR REPORTING AND LOGGING section of the file, use the following config parameters. For more information on the following parameters, click here.

  log_destination = 'stderr'
  logging_collector = on
  log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
  log_truncate_on_rotation = off
  log_rotation_age = 1d
  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 '

Save the postgresql.conf file and restart the postgresql server:

sudo service postgresql restart

For Linux systems with ACL Support, the otelcol install process should have created the ACL grants necessary for the otelcol system user to access default log locations. You can verify the active ACL grants using the getfacl command. Install the ACL in your Linux environment, if not installed.

The required ACL may not be supported for some rare cases, for example, Linux OS Distro, which is officially not supported by Sumo Logic. In this case, you can run the following command to explicitly grant the permissions.

sudo setfacl -R -m d:u:otelcol-sumo:r-x,d:g:otelcol-sumo:r-x,u:otelcol-sumo:r-x,g:otelcol-sumo:r-x <PATH_TO_LOG_FILE>

Run the above command for all the log files in the directory that need to be ingested, which are not residing in the default location.

If Linux ACL Support is not available, traditional Unix-styled user and group permission must be modified. It should be sufficient to add the otelcol system user to the specific group that has access to the log files.

For Windows systems, log files which are collected should be accessible by the SYSTEM group. Use the following set of PowerShell commands if the SYSTEM group does not have access.

$NewAcl = Get-Acl -Path "<PATH_TO_LOG_FILE>"
# Set properties
$identity = "NT AUTHORITY\SYSTEM"
$fileSystemRights = "ReadAndExecute"
$type = "Allow"
# Create new rule
$fileSystemAccessRuleArgumentList = $identity, $fileSystemRights, $type
$fileSystemAccessRule = New-Object -TypeName System.Security.AccessControl.FileSystemAccessRule -ArgumentList $fileSystemAccessRuleArgumentList
# Apply new rule
$NewAcl.SetAccessRule($fileSystemAccessRule)
Set-Acl -Path "<PATH_TO_LOG_FILE>" -AclObject $NewAcl

Collection configuration and app installation

As part of data collection setup and app installation, you can select the App from App Catalog and click on Install App. Follow the steps below.

Step 1: Set up Collector

note

If you want to use an existing OpenTelemetry Collector, you can skip this step by selecting the Use an existing Collector option.

To create a new Collector:

Select the Add a new Collector option.
Select the platform where you want to install the Sumo Logic OpenTelemetry Collector.

This will generate a command that you can execute in the machine environment you need to monitor. Once executed, it will install the Sumo Logic OpenTelemetry Collector.

Step 2: Configure integration

In this step, you will configure the yaml file required for Mysql collection.

Below is the required input:

Error Log Path. enter the path of the error log file for your PostgreSQL instance.
Endpoint. enter the url of the server which needs to be monitored. Default endpoint is localhost:5432
UserName. enter the PostgreSQL username.
Password. password for the user name which is being used for scrapping the PostgreSQL metrics.

You can add any custom fields which you want to tag along with the data ingested in Sumo. Click on the Download YAML File button to get the yaml file.

For linux platform - Click on Download Environment Variables File button to get the file with the password which is supposed to be set as environment variable.

Step 3: Send logs and metrics to Sumo Logic

Once you have downloaded the YAML file as described in the previous step, follow the below steps based on your platform.

Copy the yaml file to /etc/otelcol-sumo/conf.d/ folder in the PostgreSQL instance which needs to be monitored.
Place Env file in the following directory:

/etc/otelcol-sumo/env/

Restart the collector using:

 sudo systemctl restart otelcol-sumo

Copy the yaml file to C:\ProgramData\Sumo Logic\OpenTelemetry Collector\config\conf.d folder in the machine which needs to be monitored.
Restart the collector using:

Restart-Service -Name OtelcolSumo

Copy the yaml file to /etc/otelcol-sumo/conf.d/ folder in the PostgreSQL instance which needs to be monitored.
Restart the otelcol-sumo process using:

  otelcol-sumo --config /etc/otelcol-sumo/sumologic.yaml --config "glob:/etc/otelcol-sumo/conf.d/*.yaml"

Copy the yaml file into your Chef cookbook files directory files/<downloaded_yaml_file>.`

Use a Chef file resource in a recipe to manage it.

cookbook_file '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>' do
	mode 0644
    notifies :restart, 'service[otelcol-sumo]', :delayed
end

Use a Chef file resource in a recipe to manage it.

cookbook_file '/etc/otelcol-sumo/env/<downloaded_env_file>' do
    mode 0600
    notifies :restart, 'service[otelcol-sumo]', :delayed
end

Add the recipe to your collector setup to start collecting. Every team typically has their established way of applying the Chef recipe. The resulting Chef recipe should look something like:

cookbook_file '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>' do
    mode 0644
    notifies :restart, 'service[otelcol-sumo]', :delayed
end

cookbook_file '/etc/otelcol-sumo/env/<downloaded_env_file>' do
    mode 0600
    notifies :restart, 'service[otelcol-sumo]', :delayed
end

Place the file into your Ansible playbook files directory.

Run the Ansible playbook using:

ansible-playbook -i inventory install_sumologic_otel_collector.yaml       
-e '{"installation_token": "<YOUR_TOKEN>", "collector_tags": {<YOUR_TAGS>}, "src_config_path": "files/conf.d", "src_env_path": "files/env"}'

Place the file into your Puppet module files directory modules/install_otel_collector/files/<downloaded_yaml>.

Use a Puppet file resource to manage it

file { '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>':
    ensure => present,
    source => 'puppet:///modules/install_otel_collector/<downloaded_yaml_file>',
    mode => '0644',
    notify => Service[otelcol-sumo],
  }

Use a Puppet file resource to manage it

file { '/etc/otelcol-sumo/env/<downloaded_env_file>'
    ensure => present,
    source => 'puppet:///modules/install_otel_collector/<downloaded_env_file>',
    mode => '0600',
    notify => Service[otelcol-sumo],
  }

Apply the Puppet manifest. Every team typically has their established way of applying the Puppet manifes. The resulting Puppet manifest should look something like:

node 'default' {
    class { 'install_otel_collector'
      installation_token => '<YOUR_TOKEN>',
      collector_tags => { <YOUR_TAGS> },
    }

    service { 'otelcol-sumo':
      provider => 'systemd',
      ensure => running,
      enable => true,
      require => Class['install_otel_collector'],
    }

    file { '/etc/otelcol-sumo/conf.d/<downloaded_yaml_file>':
      ensure => present,
      source => 'puppet:///modules/install_otel_collector/<downloaded_yaml_file>',
      mode => '0644',
      notify => Service[otelcol-sumo],
    }

    file { '/etc/otelcol-sumo/env/<downloaded_env_file>':
      ensure => present,
      source => 'puppet:///modules/install_otel_collector/<downloaded_env_file>',
      mode => '0600',
      notify => Service[otelcol-sumo],
    }
  }

After successfully executing the above command, Sumo Logic will start receiving data from your host machine.

Click Next. This will install the app (dashboards and monitors) to your Sumo Logic Org.

Dashboard panels will start to fill automatically. It's important to note that each panel fills with data matching the time range query and received since the panel was created. Results won't immediately be available, but within 20 minutes, you'll see full graphs and maps.

Sample log messages

2021-04-01 08:30:20.002 UTC [11916] postgres@postgres LOG:  connection authorized: user=postgres database=postgres

Sample queries

This sample query is from the PostgreSQL - Overview dashboard, Fatal Errors panel.

sumo.datasource=postgresql db.cluster.name=*
| json auto maxdepth 1 nodrop
| if (isEmpty(log), _raw, log) as _raw
| parse "* * * [*] *@* *:  *" as date,time,time_zone,thread_id,user,db,severity,msg
| where severity IN ("ERROR", "FATAL")
| count by date, time, severity, db, user, msg

This sample query is from the PostgreSQL - Database Metrics dashboard, Number of Active Databases panel.

sumo.datasource=postgresql deployment.environment=* db.cluster.name=* metric=postgresql.backends postgresql.database.name=* db.node.name=* | count by postgresql.database.name | count

Sample metrics

{"queryId":"A","_source":"postgresql-metric-otel","source":"idx_read","db.table":"company","_sourceName":"Http Input","host":"ip-172-31-91-203.ec2.internal","os.type":"linux","sumo.datasource":"postgresql","db.system":"postgresql","postgresql.database.name":"postgres","_sourceCategory":"Labs/postgresql-otel/metric","deployment.environment":"postgresqlEnvanema","_contentType":"Carbon2","metric":"postgresql.blocks_read","_collectorId":"000000000CD05E30","db.schema":"public","_sourceId":"000000004453F6D9","unit":"1","db.cluster.name":"postgresqlOtelClusteranema","postgresql.table.name":"public.company","_collector":"Labs - postgresql-otel","max":5,"min":0,"avg":1.92,"sum":115,"latest":0,"count":60}

Viewing PostgreSQL dashboards

Overview

The PostgreSQL - Overview dashboard gives you an at-a-glance view of the state of your database clusters by monitoring errors, failed logins, slow queries and trends over time.

Use this dashboard to:

Determine the number of active databases and clusters.
Drill-down into database errors, failed logins and slow queries.
Determine if your database or queries need to be tuned based on comparing the number of slow queries.

Query Execution

The PostgreSQL - Query Execution dashboard gives you insights into the number and time taken to execute queries:

Use this dashboard to:

Monitor query performance and identify slow queries.
Examine query execution trends.

Database Metrics

The PostgreSQL - Database Metrics dashboard allows you to monitor the database performance, which includes disk usage, commits, rollbacks, and scans.

Use this dashboard to:

Understand the behavior and performance of your database clusters.
Monitor database size and disk usage.
Identify top 5 and least 5 frequently scanned indexes.

Schema Metrics

The PostgreSQL - Schema Metrics dashboard allows you to view and analyze the metrics for monitoring the schema in a cluster.

Use this dashboard to view:

Head Only Tuple updated by schema.
Disk block reads and Disk usage by schema.
Buffer hits and rows inserted, updated and deleted by schema.

Query performance can degrade with the growth of the size of table, database and/or indexes. This means that you either need to scale up the database instance, partition your data, or redesign your indexes. Unusual growth in disk space can also mean there are problems with VACUUMs .

If your database regularly performs more sequential scans over time, you can improve its performance by creating an index on frequently accessed data.

Security

The PostgreSQL - Security dashboard provides insight into locations of incoming connections, failed authentications, and top database errors and warnings.

Use this dashboard to:

Monitor incoming connections, failed authorization requests, and outliers in the number of queries executed outlier.
Identify known malicious IPs that are accessing your databases and use firewall access control lists to prevent them from sending you traffic going forward.

Error Logs

The PostgreSQL - Error Logs dashboard provides insight into database error logs by specifically monitoring errors, user activity, database activity, and database shutdown/start events.

Use this dashboard to:

Quickly identify errors and patterns in logs for troubleshooting.
Monitor error trends and quickly identify outliers.
Identify unexpected database or user activity.

Slow Queries

The PostgreSQL - Slow Queries dashboard provides insights into all slow queries executed on the database.

Use this dashboard to:

Identify all slow queries.
Monitor users and databases running slow queries.
Determine which SQL commands are slower than others.
Examine slow query trends to determine if there are periodic performance bottlenecks in your database clusters.

Relation Metrics

The PostgreSQL - Relation Metrics dashboard allows you to view and analyze the metrics for monitoring the relations in a schema.

Use this dashboard to:

Monitor PostgreSQL relation metrics (disk blocks, buffer hits, hot updates etc) trends over time.
Monitor index scans and size to determine if executed queries are accessing them for a relation.
Track index utilization of existing indexes in a relation.

Fields creation in Sumo Logic for PostgreSQL​

Prerequisites​

For metrics collection​

For logs collection​

Collection configuration and app installation​

Step 1: Set up Collector​

Step 2: Configure integration​

Step 3: Send logs and metrics to Sumo Logic​

Sample log messages​

Sample queries​

Sample metrics​

Viewing PostgreSQL dashboards​

Overview​

Query Execution​

Database Metrics​

Schema Metrics​

Security​

Error Logs​

Slow Queries​

Relation Metrics​

Fields creation in Sumo Logic for PostgreSQL

Prerequisites

For metrics collection

For logs collection

Collection configuration and app installation

Step 1: Set up Collector

Step 2: Configure integration

Step 3: Send logs and metrics to Sumo Logic

Sample log messages

Sample queries

Sample metrics

Viewing PostgreSQL dashboards

Overview

Query Execution

Database Metrics

Schema Metrics

Security

Error Logs

Slow Queries

Relation Metrics