Configuring PostgreSQL monitoring

Edit online

You must configure the Monitoring Agent for PostgreSQL so that the agent can collect data from the PostgreSQL database that is being monitored.

Before you begin

You must install the PostgreSQL JDBC driver before you install this agent. The path to this driver is required at the time of agent configuration.

JDBC type 4 driver is the new version and hence preferable. User can install the subtype of JDBC 4 version according to the JDK version the agent uses. For mapping of JDBC version to JDK version get more information at https://jdbc.postgresql.org/download.html.

The pg_hba.conf file is the PostgreSQL database file that contains authentication settings. When the auth-method parameter value is set to ident in the pg_hba.conf file, the PostgreSQL agent cannot connect to the PostgreSQL database. Ensure that the authentication settings for the auth-method parameter are correct. For example, you can set these values for auth-method parameter: md5, trust, or password.

For remote monitoring, pg_hba.conf file should be updated according to SSL configuration done in postgresql.conf file.
If SSL is off then host entry should be added:

Table 1.
host	<DB name>	<user name>	<Agent machine IP>/32	<auth-method>

If SSL is on then hostssl entry should be added

Table 2.
hostssl	<DB name>	<user name>	<TEMA IP>/32	cert

Here, DB name is specific database name or you can enter all, user name is default user name postgres or you can enter all, auth-method can be md5 or password or trust.

Note: host or hostssl entry is not required for PostgreSQL server local monitoring.

A few of the attributes collected by the agent rely on the pg_stat_statements extension. To add pg_stat_statements first install the package postgresql-contrib. You must modify the postgresql.conf configuration file in order for the PostgreSQL server to load the pg_stat_statements extension.

Open the postgresql.conf file in a text editor and update the shared_preload_libraries line:
```
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track_utility = false
```
These changes are required to monitor SQL statements, except utility commands.
Note: The status of pg_stat_statements.track_utility is set or modified by a superuser only.
Update the connection settings section in postgresql.conf file listen_addresses = <Agent machine IP> here, mention specific agent IP or * to listen all IP addresses.
Note: This setting is required for remote monitoring.
Restart the PostgreSQL server after you update and save the postgresql.conf.
Run the following SQL command by using psql, that should be connected to the same database that would be provided later in the agent configuration for JDBC connectivity:
```
create extension pg_stat_statements; 
select pg_stat_statements_reset();
```
Note: The command create extension and function pg_stat_statements_reset() are run by a superuser only.
The view pg_stat_statements needs to be enabled for specific database, for more details refer https://www.postgresql.org/docs/9.6/static/pgstatstatements.html.

Review hardware and software prerequisites. For the up-to-date system requirement information, see the Software Product Compatibility Reports (SPCR) for the PostgreSQL agent.

About this task

The PostgreSQL agent is a multiple instance agent; you must create the first instance and start the agent manually. The managed system name includes the instance name that you specify, for example, instance_name:host_name:pc, where pc is your two character product code. The managed system name is limited to 32 characters. The instance name that you specify is limited to 28 characters, minus the length of your host name. For example, if you specify PostgreSQL2 as your instance name, your managed system name is PostgreSQL2:hostname:PN.

Important: If you specify a long instance name, the managed system name is truncated and the agent code is not displayed completely.

The product version and the agent version often differ. The directions here are for the most current release of this agent. For information about how to check the version of an agent in your environment, see Agent version command. For detailed information about the agent version list and what's new for each version, see the Change history.

What to do next

Log in to the Cloud APM console to view the data that is collected by the agent in the dashboards. For information about using the Cloud APM console, see Starting the Cloud APM console.