PostgreSQL Source Template

The PostgreSQL source template creates an OpenTelemetry configuration that can be pushed to a remotely managed OpenTelemetry collector (abbreviated as otelcol). By creating this source template and pushing the config to the appropriate OpenTelemetry agent, you can ensure collection of PostgreSQL logs and metrics to Sumo Logic.

Fields creation in Sumo Logic for PostgreSQL

If not already present, the following Fields are created as part of source template creation.

• sumo.datasource. Fixed value of postgresql.
• deployment.environment. This is a user-configured field set at the time of collector installation. It identifies the environment where the host resides, such as dev, prod, or qa.
• db.cluster.name. User configured. Enter a uniquely identifiable name for your PostgreSQL cluster to show in the Sumo Logic dashboards.
• db.node.name. Includes the value of the hostname of the machine which is being monitored.

Prerequisites

For metrics collection

• The PostgreSQL metrics receiver collects metrics by querying the PostgreSQL statistics collector.
• The monitoring user which is used in the source template must be granted permission to SELECT permission for pg_stat_database.
• Make sure to set the PostgreSQL user's password as an environment variable for the OpenTelemetry agent. Refer to Setting Environment Variables with Secret Values for Source Templates.

note

For Sumo Logic OpenTelemetry Collector versions below 0.116, metric collection supports only PostgreSQL version 16 and earlier. With Sumo Logic OpenTelemetry collector version 0.116 and above, metric collection also supports PostgreSQL version 17.

For logs collection

Configure logging in PostgreSQL:

1. 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
2. Connect to the database server (using SSH) in a terminal window.
3. Open postgresql.conf configuration file.
4. 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 '

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

sudo service postgresql restart

Ensure that the otelcol has adequate permissions to access all log file paths. Execute the following command:

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

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, the collected log files 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 a new rule
$fileSystemAccessRuleArgumentList = $identity, $fileSystemRights, $$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

Configuring the PostgreSQL source template

You can follow the below steps to set a remotely managed OpenTelemetry collector and push the source template to it.

Step 1: Set up remotely managed OpenTelemetry collector

In this step, we'll install the collector and add a uniquely identifiable tag to these collectors.

1. Classic UI. In the main Sumo Logic menu, select Manage Data > Collection > OpenTelemetry Collection.
   New UI. In the Sumo Logic top menu select Configuration, and then under Data Collection select OpenTelemetry Collection. You can also click the Go To... menu at the top of the screen and select OpenTelemetry Collection.
2. On the OpenTelemetry Collection page, click + Add Collector.
3. In the Set up Collector step:
   1. Choose your platform (for example, Linux).
   2. Enter your Installation Token.
   3. Under Tag data on Collector level, add a new tag to identify these collectors as having Apache running on them (for example, application = Apache).
   4. Leave the Collector Settings at their default values to configure collectors as remotely managed.
   5. Under Generate and run the command to install the collector, copy and run the installation command in your system terminal where the collector needs to be installed.
4. After installation is complete, click Next to proceed.
5. Select a source template (for example, Apache source template) to start collecting logs from all linked collectors, then proceed with the data configuration.
   

To revisit this screen later: From the Classic UI, select Manage Data > Collection > Source Template. From the New UI, select Configuration > Source Template.

Step 2: Configure the source template

In this step, you will configure the yaml required for PostgreSQL collection. Below are the inputs required for configuration:

• Name. Name of the source template.
• Description. Description for the source template.
• Error Log Path. Enter the path of the error log file for your PostgreSQL instance.
• Endpoint. The endpoint of the PostgreSQL server. This value should be host:port. Default endpoint is localhost:5432.
  note

  There should not be any http prefixed to this value. For example, http://localhost:port.

• UserName. Enter the PostgreSQL username.
• Password Environment Variable Name. Enter the PostgreSQL password environment variable name.
• Fields/Metadata. You can provide any customer fields to be tagged with the data collected. By default, Sumo Logic tags _sourceCategory with the value otel/postgresql user needs to provide the value for db.cluster.name.

Advance options for log collection can be used as follows:

• Timestamp Format. By default, Sumo Logic will automatically detect the timestamp format of your logs. However, you can manually specify a timestamp format for a source by configuring the following:
  • Timestamp locator. Use a Go regular expression to match the timestamp in your logs. Ensure the regular expression includes a named capture group called timestamp_field.
  • Layout. Specify the exact layout of the timestamp to be parsed. For example, - %Y-%m-%dT%H:%M:%S.%LZ. To learn more about the formatting rules, refer to this guide.
  • Location (Time zone). Define the geographic location (timezone) to use when parsing a timestamp that does not include a timezone. The available locations depend on the local IANA Time Zone database. For example, America/New_York. See more examples here.

Processing Rules. You can add processing rules for logs/metrics collected. To learn more, refer to Processing Rules.

Step 3: Push the source template to the desired remotely managed collectors

info

A new source template will always be created with the latest version of the source template.

Follow the below steps to create a data collection configuration to gather the required logs and link them to all the collectors with the help of collector tags.

1. Complete the source template form with the name and file path for your logs (for example, error logs or access logs), then click Next.
2. Under Link Collectors, you will have the option to link the collectors using the collector name or by adding tags to find the group of collectors (for example, application = Apache).
   
3. Preview and confirm the collectors that will be linked (fetched automatically) to the newly created source template.
   
   
4. Click Next to complete the source template creation. In the background, the system will apply the configuration to all the linked collectors and will start collecting the respective telemetry data from the remote host (in the example, it would start collecting Apache error logs).
5. Click the Log Search or Metrics Search icons to search for and analyze your data collected for this source template.

info

Refer to the changelog for information on periodic updates to this source template.