PRTG Manual: PostgreSQL Sensor

The PostgreSQL sensor monitors a database on a PostgreSQL server and executes a query.

i_round_blueThe sensor can also process the data table and show the values that you define in individual channels.

PostgreSQL Sensor

PostgreSQL Sensor

i_square_cyanFor a detailed list and descriptions of the channels that this sensor can show, see section Channel List.

Sensor in Other Languages

Dutch: PostgreSQL

French: PostgreSQL

German: PostgreSQL

Japanese: PostgreSQL

Portuguese: PostgreSQL

Russian: PostgreSQL

Simplified Chinese: PostgreSQL

Spanish: PostgreSQL

Remarks

This sensor has a high performance impact. Use it with care. We recommend that you use no more than 200 sensors of this sensor type on each probe.

This sensor requires .NET 4.7.2 or later from Microsoft on the probe system.

You must store your Structured Query Language (SQL) query in a file on the probe system. In a cluster, copy the file to every cluster node.

Save the SQL script with the query in the \Custom Sensors\sql\postgresql subfolder of the PRTG program directory.

Define credentials, custom port (if required), and timeout in the credentials for database management systems settings of the parent device, or in the settings of a group or probe above.

This sensor supports as of PostgreSQL 7.x.

This sensor supports the IPv6 protocol.

This sensor is capable of using lookups to determine the status value of one or more channels. Select Lookup as Channel #x Unit and define the lookup file in Channel #x Lookup.

See section Monitoring Databases for an example for channel value selection.

See the Knowledge Base: How can I monitor strings from an SQL database and show a sensor status depending on it?

i_podYou cannot add this sensor to the hosted probe of a PRTG Hosted Monitor instance. If you want to use this sensor, add it to a remote probe device.

Detailed Requirements

Requirement

Description

.NET 4.7.2 or later

This sensor requires .NET 4.7.2 or later to be installed on the probe system (on every cluster node, if on a cluster probe).

i_round_redIf the framework is missing, you cannot create this sensor.

i_square_cyanFor more information, see the Knowledge Base: Which .NET version does PRTG require?

Add Sensor

The Add Sensor dialog appears when you manually add a new sensor to a device. It only shows the settings that are required to create the sensor. You can change nearly all settings on the sensor's Settings tab after creation.

Data

Setting

Description

SQL Query File

Select the SQL query file that includes a valid SQL statement that the sensor executes on the server with every scanning interval. The list contains SQL scripts from the \Custom Sensors\sql subfolder of the PRTG program directory on the probe system. Store your script there. If you use the script on a cluster probe, you must store the script on all cluster nodes.

A correct expression in the file could be: SELECT AVG(UnitPrice) FROM Products. If you want to use transactions, separate the individual steps with semicolons ";".

i_round_blueNote that with each request, PRTG transfers the full result set, so use filters and limits in your query.

i_round_blueThe demo script Demo Serveruptime.sql is available by default. You can use the it to monitor the uptime of the target server.

i_square_cyanSee also the Knowledge Base: Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

Data Processing

Define whether the sensor processes data from the database:

Only execute query: Only show information about the number of affected rows and the execution time of the query. Affected rows are rows that were changed by the query (for example, created, deleted, or edited).

Count table rows: Execute a SELECT statement and monitor how many rows of the data table this statement returns.

Process data table: Read and analyze the data table. If you select this option, the sensor counts rows with SELECT statements as well.

Channel #2 - #10

This setting is only visible if you select Process data table above. You can define up to 10 channels. You must define at least one data channel, so you see all available settings for Channel #1. Specify how to handle all other possible channels:

Disable: Do not create this channel.

Enable: Create this channel.

i_round_blueIt is not possible to enable or disable channels after sensor creation.

Channel #x Name

This setting is only visible if you select Process data table above. Enter a name for the channel. Enter a string. The sensor dynamically generates channels with this name as identifier.

i_round_blueIf the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include?

Channel #x Mode

This setting is only visible if you select Process data table above. Define how to display the determined value in the channel:

Absolute (recommended): Show the value as the sensor retrieves it from the data table.

Difference: The sensor calculates and shows the difference between the last and the current value returned from the data table.
i_round_redThis mode is not compatible with the unit Lookup.
 
i_round_redThis mode only works if the difference between the last and the current value is positive and increases with each scanning interval. This mode does not support negative and decreasing values.

Channel #x Unit

This setting is only visible if you select Process data table above. Define the unit of the channel value:

BytesBandwidth

BytesMemory

BytesDisk

Temperature

Percent

TimeResponse

TimeSeconds

TimeHours

Count

CPU

BytesFile

SpeedDisk

SpeedNet

Custom

Lookup

i_square_cyanFor more information about the available units, see section Custom Sensors.

i_round_blueTo use lookups with this channel, select Lookup and define the lookup file in Channel #x Lookup. Do not use Custom if you use lookups with this sensor.

i_round_blueIt is not possible to use the unit Lookup in combination with the Difference mode. You are not able to create the sensor in this case.

Basic Sensor Settings

Click the Settings tab of a sensor to change its settings.

Basic Sensor Settings

Basic Sensor Settings

Setting

Description

Sensor Name

Enter a name to identify the sensor. By default, PRTG shows this name in the device tree, as well as in alarms, logs, notifications, reports, maps, libraries, and tickets.

i_round_blueIf the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include?

Parent Tags

Shows tags that the sensor inherits from its parent device, parent group, and parent probe.

i_round_blueThis setting is for your information only. You cannot change it.

Tags

Enter one or more tags. Confirm each tag with the Spacebar key, a comma, or the Enter key. You can use tags to group objects and use tag-filtered views later on. Tags are not case-sensitive. Tags are automatically inherited.

i_round_blueIt is not possible to enter tags with a leading plus (+) or minus (-) sign, nor tags with parentheses (()) or angle brackets (<>).

i_round_blueFor performance reasons, it can take some minutes until you can filter for new tags that you added.

The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:

sqlsensor

Priority

Select a priority for the sensor. This setting determines the position of the sensor in lists. The highest priority is at the top of a list. Choose from the lowest priority (i_priority_1) to the highest priority (i_priority_5).

Database Specific

Database Specific

Database Specific

Setting

Description

Database

Enter the name of the PostgreSQL database to which the sensor connects, for example MyDatabase. This is a logical entity on the database server where database objects exist.

SSL Mode

Select the PostgreSQL Secure Sockets Layer (SSL) mode for the connection:

Disable: Do not use SSL.

Allow: Use SSL if the server demands it.

Prefer: Use SSL if the server supports it.

Require: Always use SSL and deny the connection if the server does not support it.

The SSL mode options that you can choose are the same as the values of the PostgreSQL sslmode parameter. PRTG sends it with the sensor requests.

i_round_redPostgreSQL SSL connections require OpenSSL to be installed on both the target server and on the probe system.

i_round_blueFor details about the PostgreSQL SSL modes, refer to the PostgreSQL documentation.

Data

Data

Data

Setting

Description

SQL Query File

Shows the SQL script file that the sensor executes on the server.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

Input Parameter Handling

Define if you want to pass a parameter to the SQL query file:

Do not use input parameter (default): Execute the SQL query file without using variables.

Use input parameter: Execute an SQL query file that contains a variable. Provide the parameter that you want to use in the query below.

Input Parameter

This setting is only visible if you select Input Parameter Handling above. Enter the parameter that you want to pass to the SQL query file. This parameter replaces the variables @prtg, :prtg, or ? in the SQL query, considering the general rules for SQL variables.

You can also use PRTG placeholders for custom sensors (command-line parameters) as input parameters, for example, %sensorid or %deviceid. For details, see section Custom Sensors.

i_round_blueProvide strings as they are and do not surround them with quotation marks. PRTG automatically and correctly inserts string parameters into the query.

Transaction Handling

Define if you want to use transactions and if they affect the database content:

Do not use transaction (default): Do not execute transactions.

Use transaction and always roll back: The query does not change data in the database. In the SQL query file, separate the single steps of the transaction with semicolons.

Use transaction and commit on success: The query changes data in the database. The changes only apply if all execution steps succeed without any errors. In the SQL query file, separate the single steps of the transaction with semicolons.

Data Processing

Shows how the sensor processes data from the database.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

DBNull Handling

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define the sensor behavior if the query returns DBNull:

Error: Show a Down status if the query returns DBNull.

Number 0: Recognize the result DBNull as a valid value and interpret it as the number 0.

Select Channel Value by

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define how to select the desired cell in the database table:

Column number: Determine the channel value by using the value in row 0 of the column whose number you specify in Channel #x Column Number.

Column name: Determine the channel value by using the value in row 0 of the column whose name you specify in Channel #x Column Name.

Row number: Determine the channel value by using the value in column 0 of the row whose number you specify in Channel #x Row Number.

Key value pair: Determine the channel value by searching in column 0 for the key you specify in Channel #x Key and by returning the value in column 1 of the same row where the key value was found.

i_round_blueDefining how the desired cell in the database table is selected is necessary to configure the cells that are used in the channels.

i_round_blueThe option you select here also defines the method of how to optionally determine a value for the sensor message. For details, see setting Use Data Table Value in Sensor Message.

i_square_cyanFor an example for channel value selection, see section Monitoring Databases.

Channel #x

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. The setting shows if the channel is disabled.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

Channel #x Name

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Enter a name for the channel. Enter a string. Enter a name for the channel. Enter a string. The sensor dynamically generates channels with this name as identifier.

i_round_blueIf the name contains angle brackets (<>), PRTG replaces them with braces ({}) for security reasons. For more information, see the Knowledge Base: What security features does PRTG include?

Channel #x Column Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column number for the setting Select Channel Value by. Provide the number of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Column Name

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Column name for the setting Select Channel Value by. Provide the name of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Row Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation and if you select Row number for the setting Select Channel Value by. Provide the number of the column to use to determine the channel value in row 0. Enter an integer.

Channel #x Mode

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. The setting shows how you want to display the determined value in the channel.

i_round_bluePRTG shows this value for reference purposes only. If you need to change this value, add the sensor anew.

Channel #x Unit

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define the unit of the channel value:

BytesBandwidth

BytesMemory

BytesDisk

Temperature

Percent

TimeResponse

TimeSeconds

TimeHours

Count

CPU

BytesFile

SpeedDisk

SpeedNet

Custom

Lookup

Channel #x Custom Unit

This setting is only visible if you select the channel unit Custom above. Define a unit for the channel value. Enter a string.

Channel #x Lookup

This setting is only visible if you select the channel unit Lookup above. Select a lookup file that you want to use with this channel.

Use Data Table Value in Sensor Message

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation. Define if the sensor message shows a value from the data table:

Disable: Do not use a custom sensor message.

Enable: Define a custom sensor message with a defined value of the data table. Define the value selection below.

The method of how to determine a value for the sensor message is defined in the setting Select Channel Value by above.

Sensor Message Column Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column number for the setting Select Channel Value by, and if you enable Use Data Table Value in Sensor Message. Enter the number of a column. The sensor message shows the value in row 0 of this column. Enter an integer.

Sensor Message Column Name

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Column name for the setting Select Channel Value by, and if you enable Use Data Table Value in Sensor Message. Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter a string.

i_round_blueColumns start with index 0.

Sensor Message Row Number

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Row number for the setting Select Channel Value by, and if you enable Use Data Table Value in Sensor Message. Enter the name of a column. The sensor message shows the value in row 0 of this column. Enter the number of a row. The sensor message shows the value in column 0 of this row. Enter an integer.

i_round_blueRows start with index 0.

Sensor Message Key

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation, if you select Key value pair for the setting Select Channel Value by, and if you enable Use Data Table Value in Sensor Message. Enter a key to search for in column 0 of the data table. The sensor message shows the value in column 1 of the row where the key was found. Enter a string.

Sensor Message

This setting is only visible if you enable Use Data Table Value in Sensor Message. Define the sensor message. Enter a string. Use the placeholder {0} at the position where you want to display the value.

Example: The message is {0}

i_round_blueThe number sign (#) is not supported in sensor messages. If a message contains a number sign, the message is clipped at this point.

If Sensor Message Changes

This setting is only visible if you select Process data table for the setting Data Processing during sensor creation.

Define what the sensor does when the sensor message changes:

Ignore changes (default): Do nothing.

Trigger 'change' notification: Send an internal message indicating that the sensor value has changed. In combination with a change trigger, you can use this mechanism to trigger a notification whenever the sensor value changes.

Result Handling

Define what PRTG does with the sensor result:

Discard result: Do not store the sensor result.

Store result: Store the last sensor result in the \Logs\sensors subfolder of the PRTG data directory on the probe system. The file names are Result of Sensor [ID].txt, Result of Sensor [ID].Data.txt, and Result of Sensor [ID].log. This setting is for debugging purposes. PRTG overwrites these files with each scanning interval.

i_round_blueIn a cluster, PRTG stores the result in the PRTG data directory of the master node.

Sensor Display

Sensor Display

Sensor Display

Setting

Description

Primary Channel

Select a channel from the list to define it as the primary channel. In the device tree, the last value of the primary channel is always displayed below the sensor's name. The available options depend on what channels are available for this sensor.

i_round_blueYou can set a different primary channel later by clicking b_channel_primary below a channel gauge on the sensor's Overview tab.

Graph Type

Define how different channels are shown for this sensor:

Show channels independently (default): Show a graph for each channel.

Stack channels on top of each other: Stack channels on top of each other to create a multi-channel graph. This generates a graph that visualizes the different components of your total traffic.
i_round_redYou cannot use this option in combination with manual Vertical Axis Scaling (available in the channel settings).

Stack Unit

This setting is only visible if you enable Stack channels on top of each other as Graph Type. Select a unit from the list. All channels with this unit are stacked on top of each other. By default, you cannot exclude single channels from stacking if they use the selected unit. However, there is an advanced procedure to do so.

Inherited Settings

By default, all of these settings are inherited from objects that are higher in the hierarchy. We recommend that you change them centrally in the root group settings if necessary. To change a setting for this object only, click b_inherited_enabled under the corresponding setting name to disable the inheritance and to display its options.

i_square_cyanFor more information, see section Inheritance of Settings.

Scanning Interval

Scanning Interval

Scanning Interval

i_square_cyanFor more information, see section Root Group Settings, section Scanning Interval.

Schedules, Dependencies, and Maintenance Window

i_round_blueYou cannot interrupt the inheritance for schedules, dependencies, and maintenance windows. The corresponding settings from the parent objects are always active. However, you can define additional schedules, dependencies, and maintenance windows. They are active at the same time as the parent objects' settings.

Schedules, Dependencies, and Maintenance Window

Schedules, Dependencies, and Maintenance Window

i_square_cyanFor more information, see section Root Group Settings, section Schedules, Dependencies, and Maintenance Window.

Access Rights

Access Rights

Access Rights

i_square_cyanFor more information, see section Root Group Settings, section Access Rights.

Channel Unit Configuration

i_round_blueWhich channel units are available depends on the sensor type and the available parameters. If no configurable channels are available, this field shows No configurable channels.

Channel Unit Configuration

Channel Unit Configuration

i_square_cyanFor more information, see section Root Group Settings, section Channel Unit Configuration.

Channel List

i_round_blueWhich channels the sensor actually shows might depend on the target device, the available components, and the sensor setup.

Channel

Description

Affected Rows

The number of rows that were addressed by the query (including SELECT statements if you process data tables)

Execution Time

The execution time of the entire request (including connection buildup, query execution, transaction handling, disconnection) in milliseconds (msec)

i_round_blueThis channel is the primary channel by default.

Downtime

In the channel table on the Overview tab, this channel never shows any values. PRTG uses this channel in graphs and reports to show the amount of time in which the sensor was in the Down status

Execution Time

The execution time of the specified query in msec

More

i_square_blueKNOWLEDGE BASE

How can I monitor strings from an SQL database and show a sensor status depending on it?

https://kb.paessler.com/en/topic/63259

Which .NET version does PRTG require?

https://kb.paessler.com/en/topic/60543

Why do I have to store SQL sensor queries and custom scripts in files on the probe computer?

https://kb.paessler.com/en/topic/75372

What security features does PRTG include?

https://kb.paessler.com/en/topic/61108