PRTG Manual: PostgreSQL Sensor
The PostgreSQL sensor monitors a database on a PostgreSQL server and executes a defined query.
The sensor can show the following:
- Execution time of the whole request (including connection buildup, query execution, transaction handling, disconnection)
- Execution time of the defined query
- Number of rows that were addressed by the query (including SELECT statements if you process data tables)
- It can also process the data table and show defined values in individual channels.
- Dutch: PostgreSQL
- French: PostgreSQL
- German: PostgreSQL
- Japanese: PostgreSQL
- Portuguese: PostgreSQL
- Russian: PostgreSQL
- Simplified Chinese: PostgreSQL
- Spanish: PostgreSQL
- This sensor requires .NET 4.7.2 or later 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 into 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 PostgreSQL 7.x or later.
- This sensor supports the IPv6 protocol.
- This sensor has a high performance impact.
- 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?
You cannot add this sensor to the hosted probe of a PRTG hosted by Paessler instance. If you want to use this sensor, add it to a remote probe device.
Requirement |
Description |
---|---|
.NET Framework |
This sensor requires the Microsoft .NET Framework. .NET 4.7.2 or later must be installed on the probe system (on every cluster node, if on a cluster probe). If the framework is missing, you cannot create this sensor.
|
The Add Sensor dialog appears when you manually add a new sensor to a device. It only shows the setting fields that are required for creating the sensor. Therefore, you do not see all setting fields in this dialog. You can change (nearly) all settings in the sensor's Settings tab later.
The following settings in the Add Sensor dialog differ in comparison to the sensor's Settings tab.
Data
Setting |
Description |
---|---|
SQL Query File |
Select an SQL script file that includes a valid SQL statement to be executed on the server. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. PRTG executes the script 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 file on all cluster nodes.
For example, 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 ";".
SQL Variables You can use the following variables in your query file to be replaced by an input parameter. This is useful if you have various SQL sensors with queries that differ in only one parameter.
Examples for variables usage: SELECT * FROM Table WHERE name = @prtg SELECT @prtg FROM Table |
Data Processing |
Define if you want to process data from the database:
Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Channel #x |
This setting is only visible if you select Process data table. You can define up to 10 different channels for the data processing of this sensor. You have to define at least one data channel if you process the data table, so you see all available settings for Channel #1 without manually enabling it. Specify how to handle all other possible channels:
Once you have created the sensor, you cannot change this value. It is shown 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. Enter a unique name for the channel. Enter a string. PRTG dynamically generates channels with this name as the identifier. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.
|
Channel #x Mode |
This setting is only visible if you select Process data table. Define how to display the determined value in the channel:
Once you have created the sensor, you cannot change this value. It is shown 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. Define the unit of the channel value:
Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.
|
Click the Settings tab of a sensor to change its settings.
Usually, a sensor connects to the IP Address or DNS Name of the parent device on which you created the sensor. See the device settings for details. For some sensors, you can explicitly define the monitoring target in the sensor settings. See below for details on available settings.
Setting |
Description |
---|---|
Sensor Name |
Enter a meaningful 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.
|
Parent Tags |
Shows tags that the sensor inherits from its parent device, parent group, and parent probe. This setting is shown for your information only and cannot be changed here. |
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.
The sensor has the following default tags that are automatically predefined in the sensor's settings when you add the sensor:
|
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 ( |
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 like tables or stored procedures exist. |
SSL Mode |
Select the PostgreSQL Secure Sockets Layer (SSL) mode for the sensor connection. PostgreSQL SSL connections require OpenSSL to be installed on both the target server and on the probe system. The SSL mode options you can choose here are the same as the values of the PostgreSQL sslmode parameter. PRTG sends it with the sensor requests. Choose from these SSL modes:
|
Setting |
Description |
---|---|
SQL Query File |
Shows the SQL script file that is executed on the server. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Use Input Parameter |
Define if you want to pass a parameter to your SQL query file:
Passing a parameter to your SQL query file replaces the variables @prtg (Microsoft SQL, MySQL, PostgreSQL), :prtg (Oracle SQL), or ? (ADO SQL) in the SQL query, considering the general rules for SQL variables. |
Input Parameter |
This setting is only visible if you enable Use input parameter 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.
|
Use Transaction |
Define if you want to use transactions and if they affect the database content:
|
Data Processing |
Shows how you process data from the database. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Handle DBNull in Channel Values as |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Define the sensor behavior if the query returns DBNull:
|
Select Channel Value by |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Define how the desired cell in the database table is selected:
Defining how the desired cell in the database table is selected is necessary to configure the cells that are used in the channels. The 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.
|
Channel #x |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. The setting shows if the channel is disabled. Once you have created the sensor, you cannot change this value. It is shown 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 when adding the sensor. Enter a unique name for the channel. Enter a string. PRTG dynamically generates channels with this name as identifier. Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew.
|
Channel #x Column Number Channel #x Column Name Channel #x Row Number Channel #x Key |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Depending on the option you selected under Select Channel Value by, enter the respective value in the respective field:
|
Channel #x Mode |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. The setting shows how you want to display the determined value in the channel. Once you have created the sensor, you cannot change this value. It is shown 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 when adding the sensor. Define the unit of the channel value:
Once you have created the sensor, you cannot change this value. It is shown for reference purposes only. If you need to change this value, add the sensor anew. |
Channel #x Custom Unit |
This setting is only visible if you select the channel unit Custom. 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. 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 when adding the sensor. Define if the sensor message shows a value from the data table:
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 Sensor Message Column Name Sensor Message Row Number Sensor Message Key |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor and if you enable Use Data Table Value in Sensor Message. Depending on the option you selected under Select Channel Value by, enter the respective value in the respective field:
|
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}
|
If Sensor Message Changes |
This setting is only visible if you select Process data table for the setting Data Processing when adding the sensor. Define what the sensor does when the sensor value changes:
|
Result Handling |
Define what PRTG does with the sensor result:
|
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.
|
Graph Type |
Define how different channels are shown for this sensor:
|
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. |
By default, all of the following settings are inherited from objects that are higher in the hierarchy. You should change them centrally in the root group settings if necessary. To change a setting for this object only, click under the corresponding setting name to disable the inheritance. You then see the options described below.
For more information, see section Inheritance of Settings.
Click to interrupt the inheritance.
Setting |
Description |
---|---|
Scanning Interval |
Select a scanning interval from the dropdown list. The scanning interval determines the amount of time that the sensor waits between two scans. Choose from:
|
If a Sensor Query Fails |
Select the number of scanning intervals that the sensor has time to reach and to check a device again if a sensor query fails. Depending on the option that you select, the sensor can try to reach and to check a device again several times before the sensor shows the Down status. This can avoid false alarms if the monitored device only has temporary issues. For previous scanning intervals with failed requests, the sensor shows the Warning status. Choose from:
|
Schedules, Dependencies, and Maintenance Window
You 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.
Setting |
Description |
---|---|
Schedule |
Select a schedule from the list. You can use schedules to monitor during a certain time span (days or hours) every week. Choose from:
|
Maintenance Window |
Select if you want to set up a one-time maintenance window. During a maintenance window, monitoring stops for the selected object and all child objects. They show the Paused status instead. Choose between:
|
Maintenance Begins |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the start date and time of the one-time maintenance window. |
Maintenance Ends |
This setting is only visible if you enable Set up a one-time maintenance window above. Use the date time picker to enter the end date and time of the one-time maintenance window. |
Dependency Type |
Select a dependency type. You can use dependencies to pause monitoring for an object depending on the status of a different object. You can choose from:
|
Dependency |
This setting is only visible if you enable Select a sensor above. Click |
Dependency Delay (Sec.) |
This setting is only visible if you enable Select a sensor above. Define a time span in seconds for the dependency delay. After the master sensor for this dependency returns to the Up status, PRTG additionally delays the monitoring of the dependent objects by the time span you define. This can prevent false alarms, for example, after a server restart or to give systems more time for all services to start. Enter an integer value.
|
Click to interrupt the inheritance.
Setting |
Description |
---|---|
User Group Access |
Define the user groups that have access to the sensor. You see a table with user groups and group access rights. The table contains all user groups in your setup. For each user group, you can choose from the following group access rights:
|
Click to interrupt the inheritance.
Setting |
Description |
---|---|
Channel Unit Types |
For each type of channel, select the unit in which PRTG displays the data. If you define this setting on probe, group, or device level, these settings can be inherited to all sensors underneath. You can set units for the following channel types (if available):
|
KNOWLEDGE BASE
How can I monitor strings from an SQL database and show a sensor status depending on it?
Which .NET version does PRTG require?
For more information about sensor settings, see the following sections: