PostgreSQL, also known as Postgres, is a free and open-source relational database management system emphasizing extensibility and SQL compliance. The database administrators can apply a variety of metrics to collect the health and performance statistics of the database.

Prerequisite

  • Database and port(5432) are reachable from the gateway.
  • Enable access according to the PostgreSQL version. For example:
    • grant pg_monitor to username privilege for all PostgreSQL versions above 10.
    • pg_stat_database to username privilege for previous PostgreSQL versions.
  • 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.

Supported version

  • PostgreSQL 9.2 or higher.
  • JDBC Version: 42.2.15.

Database discovery and monitoring

The administrator can deploy an agent or gateway to support Postgres agent-based or agentless monitoring.

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 to add them to your inventory:

  • WMI
  • SSH
  • SNMP

Optionally, add a database server manually to the infrastructure inventory.

To add a resource manually to the inventory:

  1. Select Infrastructure.
  2. Click the Settings > Resource > Add.
  3. From the Add Device window, provide the required details such as Device Type and Management Profile.
  4. Click Save.

You can also apply agent-based templates to initiate MS SQL monitoring.

Prepare the resource to start monitoring

To monitor PostgreSQL:

  • Associate appropriate database credentials to the discovered database.
  • Assign 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.

To track monitoring of PostgreSQL database in your cloud environment, see Monitoring Cloud Database using Gateway.

Global templates and metrics

Global templates

Collector TypeTemplate Name
AgentLinux - PostgreSQL Monitors
PostgreSQL Status and Performance Check
GatewayAdvanced 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

Global metrics

OpsRamp MetricMetric Display NameUnitDescription
postgresql.activity.countPostgresql Activity Count-The maximum number of connection limits and the clients displaying the database connections.
postgresql.activity.long_transactionsPostgresql Activity Long Transactionshours(h)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.bgwriter.buffers_backendPostgresql Bgwriter Buffers Backend-The number of buffers written directly by a backend.
postgresql.bgwriter.buffers_checkpointPostgresql Bgwriter Buffers Checkpoint-The number of buffers written during checkpoints.
postgresql.bgwriter.buffers_cleanPostgresql Bgwriter Buffers Clean-The number of buffers written by the background writer.
postgresql.bgwriter.checkpoints_reqPostgresql Bgwriter Checkpoints Request-The number of requested checkpoints that are already executed.
postgresql.bgwriter.checkpoints_timedPostgresql Bgwriter Checkpoints Timed-The number of scheduled checkpoints that are already executed.
postgresql.class.relpagesPostgresql Class RelpagescountDisplay the tables and the respective indexes in the descending order of relpages.
postgresql.database. deadlocksPostgresql Database DeadlockscountThe number of deadlocks detected in each database.
postgresql.database.blkshitPostgresql Database Blocks Hits-The number of times disk blocks were found already in the buffer cache.
postgresql.database.conflict.deadlocksPostgresql Database Conflict DeadlockscountThe number of conflicts in the database that hare cancelled due to deadlocks.
postgresql.database.connectionsPostgresql Database Connections-The number of active connections to postgres database.
postgresql.database.deadlocksPostgresql Database DeadlockscountThe number of deadlocks detected in this database.
postgresql.database.rows_deletedPostgresql Database Rows Deleted-The number of rows deleted by queries in this database.
postgresql.database.rows_fetchedPostgresql Database Rows Fetched-The number of rows fetched by queries in this database.
postgresql.database.rows_insertedPostgresql Database Rows Inserted-The number of rows inserted by queries in this database.
postgresql.database.rows_returnedPostgresql Database Rows Returned-The number of rows returned by queries in this database.
postgresql.database.rows_updatedPostgresql Database Rows Updated-The number of rows updated by queries in this database.
postgresql.database.sizePostgresql Database Sizegigabytes(GB)The size of the database.
postgresql.database.temp_bytesPostgresql Database Temporary 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.

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 modify 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 ParameterDescription
Connection Time OutThe 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 TypeTo 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 PortThe 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 NameThe name of the database to connect to. The default name is postgres.
  • If you have multiple database names with different ports, use the following syntax: `DBname1:Port1, DBname2:Port2,..` (Comma-separated). For example, if DBname 1 has credential set A and DBname 2 has credential set B, create separate credentials sets on the resource.
    Important! Create separate credential sets on the resource if databases consist of different users.
  • If you have multiple database names with the same port, use the following syntax: `DBname1, DBname2,..` (Comma-separated).
Application TypeThe application type value to identify the adapter. For example, POSTGRESQL.

Warning: Do not change the default application type value.

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.

Troubleshooting

Verify the following if the graphs are not plotted correctly:

  • The resource is accessible and Telnet is established for the IP address and port.
  • Valid credentials are used for the resource.

If you experience PostgreSQL monitoring issues, follow these troubleshooting steps:

  1. Verify server and gateway connectivity using Ping.
ping <IP Address>

Example:

ping 12.26.105.173
  1. Verify Telnet-to-server listening port connectivity.
telnet <IP Address> <Port>

Example:

telnet 172.26.105.173 1433
  1. Execute the following commands from GCLI to verify the database connectivity:
gcli
db <Database Type> <IP Address> <User Name> <Password> <Port> <Database Name> <Connect Timeout> <ReadTimeout> <Secure Flag> <Query>

Example:

db postgresql 172.26.105.173 postgres Pass@123 5432 postgres 15000 10000 insecure "SELECT count(*) FROM pg_stat_activity"

From gateway version 5.3.0, use the following format to execute the 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>

Example:

 db postgresql 172.26.105.173 postgres Pass@123 5432 postgres 15000 10000 insecure Yes "SELECT count(*) FROM pg_stat_activity"