Datasentinel offers two monitoring approaches: agent-based and agentless. This guide focuses on the installation of an agent, applicable when you opt for the agent-based monitoring method.
Datasentinel requires the installation of the pg_stat_statements extension within your PostgreSQL instances.
Refer to the installation guide for pg_stat_statements and instructions on setting the recommended parameters.
The extension mut be installed on the internal database named postgres
The examples are based on a standard PostgreSQL installation with a server running the CentOS 7 operating system
Step-by-Step Installation Instructions
You need to install just one agent per server, which can effectively monitor multiple PostgreSQL clusters.
When required, the agent can also be installed on an intermediary server.
The agent registers its server name with the platform, enabling communication between the platform and the agent, as well as ensuring proper functionality of the agent CLI
If the server name is unknown (for example, not present in DNS servers), you can set the following environment variable before starting the agent:
// Example
export DATASENTINEL_AGENT_HOST=172.16.250.199
The server name will also be utilized to generate the distinctive pg_instance tag, uniquely identifying instances across the entire platform.
pg_instance is formed by concatenating the server name with @ and the connection name. Refer to Tags section for more details
4
Platform upload
The platform is where metrics are sent. In SaaS mode, it’s the name of the Datasentinel SaaS server.
The default port for https is 443, unless you’ve customized it during local platform installation.
// Setting the platform
datasentinel set server <<repository_server>> <<port>>
5
Token settings
A token is required to communicate with the platform.
The token is the license provided by the Datasentinel team. You can also copy the token from Agents Submenu in the User Interface.
// Setting the token
datasentinel set token <<token_value>>
The agent uses a self-signed certificate found in the config subdirectory.
cert_datasentinel.pem
key_datasentinel.pem
6
Check platform and token
The command checks the communication between the agent and the platform server
datasentinel show token
PostgreSQL
Example of configuring and adding a connection. Refer to PostgreSQL clusters for more details
User
Version >= 10
create user datasentinel password 'myPassword';
grant pg_monitor,pg_read_all_settings,pg_read_all_stats to datasentinel;
// If you intend to utilize the option for terminating a session directly from the User Interface
grant pg_signal_backend to datasentinel;
It's important to highlight that the user doesn't write data in PostgreSQL
Access is confined to read-only permissions solely for retrieving metrics.
Version < 10
The user must have the role superuser
create user datasentinel password 'myPassword';
alter user datasentinel with superuser;
The default setting for the PostgreSQL search_path variable is "$user", public.
If the search_path has been modified at the instance level, it's essential to ensure that the user account, typically datasentinel, includes the public schema in its search_path.
If the public schema is not included, you will need to update it accordingly.
ALTER USER datasentinel SET search_path TO "$user", public;
// Connect with datasentinel user
SHOW search_path;
File pg_hba.conf
Add authorization for the user datasentinel to connect to all databases with a password
# TYPE DATABASE USER ADDRESS METHOD
host all datasentinel 127.0.0.1/0 md5
The user needs to be able to connect to ALL databases.
A script is present in the datasentinel subdirectory as an example named connection_example.sh
The tags are customizable, allowing you to define your own tags.
They are extremely useful in the user interface for filtering, grouping data, and defining role-based access.
For a comprehensive list of options, simply type datasentinel without any arguments
datasentinel
Copyright 2023 (c) Datasentinel- All rights reserved www.datasentinel.io
================================================================================
Agent:
- start agent
- stop agent
- status agent
- show metrics
Show internal agent metrics and cache activity
- set port <port number>
- set collection-rate <low|high>
low: The sessions collection is done every 10 seconds
high: The sessions collection is done every second (Default value)
If the datasentinel extension is not installed, the collection-rate is automatically adjusted to low value
- set tables-monitoring-limit (default 1000)
The agent monitors the activity of tables and indexes if the number of tables in the connection is less than the defined limit
- set query-monitoring-limit (default 15000)
The agent tracks query activity, storing unique IDs in a daily cache. Exceeding limit triggers automatic deactivation of query monitoring.
Connections: when the connections are disabled, the agent is disconnected.
- enable all
- disable all
Connection:
- add connection <name> -f <json file>
- update connection <name> -f <json file>
json example: {
"host": "hostname",
"port": 4587,
"user": "username",
"password": "value",
"tags": "key=value,key=value,..."
}
- update connection <name> samples <on|off> (default off)
Collect and send sample queries, with literal values if present
- update connection <name> lock-monitoring <on|off> (default on)
Scan blocking sessions (longer than lock-monitoring-delay) and send blocking reports
- update connection <name> lock-monitoring-delay <seconds> (default 30)
Minimum number of seconds before triggering a blocking scenario
- update connection <name> table-monitoring <on|off> (default on)
Monitor the activity of tables and indexes (The number of tables must be lower than the maximum limit set at the agent level (default 1000)
- update connection <name> query-monitoring <on|off> (default on)
Monitor pg_stat_statements query activity
- update connection <name> query-monitoring-optimize <on|off> (default on)
Group similar pg_stat_statements queries
- update connection <name> query-monitoring-min-calls <calls> (default 2)
- update connection <name> query-monitoring-min-time <seconds> (default 1)
Retrieve pg_stat_statements queries executed n times (calls) OR with total execution time exceeding specified seconds
- update connection <name> test-query-monitoring-optimize
Test and optimize pg_stat_statements query contents with previous parameters, display results
- delete connection <name>
- enable connection <name>
- disable connection <name>
- show connections
- show connection <name>
Dump connection in JSON format:
- dump connection <name>
Upload server:
- set server <host> <port>
- show server
- test server
Token:
- set token <value>
- show token
Proxy:
- set proxy -f <json file>
json example: {
"host": "hostname",
"port": 4587,
"user": "username (optional)",
"password": "value (optional)"
}
- delete proxy
- show proxy
Most of the operations need a token to be passed in the headers calls.
Configuration Files
The agent stores its configuration on the hidden directory .datasentinel located in the user’s home directory.
2 files are present:
agent.yml
connections.yml
Agent properties can be directly modified through these files, except for passwords which are encrypted for security reasons.
Log File
The agent’s actions are recorded in the datasentinel.log file located in the log subdirectory of its distribution.
The log file is automatically rotated to remove outdated entries and manage data retention.
By default, the log level is set to INFO. To increase the log level, one can set it to debug in the [agent] section of the configuration file agent.yml.
