PostgreSQL

Connect to and query PostgreSQL databases with OIBus.

Compatibility Notes

• Supports PostgreSQL 9.6 and later

Specific Settings

Setting	Description	Example Value
Host	IP address or hostname of the PostgreSQL server.	192.168.1.10
Port	Server port number. Default: 5432.	5432
SSL	Enable encrypted connection. Recommended for production.	Enabled/Disabled
Database	Name of the database to connect to.	production
Connection timeout	Maximum time in milliseconds to establish connection. Default: 10000.	10000
Request timeout	Maximum execution time in milliseconds for individual SQL queries.	15000
Username	Authentication username.	db_user
Password	Authentication password.	••••••••

Security Best Practices

• Use a read-only database user

Performance Considerations

Query Optimization:

• Use indexed columns in WHERE clauses
• Limit result sets (avoid SELECT *)

Resource Management:

• SSL encryption adds minimal overhead
• Increase timeouts for large datasets or complex queries

Group Settings

Items can be organised into groups. Each group defines a shared collection schedule and default throttling settings. Items in the same group are still fetched one at a time in sequence — the group simply provides common defaults that individual items can override.

Setting	Description	Example Value
Name	Unique label for the group within this connector.	Group A
Scan mode	Schedule used to collect all items in the group.	Every 1 min
Throttling	Default throttling values (Max read interval, Read delay, Start time offset, End time offset, Recovery strategy) inherited by items in the group.	3600, 200, 0, 0, oldest

Item Settings

Each item can be individually configured with its own query parameters and datetime handling. Items inherit their scan mode and throttling defaults from their group, but each setting can be overridden per item by disabling Sync with group.

Throttling Settings

Throttling controls how OIBus paces historical data requests. These settings appear on each group (for connectors that support groups) or on each item (for single-item connectors). Items in a group can override the group defaults by disabling the Sync with group toggle.

Setting	Description	Example Value
Max read interval	Maximum duration of each sub-query in seconds. Larger time ranges are automatically split into chunks not exceeding this value.	3600
Read delay	Pause in milliseconds between consecutive sub-queries. Helps prevent server overload and manages rate limits.	1000
Start time offset	Milliseconds added to the start of the query window (@StartTime). A negative value moves the start earlier, to capture late-arriving data from the previous interval — this is the old "Overlap" behavior. A positive value moves the start later instead, skipping that much of the window.	-60000
End time offset	Milliseconds added to the end of the query window (@EndTime). A negative value pulls the end in earlier — useful for eventually-consistent sources where the very latest rows aren't reliable yet. A positive value extends the window later. If the resulting end is not after the effective start, the query is skipped for this run.	0
Recovery strategy	Order in which OIBus catches up on a backlog of unqueried sub-intervals — e.g. after being stopped for a while, or on first run against a wide time range. From oldest to newest (default) processes the backlog chronologically. From newest to oldest queries the most recent sub-interval first, so up-to-date values become available immediately while older gaps are backfilled afterward.	From oldest to newest

How Throttling Works

1. Interval splitting — A 24-hour range with Max read interval = 3600 (1 hour) is split into 24 separate 1-hour sub-queries.
2. Read delay — A pause is inserted between sub-queries to manage server load.
3. Start/End time offset — With Start time offset = -60000 (-1 minute), a query for [10:00–11:00] actually requests [9:59–11:00], ensuring no late-arriving data is missed. End time offset shifts the other boundary the same way.
4. Recovery strategy — Only matters when there's more than one sub-interval to catch up on. With From newest to oldest, the tracked instant only advances once every sub-interval in the backlog has been queried — this avoids skipping over not-yet-queried older intervals if OIBus restarts mid-catch-up.

Offsets apply to the full query range

Start/End time offset are applied once, to the start and end of the overall query window — not to the start of each individual sub-interval when a large range is split into chunks by Max read interval.

Recommended Configurations

Scenario	Max read interval	Read delay	Start time offset
Stable network, small datasets	3600 (1 hour)	500	0 (none)
Unstable network	1800 (30 min)	2000	0 (none)
Large historical retrievals	7200 (2 hours)	1000	0 (none)
Real-time with occasional gaps	900 (15 min)	200	-15000 (-15 sec)

Going deeper

For the reasoning behind these numbers — sizing Max read interval against real data volumes, the Read delay / Max read interval trade-off on a large backlog, and worked examples of Start vs. End time offset (including the batched multi-item case where items don't all flush at once) — see Tuning South History Call Settings.

Query Configuration

The query field accepts standard SQL syntax with support for internal variables that enhance data retrieval resilience and performance optimization.

Query Variables

Setting	Description	Example Value
@StartTime	Initialized to first execution time, then updated to the most recent timestamp from reference field	2024-01-15T10:00:00.000Z
@EndTime	Set to current time (now()) or sub-interval end when queries are split	2024-01-15T11:00:00.000Z

Example Query:

SELECT device_id, value, reading_time
FROM sensor_data
WHERE reading_time > @StartTime
AND reading_time < @EndTime
ORDER BY reading_time

Query Splitting

For handling large datasets, queries can be automatically divided into smaller time-based chunks based on the Max read interval throttling setting.

Datetime Field Configuration

Setting	Description	Example Value
Field name	Name of the datetime field in your SELECT statement	timestamp, reading_time
Reference field	Designates which field determines the @StartTime for subsequent queries	reading_time
Type	Data type of the datetime field. Available values differ by connector — see the connector-specific note below.	iso-string, unix-epoch
Timezone	Timezone of the stored datetime (for string/date types)	UTC, Europe/Paris
Format	Format pattern for string-based datetimes	yyyy-MM-dd HH:mm:ss
Locale	Locale for format elements (e.g., month names)	en_US, fr_FR

Type Consistency Requirement

When using datetime conversions in queries, keep the type consistent in both the SELECT list and the WHERE clause:

Problematic approach (may cause unexpected behavior):

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE string_field > @StartTime

Recommended approach:

SELECT value, CONVERT(datetime, string_field) AS timestamp
FROM table
WHERE CONVERT(datetime, string_field) > @StartTime

Data Flow Process

1. OIBus executes each configured query sequentially.
2. Results are consolidated into structured output.
3. The max instant is extracted from the reference datetime field, converted to UTC, and stored as the next @StartTime.
4. Final output is formatted according to serialization settings.
5. Processed data is sent to configured North connectors.

Best Practices

1. Choose reference fields with consistent, increasing values.
2. For string datetimes, ensure the format matches between database storage and query variables.
3. Add indexes on datetime fields for better query performance.
4. Use the Max read interval to split large time ranges into manageable chunks.

CSV Serialization

OIBus provides flexible options for serializing retrieved data into CSV format with customizable output settings.

File Configuration

Setting	Description	Example Value
Filename	Name pattern for output files.	data_@ConnectorName_@CurrentDate.csv
Delimiter	Character used to separate values in the CSV file	COMMA (,), SEMI_COLON (;), DOT (.), COLON (:), PIPE (|), SLASH (/), TAB (\t), NON_BREAKING_SPACE
Compression	Enable gzip compression for output files	Enabled/Disabled

Filename Variables

The following variables can be used in filename patterns:

• @ConnectorName: Automatically replaced with the connector's name
• @CurrentDate: Inserts current timestamp in fixed yyyy_MM_dd_HH_mm_ss_SSS format

Temporal Data Handling

Setting	Description	Example Value
Output datetime format	Format pattern for datetime fields in the CSV (does not affect @CurrentDate in filenames)	yyyy-MM-dd HH:mm:ss
Output timezone	Timezone used for datetime values in the CSV	UTC or Europe/Paris

Important Notes

1. The @CurrentDate variable in filenames uses a fixed format (yyyy_MM_dd_HH_mm_ss_SSS) regardless of the datetime format setting
2. Only datetime fields specified in your configuration will be formatted according to these settings
3. Timezone conversion only applies to datetime values in the CSV content, not to the filename timestamp

PostgreSQL datetime field types

The following Type values are available for datetime field configuration:

Type	Description
String	String representation parsed with a custom format
Timestamp	PostgreSQL TIMESTAMP column (no timezone)
Timestamp with timezone	PostgreSQL TIMESTAMPTZ column (with timezone)
ISO String	ISO 8601 string (e.g. 2024-01-15T10:30:00.000Z)
UNIX epoch (s)	Unix timestamp in seconds
UNIX epoch (ms)	Unix timestamp in milliseconds