postgresql output plugin cannot create schemas

[feld]

Relevant telegraf.conf

[[outputs.postgresql]]
  connection = "REDACTED"

  schema = "telegraf"

  tags_as_foreign_keys = true

  create_templates = [
    '''CREATE TABLE {{ .table }} ({{ .columns }})''',
    '''CREATE VIEW {{ .table.WithSchema "public" }} AS SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }} FROM {{ .table }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id
''',
  ]

add_column_templates = [
    '''ALTER TABLE {{ .table }} ADD COLUMN IF NOT EXISTS {{ .columns|join ", ADD COLUMN IF NOT EXISTS " }}''',
    '''DROP VIEW IF EXISTS {{ .table.WithSchema "public" }}''',
    '''CREATE VIEW {{ .table.WithSchema "public" }} AS SELECT time, {{ (.tagTable.Columns.Tags.Concat .allColumns.Fields).Identifiers | join "," }} FROM {{ .table }} t, {{ .tagTable }} tt WHERE t.tag_id = tt.tag_id
''',
]

tag_table_add_column_templates = [
    '''ALTER TABLE {{.table}} ADD COLUMN IF NOT EXISTS {{.columns|join ", ADD COLUMN IF NOT EXISTS "}}''',
    '''DROP VIEW IF EXISTS {{ .metricTable.WithSchema "public" }}''',
    '''CREATE VIEW {{ .metricTable.WithSchema "public" }} AS SELECT time, {{ (.allColumns.Tags.Concat .metricTable.Columns.Fields).Identifiers | join "," }} FROM {{ .metricTable }} t, {{ .tagTable }} tt WHERE t.tag
_id = tt.tag_id''',
]

Logs from Telegraf

n/a, it's just sql errors

System info

1.24.0

Docker

No response

Steps to reproduce

Use the above example config where raw data is dumped into the Telegraf schema, but a view is created in the public schema.

Expected behavior

The telegraf schema is created automatically

Actual behavior

The telegraf schema is not created automatically, so it doesn't work cleanly

Additional info

There are 4 templated groups of queries that Telegraf handles:

• create_templates
• add_column_templates
• tag_table_create_templates
• tag_table_add_column_templates

I propose that an additional one be added called create_views in which we can enter e.g., CREATE SCHEMA IF NOT EXISTS telegraf; and this always runs first.

Simply adding the CREATE SCHEMA... as the first entry in create_templates does not work. CREATE SCHEMA telegraf CREATE TABLE foo (...) will error if the schema already exists, and you cannot do CREATE SCHEMA IF NOT EXISTS... and define tables in a single query. They will need to be handled in different transactions.

[powersj]

I wondered if this was something in the original PR and sure enough found a couple comments about why this plugin does not manage schemas, see this comment and this one.

Given the deliberate omission specifically around security + permissions, this does not seem wise to add.

Thoughts?

[telegraf-tiger]

Hello! I am closing this issue due to inactivity. I hope you were able to resolve your problem, if not please try posting this question in our Community Slack or Community Page. Thank you!

[Ivan076]

Does this thing actually works? there seems to be nowhere stated that telegraf can actually use postgres instead of influx. this is my case. I don't want to install influxdb but i want to use postgres. Does this mean that i cannot?