PostgreSQL

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

Note: All field attributes are mandatory, use default values wherever applicable.

Collected Metrics

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.

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

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.

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

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:

1. 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

2. 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

3. If the certificates and respective application folder is not available under /opt/gateway/content/ path, we recommend in creating those using thebelow commands

   1. Navigate to the /opt/gateway/content/ path
      Command

      	   	cd /opt/gateway/content/
      	   	

   2. Create the certificates folder in the above path using below command:

      Command

      	   	mkdir certificates
      	   	

   3. In the certificates folder, create respective application folder

      Command

      	   	cd certificates
                                     mkdir POSTGRESQL
      	   	

4. 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.

5. While assigning the template on the devices, provide the required values in the configuration parameters.

Example

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.

Supported metrics