Postgres or PostgreSQL is a relational database management system (RDBMS) emphasizing extensibility and technical standards compliance. Cavisson enables you to keep an eye on the performance of your database servers. Using the PostgreSQL Monitoring tool, you can optimize the performance of your applications and ensure the availability of your SQL servers. It provides end-to-end monitoring of PostgreSQL queries with insights into various aspects, such as SQL Activity, Temp files, and Server stats. There are two types of monitoring:

• PostgreSQL DB-Based Monitor: This monitor provides information about the list of LPS-based DB Monitors. Extensive coverage includes multiple data points from the following categories:
  • PostgreSQL BG Writer Stats
  • PostgreSQL Blocking Queries
  • PostgreSQL Connection Summary
  • PostgreSQL DB Activity Stats
  • PostgreSQL DB Connection Statistics
  • PostgreSQL DB Table Hit Ratio
  • PostgreSQL Detail IO Activity Stats
  • PostgreSQL Execution Plans
  • PostgreSQL I/O Activity Stats
  • PostgreSQL Lock Mode Stats
  • PostgreSQL Locks Stats
  • PostgreSQL Query Response Time
  • PostgreSQL Session Summary
  • PostgreSQL Temp DB Summary
• Postgre SQL Access Log Based Monitor: This monitor provides information about the queries executed and their plan. The focus of this monitor is on the SQL statement selected on the Query Execution Statistic table. This section displays the sequence of execution for the selected query plan. An extensive coverage that includes multiple data points from the following category:
  1. PostgreSQL Access Log

Note: You can see the monitor information for specific monitors by clicking on the Information icon provided alongside the monitor type.

Platform Supported

• Linux Server -- Ubuntu 16 onwards
• RHEL version 7 & above

Note: You can use either the JAVA-based machine agent (CMON) or the .NET-based machine agent (CavWMon) to collect PostgreSQL DB metrics.

Prerequisites

• For viewing PostgreSQL Execution Stats, the Postgres server version should be 5 or above.
• Below are steps that need to be performed for generating data in the PostgreSQL Execution Stats screen:
  • Add the below keywords in the conf file.
    To enable pg_stat_statements (Queries)
    1. max = 10000
    2. track = all
    3. track_activity_query_size = 2048
       For Execution Plan
    4. log_min_duration = 0
    5. log_format = ‘XML’
    6. log_nested_statements = ON — multiple statement
    7. shared_preload_libraries = ‘pg_stat_statements, auto_explain’
  • Connect the database with super users. Make sure to enable the pg_stat_statements extension on the databases to which the agent will connect. Execute the below command after connecting to the database:
    Create extension pg_stat_statements;
  • Restart the PostgreSQL server.

Monitor Group Configuration

Common Configuration

All metrics in both monitors have the following common fields which are optional:

• Frequency: Each sample will come in a selected frequency in the graph. It can be 10 secs, 10 minutes, 1 hour, 4 hours, or 1 day.
• Other Arguments: It is for enabling debug mode or passing any extra argument JSON.

Monitor Group-Specific Configuration

The following monitor groups have additional configuration fields, described below:

• PostgreSQL DB-Based Monitor: This monitor provides information about the list of LPS-based DB Monitors There are the following fields that are mandatory:
• DB Name: In this field, you have to provide the hostname/IP of the machine on which the PostgreSQL db database resides. The default value Host IP is 127.0.0.1.
• Username/Password: In this field, you have to provide the username and password where the PostgreSQL database is set up.
• Advanced Settings: There are the following advanced settings:
• Port: In this field, you have to provide the port on which the connection will be made along with the hostname/IP. The default value is 27017. It is a mandatory field.
• SSL Settings: In this field, you have to provide the PostgreSQL pem file path. It is an optional field.

Note: Once all the configurations are done and monitors are applied, you can see the monitor details and stats on the Monitor UI and Dashboard.

• PostgreSQL Accesslog-Based Monitor: This monitor provides information about the queries executed and their plan. There are the following fields that are mandatory:
• Access log File Path: Specify the absolute Postgres log file path and pattern.

Deploy Monitors

Deploy the configured monitors using our machine agent. The following fields need to be filled in to deploy:

• Tier: Tier is used to select the tier on which the monitor(s) will be deployed. Select the required tier from the drop-down list.
• Server: You can select the server(s) of the selected tier on which the configured monitor(s) will get deployed.

Based on the requirement, you can select any one of the following:

• All: Deploy the configured monitors on all the servers of the selected tier.
• Any: Select any one of the servers where monitors will be deployed.
• Specified Server: Select one or multiple servers of the chosen tier on which the monitor(s) will be deployed. It means that with monitors whether we require all servers, any server, or a specified server.
• Exclude Server: In this field, you have to select one or more servers that you want to exclude.
• Exclude Tier: In this field, you have to select one or more tiers that you want to exclude.

Notes:

• If you want to add more tiers and servers, then you can click on the Add Agent Tier-Server
• Once you have selected all the fields, you have to click on the Add Add button to add the tier and server.
• On clicking the add button, the tier server will be added in the table.
• You can perform the following actions in the table which are listed below:
  1. Edit: In edit option, you can edit the configuration. To perform this operation, you have to click on the Edit Monitor Configuration Once you have edited the configuration, you have to click on the Update Update button to update the table. You can also click on the
     Cancel Cancel button if you don’t want to edit the window.
  2. Delete: In delete, you can delete the configuration. To perform this operation, you have to click on the Delete button
  3. Test Monitor:
     • In test monitor, you have to select tier, server, frequency, and duration from the dialog box and then click on the Test Monitor Once you click on the test button, the test will start and the Open Dashboard Open Dashboard button will be enabled and the monitor log will be displayed.
     • Once you click on the open dashboard button, a new dashboard will open in a separate tab where you can see the data.

Validation

Once the PostgreSQL monitors are configured, you can validate by either checking the monitor homepage where a section for PostgreSQL monitors along with details like total, running, and failed monitors are given, or check for the configured monitors from the metric tree, against the tier/server/instance for which this metric is configured.