Managing PostgreSQL Statements

Unlock the full potential of your PostgreSQL environments with the Nodinite Database Monitoring Agent. This page shows you how to leverage custom PSQL scripts for proactive monitoring, instant alerts, and self-service management—empowering both integration experts and business users.

✅ Monitor PostgreSQL health and business data using your own PSQL logic
✅ Instantly receive alerts and automate remediation with Remote Actions
✅ Empower self-service for business users to execute pre-defined scripts
✅ Centralize monitoring and management for all your PostgreSQL resources

Bring your own PSQL logic and Nodinite will monitor and alert you whenever there is a problem detected!

To use this feature, provide AdHoc PSQL statements with your business logic as described in the examples on this page. The Database Monitoring Agent creates one Resource for each configuration. Each SQL Statement returns its evaluated state, which you can monitor in Monitor Views and push external alerts using any of the Alarm Plugins.

PostgreSQL PSQL Statements are grouped by the Category PostgreSQL - SQL Statements.

Here's an example of a Monitor View filtered by the 'PostgreSQL - SQL Statements' category.

• Your custom-built PostgreSQL - SQL Statements are listed in Nodinite as resources, where the name of the SQL Statement configuration becomes the Resource name.

Each configuration is manifested as a Resource.

What are the key features for Monitoring SQL Statements?

• State Evaluation – Monitor and evaluate the state based on your own PSQL logic. See PSQL/Stored procedure code examples further down in this guide.
• Actions – Support for the execution of Remote Actions

What is evaluated for SQL Statements?

The different possible evaluated states for your custom coded SQL Statements are provided in the table below:

Tip

The evaluated state may be reconfigured using the Expected State functionality on every Resource within Nodinite.

How do I add my own SQL Statements?

The Database Monitoring Agent automatically monitors each configured SQL Statement. You can add new and modify existing SQL Statements using the Remote Configuration dialogue for the Database Monitoring Agent.

There are two different ways to configure content for the category PostgreSQL - SQL Statements:

• Global configuration
• Specific configuration available on Resource level within applicable Monitor Views using the Remote Action Edit SQL Statement

Global configuration

From the PostgreSQL databases tab, configure global settings. Then click on the Size check tab.

A Nodinite Administrator can add new SQL Statements from the Remote Configuration dialogue, detailed here.

From the PostgreSQL statements tab for the PostgreSQL Database Connection configuration, click the Add button.

Any number of SQL Statements can be provided, but make sure that each one is fast to execute or you may end up hogging the agent and probably resulting in various timeouts.

Example configuration for SQL Statements with the Add button

Expand the accordion to edit available properties.

Here's an example of configuration of SQL Statements

You can manage the following properties:

✅ Enabled

• ApplicationId
• Display Name
• Command Type
• Command Text
• Description

Enabled

Flag to set if monitoring for this configuration is enabled or not.

ApplicationId

Select the Id of which Application to associate this Resource (SQL Statement configuration) with.

Display Name

You can manage the user-friendly Name. This setting must be unique for each SQL Statement configuration.

Command Type

The Type of 'SQL Command', select one of the following:

• Text – For AdHoc PSQL Statements

Command Text

Enter your Command text, see examples further down in this guide.

• Execute Command – the SQL command(s) to execute

An AdHoc PSQL query with your own custom logic.

Important

This feature can be dangerous and the connection string used should be configured with a user with read-only access.

Description

• Description – user-friendly description for SQL Statement resource

Actions

The following Actions are currently implemented:

• Edit SQL Statement

Edit SQL Statement

You can manage your SQL Statements directly from Monitor Views where Remote Actions are allowed.

Click the Edit SQL Statement to open the Edit Modal for the selected Resource

The Edit Modal will then open.

Here's an example of Edit Modal for the SQL Statements

The same properties as described in the Global configuration are available.

Save

You must click 'Save' or 'Save and close' for changes to persist. The new settings/thresholds are evaluated on the next synchronization.

Here's an example of the Save options.

Note

Depending on the synchronization interval set for the agent, there might be a delay before the Web Client reflects upon the change. You can force the agent to synchronize the Configuration for all Nodinite Monitoring Agents.

Save and close saves and closes the dialogue.
Cancel closes the dialogue without saving any changes.

Examples

The outcome (State) from the logic in your PostgreSQL - SQL Statements is based on whether you raise an exception or not.

Internally, the Database Monitoring Agent executes the Execute Scalar method. This will pick up data from the first column at the first row, and set this as Log Text unless you raised an exception.

Select + throw (raiserror)

The LogText presented for the PostgreSQL Statement configuration (The Resource) comes from your last SELECT statement (1st row, 1st column) or from the Exception. If you do not select anything the Log Text simply becomes empty.

Text Example

Example of using the SQL Statement with Command Type set to Text. This example counts the rows in the actual table. Depending on your logic, different states can be returned.

DO $$ BEGIN
DECLARE counter integer;
BEGIN
counter:= 0;
 SELECT count(*) into counter FROM public."LogEvents" WHERE "ValidationFailed"=true;
    IF (counter=0) THEN -- OK
		--return query SELECT 'OK';
	ELSEIF (counter<10) THEN -- WARNING
		RAISE EXCEPTION SQLSTATE '01000' 
      	USING HINT = 'Make sure to clean these up manually after inspection of the content within the ValidationFailedText column';
	ELSE -- EXCEPTION
		RAISE EXCEPTION 
      	USING HINT = 'Make sure to clean these up manually after inspection of the content within the ValidationFailedText column';
	END IF;
END;
END $$;

Next Step

Add or manage Monitor View

Related Topics

Azure SQL Statements
SQL Statements
Database Monitoring Agent
SQL Categories
Resources
Monitoring Agents
Monitor Views