search

influxdata / telegraf

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

Postgres output plugin show error with custom "create_templates" #15273

Closed pbwur closed 5 months ago

pbwur commented 5 months ago

Relevant telegraf.conf

[[outputs.postgresql]]
      connection = "host=postgres-postgresql-hl user=metrics password=xxxxxx sslmode=disable dbname=xxxxxxxx"
      # timestamp_column_name = "timestamp"
      schema = "public"
      create_templates = [
        '''CREATE TABLE {{.table}} ({{.columns}})''',
      ]

Logs from Telegraf

2024-05-01T17:24:17Z I! [outputs.postgresql] PG Exec - map[args:[] commandTag:SAVEPOINT pid:426127 sql:savepoint sp_1 time:462.999µs]
2024-05-01T17:24:17Z I! [outputs.postgresql] PG Exec - map[args:[5705450890675909945] commandTag:SELECT 1 pid:426127 sql:SELECT pg_advisory_xact_lock($1) time:521.423µs]
2024-05-01T17:24:17Z I! [outputs.postgresql] PG Query - map[args:[public sanbi.wurnet.nl] pid:426127 rowCount:0 sql:
        SELECT
            column_name,
            CASE WHEN data_type='USER-DEFINED' THEN udt_name ELSE data_type END,
            col_description(format('%I.%I', table_schema, table_name)::regclass::oid, ordinal_position)
        FROM information_schema.columns
        WHERE table_schema = $1 and table_name = $2 time:1.241104ms]
2024-05-01T17:24:17Z E! [outputs.postgresql] PG Exec - map[args:[] err:ERROR: syntax error at or near "(" (SQLSTATE 42601) pid:426127 sql:CREATE TABLE  () time:604.871µs]
2024-05-01T17:24:17Z I! [outputs.postgresql] PG Exec - map[args:[] commandTag:ROLLBACK pid:426127 sql:rollback to savepoint sp_1 time:297.093µs]
2024-05-01T17:24:17Z E! [outputs.postgresql] Permanent error updating schema for sanbi.wurnet.nl: executing "CREATE TABLE  ()": ERROR: syntax error at or near "(" (SQLSTATE 42601)
2024-05-01T17:24:17Z E! [outputs.postgresql] write error (permanent, dropping sub-batch): metric/table mismatch: Unable to omit field/column from "sanbi.wurnet.nl": critical column "time"
2024-05-01T17:24:17Z I! [outputs.postgresql] PG Exec - map[args:[] commandTag:ROLLBACK pid:426127 sql:rollback time:816.001µs]

System info

Telegraf: 1.30.2, Kubernetes: v1.25.16, Postgres: 16

Docker

No response

Steps to reproduce

  1. Start Telegraf with the Postgres ouput plugin config as shown
  2. Wait till metrics are received
  3. Postgres output plugin tries to process the metrics by creating a new table in Postres ...

Expected behavior

Without any configuration of the Postgres output plugin everything works as expected. But using a custom "create_templates" template I'm receiving the shown error message:

syntax error at or near "(" (SQLSTATE 42601) pid:426127 sql:CREATE TABLE () time:604.871µs]

Even if the "create_template" configuration is the same as the default (without the "create_template").

Actual behavior

The expected behavior is that the the sql statements in "create_template" are executed successfully. Starting with the one that, reading the documentation, should be the same as the default.

Additional info

No response

powersj commented 5 months ago

Hi,

Unable to omit field/column from "sanbi.wurnet.nl": critical column "time"

I think this error message is also important, no? It sounds like you may be trying to access an existing table with schema. Since you are asking Telegraf to set the schema, you probably need to add schema = "telegraf" to your config.

Similar to this comment: https://github.com/influxdata/telegraf/pull/8651#issuecomment-1100157451

pbwur commented 5 months ago

Hi,

Thanks for responding! I do have "schema=public" in the configuration. The strange thing is that without any configuration except the connection and schema properties everything goes fine:

2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:CREATE TABLE pid:435887 sql:CREATE TABLE "public"."sanbi.wurnet.nl" ("time" timestamp without time zone, "cpu" text, "device" tex │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."cpu" IS 'tag' time:532.472µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."device" IS 'tag' time:305.105µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."fstype" IS 'tag' time:305.154µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."host" IS 'tag' time:322.83µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."mode" IS 'tag' time:291.996µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."path" IS 'tag' time:330.41µs] │ │ 2024-05-01T19:06:23Z I! [outputs.postgresql] PG Exec - map[args:] commandTag:COMMENT pid:435887 sql:COMMENT ON COLUMN "public"."sanbi.wurnet.nl"."topic" IS 'tag' time:318.282µs]

But if I add the "create_template" property the error occurs.

powersj commented 5 months ago

I do have "schema=public" in the configuration

Right and I didn't say public, I said schema = "telegraf"

pbwur commented 5 months ago

I'm probably just not understanding it properly. The schema "telegraf" does not exist in the database, the schema "public" does. And that's the scheme I want to use. Without the "create_templates" property, the table is created in the "public" schema as expected.

powersj commented 5 months ago

My reading of the comment I linked to was that you needed to use the telegraf schema after creating the schema. Similar to the original PR comment:

The templates basically allow creating the tables in the telegraf schema, and then a view in the public schema which joins the tag table and the data table making it easier to work with

pbwur commented 5 months ago

I see what you mean. Unfortunately I get the same error after setting schema = "telegraf"

pbwur commented 5 months ago

Somehow it looks like the name of the table, and perhaps other values also, is not passed to the template.

powersj commented 5 months ago

can you provide an example data point please?

pbwur commented 5 months ago

I'm quite new to telegraf so I hope this is an example of a data point:

sanbi.wurnet.nl,host=sanbi.wurnet.nl,topic=unlock/fdp/sanbi.wurnet.nl/system uptime="7985729" 1714594920000000000

Without the create_templates this is saved successful in a Postgres table "sanbi.wurnet.nl" in the public schema.

powersj commented 5 months ago

Launched postgres via:

docker run -it --rm --net host -e POSTGRES_PASSWORD=password postgres

My config:

[agent]
  debug = true
  omit_hostname = true

[[outputs.file]]

[[inputs.file]]
  files = ["data"]
  data_format = "influx"

[[outputs.postgresql]]
      connection = "host=localhost user=postgres password=password sslmode=disable dbname=postgres"
      schema = "public"
      create_templates = [
        '''CREATE TABLE {{.table}} ({{.columns}})''',
      ]
❯ ./telegraf --config config.toml --once
2024-05-01T20:43:17Z I! Loading config: config.toml
2024-05-01T20:43:17Z I! Starting Telegraf 1.31.0-553d972c brought to you by InfluxData the makers of InfluxDB
2024-05-01T20:43:17Z I! Available plugins: 234 inputs, 9 aggregators, 32 processors, 25 parsers, 60 outputs, 6 secret-stores
2024-05-01T20:43:17Z I! Loaded inputs: file
2024-05-01T20:43:17Z I! Loaded aggregators: 
2024-05-01T20:43:17Z I! Loaded processors: 
2024-05-01T20:43:17Z I! Loaded secretstores: 
2024-05-01T20:43:17Z I! Loaded outputs: file postgresql
2024-05-01T20:43:17Z I! Tags enabled: 
2024-05-01T20:43:17Z D! [agent] Initializing plugins
2024-05-01T20:43:17Z D! [agent] Connecting outputs
2024-05-01T20:43:17Z D! [agent] Attempting connection to [outputs.file]
2024-05-01T20:43:17Z D! [agent] Successfully connected to outputs.file
2024-05-01T20:43:17Z D! [agent] Attempting connection to [outputs.postgresql]
2024-05-01T20:43:18Z D! [agent] Successfully connected to outputs.postgresql
2024-05-01T20:43:18Z D! [agent] Starting service inputs
2024-05-01T20:43:18Z D! [agent] Stopping service inputs
2024-05-01T20:43:18Z D! [agent] Input channel closed
2024-05-01T20:43:18Z I! [agent] Hang on, flushing any cached metrics before shutdown
sanbi.wurnet.nl,host=sanbi.wurnet.nl,topic=unlock/fdp/sanbi.wurnet.nl/system uptime="7985729" 1714594920000000000
2024-05-01T20:43:18Z D! [outputs.file] Wrote batch of 1 metrics in 17.33µs
2024-05-01T20:43:18Z D! [outputs.file] Buffer fullness: 0 / 10000 metrics
2024-05-01T20:43:18Z D! [outputs.postgresql] Wrote batch of 1 metrics in 18.449166ms
2024-05-01T20:43:18Z D! [outputs.postgresql] Buffer fullness: 0 / 10000 metrics
2024-05-01T20:43:18Z I! [agent] Stopping running outputs
2024-05-01T20:43:18Z D! [agent] Stopped Successfully
root@ryzen:/# psql -h localhost -U postgres
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# use postgres
postgres-# \d
              List of relations
 Schema |      Name       | Type  |  Owner   
--------+-----------------+-------+----------
 public | sanbi.wurnet.nl | table | postgres
(1 row)

postgres=# select * from "sanbi.wurnet.nl";
        time         |      host       |               topic               | uptime  
---------------------+-----------------+-----------------------------------+---------
 2024-05-01 20:22:00 | sanbi.wurnet.nl | unlock/fdp/sanbi.wurnet.nl/system | 7985729
(1 row)
pbwur commented 5 months ago

Thanks for testing. I's really strange, I have the exact same config. Especially the fact that it is working without the "create_templates". I will keep on trying and keep you posted. Thanks a lot so far!

pbwur commented 5 months ago

Hi, I just found out what my problem is. I'm using a helm chart for deploying Telegraf to kubernetes. Helm sees the accolades as part of its own templating and removes them. So only this is put in the config file:

create_templates = [ '''CREATE TABLE ()''', ]

And then everything makes sense. So now I only have to find out how to make helm ignore those accolades.

pbwur commented 5 months ago

using the following works:

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

powersj commented 5 months ago

I'm using a helm chart for deploying Telegraf to kubernetes. Helm sees the accolades as part of its own templating and removes them.

oh wow - TIL! Thank you very much for digging into it! I'll go ahead and close this then.