Supported Collector Type
Gateway
Supported Target Versions
9.2 or higher
JDBC: 42.2.15

This document describes the discovery and monitoring setup for the PostgreSQL database.

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

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

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

VersionPrivilege
Before PostgreSQL v10pg_stat_database granted to username
PostgreSQL v10 and abovepg_monitor granted to username
PostgreSQL v10 and aboveGrant 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 and psql 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.

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:

  1. Select Infrastructure and click Resources.
  2. Click the Settings icon on the top-right and click Add.
  3. On the Add Device page, enter the Device Type and any other information you want to enter.
  4. Click Save.

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

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 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 root.
  • If you have multiple instances with different ports, use the following syntax:

    Instance1:Port1,Instance2:Port2

    Note: Do not consider the service port configuration value here.

  • If you have a single instance, use the following syntax:

    “Instance1”

    Note: Consider the port from the service port configuration for this case.

  • If you have multiple databases with the same port, use the following syntax:

    “Database1, Database2, Database3”

    Note: Consider the port from the service port configuration for this case.

Application TypeThe application type value to identify the adapter. For example, POSTGRESQL. 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

If you have 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>

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 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
Gateway v7 and above for MultiInstanceG2 PostgresDB Performance
Gateway v7 and above for MultiInstanceG2-PostgresDB-Replication Running Status
Gateway v10 and above for MultiInstanceG2 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 MultiInstanceG2-PostgresDB-PerformanceQueries
Gateway v10 and above for MultiInstanceG2-PostgresDB-Replication

Supported metrics

MetricMetric Display NameUnit
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 Transactionshours(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 Relpagescount
postgresql.database. deadlocks

The number of deadlocks detected in each database.
Postgresql Database Deadlockscount
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 Deadlockscount
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 Deadlockscount
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 Sizegigabytes(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-