Monitoring PostgreSQL

Enable statistics collection

Collection of statistics is controlled by configuration parameters that are set in the postgresql.conf file, which is created on configuring the postgresql. To monitor the collection of statistics, set the following parameters to on (yes, true, and 1 are also accepted):

For more information about the statistics collection configuration, see the PostgreSQL documentation.

Create a monitoring user

Minimal permission for monitoring the metric collection is SELECT on the pg_stat_database database. The following example shows how to create a user for agent's needs:

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

Sensor

To configure the authentication that is required when the sensor connects to the PostgreSQL, complete the following steps:

1. Configure the user credentials by enabling the configuration section in the agent configuration file <agent_install_dir>/etc/instana/configuration.yaml.

2. Enter a username and password.

   The password is a clear-text password.

For password-based authentication, the authentication mechanism of the corresponding PostgreSQL user must be md5, scram-sha-256 (version 10 and later), or a password. For more information, see the Password Authentication and the Client Authentication section in the PostgreSQL documentation.

For a connection to be established between the sensor and PostgreSQL, PostgreSQL must contain a database with a name identical to the username:

com.instana.plugin.postgresql:
  user: '<USERNAME>'
  password: '<PASSWORD>'
  database: '' # by default PostgreSQL will use 'user' as database to connect to.

The Database field is used for authentication only. Agent automatically reads the changes to the configuration file <agent_install_dir>/etc/instana/configuration.yaml. Therefore, changes to this configuration file are hot reloaded (no agent restart is necessary).

To collect database metrics, you must assign the specified user the CONNECT privilege for all the databases that are present within the PostgreSQL instance. The following example shows how to grant the CONNECT privilege to a user:

GRANT CONNECT ON DATABASE <DATABASE_NAME> TO <USERNAME>;

Configuration data

See the following configuration data that is collected from the running Postgres instance:

• Process ID
• Start time
• Port
• Version
• Role
• Max connections

Performance metrics

The following are the performance metrics that are collected for each Postgres instance:

Health signatures

Each sensor has a curated knowledgebase of health signatures that are evaluated continuously against the incoming metrics and are used to raise issues or incidents that depend on user impact.

Built-in events trigger issues or incidents based on failing health signatures on entities, and custom events trigger issues or incidents based on the thresholds of an individual metric of any specific entity.

For more information about built-events for the PostgreSQL sensor, see the Built-in events reference.

PostgreSQL stats not enabled

Monitoring issue type: postgresql_stats_not_enabled

The PostgreSQL server is detected, but statistics collection is not enabled. You need to enable statistics collection. For more information, see the Enable statistics collection section.

PostgreSQL authentication failed

Monitoring issue type: postgresql_authentication_failed

The PostgreSQL authentication failed. To create a monitoring user and configure the agent, refer to the Create a monitoring user section.

PostgreSQL connection failed

Monitoring issue type: postgresql_connection_failed

Agent fails to connect to the PostgreSQL server. You need to verify that the connection is established between the agent and the sensor.