PostgreSQL
Introduction
PostgreSQL is a powerful open-source relational database management system (RDBMS). It is known for its extensibility, standards compliance, and robust feature set.
Getting Started
Compatibility
The PostgreSQL O11ySource supports PostgreSQL versions 14 and later.
Data Collection Method
vuSmartMaps collects health and performance data from Postgresql server through a data collector deployed within the vuSmartMaps. Logs are collected using the log collection probe of the OmniAgent.
Prerequisites
Dependent Configuration
To configure this O11ySource, create a 'credential' of type 'user' under the 'Definition' tab.
Inputs for Configuring Data Source
- Instance Name: Please enter the name of the PostgreSQL instance. This should be a unique identifier for the PostgreSQL deployment you want to monitor.
- DB Details: Provide the database connection details.
- IP Address: IP address needs to be a valid IP Address.
- Port: Enter a valid port.
- Credential: This field is required.
- DB Name: Name of the database to be polled for metrics
- DB Mode: Choose the Collection Mode
- Metrics Collection Queries: These queries are used to collect PostgreSQL server metrics.
- Query: Enter the SQL query to execute.
- Polling Interval [seconds]: This field is optional.
- Collection Offset [in seconds]: Specify the delay (in seconds) from the scheduled execution time to avoid simultaneous execution of queries.
- Custom SQL Queries: Optional.
- Query Name: Enter a unique query name (max 63 chars; letters/numbers/_/-).
- Query Topic: This field is required.
nan
- PostgreSQL Log Path: Enter the full path to the PostgreSQL log file.
- Encoding: Select the file encoding to correctly read logs that contain international characters. If no encoding is selected, plain (ASCII) will be used by default.
- Max Bytes: The maximum number of bytes that a single log message can have. All bytes after max_bytes are discarded and not sent. This setting is especially useful for multiline log messages, which can get large. The default is 10MB (10485760).
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps Collection/Ingress node IP | IP address or FQDN of the PostgreSQL server | 5432 | TCP | Outbound |
| IP address or FQDN of the PostgreSQL server | vuSmartMaps Collection/Ingress node IP | 443* | TCP | Inbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
Configure metrics collection from PostgreSQL Metrics from PostgreSQL are collected by establishing a JDBC connection to the server.
To get started, create a read-only vunet user with the required access on your PostgreSQL server.
Start psql on your PostgreSQL server and run:
CREATE USER vunet WITH PASSWORD '<PASSWORD>';
GRANT pg_monitor TO vunet;
GRANT pg_read_all_stats TO vunet;
To retrieve query latency statistics, enable pg_stat_statements.
Add or uncomment the following line in postgresql.conf and restart PostgreSQL:
shared_preload_libraries = 'pg_stat_statements'
Then enable the extension in the required database:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
GRANT SELECT ON pg_stat_statements TO vunet;
Configure Log Collection from PostgreSQL PostgreSQL logs are collected using VuNet’s logs collection probe.
By default, PostgreSQL logs may go to stderr. To log into a file with useful details, update the logging section in:
/etc/postgresql/\<VERSION\>/main/postgresql.conf* (Linux) orC:\Program Files\PostgreSQL\\<VERSION\>\data\postgresql.conf* (Windows).
Recommended parameters:
logging_collector = on
log_directory = 'pg_log'
log_filename = 'pg.log'
log_line_prefix = '%m [%p] %d %a %u %h %c'
log_file_mode = 0644
log_min_duration_statement = 0
log_timezone = 'Etc/UTC'
Configuration Steps
Metrics Collected
| Name | Description | Data Type |
|---|---|---|
| doc_type | Type of document or record. | String |
| target | The target system or database being referenced. | String |
| @timestamp | Timestamp in string format. | String |
| timestamp | Timestamp in DateTime64 format with millisecond precision. | DateTime64(3) |
| buffers_backend | Total number of backend buffers used. | UInt64 |
| buffers_backend_diff | Difference in the backend buffers since the last measurement. | UInt64 |
| buffers_checkpoint | Total number of buffers written during checkpoints. | UInt64 |
| buffers_clean | Total number of buffers cleaned. | UInt64 |
| buffers_clean_diff | Difference in the cleaned buffers since the last measurement. | UInt64 |
| checkpoints_req | Number of requested checkpoints. | UInt64 |
| checkpoints_req_diff | Difference in the number of requested checkpoints since the last measurement. | UInt64 |
| checkpoints_timed | Number of timed checkpoints. | UInt64 |
| checkpoints_timed_diff | Difference in the number of timed checkpoints since the last measurement. | UInt64 |
| db | Name of the database. | String |
| state | Current state of the system or process. | String |
| commit_ratio | The ratio of committed transactions. | Float64 |
| formatted_size | Formatted size (likely human-readable). | String |
| size | Size of the database or record in bytes. | UInt64 |
| application_name | Name of the application making the request or transaction. | String |
| backend_start | Start time of the backend process. | DateTime |
| backend_type | Type of backend system (e.g., connection, service). | String |
| process_id | Unique identifier for the backend process. | UInt32 |
| query | SQL query being executed. | String |
| wait_event | Event the process is waiting for, if any. | String |
| wait_event_type | Type of wait event. | String |
| uptime | Time the system has been running (in seconds or milliseconds). | UInt64 |
| blks_hit | Number of blocks hit (cache hits). | UInt64 |
| blks_hit_diff | Difference in the number of blocks hit since the last measurement. | UInt64 |
| blks_read | Number of blocks read from disk. | UInt64 |
| blks_read_diff | Difference in the number of blocks read since the last measurement. | UInt64 |
| deadlocks | Number of deadlocks encountered. | UInt32 |
| deadlocks_diff | Difference in the number of deadlocks since the last measurement. | UInt32 |
| numbackends | Number of backend processes. | UInt32 |
| tup_deleted | Number of tuples (rows) deleted. | UInt64 |
| tup_deleted_diff | Difference in the number of tuples deleted since the last measurement. | UInt64 |
| tup_fetched | The number of tuples (rows) fetched from the database. | UInt64 |
| tup_fetched_diff | The difference in the number of tuples fetched since the last recorded value. | UInt64 |
| tup_inserted | The number of tuples inserted into the database. | UInt64 |
| tup_inserted_diff | The difference in the number of tuples inserted since the last recorded value. | UInt64 |
| tup_returned | The number of tuples returned by the database. | UInt64 |
| tup_returned_diff | The difference in the number of tuples returned since the last recorded value. | UInt64 |
| tup_updated | The number of tuples updated in the database. | UInt64 |
| tup_updated_diff | The difference in the number of tuples updated since the last recorded value. | UInt64 |
| xact_commit | The number of transactions committed. | UInt64 |
| xact_commit_diff | The difference in the number of transactions committed since the last recorded value. | UInt64 |
| xact_rollback | The number of transactions rolled back. | UInt64 |
| xact_rollback_diff | The difference in the number of transactions rolled back since the last recorded value. | UInt64 |
| mode | The current mode of the database or session (e.g., read, write). | String |
| databaseid | Unique identifier of the PostgreSQL database where the long-running query is executing | UInt64 |
| usesysid | Internal PostgreSQL user ID associated with the session executing the query. | UInt64 |
| databasename | Name of the database. | String |
| query_run_time | Total duration for which the query has been running | String |
| query_start_time | Timestamp indicating when the query execution started | UInt64 |
| name | Type or category of operation generating the query (e.g., SQL execution). | String |
| num_locks | The number of locks currently held in the database. | UInt32 |
| cache_hit_ratio | The ratio of cache hits to total requests, indicating the efficiency of cache usage. | Float64 |
| client_address | The address of the client connected to the database. | String |
| username | The username used to connect to the database. | String |
| duration_ms | The duration of the query or transaction in milliseconds. | UInt32 |
| pid | The process ID of the database session. | UInt32 |
| receiving_lag | The lag in data being received by the database, typically in seconds. | Float64 |
| replaying_lag | The lag in replaying WAL (Write-Ahead Logging) entries. | Float64 |
| sending_lag | The lag in data being sent from the database, typically in seconds. | Float64 |
| total_lag | The total lag across all operations in the database, typically in seconds. | Float64 |
| rows_processed | The total number of rows processed during the operation. | UInt32 |
| avg_latency_seconds | The average latency of operations, typically in seconds. | Float64 |
| max_latency_seconds | The maximum latency recorded during operations, typically in seconds. | Float64 |
| wal_size_formatted | The formatted size of the Write-Ahead Logging (WAL) data. | String |
| wal_size | The size of the WAL data, typically in bytes. | Float64 |
| message | The log message containing information about the event or error. | String |
| timestamp | The timestamp when the log entry was recorded, with millisecond precision. | DateTime64(3) |
| log_type | The type of log entry (e.g., error, info, warning). | String |
| severity | The severity level of the log entry (e.g., DEBUG, INFO, WARN, ERROR). | String |
| database | The name of the database associated with the log entry. | String |
| lg_info | Additional logging information or context relevant to the log entry. | String |
| timezone | The timezone in which the timestamp is recorded. | String |
| client | The name or identifier of the client making the database request. | String |
| user | The username of the individual or application accessing the database. | String |
| duration_ms | The duration of the logged operation in milliseconds. | Float64 |
| process_id | The process ID associated with the logged operation. | UInt32 |
| log_level | The log level indicating the importance of the log entry. | String |
| statement | The SQL statement or query executed that generated the log entry. | String |
| session_id | The unique identifier for the session during which the log entry was created. | String |
| client_ip | The IP address of the client making the request to the database. | String |
| bind_or_execute | Indicates whether the log entry pertains to a binding or executing operation. | String |
| statement_id | A unique identifier for the statement that generated the log entry. | String |
| host | The hostname or identifier of the server where the database is hosted. | String |
| target | The target resource or operation being logged. | String |
| DBName | An alternative name for the database being logged, if applicable. | String |
