PostgreSQL
StackState Self-hosted v5.0.x

Overview

Get metrics from the PostgreSQL service in real time to:
  • Visualize and monitor PostgreSQL states
  • Received notifications about PostgreSQL failovers and events

Setup

Installation

The PostgreSQL check is included in the Agent V2 StackPack StackPack. To start gathering your PostgreSQL metrics and logs, install the Agent v2 first.

Configuration

Edit the postgres.d/conf.yaml file, in the conf.d/ folder at the root of your Agent's configuration directory to start collecting your PostgreSQL metrics and logs. See the sample postgres.d/conf.yaml for all available configuration options.

Prepare Postgres

To get started with the PostgreSQL integration, create a read-only stackstate user with proper access to your PostgreSQL server. Start psql on your PostgreSQL database and run:
For PostgreSQL version 10 and above:
1
create user stackstate with password '<PASSWORD>';
2
grant pg_monitor to stackstate;
Copied!
For older PostgreSQL versions:
1
create user stackstate with password '<PASSWORD>';
2
grant SELECT ON pg_stat_database to stackstate;
Copied!
Note: When generating custom metrics that require querying additional tables, you may need to grant the CONNECT permission on those tables to the stackstate user.
To verify the permissions are correct, run the following command:
1
psql -h localhost -U stackstate postgres -c \
2
"select * from pg_stat_database LIMIT(1);" \
3
&& echo -e "\e[0;32mPostgres connection - OK\e[0m" \
4
|| echo -e "\e[0;31mCannot connect to Postgres\e[0m"
Copied!
When it prompts for a password, enter the one used in the first command.

Metric Collection

  • Edit the postgres.d/conf.yaml file to point to your server / port and set the masters to monitor. See the sample postgres.d/conf.yaml for all available configuration options.
Option
Required
Description
username
No
The user account used to collect metrics, created in the Installation section above.
password
No
The password for the user account.
dbname
No
The name of the database you want to monitor.
ssl
No
Defaults to False. Indicates whether to use an SSL connection.
use_psycopg2
No
Defaults to False. Setting this option to True forces the StackState Agent to collect PostgreSQL metrics using psycopg2 instead of pg8000. Note: pyscopg2 does not support SSL connections.
tags
No
A list of tags applied to all metrics collected. Tags may be simple strings or key-value pairs.
relations
No
By default, all schemas are included. Add specific schemas here to collect metrics for schema relations. Each relation generates 10 metrics and an additional 10 metrics per index.
collect_function_metrics
No
Collect metrics regarding PL/pgSQL functions from pg_stat_user_functions.
collect_count_metrics
No
Collect count metrics. The default value is True for backward compatibility, but this might be slow. The recommended value is False.
collect_activity_metrics
No
Defaults to False. Collect metrics regarding transactions from pg_stat_activity. Make sure the user has sufficient privileges to read from pg_stat_activity before enabling this option.
collect_database_size_metrics
Yes
Collect database size metrics. Default value is True but this might be slow with large databases.
collect_default_database
No
Defaults to False. Include statistics from the default database postgres in the check metrics.
For PostgreSQL versions 9.6 and below, run the following and create a SECURITY DEFINER to read from pg_stat_activity.
1
CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity AS
2
$ SELECT * from pg_catalog.pg_stat_activity; $
3
LANGUAGE sql VOLATILE SECURITY DEFINER;
4
​
5
CREATE VIEW pg_stat_activity_dd AS SELECT * FROM pg_stat_activity();
6
grant SELECT ON pg_stat_activity_dd to stackstate;
Copied!
  • Restart the Agent to start sending PostgreSQL metrics to StackState.

Log Collection

PostgreSQL default logging is to stderr and logs do not include detailed information. It is recommended to log into a file with additional details specified in the log line prefix.
  • Edit your PostgreSQL configuration file /etc/postgresql/<version>/main/postgresql.conf and uncomment the following parameter in the log section:
    1
    logging_collector = on
    2
    log_directory = 'pg_log' # directory where log files are written,
    3
    # can be absolute or relative to PGDATA
    4
    log_filename = 'pg.log' #log file name, can include pattern
    5
    log_statement = 'all' #log all queries
    6
    log_line_prefix= '%m [%p] %d %a %u %h %c '
    7
    log_file_mode = 0644
    8
    ## For Windows
    9
    #log_destination = 'eventlog'
    Copied!
  • Collecting logs is disabled by default in the StackState Agent, enable it in stackstate.yaml:
    1
    logs_enabled: true
    Copied!
  • Add this configuration block to your postgres.d/conf.yaml file to start collecting your PostgreSQL logs:
    1
    logs:
    2
    - type: file
    3
    path: /var/log/pg_log/pg.log
    4
    source: postgresql
    5
    sourcecategory: database
    6
    service: myapp
    7
    #To handle multi line that starts with yyyy-mm-dd use the following pattern
    8
    #log_processing_rules:
    9
    # - type: multi_line
    10
    # pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])
    11
    # name: new_log_start_with_date
    Copied!
  • Change the service and path parameter values to configure for your environment. See the sample postgres.d/conf.yaml for all available configuration options.
  • Restart the Agent.

Validation

Run the Agent's status subcommand and look for postgres under the Checks section.

Data Collected

Some of the metrics listed below require additional configuration, see the sample postgres.d/conf.yaml for all configurable options.

Metrics

See metadata.csv for a list of metrics provided by this integration.

Events

The PostgreSQL check does not include any events.

Service Checks

postgres.can_connect Returns CRITICAL if the Agent is unable to connect to the monitored PostgreSQL instance. Returns OK otherwise.