Monitoring PostgreSQL

The PostgreSQL sensor is automatically deployed and installed after you install the Instana agent.

Configuration

Enable statistics collection

Collection of statistics is controlled by configuration parameters that are normally set in postgresql.conf. The following parameters need to be set to on (yes, true and 1 are also accepted) to enable collection of statistics:

Parameter Description
track_activities = on Enables monitoring of the current command being executed by any server process.
track_counts = on Controls whether statistics are collected about table and index accesses.
track_io_timing = on Enables monitoring of block read and write times.

For more information see the PostgreSQL documentation.

To display the detail query count, the pg_stat_statements extension must be loaded via shared_preload_libraries in postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

More info about shared_preload_libraries can be found here.

Create a monitoring user

Minimal permission for monitoring is SELECT on pg_stat_database. This 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

When the sensor connects to the PostgreSQL, it might need to authenticate itself. User credentials can be configured by enabling the configuration section as follows in the agent configuration file <agent_install_dir>/etc/instana/configuration.yaml and providing a username and password ( note that this is a clear-text password!). In the case of password-base authentication, the authentication mechanism of the corresponding PostgreSQL user has to be md5 or a password. Please refer to the "Client Authentication" section in the PostgreSQL documentation for more information.

For a connection to be established, PostgreSQL should also contain a database with a name identical to the user name:

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. Changes to this file are hot reloaded (no agent restart is necessary).

The specified user should be assigned the CONNECT privilege for all databases present within the PostgreSQL instance in order to collect database metrics. The example shows how to grant the CONNECT role:

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

Metrics collection

To view the metrics, select Infrastructure in the sidebar of the Instana User interface, click a specific monitored host, and then you can see a host dashboard with all the collected metrics and monitored processes.

Configuration data

Performance metrics

PostgreSQL server

Metric Description Granularity
Total Committed Transactions Number of transactions that have been committed across all databases. 1 second
Total Active Connections Number of active connections across all databases. 1 second
Connection Usage Number of active connections as a fraction of the maximum number of allowed connections. 1 second
Replication Delay (Available only for replications) Replication delay between master and replica displayed in bytes and seconds. 1 second

Databases

Metric Description Granularity
Committed Transactions Number of transactions that have been committed in this database. 1 second
Rolled Back Transactions Number of transactions that have been rolled back in this database. 1 second
Cache Hit Ratio Percentage of disk blocks found already in the buffer cache, so that a read was not necessary. 1 second
Standby Conflicts Number of queries canceled due to conflicts with recovery in this database. 1 second
Tuples Read Number of index entries returned by scans on this index. 1 second
Tuples Fetched Number of live rows fetched by index scans. 1 second
Database Size Disk space used by this database. 1 second
Active Connections Number of active connections to this database. 1 second

Health signatures

For each sensor, there is a curated knowledgebase of health signatures that are evaluated continuously against the incoming metrics and are used to raise issues or incidents depending 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 given entity.

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

Troubleshooting

PostgreSQL stats not enabled

Monitoring issue type: postgresql_stats_not_enabled

Detected PostgreSQL server but statistics collection is not enabled by server configuration. To enable statistics collection, refer to 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 could not connect to PostgreSQL server. Verify that the connection between the agent and the sensor can be established.