search

influxdata / telegraf

Agent for collecting, processing, aggregating, and writing metrics, logs, and other arbitrary data.
https://influxdata.com/telegraf
MIT License
14.57k stars 5.56k forks source link

[outputs_postgresql] Problem with long column name #15964

Open Lumiond opened 1 week ago

Lumiond commented 1 week ago

Relevant telegraf.conf

[global_tags]
[agent]
  interval = "10s"
  round_interval = true
  metric_batch_size = 1000
  metric_buffer_limit = 10000
  collection_jitter = "0s"
  flush_interval = "10s"
  flush_jitter = "0s"
  precision = ""
  hostname = ""
  omit_hostname = false
  debug = false
  quiet = false
  logtarget = "file"
  logfile = "/var/log/telegraf/telegraf.log"

[[outputs.postgresql]]
  connection = "host=192.168.1.2 user=telegraf password=telegraf sslmode=disable dbname=sysmetrics"

  create_templates = [
    '''CREATE TABLE IF NOT EXISTS {{.table}}({{.allColumns}})''',
    '''SELECT create_hypertable({{.table|quoteLiteral}},'time',chunk_time_interval := INTERVAL '1 week',if_not_exists := true)''',
    '''SELECT add_retention_policy({{ .table|quoteLiteral }}, INTERVAL '8 week')''',
  ]

[[inputs.elasticsearch]]
  servers = ["https://192.168.1.3:9200"]

  http_timeout = "5s"

  local = false
  cluster_health = true

  cluster_stats = true
  cluster_stats_only_from_master = true

  indices_include = ["_all"]
  indices_level = "shards"

  node_stats = ["os", "process", "fs", "transport"]

  username = "admin"
  password = "admin"

  tls_ca = "/etc/opensearch/root-ca.pem"
  insecure_skip_verify = true

Logs from Telegraf

2024-10-02T12:47:04Z I! Loading config: /etc/telegraf/telegraf.conf
2024-10-02T12:47:04Z W! DeprecationWarning: Option "http_timeout" of plugin "inputs.elasticsearch" deprecated since version 1.29.0 and will be removed in 1.35.0: use 'timeout' instead
2024-10-02T12:47:04Z W! Agent setting "logtarget" is deprecated, please just set "logfile" and remove this setting! The setting will be removed in v1.40.0.
2024-10-02T12:47:04Z I! Starting Telegraf 1.32.0 brought to you by InfluxData the makers of InfluxDB
2024-10-02T12:47:04Z I! Available plugins: 235 inputs, 9 aggregators, 32 processors, 26 parsers, 62 outputs, 6 secret-stores
2024-10-02T12:47:04Z I! Loaded inputs: elasticsearch
2024-10-02T12:47:04Z I! Loaded aggregators:
2024-10-02T12:47:04Z I! Loaded processors:
2024-10-02T12:47:04Z I! Loaded secretstores:
2024-10-02T12:47:04Z I! Loaded outputs: postgresql
2024-10-02T12:47:04Z I! Tags enabled: host=tst-opnsrch
2024-10-02T12:47:04Z W! Deprecated inputs: 0 and 1 options
2024-10-02T12:47:04Z I! [agent] Config: Interval:10s, Quiet:false, Hostname:"tst-opnsrch", Flush Interval:10s
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Table "elasticsearch_clusterstats_indices" is missing columns (omitting fields): segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "network_types_http_types_org.opensearch.security.http.SecurityHttpServerTransport"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "network_types_transport_types_org.opensearch.security.ssl.http.netty.SecuritySSLNettyTransport"
2024-10-02T12:47:14Z E! [outputs.postgresql] Table "elasticsearch_clusterstats_nodes" is missing columns (omitting fields): network_types_http_types_org.opensearch.security.http.SecurityHttpServerTransport double precision, network_types_transport_types_org.opensearch.security.ssl.http.netty.SecuritySSLNettyTransport double precision
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_failed_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__translog_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Table "elasticsearch_indices_stats_primaries" is missing columns (omitting fields): __segments_remote_store_download_total_download_size_failed_bytes double precision, __segments_remote_store_download_total_download_size_started_bytes double precision, __segments_remote_store_download_total_download_size_succeeded_bytes double precision, __segments_remote_store_upload_total_upload_size_succeeded_bytes double precision, __translog_remote_store_upload_total_upload_size_succeeded_bytes double precision, segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_failed_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "__translog_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Table "elasticsearch_indices_stats_total" is missing columns (omitting fields): __segments_remote_store_download_total_download_size_failed_bytes double precision, __segments_remote_store_download_total_download_size_started_bytes double precision, __segments_remote_store_download_total_download_size_succeeded_bytes double precision, __segments_remote_store_upload_total_upload_size_succeeded_bytes double precision, __translog_remote_store_upload_total_upload_size_succeeded_bytes double precision, segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:14Z E! [outputs.postgresql] Table "elasticsearch_indices_stats_shards" is missing columns (omitting fields): segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_failed_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "__segments_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "__translog_remote_store_upload_total_upload_size_succeeded_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Table "elasticsearch_indices_stats_total" is missing columns (omitting fields): __segments_remote_store_download_total_download_size_failed_bytes double precision, __segments_remote_store_download_total_download_size_started_bytes double precision, __segments_remote_store_download_total_download_size_succeeded_bytes double precision, __segments_remote_store_upload_total_upload_size_succeeded_bytes double precision, __translog_remote_store_upload_total_upload_size_succeeded_bytes double precision, segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_started_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "segments_remote_store_download_total_download_size_succeeded_bytes"
2024-10-02T12:47:24Z E! [outputs.postgresql] Table "elasticsearch_indices_stats_shards" is missing columns (omitting fields): segments_remote_store_download_total_download_size_started_bytes double precision, segments_remote_store_download_total_download_size_succeeded_bytes double precision
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "network_types_http_types_org.opensearch.security.http.SecurityHttpServerTransport"
2024-10-02T12:47:24Z E! [outputs.postgresql] Column name too long: "network_types_transport_types_org.opensearch.security.ssl.http.netty.SecuritySSLNettyTransport"

System info

Telegraf 1.32.0, Debian 10, PgSQL 13.5, TimescaleDB 2.5.1

Docker

No response

Steps to reproduce

  1. Use elasticsearch input and postgresql output. See config for settings
  2. Start telegraf

Expected behavior

All column's names with long length (greater than default pgsql column length name which equal 63 bytes, see https://www.postgresql.org/docs/current/limits.html) must be cut off. At least I have seen this behavior in old custom telegraf with pgsql_out (version 1.13)

Actual behavior

Long name does not change, so I lose metrics and get many errors in logs.

Additional info

I use elasticsearch input for opensearch cluster

srebhan commented 6 days ago

We cannot simply trim the field names without knowing anything. What if for examples there are two fields called segments_remote_store_download_initial_session_download_size_bytes_succeeded and segments_remote_store_download_initial_session_download_size_bytes_failed? After auto-trimming both would be named segments_remote_store_download_initial_session_download_size_by...

What you can do is to trim the fields yourself using the regex processor using

[[processors.regex]]
  [[processors.regex.field_rename]]
    pattern = '^_*(.{0,63}).*$'
    replacement = "${1}"
    ## If the new field name already exists, you can either "overwrite" the
    ## existing one with the value of the renamed field OR you can "keep"
    ## both the existing and source field.
    # result_key = "keep"
Lumiond commented 6 days ago

I see, but we have an official limit on identifier length. I think it's not acceptable to break official limits, so we need something to control identifiers, maybe some kind of mapping? And if you don't want to fix this bug, it's better to stop spamming the same information in logs.

srebhan commented 4 days ago

First of all, I do agree that we should mention each field only once in the log. Regarding cutting the length: How about an option to cut the fields with the default to do nothing? It has to be an option as different versions of Postgres do have different limits and even the official docs suggest that you might change the limit in code. Would that work for you?