How to Prevent Telegraf from Filling a Postgres Database with NULLs When Collecting OPCUA Data?

,
1

Hello! I’m new to Telegraf and need some advice regarding my setup. I have Telegraf configured to collect data from an OPCUA server on a PLC and store it in a Postgres database (TimescaleDB specifically). While the basic setup works fine, I’ve noticed a significant issue: the database is filling up with NULL values, and the data appears staggered across rows.

image
image1891×297 10.3 KB

This causes problems with queries like:

SELECT "column_a" - "column_b" AS "column_c" FROM MyTable;

Because of the NULLs, column_c ends up being entirely NULL.

I’d like to understand:

  1. Why Telegraf might be inserting NULLs instead of actual values.
  2. How to configure Telegraf to handle missing or unavailable data more gracefully.
  3. Any best practices for dealing with sparse or staggered data in this kind of setup.

Any insights or suggestions would be greatly appreciated. Thank you!

Below is some info about my setup. Please let me know if I can provide more info!

  • Telegraf version: Docker image telegraf:1.32.0
  • Postgres / TimescaleDB version: Docker image timescale/timescaledb:2.16.1-pg16

Config file

[global_tags]
    project = "MyProject"


[[outputs.postgresql]]
    ## Connection string
    connection = "host=timescaledb user=user password=psswrd sslmode=disable dbname=MyDB"

    create_templates = ['''CREATE TABLE {{ .table }} ({{ .columns }})''']

    schema = "public"

    # Required for Grafana usage
    timestamp_column_name = "time"


[[processors.date]]
    date_format = "unix_ms"
    timezone = "UTC"
    field_key = "timestamp"


[[inputs.opcua]]
    ## Metric name
    name = "opcua"

    ## OPCUA endpoint
    endpoint = "opc.tcp://my.plc.ip.addr:port"

    ## Maximum time allowed to establish a connect to the endpoint.
    connect_timeout = "10s"

    auth_method = "Anonymous"
    security_mode = "None"
    security_policy = "None"

    # Interval to poll the PLC
    interval = "1s"

    [[inputs.opcua.group]]
        name = "table 0"
        nodes = [
            {name="column_a", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_a"},
            {name="column_b", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_b"},
        ]


    [[inputs.opcua.group]]
        name = "table 1"
        nodes = [
            {name="column_a", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_a"},
            {name="column_b", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_b"},
        ]


    [[inputs.opcua.group]]
        name = "table 2"
        nodes = [
            {name="column_a", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_a"},
            {name="column_b", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_b"},
        ]


    [[inputs.opcua.group]]
        name = "table 3"
        nodes = [
            {name="column_a", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_a"},
            {name="column_b", namespace="2", identifier_type="s", identifier="[edge]/path/to/column_b"},
        ]