PostgreSQL

StackState Self-hosted v5.1.x

Overview

Get metrics from the PostgreSQL service in real time to:

• Visualize and monitor PostgreSQL states

• Received notifications about PostgreSQL failovers and events

PostgreSQL is a StackState curated integration.

Setup

Installation

The PostgreSQL check is included in the Agent V2 StackPack. To start gathering your PostgreSQL metrics and logs, install the 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 PostgreSQL

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:

create user stackstate with password '<PASSWORD>';
grant pg_monitor to stackstate;

For older PostgreSQL versions:

create user stackstate with password '<PASSWORD>';
grant SELECT ON pg_stat_database to stackstate;

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:

psql -h localhost -U stackstate postgres -c \
"select * from pg_stat_database LIMIT(1);" \
&& echo -e "\e[0;32mPostgreSQL connection - OK\e[0m" \
|| echo -e "\e[0;31mCannot connect to PostgreSQL\e[0m"

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 StackState to collect PostgreSQL metrics using psycopg2 instead of pg8000. Note: pyscopg2 doesn't 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.

CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity AS
$$ SELECT * from pg_catalog.pg_stat_activity; $$
LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE VIEW pg_stat_activity_dd AS SELECT * FROM pg_stat_activity();
grant SELECT ON pg_stat_activity_dd to stackstate;

• Restart the Agent to start sending PostgreSQL metrics to StackState.

Log Collection

PostgreSQL default logging is to stderr and logs don't include detailed information. It's 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:

  logging_collector = on
  log_directory = 'pg_log'  # directory where log files are written,
                            # can be absolute or relative to PGDATA
  log_filename = 'pg.log'   #log file name, can include pattern
  log_statement = 'all'     #log all queries
  log_line_prefix= '%m [%p] %d %a %u %h %c '
  log_file_mode = 0644
  ## For Windows
  #log_destination = 'eventlog'

• Collecting logs is disabled by default in StackState , enable it in stackstate.yaml:

  logs_enabled: true

• Add this configuration block to your postgres.d/conf.yaml file to start collecting your PostgreSQL logs:

  logs:
     - type: file
       path: /var/log/pg_log/pg.log
       source: postgresql
       sourcecategory: database
       service: myapp
       #To handle multi line that starts with yyyy-mm-dd use the following pattern
       #log_processing_rules:
       #  - type: multi_line
       #    pattern: \d{4}\-(0?[1-9]|1[012])\-(0?[1-9]|[12][0-9]|3[01])
       #    name: new_log_start_with_date

• 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 doesn't 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.