Monitoring PostgreSQL
The PostgreSQL sensor is automatically deployed and installed after you install the Instana agent.
Supported information
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.
Supported versions
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
The PostgreSQL versions supported on Instana are listed in the following table:
Sensor | Support policy | Latest version | Last supported version |
---|---|---|---|
PostgreSQL | 45 days | 16.2 | 15.x |
Supported client-side tracing
For this technology, Instana supports client-side tracing for the following languages and runtimes:
Configuration
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):
Parameter | Description |
---|---|
track_activities = on |
Monitors the current command that is run by any server process. |
track_counts = on |
Monitors the cumulative statistics that is collected related to the table and index accesses. |
track_io_timing = on |
Monitors block read and write times. |
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:
-
Configure the user credentials by enabling the configuration section in the agent configuration file
<agent_install_dir>/etc/instana/configuration.yaml
. -
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>;
Metrics collection
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.
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:
PostgreSQL server
Metric | Description | Granularity |
---|---|---|
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 primary server and the replica server is displayed in bytes and seconds. | 1 second |
Databases
Metric | Description | Granularity |
---|---|---|
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 |
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.
Troubleshooting
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: 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.