The PostgreSQL sensor is automatically deployed and installed after you install the Instana agent.
- Supported operating systems
- Supported versions
- Metrics collection
Supported operating systems
The supported operating systems of the PostgresQL sensor are consistent with host agent requirements, which can be checked in the Supported operating systems section of each host agent, such as Supported operating systems for Unix.
Instana supports the following versions of PostgresQL:
- PostgresQL 8.0.x to 8.4.x
- PostgresQL 9.0.x to 9.6.x
- PostgresQL 10.x
- PostgresQL 11.x
- PostgresQL 12.x
- PostgresQL 13.x
- PostgresQL 14.x
- PostgresQL 15.x
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
1 are also accepted):
||Monitors the current command that is run by any server process.|
||Monitors the cumulative statistics that is collected related to the table and index accesses.|
||Monitors block read and write times.|
For more information about the statistics collection configuration, see the PostgreSQL documentation.
To display the detail query count, you must load the
pg_stat_statements extension by configuring the
shared_preload_libraries field in the
postgresql.conf file as follows:
shared_preload_libraries = 'pg_stat_statements'
For more information about
shared_preload_libraries field, see Shared library preloading.
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>;
To configure the authentication that is required when the sensor connects to the PostgreSQL, complete the following steps:
Configure the user credentials by enabling the configuration section in the agent configuration file
Enter a username and password.
Note: The password is a clear-text password.
For password-based authentication, the authentication mechanism of the corresponding PostgreSQL user must be md5 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
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>;
To view the metrics, complete the following steps:
- Select Infrastructure in the sidebar of the Instana user interface.
- Click a specific monitored host to view the host dashboard with all the collected metrics and monitored processes.
See the following configuration data that is collected from the running Postgres instance:
- Process ID
- Start time
- Max connections
The following are the performance metrics that are collected for each Postgres instance:
|Total Committed Transactions||Number of transactions that are 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 the master server and the replica server is displayed in bytes and seconds.||1 second|
|Committed Transactions||Number of transactions that are committed in this database.||1 second|
|Rolled Back Transactions||Number of transactions that are rolled back in this database.||1 second|
|Cache Hit Ratio||Percentage of disk blocks that are found in the buffer cache so that a read is not necessary.||1 second|
|Standby Conflicts||Number of queries that are canceled due to conflicts with recovery in this database.||1 second|
|Tuples Returned||Number of live rows that are fetched by sequential scans plus the number of index entries that are returned by index scans in this database.||1 second|
|Tuples Fetched||Number of live rows that are fetched by index scans in this database.||1 second|
|Database Size||Disk space that is used by this database.||1 second|
|Active Connections||Number of active connections to this database.||1 second|
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.
Use these troubleshooting entries to help resolve problems that are related to PostgreSQL. An error message with the stated issue type and a brief explanation on what went wrong is displayed on the Postgresql dashboard on Instana, and also in the logs.
PostgreSQL stats not enabled
Monitoring issue type:
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:
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:
Agent fails to connect to the PostgreSQL server. You need to verify that the connection is established between the agent and the sensor.