PostgreSQL

Configuration

PostgreSQL

Set track_counts, track_activities, track_io_timing to on (yes, true and 1 are also accepted) in PostgreSQL. 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.

User permission

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 below in the agent /etc/instana/configuration.yml and providing a username and password (please 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: ''
  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).

Metrics collection

Configuration data

  • Port
  • Max connections
  • Version
  • Role
  • Started At

Performance metrics

Postgres server

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

Databases

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

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.