PostgreSQL
Overview
PostgreSQL, the powerful and open-source relational database management system, seamlessly integrates with Motadata AIOps to provide comprehensive monitoring and management capabilities. With this integration, organizations gain real-time visibility into the performance and health of their PostgreSQL databases. Monitor critical database metrics such as query execution times, transaction rates, and database size to ensure efficient data processing and retrieval.
Supported Versions
| Versions |
|---|
| 9.3 |
| 9.6 |
| 10 |
| 11 |
| 11.5(windows) |
| 15(linux) |
Prerequisites for PostgreSQL Integration with Motadata AIOps:
Ensure that the PostgreSQL port (default: 5432) is open for the Motadata AIOps server.
Ensure you have the necessary credentials, including the username and password, for Motadata AIOps to connect to the PostgreSQL database.
Ensure that JDBC is supported on the server where PostgreSQL is configured.
For agentless monitoring, ensure that the user has the required access for remote access to the PostgreSQL server. For agent-based monitoring, this is not required.
Ensure that the PostgreSQL service is active and running on the server.
Ensure you have the name of the PostgreSQL database that you want to monitor.
Update the pg_hba.conf configuration file by specifying the IPv4 or IPv6 address of the server where Motadata AIOps is installed.
Update the postgresql.conf configuration file by changing the listen_addresses to '[IP]', where 'IP' is the IP address of the server where Motadata AIOps is installed.
Confirm that the PostgreSQL process and service are listed in the process and service monitor settings of Motadata AIOps. While these may be listed by default, verify that the names of the service and process match the specific PostgreSQL version that you intend to monitor.
By following these prerequisites, you can integrate PostgreSQL with Motadata AIOps and ensure smooth functioning of the monitoring process.
List of Supported KPIs
| Name | Description | Type |
|---|---|---|
| system.tags | The system tags associated with the PostgreSQL instance. | String |
| postgresql.connection.used.percent | The percentage of used connections in the PostgreSQL instance. | Count |
| postgresql.active.connections | The number of active connections in the PostgreSQL instance. | Count |
| postgresql.users | The number of users connected to the PostgreSQL instance. | Count |
| postgresql.held.locks | The number of locks currently held in the PostgreSQL instance. | Count |
| postgresql.wait.locks | The number of locks waiting in the PostgreSQL instance. | Count |
| postgresql.active.queries | The number of active queries in the PostgreSQL instance. | Count |
| postgresql.block.hits.rate | The rate of block hits in the PostgreSQL instance. | Count |
| postgresql.block.reads.rate | The rate of block reads in the PostgreSQL instance. | Count |
| postgresql.cache.hit.ratio.percent | The percentage of cache hit ratio in the PostgreSQL instance. | Count |
| postgresql.commits.rate | The rate of commits in the PostgreSQL instance. | Count |
| postgresql.rollbacks.rate | The rate of rollbacks in the PostgreSQL instance. | Count |
| postgresql.inserted.rows.rate | The rate of inserted rows in the PostgreSQL instance. | Count |
| postgresql.updated.rows.rate | The rate of updated rows in the PostgreSQL instance. | Count |
| postgresql.deleted.rows.rate | The rate of deleted rows in the PostgreSQL instance. | Count |
| postgresql.data.directory | The data directory path of the PostgreSQL instance. | String |
| postgresql.configuration.directory | The configuration directory path of the PostgreSQL instance. | String |
| postgresql.ident.map.directory | The ident map directory path of the PostgreSQL instance. | String |
| postgresql.hba.directory | The directory path of the PostgreSQL HBA configuration files. | String |
| postgresql.external.pid.directory | The directory path for external PostgreSQL PID files. | String |
| postgresql.sequential.scans.rate | The rate of sequential scans in the PostgreSQL instance. | Count |
| postgresql.sequential.row.reads.rate | The rate of sequential row reads in the PostgreSQL instance. | Count |
| postgresql.indexes.scan.rate | The rate of index scans in the PostgreSQL instance. | Count |
| postgresql.indexes.fetched.row.rate | The rate of fetched rows from indexes in the PostgreSQL instance. | Count |
| postgresql.indexes.row.read.rate | The rate of row reads from indexes in the PostgreSQL instance. | Count |
| postgresql.hot.updated.rows.rate | The rate of hot updated rows in the PostgreSQL instance. | Count |
| postgresql.version | The version of PostgreSQL being used. | String |
| postgresql.maintenance.work.memory.bytes | The amount of memory used for maintenance work in PostgreSQL. | Count |
| postgresql.shared.buffer.bytes | The size of shared buffers in PostgreSQL. | Count |
| postgresql.effective.cache.size.bytes | The effective cache size in PostgreSQL. | Count |
| postgresql.auto.vacuum | The auto vacuum setting in PostgreSQL. | String |
| postgresql.work.memory.bytes | The amount of memory used for work in PostgreSQL. | Count |
| postgresql.maximum.connections | The maximum number of connections allowed in PostgreSQL. | Count |
| postgresql.wal.buffer.bytes | The size of the write-ahead log (WAL) buffers in PostgreSQL. | Count |
| postgresql.procedures | The number of procedures in the PostgreSQL database. | Count |
| postgresql.triggers | The number of triggers in the PostgreSQL database. | Count |
| postgresql.tables | The number of tables in the PostgreSQL database. | Count |
| postgresql.index.size.bytes | The size of indexes in the PostgreSQL database. | Count |
| postgresql.data.size.bytes | The size of data in the PostgreSQL database. | Count |
| postgresql.max.clean.buffers | The maximum number of clean buffers in PostgreSQL. | Count |
| postgresql.clean.buffers | The number of clean buffers in PostgreSQL. | Count |
| postgresql.requested.checkpoints | The number of requested checkpoints in PostgreSQL. | Count |
| postgresql.waiting.queries | The number of queries waiting in PostgreSQL. | Count |
| postgresql.checkpoint.buffers | The number of buffers used for checkpoints in PostgreSQL. | Count |
| postgresql.fetched.rows.rate | The rate of fetched rows in PostgreSQL. | Count |
| postgresql.allocated.buffers | The number of allocated buffers in PostgreSQL. | Count |
| postgresql.backend.fsync.buffers | The number of backend fsync buffers in PostgreSQL. | Count |
| postgresql.ideal.transactions | The number of ideal transactions in PostgreSQL. | Count |
| postgresql.checkpoint.write.time.ms | The time taken for checkpoint writes in PostgreSQL (in ms). | Count |
| postgresql.checkpoint.sync.time.ms | The time taken for checkpoint syncs in PostgreSQL (in ms). | Count |
| postgresql.returned.rows.rate | The rate of returned rows in PostgreSQL. | Count |
| postgresql.deadlocks | The number of deadlocks in PostgreSQL. | Count |
| postgresql.backend.buffers | Number of buffers allocated for backends in PostgreSQL | Count |
| postgresql.scheduled.checkpoints | Total number of scheduled checkpoints in PostgreSQL | Count |
| postgresql.temporary.bytes | Total temporary disk space used in PostgreSQL (bytes) | Count |
| postgresql.temporary.files | Total number of temporary files used in PostgreSQL | Count |
| postgresql.before.xid.wraparound.transactions | Total number of transactions before XID wraparound in PostgreSQL | Count |
| postgresql.table.space | PostgreSQL table space | String |
| postgresql.table.space.size.bytes | Size of PostgreSQL table space in bytes | Count |
| postgresql.table.space.owner | Owner of PostgreSQL table space | String |
| postgresql.table.space.location | Location of PostgreSQL table space | String |
| correlation.metrics | Count of correlation metrics recorded | Count |
| postgres.held.locks | Count of locks currently held in PostgreSQL | Count |
| postgres.wait.locks | Count of locks for which processes are waiting in PostgreSQL | Count |
| postgres.session.lock.id | Identifier of a lock acquired by a PostgreSQL session | String |
| postgres.session.lock.mode | Mode of the lock acquired by a PostgreSQL session | Count |
| postgres.session.lock.granted | Status indicating if a lock is granted or not | String |
| postgres.session.lock.type | Type of lock acquired by a PostgreSQL session | String |
| postgres.session.id | Identifier of a PostgreSQL session | String |
| postgres.session.username | Username associated with a PostgreSQL session | Count |
| postgres.session.query | Currently executing query in a PostgreSQL session | String |
| postgres.session.application | Application associated with a PostgreSQL session (random value) | String |
| postgres.session.state | State of a PostgreSQL session (random value) | String |
| postgres.session.remote.client | Remote client information for a PostgreSQL session (random value) | String |
| postgres.session.start.time | Start time of a PostgreSQL session (random value) | String |
| correlation.metrics | Count of correlation metrics recorded | Count |
| postgres.held.locks | Count of locks currently held in PostgreSQL | Count |
| postgres.wait.locks | Count of locks for which processes are waiting in PostgreSQL | Count |
| postgres.session.lock.id | Identifier of a lock acquired by a PostgreSQL session | String |
| postgres.session.lock.mode | Mode of the lock acquired by a PostgreSQL session | Count |
| postgres.session.lock.granted | Status indicating if a lock is granted or not | String |
| postgres.session.lock.type | Type of lock acquired by a PostgreSQL session | String |
| postgres.session.id | Identifier of a PostgreSQL session | String |
| postgres.session.username | Username associated with a PostgreSQL session | Count |
| postgres.session.query | Currently executing query in a PostgreSQL session | String |
| postgres.session.application | Application associated with a PostgreSQL session (random value) | String |
| postgres.session.state | State of a PostgreSQL session (random value) | String |
| postgres.session.remote.client | Remote client information for a PostgreSQL session (random value) | String |
| postgres.session.start.time | Start time of a PostgreSQL session (random value) | String |
| postgresql.unused.index | Unused index in PostgreSQL | String |
| postgresql.unused.index.schema.name | Schema name of the unused index | String |
| postgresql.unused.index.table.name | Table name of the unused index | String |
| postgresql.unused.index.size.bytes | Size of the unused index in bytes | Count |
| postgresql.index | Index in PostgreSQL | String |
| postgresql.index.scans | Number of scans on the index | Count |
| postgresql.index.schema.name | Schema name of the index | String |
| postgresql.index.table.name | Table name of the index | String |
| postgresql.index.read.tuples | Number of tuples read from the index | Count |
| postgresql.index.fetched.tuples | Number of tuples fetched from the index | Count |
| postgresql.index.size.bytes | Size of the index in bytes | Count |
| postgresql.index.rows | Number of rows in the index | Count |