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
- Sample log message
- Query samples
- Collect logs from PostgreSQL
- Collect metrics from PostgreSQL
- Install and configure Collectd plugins
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;
This section provides samples for both metrics and log queries.
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
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.
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:
- Connect to the database server in a terminal window.
- Open postgresql.conf configuration file.
- 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:
The following example uses an Ubuntu server with the log file path:
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:
- Download and install the Collectd Plugin and Sumo Logic Collectd Plugin, following the instructions for Steps 1, 2, 3 (Option 1), and 4.
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"
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.
- 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
Here is the sample Sumo Logic CollectD config looks like for our environment:
- 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.
- Restart Collectd.
service collectd stop service collectd start