Skip to main content

PostgreSQL - Classic Collector

Thumbnail icon

The Sumo Logic app for PostgreSQL is a unified logs and metrics app for monitoring your PostgreSQL database. The app provides operational insights into the PostgreSQL database—installed on your local hardware—for real time analysis.

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 guide provides an overview of the Sumo app for PostgreSQL features and Dashboards, as well as instructions for collecting logs and metrics from PostgreSQL and installing the app.

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

Sample log messages​

Sample Kubernetes log message
{
  "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"
}
Sample Non-Kubernetes log message
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 Fatal Errors panel of the PostgreSQL - Overview dashboard.

Query String
_sourceCategory=/PostgreSQL/*  db_system=postgresql db_cluster={{db_cluster}}
| 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

Collecting logs and metrics from PostgreSQL​

This section provides instructions for configuring log and metric collection for the Sumo Logic app for PostgreSQL. This app works for PostgreSQL database clusters running on PostgreSQL versions 11.x or 12.x.

Step 1: Configure Access​

On your PostgreSQL database cluster, create a user that has access to following tables:

  • pg_stat_database
  • pg_stat_bgwriter
  • pg_stat_replication
  • pg_database
  • pg_locks
  • pg_stat_user_tables
  • pg_stat_user_indexes
  • pg_statio_user_indexes
  • pg_statio_user_tables
  • pg_class

Step 2: Configure PostgreSQL Logs and Metrics Collection​

Sumo Logic supports collection of logs and metrics data from PostgreSQL in both Kubernetes and non-Kubernetes environments. Click on the appropriate tab below based on the environment where your PostgreSQL clusters are hosted.

In Kubernetes environments, 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, Telegraf Operator, Prometheus, and Sumo Logic Distribution for OpenTelemetry Collector.

postgresql

The first service in the metrics 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 pulls metrics from Telegraf and sends them to Sumo Logic Distribution for OpenTelemetry Collector which enriches metadata and sends metrics to Sumo Logic.

In the logs pipeline, Sumo Logic Distribution for OpenTelemetry Collector collects logs written to standard out and forwards them to another instance of Sumo Logic Distribution for OpenTelemetry Collector, which enriches metadata and sends logs to Sumo Logic.

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.

Configure Metrics Collection​

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

  1. On your PostgreSQL Pods, add the following annotations mentioned in this file.
  2. Enter in values for the following annotation parameters (marked with CHANGE_ME) in the downloaded file:
    • 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.
      • db_cluster_address. Enter the cluster hostname or ip address that is used by the application to connect to the database. It could also be the load balancer or proxy endpoint.
      • db_cluster_port. Enter the database port. If not provided, a default port will be used
note

db_cluster_address and db_cluster_port should reflect the exact configuration of DB client configuration in your application, especially if you instrument it with OT tracing. The values of these fields should match exactly the connection string used by the database client (reported as values for net.peer.name and net.peer.port metadata fields).

For example, if your application uses “postgresql-prod.sumologic.com:3306” as the connection string, the field values should be set as follows: db_cluster_address=postgresql-prod.sumologic.com db_cluster_port=3306.

If your application connects directly to a given postgresql node, rather than the whole cluster, use the application connection string to override the value of the “host” field in the Telegraf configuration: host=postgresql-prod.sumologic.com

Pivoting to Tracing data from Entity Inspector is possible only for “PostgreSQL address” Entities.

  • Do not modify the following 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, see this doc. For more information on all other Telegraf related global parameters, please see this doc.
  1. 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"

Collecting Metrics from Multiple Databases (Optional)​

If you want to monitor multiple databases, copy and paste the text from this file, 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. Click here for an example.

Configure Logs Collection​

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

  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, see the PostgreSQL documentation.
    • It’s recommended to save configurations in ConfigMap so that when pods are spawned/killed, the configuration is not lost. See these instructions on how to customize the config file in the bitnami helm chart.

  2. Apply the following labels to your PostgreSQL pods:

environment: "<environmentname-CHANGEME>"
  --For example, prod, dev, qa
component: "database"
db_system: "postgresql"
db_cluster: "<clustername-CHANGEME>"
  --for example, analytics-dbcluster, webapp-dbcluster
  1. Enter in values for the following parameters (marked CHANGEME above):
    • environment. This is the deployment environment where the PostgreSQL cluster identified by the value of servers resides. 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.
    • db_cluster_address. Enter the cluster hostname or ip address that is used by the application to connect to the database. It could also be the load balancer or proxy endpoint.
    • db_cluster_port. Enter the database port. If not provided, a default port will be used.
note

db_cluster_address and db_cluster_port should reflect exact configuration of DB client configuration in your application, especially if you instrument it with OT tracing. The values of these fields should match exactly the connection string used by the database client (reported as values for net.peer.name and net.peer.port metadata fields).

For example, if your application uses “postgresql-prod.sumologic.com:3306” as the connection string, the field values should be set as follows: db_cluster_address=postgresql-prod.sumologic.com db_cluster_port=3306

If your application connects directly to a given postgresql node, rather than the whole cluster, use the application connection string to override the value of the “host” field in the Telegraf configuration: host=postgresql-prod.sumologic.com.

Pivoting to Tracing data from Entity Inspector is possible only for “PostgreSQL address” Entities.

  1. Do not modify these values, as it will cause the Sumo Logic apps to not function correctly.
    • component: “database”. This value is used by Sumo Logic apps to identify application components.
    • db_system: “postgresql”. This value identifies the database system.
  2. 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.
  3. 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>
    1. Sumo Logic Kubernetes collection will automatically start collecting logs from the pods having the annotations defined above.

Since pods are frequently killed and spawned it’s recommended to use operators like this postgresql operator so that when new pods are created the annotations and labels are automatically applied using the ConfigMap or CRD based configurations.

  1. Add an FER to normalize the fields in Kubernetes environments. This step is not needed if using application components solution terraform script. 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. Classic UI. In the main Sumo Logic menu, select Manage Data > Logs > Field Extraction Rules.
      New UI. In the top menu select Configuration, and then under Logs select Field Extraction Rules. You can also click the Go To... menu at the top of the screen and select Field Extraction Rules.
    2. Click the + Add button on the top right of the table.
    3. The Add Field Extraction Rule form will appear:
    4. 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
      | if (!isEmpty(pod_labels_db_cluster), pod_labels_db_cluster, null) as db_cluster
    5. Click Save to create the rule.
    6. Verify logs are flowing into Sumo Logic by running the following logs query
    component="database" and db_system="postgresql"

Installing the PostgreSQL app​

To install the app, do the following:

note

Next-Gen App: To install or update the app, you must be an account administrator or a user with Manage Apps, Manage Monitors, Manage Fields, Manage Metric Rules, and Manage Collectors capabilities depending upon the different content types part of the app.

  1. Select App Catalog.
  2. In the 🔎 Search Apps field, run a search for your desired app, then select it.
  3. Click Install App.
    note

    Sometimes this button says Add Integration.

  4. Click Next in the Setup Data section.
  5. In the Configure section of your respective app, complete the following fields.
    1. Is K8S deployment involved. Specify if resources being monitored are partially or fully deployed on Kubernetes (K8s)
  6. Click Next. You will be redirected to the Preview & Done section.

Post-installation

Once your app is installed, it will appear in your Installed Apps folder, and dashboard panels will start to fill automatically.

Each panel slowly fills with data matching the time range query received since the panel was created. Results will not immediately be available but will be updated with full graphs and charts over time.

As part of the app installation process, the following fields will be created by default:

  • component
  • environment
  • db_system
  • db_cluster
  • pod
  • db_cluster_address
  • db_cluster_port

Additionally, if you're using Cassandra in the Kubernetes environment, the following additional fields will be created by default during the app installation process:

  • pod_labels_component
  • pod_labels_environment
  • pod_labels_db_system
  • pod_labels_db_cluster
  • pod_labels_db_cluster_address
  • pod_labels_db_cluster_port

For information on setting up fields, see Fields.

Viewing PostgreSQL dashboards​

All dashboards have a set of filters that you can apply to the entire dashboard. Use these filters to drill down and examine the data to a granular level.

  • You can change the time range for a dashboard or panel by selecting a predefined interval from a drop-down list, choosing a recently used time range, or specifying custom dates and times. Learn more.
  • You can use template variables to drill down and examine the data on a granular level. For more information, see Filtering Dashboards with Template Variables.
  • Most Next-Gen apps allow you to provide the scope at the installation time and are comprised of a key (_sourceCategory by default) and a default value for this key. Based on your input, the app dashboards will be parameterized with a dashboard variable, allowing you to change the dataset queried by all panels. This eliminates the need to create multiple copies of the same dashboard with different queries.

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, clusters and deadlocks.
  • 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 slow queries.
  • Monitor the number of insert, update, delete operations by cluster.
PostgreSQL dashboards

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.
PostgreSQL dashboards

Database Metrics​

The PostgreSQL - Database Metrics dashboard allows you to monitor the database performance, which includes disk usage, deadlocks, buffer hits, server processes, 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.
PostgreSQL dashboards

Relation and Schema Metrics​

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

Use this dashboard to:

  • Monitor PostgreSQL relation and schema metrics trends over time.
  • Monitor sequential scans and index scans and determine if executed queries are accessing them.
  • Monitor the size of tables, and query operations which will determine the performance of your queries.
PostgreSQL dashboards

Query performance can degrade with growth in 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.
PostgreSQL dashboards

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.
PostgreSQL dashboards

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.
PostgreSQL dashboards

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 sequential scans and index scans and determine if executed queries are accessing them for a relation.
  • Track index utilization of existing indexes in a relation.

Create monitors for PostgreSQL app​

From your App Catalog:

  1. From the Sumo Logic navigation, select App Catalog.
  2. In the Search Apps field, search for and then select your app.
  3. Make sure the app is installed.
  4. Navigate to What's Included tab and scroll down to the Monitors section.
  5. Click Create next to the pre-configured monitors. In the create monitors window, adjust the trigger conditions and notifications settings based on your requirements.
  6. Scroll down to Monitor Details.
  7. Under Location click on New Folder.
    note

    By default, monitor will be saved in the root folder. So to make the maintenance easier, create a new folder in the location of your choice.

  8. Enter Folder Name. Folder Description is optional.
    tip

    Using app version in the folder name will be helpful to determine the versioning for future updates.

  9. Click Create. Once the folder is created, click on Save.

PostgreSQL Alerts​

Alert NameAlert Description and conditionsAlert ConditionRecover Condition
PostgreSQL - Instance DownThis alert fires when the Postgres instance is down>= 1< 1
PostgreSQL - TooManyConnectionsThis alert fires when we detect that a PostgreSQL instance has too many (90% of allowed) connections)>= 90< 90
PostgreSQL - SlowQueriesThis alert fires when we detect that the PostgreSQL instance is executing slow queries>= 1< 1
PostgreSQL - Commit Rate LowThis alert fires when we detect that Postgres seems to be processing very few transactions.commit rate < 10commit rate >= 10
PostgreSQL - High Rate of Statement TimeoutThis alert fires when we detect Postgres transactions show a high rate of statement timeoutstimeout rate > 3timeout rate < 3
PostgreSQL - High Rate DeadlockThis alert fires when we detect deadlocks in a Postgres instancedeadlock rate >= 1deadlock rate < 1
PostgreSQL - High Replication LagThis alert fires when we detect that the Postgres Replication lag (in bytes) is high.> 1000000000 bytes< 1000000000 bytes
PostgreSQL - SSL Compression ActiveThis alert fires when we detect database connections with SSL compression are enabled. This may add significant jitter in replication delay. Replicas should turn off SSL compression via sslcompression=0 in recovery.conf> 0<= 0
PostgreSQL - Too Many Locks AcquiredThis alert fires when we detect that there are too many locks acquired on the database. If this alert happens frequently, you may need to increase the postgres setting max_locks_per_transaction.> 20 percent of max allowed locks assuming default max connection = 100 and max_locks per transaction = 64< 20 percent of max allowed locks
PostgreSQL - Access from Highly Malicious SourcesThis alert will fire when a Postgres instance is accessed from known malicious IP addresses.> 0<= 0

PostgreSQL Metrics​

Here are the metrics available for PostgreSQL (click to expand).

postgresql_numbackends
postgresql_xact_commit
postgresql_xact_rollback
postgresql_blks_read
postgresql_blks_hit
postgresql_tup_inserted
postgresql_tup_updated
postgresql_tup_deleted
postgresql_deadlocks
postgresql_tup_fetched
postgresql_tup_returned
postgresql_checkpoints_timed
postgresql_checkpoints_req
postgresql_buffers_checkpoint
postgresql_buffers_clean
postgresql_buffers_backend
postgresql_stat_ssl_compression_count
postgresql_replication_delay
postgresql_replication_lag
postgresql_replay_lag
postgresql_flush_lag
postgresql_write_lag
postgresql_db_size
postgresql_num_locks
postgresql_seq_scan
postgresql_seq_tup_read
postgresql_idx_scan
postgresql_idx_tup_fetch
postgresql_n_tup_ins
postgresql_n_tup_upd
postgresql_n_tup_del
postgresql_n_tup_hot_upd
postgresql_n_live_tup
postgresql_n_dead_tup
postgresql_idx_scan
postgresql_idx_tup_read
postgresql_idx_tup_fetch
postgresql_idx_blks_read
postgresql_idx_blks_hit
postgresql_heap_blks_read
postgresql_heap_blks_hit
postgresql_idx_blks_read
postgresql_idx_blks_hit
postgresql_index_size
postgresql_table_size

Additional resources​

Status
Legal
Privacy Statement
Terms of Use

Copyright © 2025 by Sumo Logic, Inc.