Supported Versions |
---|
9.2 or higher |
JDBC: 42.2.15 |
Introduction
PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance.
Discovery with the agent
Collector Type: Agent
Category: Application Monitors
Application Name: PostgreSQL
Global Template Name: Linux - PostgreSQL Monitors
Prerequisites
- Python postgres driver package (python-psycopg2) is required for agent to connect to postgresql.
- Gateway version: 17.2.0
Parameters
Name | Default Value | Description |
---|---|---|
Host IP Address | 127.0.0.1 | The host on which PostgreSQL is running. |
Port | 5432 | TThe port on which PostgreSQL is running. |
User Name | postgres | The username of the PostgreSQL. |
Password | postgres | The password of the PostgreSQL. |
DBName | postgres | The name of the database to connect. |
Note: All field attributes are mandatory, use default values wherever applicable.
Collected Metrics
Metric Name | Display Name | Description |
---|---|---|
postgres.autovac.freeze | PostgreSQL Autovac Freeze | Provides the percentage of current transactions to the max freeze number. |
postgres.ping | PostgreSQL Ping | Provides the Ping response time of PostgreSQL database. |
postgres.bloat | PostgreSQL Bloat | Checks the amount of bloat in tables and indexes. |
postgres.conn.idle.tnx | PostgreSQL Connections Idle Tnx | Checks the number connections "idle in transaction" state. |
postgres.conn.idle | PostgreSQL Connections Idle | Checks number of connections idle in given state. |
postgres.conn.total | PostgreSQL Connections Total | Checks total number of connections in given state. |
postgres.conn.running | PostgreSQL Connections Running | Checks number of connections running in given state. |
postgres.conn.waiting | PostgreSQL Connections Waiting | Checks number of connections waiting in given state. |
postgres.tnxage.idle.tnx | PostgreSQL Tnxage IdleTnx | Checks the number and duration of "idle in transaction" queries on one or more databases. |
postgres.tnxage.running | PostgreSQL Tnxage Running | Checks the number and duration of "running transaction" queries on one or more databases. |
postgres.wal | PostgreSQL Wal | Checks how many WAL files exist in the pg_xlog directory. |
postgres.locks.all | PostgreSQL Locks | Checks the total number of locks on one or more databases. |
postgres.locks.granted | PostgreSQL Locks Granted | Checks the total number of locks are granted on one or more databases. |
postgres.locks.not.granted | PostgreSQL Locks Not Granted | Checks the total number of locks are not granted on one or more databases. |
postgres.dbstat.commit | PostgreSQL Commits | The total number of commits for this database since it was created or reset. |
postgres.dbstat.rollback | PostgreSQL Rollbacks | The total number of rollbacks for this database since it was created or reset. |
postgres.dbstat.reads | PostgreSQL Reads | Reports information from the pg_stat_database view, The total number of disk blocks read. |
postgres.dbstat.hits | PostgreSQL Hits | Reports information from the pg_stat_database view , The total number of buffer hits. |
postgres.dbstat.tup.returned | PostgreSQL Tup Returned | Reports information from the pg_stat_database view, The total number of tuples returned. |
postgres.dbstat.tup.fetched | PostgreSQL Tup Fetched | Reports information from the pg_stat_database view, The total number of tuples fetched. |
postgres.dbstat.tup.inserted | PostgreSQL Tup Inserted | Reports information from the pg_stat_database view, The total number of tuples inserted. |
postgres.dbstat.tup.updated | PostgreSQL Tup Updated | Reports information from the pg_stat_database view, The total number of tuples updated. |
postgres.dbstat.tup.deleted | PostgreSQL Tup Deleted | Reports information from the pg_stat_database view, The total number of tuples deleted. |
postgres.backends | PostgreSQL Backends | Check the number of connections to the database. Compares it with the Max_connection provided in the postgres conf file. |
postgres.db.size | PostgreSQL Database Size | Total Size of all dbs. |
Discovery using the gateway
The gateway establishes a connection to the database using the Java Database Connectivity (JDBC) Java API and collects metrics using SQL queries. To monitor PostgreSQL, install gateway version 5.0.0 or later.
Use one of the following mechanisms to discover PostgreSQL servers and add them to your inventory:
- WMI
- SSH
- SNMP
Optionally, manually add a database server to the infrastructure inventory:
- Select Infrastructure and click Resources.
- Click the Settings icon on the top-right and click Add.
- On the Add Device page, enter the Device Type and any other information you want to enter.
- Click Save.
You can also apply agent-based templates to initiate MS SQL monitoring.
Prerequisite
- Database and port 5432 are reachable from the gateway.
- A gateway management profile mapped to a resource.
- Create credentials with type Database before assigning a template to a resource. The Port, Connection Time-out, and Is Secure values are not used to create credentials.
- Enable privileges according to the PostgreSQL version used.
Privileges
Version | Privilege |
---|---|
Before PostgreSQL v10 | pg_stat_database granted to username |
PostgreSQL v10 and above | pg_monitor granted to username |
PostgreSQL v10 and above | Grant the user EXECUTE permission for the pg_ls_dir function, for the monitored database. |
PostgresDB
Prerequisites for applying PostgresDB templates:
- Use Gateway 7.0.0 and above.
- Create a Postgres environment file and provide the file path as the input parameter while applying the template. Along with setting up the Postgres environment, make sure that the environmental file includes other parameters, such as PGDATADIR, PGWALDIR, PGDATABASE, PGARCHIVEDIR, and PGPORT. Also, after loading the environment file, the
pg_isready
andpsql
commands are available for the script to use. - Add the Postgres database credentials with the PostgresDB_Credentials set name.
- Assign the pg_monitor role to the user.
Template names:
G2-PostgresDB-Performance
G2-PostgresDB-Replication Running Status
To use the multi-instance functionality with the G2-PostgresDB-Replication Running Status template, the credentials set name needs to be updated in the monitor script. Also, end-users need to create the credentials using the same credential set name on the device.
Set up PostgreSQL monitoring
PostgreSQL monitoring setup involves:
- Associating appropriate database credentials to the discovered database.
- Assigning one or more database-specific global monitors or templates on the device. Optionally, create custom metrics or monitors using SQL queries and assign them to the database.
Create custom metrics
To create a metric using SQL query for PostgreSQL monitoring, navigate to Monitoring > Metrics > Create Metric. For example, to create a metric to check the number of records added in the pg_stat_activity table, use:
SELECT count(*) FROM pg_stat_activity
For more information, see Assign a Template.
Assign templates from setup
Assign PostgreSQL templates to one or more resources for a selected client and change the configuration parameters while assigning templates. For more information, see Assign Templates from Setup.
Assign templates at the device level
Applying PostgreSQL templates at the device level helps assign one or more templates to a specific resource. You can change the configuration parameter default values while assigning the templates. For more information, see Assign Templates at the Device Level.
Template configuration parameters
Configuration Parameter | Description |
---|---|
Connection Time-out | The maximum time to connect to the database. The driver waits for the specified time before timing out due to connection failure. The default time is 15000 milliseconds (ms). |
Service Transport Type | To configure the database at a secure end-point. The default type is In-secure . The connection is Secure when the data sent between the client and server is encrypted. |
Service Port | The port number where the database is running. The connection is made to the specified port if you specify the port and the database name. The default port is 5432. |
DB Instance Name | The name of the database to connect to. The default name is postgres .
|
Application Type | The application type value to identify the adapter. For example, POSTGRESQL . Do not change the default application type value. |
use.ssl.certificate | Configure the database to use ssl certificate. By default, it is No. |
postgresql.ssl.mode | The different values for the ssl mode parameter provide different levels of protection. Possible ssl mode values are verify-ca, verify-full, require, allow, prefer, disable. By default, ssl mode configuration parameter value is verify-ca. |
postgresql.root.certificate | Checks that client certificate is signed by a trusted certificate authority. Give the certificate along with the complete path like (/opt/gateway/content/certificates/POSTGRESQL/root.crt). |
postgresql.ssl.certificate | Client ssl certificate in DER format. Give the certificate along with the complete path like (/opt/gateway/content/certificates/POSTGRESQL/postgresql.crt.der) . |
postgresql.ssl.certificate.key | Client ssl certificate key in DER format. Give the certificate along with the complete path like (/opt/gateway/content/certificates/POSTGRESQL/postgresql.key.pk8) .Note: SSL support for POSTGRESQL db monitoring works in classic gateway only. |
Assign template from device management policies
Device management policies help manage resources. You can assign monitoring templates, knowledge base articles, and custom attributes using device management policies. The device management policy can be applied to one or a set of resources. For more information, see Creating Policies.
View resource metrics
The gateway monitors the application using the assigned templates and displays the results in graphical format. To view resource metrics, click the database resource name > resource Details > Metrics.
Steps to follow for monitoring a database through SSL with certificates
If you are using a trusted certificate on the end device to establish the connection, then follow the steps below:
Clone the existing global monitors and mandatory to add the below configuration parameters:
use.ssl.certificate
postgresql.ssl.mode
postgresql.root.certificate
postgresql.ssl.certificate
postgresql.ssl.certificate.key
Convert the client certificate and client key in DER format using below commands:
To convert the client certificate in DER format:
Syntax
openssl x509 -in <server.crt> -out <outputfileName.crt.der> -outform der
Example
openssl x509 -in postgresql.crt -out postgresql.crt.der -outform der
To convert the client key in DER format:
Syntax
openssl pkcs8 -topk8 -outform DER -in <server.key> -out <outputfileName.key.pk8> -nocrypt
Example
openssl pkcs8 -topk8 -outform DER -in postgresql.key -out postgresql.key.pk8 -nocrypt
Note: In the above commands, please replace the client certificate and client key files
If the certificates and respective application folder is not available under
/opt/gateway/content/
path, we recommend in creating those using thebelow commandsNavigate to the
/opt/gateway/content/
path
Commandcd /opt/gateway/content/
Create the certificates folder in the above path using below command:
Command
mkdir certificates
In the certificates folder, create respective application folder
Command
cd certificates mkdir POSTGRESQL
Copy the trusted certificates (root certificate, client certificate & client certificate key (which are in DER format)) manually into gateway under
/opt/gateway/content/certificates/POSTGRESQL
path.While assigning the template on the devices, provide the required values in the configuration parameters.
Example
Configuration Parameter | Values |
---|---|
use.ssl.certificate | yes |
postgresql.ssl.mode | verify-ca |
postgresql.root.certificate | /opt/gateway/content/certificates/POSTGRESQL/root.crt |
postgresql.ssl.certificate | /opt/gateway/content/certificates/POSTGRESQL/postgresql.crt.der |
postgresql.ssl.certificate.key | /opt/gateway/content/certificates/POSTGRESQL/postgresql.key.pk8 |
postgresql.service.transport | secure |
Troubleshooting
PostgreSQL monitoring issues, verify gateway, telnet, and database connectivity:
ping <IP Address>
telnet <IP Address> <Port>
gcli
db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>
Beginning with gateway version 5.3.0, use the following format for the last command, including the result-set:
db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <Query>
GCLI command for SSL connectivity check
Syntax
db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <Read Timeout> <Secure Flag> <ResultSet?: Yes/No> <UseSSLCertificate> <SSLMode> <RootCertificatePath> <SSLCertificate> <SSLCertificateKey> <Query>
Example
db postgresql 172.25.251.160 postgres postgres 5432 postgres 15000 10000 secure Yes Yes verify-ca /home/ruser/postgres_cert.crt /home/ruser/postgresql.crt.der /home/ruser/postgresql.key.pk8 "SELECT count(*) FROM pg_stat_activity"
Supported Templates
If multi-instance monitoring support is needed, you need to create monitor and template copies. This is a mandatory requirement for all multi-instance templates.
Collector Type | Template Name |
---|---|
Agent | Linux - PostgreSQL Monitors |
PostgreSQL Status and Performance Check | |
Gateway | Advanced Cloud Database Template for PostgreSQL |
Advanced Cloud PostgreSQL Database BlockHits Template | |
Advanced Cloud PostgreSQL Database Connection Statistics Template | |
Advanced Cloud PostgreSQL Database Cpu Utilization Template | |
Advanced Cloud PostgreSQL Database Deadlocks Template | |
Advanced Cloud PostgreSQL Database Disk Usage Template | |
Advanced Cloud PostgreSQL Database IO Template | |
Advanced Cloud PostgreSQL Database Memory Template | |
Advanced Cloud PostgreSQL Database Performance Template | |
Advanced Cloud PostgreSQL Database Session Template | |
Advanced Cloud PostgreSQL Database Transactions RolledBack Template | |
G2 PostgreSQL Database Blockhits Template | |
G2 PostgreSQL Database Connection Statistics Template | |
G2 PostgreSQL Database CPU Utilization Template | |
G2 PostgreSQL Database Deadlocks Template | |
G2 PostgreSQL Database Disk Usage Template | |
G2 PostgreSQL Database IO Template | |
G2 PostgreSQL Database Locks Template | |
G2 PostgreSQL Database Memory Template | |
G2 PostgreSQL Database Performance Template | |
G2 PostgreSQL Database Session Template | |
G2 PostgreSQL Database Transactions Rollback Template | |
Gateway v7 and above for MultiInstance | G2 PostgresDB Performance |
Gateway v7 and above for MultiInstance | G2-PostgresDB-Replication Running Status |
Gateway v10 and above for MultiInstance | G2 PostgreSQL Database BlockHits Template - MultiInstance |
G2 PostgreSQL Database Connection Statistics Template - MultiInstance | |
G2 PostgreSQL Database Deadlocks Template - MultiInstance | |
G2 PostgreSQL Database Disk Usage Template - MultiInstance | |
G2 PostgreSQL Database IO Template - MultiInstance | |
G2 PostgreSQL Database Locks Template - MultiInstance | |
G2 PostgreSQL Database Memory Template - MultiInstance | |
G2 PostgreSQL Database Performance Template - MultiInstance | |
G2 PostgreSQL Database Session Template - MultiInstance | |
G2 PostgreSQL Database Transactions RolledBack Template - MultiInstance | |
G2 PostgreSQL Hot Standby Feature Status Template - MultiInstance | |
G2 PostgreSQL Activity Long Transactions Template - MultiInstance | |
Gateway v10 and above for MultiInstance | G2-PostgresDB-PerformanceQueries |
Gateway v10 and above for MultiInstance | G2-PostgresDB-Replication |
Supported metrics
Metric | Metric Display Name | Unit |
---|---|---|
postgresql.activity.count The maximum number of connection limits and the clients displaying the database connections. | Postgresql Activity Count | - |
postgresql.activity.long_transactions Long-running transactions are bad because they prevent Postgres from vacuuming old data. This causes database bloat and, in extreme circumstances, shutdown due to transaction ID, xid, wraparound. Transactions should be kept as short as possible, ideally less than a minute. | Postgresql Activity Long Transactions | hours(h) |
postgresql.bgwriter.buffers_backend The number of buffers written directly by a backend. | Postgresql Bgwriter Buffers Backend | - |
postgresql.bgwriter.buffers_checkpoint The number of buffers written during checkpoints. | Postgresql Bgwriter Buffers Checkpoint | - |
postgresql.bgwriter.buffers_clean The number of buffers written by the background writer. | Postgresql Bgwriter Buffers Clean | - |
postgresql.bgwriter.checkpoints_req The number of requested checkpoints that are already executed. | Postgresql Bgwriter Checkpoints Request | - |
postgresql.bgwriter.checkpoints_timed The number of scheduled checkpoints that are already executed. | Postgresql Bgwriter Checkpoints Timed | - |
postgresql.class.relpages Display the tables and the respective indexes in the descending order of relpages. | Postgresql Class Relpages | count |
postgresql.database. deadlocks The number of deadlocks detected in each database. | Postgresql Database Deadlocks | count |
postgresql.database.blkshit The number of times disk blocks were found already in the buffer cache. | Postgresql Database Blocks Hits | - |
postgresql.database.conflict.deadlocks The number of conflicts in the database that hare cancelled due to deadlocks. | Postgresql Database Conflict Deadlocks | count |
postgresql.database.connections The number of active connections to postgres database. | Postgresql Database Connections | - |
postgresql.database.deadlocks The number of deadlocks detected in this database. | Postgresql Database Deadlocks | count |
postgresql.database.rows_deleted The number of rows deleted by queries in this database. | Postgresql Database Rows Deleted | - |
postgresql.database.rows_fetched The number of rows fetched by queries in this database. | Postgresql Database Rows Fetched | - |
postgresql.database.rows_inserted The number of rows inserted by queries in this database. | Postgresql Database Rows Inserted | - |
postgresql.database.rows_returned The number of rows returned by queries in this database. | Postgresql Database Rows Returned | - |
postgresql.database.rows_updated The number of rows updated by queries in this database. | Postgresql Database Rows Updated | - |
postgresql.database.size The size of the database. | Postgresql Database Size | gigabytes(GB) |
postgresql.database.temp_bytes Total amount of data written to temporary files by queries in this database. All temporary files are counted, regardless of why the temporary file was created, and regardless of the log_temp_files setting. | Postgresql Database Temporary Bytes | - |