PostgreSQL
Introduction
PostgreSQL, often referred to as Postgres, 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 is tailored for all versions of PostgreSQL greater than or equal to 14.
Data Collection Method
vuSmartMaps collects PostreSQL metrics data for this O11ySource by using an internal data collector. This agent collects data based on the source configuration. Logs for PostgreSQL are collected using Logbeat agent.
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 specific Oracle deployment you want to monitor.
- DB Details: Please provide the details of DB
- 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 utilized for Postgresql server's metrics.
- Query: Choose the Queries
- Polling Interval [seconds]: How frequently data is gathered. interval should be between 180 – 86400
- Postgres Log Path: Enter the PostgreSQL logfile's entire path
Firewall Requirement
To collect data from this O11ySource, ensure the following ports are opened:
| Source IP | Destination IP | Destination Port | Protocol | Direction |
|---|---|---|---|---|
| vuSmartMaps IP | IP address of the PostgreSQL server | 5432* | TCP | Outbound |
| IP address of the PostgreSQL server | vuSmartMaps Data Collector End Point | 9092* | TCP | Inbound |
*Before providing the firewall requirements, please update the port based on the customer environment.
Configuring the Target
Configure Metrics Collection from PostgreSQL Server
- To get started with the standard PostgreSQL integration, create a read-only vunet user with proper access to your PostgreSQL server.
- Start psql on your PostgreSQL database and run:
CREATE USER vunet with password '<PASSWORD>';
GRANT pg_monitor to vunet;
GRANT pg_read_all_stats to vunet;
GRANT SELECT ON pg_stat_database to vunet;
Note: For PostgreSQL versions 9.6 and below, run the following and create a SECURITY DEFINER to read from pg_stat_activity.
CREATE FUNCTION pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity AS $$ SELECT * from pg_catalog.pg_stat_activity; $$
LANGUAGE sql VOLATILE SECURITY DEFINER;
CREATE VIEW pg_stat_activity_dd AS SELECT * FROM pg_stat_activity();
grant SELECT ON pg_stat_activity_dd to vunet;
Note: When generating custom metrics that require querying additional tables, you may need to grant the SELECT permission on those tables to the vunet user. Example: GRANT SELECT on to vunet;
- To retrieve query latency statistics in PostgreSQL, ensure that the pg_stat_statements extension is enabled in your PostgreSQL configuration. If it's not enabled, you can enable it by adding or uncommenting the following line in your postgresql.conf file:
shared_preload_libraries = 'pg_stat_statements'
- Enable the pg_stat_statements extension is not installed or enabled in your PostgreSQL database.
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
- Then restart your PostgreSQL server. Additionally, make sure that you have the necessary permissions to access the pg_stat_statements view or extension.
GRANT SELECT ON pg_stat_statements TO vunet;
Configure Logs Collection from PostgreSQL Server
- The logs from PostgreSQL are collected by vuSmartMaps' Logbeat agent. PostgreSQL default logging is to stderr, and logs do not include detailed information. It is recommended to log into a file with additional details specified in the log line prefix. Logging is configured within the file /etc/postgresql//main/postgresql.conf or C:\Program Files\PostgreSQL\data\postgresql.conf by default. For regular log results, please use the following parameters in the log section:
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
Enablethe PostgreSQL O11ySource.- Select the Sources tab and press the
+button to add PostgreSQL instance details that has to be monitored. - Set up metrics and log collection configurations. The O11ysource already provides predefined metric collection queries. You have the flexibility to adjust query intervals, modify existing queries, and introduce new queries as needed. Afterwards, select Save and Continue to proceed with downloading the Logbeat agent.
- The following packages will be available for download based on the OS:Download Linux Logbeat Installation Binary - Downloads the full Logbeat Linux package with required configurations for a fresh installationDownload Linux Logbeat Update Configuration Binary - Downloads the agent configuration package to update an existing Logbeat Linux installationDownload Windows Logbeat Installation Binary - Downloads the full Logbeat Windows package with required configurations for a fresh installationDownload Windows Logbeat Update Configuration Binary - Downloads the agent configuration package to update an existing Logbeat Windows installation
- Download the agent installation or update package, then click
Finishto close the data source window.
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 |
| 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 |
