Here we will detail the necessary metrics to verify the performance on the PostgreSQL database engine and verify its status. The fundamental characteristics are:
- Active connections (SQL processing).
- Detailed use of tampons.
- Count of control points
- Inactive in transaction connections.
- Block expected connections.
- Use of the connection
- Slow consultations
- Committed to delayed transactions.
The necessary requirements for configuring the PostgreSQL monitreo are:
- Use versions greater than 9.2.
- User with trusted access in pg_hba
To start with the corresponding monitoring configuration, enter the server where the PostgreSQL database engine is located, and perform the following steps:
- Create fictitious database nubity:
postgres- # create database nubity;
- Add trusted user to the pg_hba.conf file. Check if you have a line that matches local connections in all databases and all users similar to this:
local all all md5
- In that case, insert this line before:
local nubity nubity trust
- Restart PostgreSQL
su - postgres /usr/pgsql-9.2/bin/pg_ctl reload
Note: the pg_ctl path may vary depending on the version of PostgreSQL
Then they should set the following values in the configuration of the addons:
- PostgreSQL IP address: Configure the IP address in which the server should listen for connections from client applications. The default value is 127.0.0.1.
- PostgreSQL port: Configure the port where the server listens. The default value is 5432.
- PostgreSQL user: Configure the authentication user to connect to PostgreSQL (access without password must be granted in pg_hba.conf)
- PostgreSQL Database: Configure the database used to log in to PostgreSQL (it could be an empty database, ie: nubity)