Skip to main content
Sumo Logic

Collect logs and metrics from PostgreSQL

  1. Last updated
  2. Save as PDF
This page shows you how to configure log and metric collection for PostgreSQL, as well as providing log and query examples.

The Sumo Logic App for PostgreSQL is used for monitoring database logs and metrics. The App collects log files from your local system for analysis in predfined Dashboards. The logs enable you to monitor database activity, user activity, incoming connections, query execution time, and errors. Metrics allow you to monitor database resource utilization and throughput performance.

This page provides instructions for customizing the postgres.conf file for log collection, and installing and configuring collectd on the server where PostgreSQL is located. Click a link to jump directly to a section:

Log types

The Sumo Logic App for PostgreSQL uses the local log file on your database server. For more information see the logging_collector section of the PostgreSQL logging documentation.

Sample log message

2018-10-29 20:33:44 UTC [15860-27753] user@backend_proc LOG:  duration: 0.092 ms statement: 
SELECT extract(seconds from (now() - pg_last_xact_replay_timestamp())) AS time_lag;

2018-10-29 20:33:44 UTC [15859-27754] user@backend_db LOG:  duration: 0.182 ms statement: 
select datname, numbackends from pg_stat_database;

2018-10-29 20:33:44 UTC [15859-27755] user@backend_db LOG:  duration: 1.457 ms statement: 
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch FROM 
pg_stat_user_indexes;

Query samples

This section provides samples for both metrics and log queries.

Metrics Query

The following is a sample query from the Sumo Logic App for PostgreSQL Resource Utilization Dashboard, Number of Server Processes by Data panel:

_sourceCategory="postgres/metrics2" plugin=postgresql type=pg_numbackends

Log Query

The following is a sample query from the Sumo Logic App for PostgreSQL Security Dashboard, Geo Location of Incoming Connections panel:

_sourceCategory=postgres/logs connection
| parse "connection received: host=* port=*" as ip,port
| lookup latitude, longitude, country_code, country_name, region, city, postal_code from 
geo://location on ip = ip
| count by latitude, longitude, country_code, country_name, region, city, postal_code
| sort _count

Collect logs from PostgreSQL

This section walks you through the process of setting up log collection from PostgreSQL for ingest into Sumo Logic.

Pre-requisites:

  • Locate your local PostgreSQL postgresql.conf configuration file in the database data_directory. For more information, see the PostgreSQL File Locations documentation.

  • Modify the PostgreSQL postgresql.conf configuration file logging parameters.

To modify the postgresql.conf file, do the following:

  1. Connect to the database server in a terminal window.
  2. Open postgresql.conf configuration file.
  3. Under the ERROR REPORTING AND LOGGING section of the file, use following config parameters. For more information on the following parameters, Click here.
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log’
log_truncate_on_rotation = off
log_rotation_age = 1d
log_min_duration_statement = 0
log_connections = on
log_duration = on
log_hostname = on
log_timezone = 'UTC'

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

sudo service postgresql restart

To install the Sumo Logic Collector, do the following:

  1. Login to your Sumo Logic account.
  2. Install a Sumo Logic Collector.
  3. Configure a local file source.

The following example uses an Ubuntu server with the log file path: /var/lib/postgresql/9.5/main/pg_log/*.log

 PostgreSQL_CollectionAndSources_Dialog.png

 

Collect Metrics from PostgreSQL

This section explains how to configure Collectd to collect PostgreSQL metrics and send data to Sumo Logic.

Collectd uses the following plugins to collect and send PostgreSQL metrics to Sumo Logic:

  • PostgreSQL plugin for Collectd—connects to and executes SQL statements on the PostgreSQL database. It then reads back the results and, depending on the configuration, the returned values are converted into collectd value lists. For more information, see the collectd wiki page for the PostgreSQL plugin.

  • Sumo Logic Collectd Plugin—built on top of the collectd-python plugin and sends collected metrics to a Sumo Logic HTTP endpoint.

Install and configure Collectd plugins

This section walks you through the process of installing and configuring the Collectd plugins.

To install and configure the Collectd plugins, do the following:

  1. Download and install the Collectd Plugin and Sumo Logic Collectd Plugin, following the instructions for Steps 1, 2, 3 (Option 1), and 4.

  2. Open the Collectd configuration file, named collectd.conf (for example, /etc/collectd/collectd.conf), in vi or a similar editor,  then uncomment the following:

LoadPlugin python

LoadPlugin postgresql

TypesDB "/usr/share/collectd/types.db" "/etc/collectd/my_types.db"

  1. To configure the PostgreSQL plugin for Collectd, in the collectd.conf file search for Plugin postgresql, then copy and paste the sample config from here and provide the following:

    • database_name: Name of the database you want to monitor

    • host: location of the postgresql host

    • username: database user that has permission to execute queries  

    • password: database user password

     Here is the sample Plugin postgresql config looks like for our environment.

  1. To configure the Sumo Logic Collectd Plugin, in the collectd.conf  file, search for Plugin python, then copy and paste sample config from here and provide the following:

    • URL: Sumo Logic HTTP endpoint url, you configured at Step 1.4 above

    • TypesDB: path to types.db, which is generally its at /usr/share/collectd/types.db

      Here is the sample Sumo Logic CollectD config looks like for our environment:

  1. Add custom metrics to types.db, open types.db file.(usually located at /usr/share/collectd/types.db ), search for pg_db_size, then add following custom metrics after that line:
pg_db_table_size        value:GAUGE:0:U
pg_replication_lag      value:GAUGE:0:U
pg_backend_connections  value:GAUGE:0:U
pg_index_scan_stat      value:GAUGE:0:U

     You can view a sample types.db file  here.

  1.  Restart Collectd.
service collectd stop
service collectd start