Postgresql output plugin (for using with timescaledb)

[leiserfg]

No description provided.

[hlihhovac]

+1

[svenklemm]

I've written an output plugin for postgresql. Let me know if you need any other features.

PR #3428

[leiserfg]

It's, cool!. Can you add a more flexible way of creating the tables? For example: when creating the tables for using with timescaledb, after the table creation, but before the first insertion, you need to execute a command that create the table partition. By example:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);
SELECT create_hypertable('conditions', 'time');

Another use case: using pg 10 and want to use a automatic partitioned table or citusdb with a sharded table.

I know that I can create the tables manually, but if I need a lot of metrics then it will imply a lot of repetitive sql writing.

Maybe a template parameter for table creation can solve this or a post_create_hook for execute a command giving the table's name and the primary key attribute.

[svenklemm]

I guess a template is most flexible as a post_create_hook wouldnt help much with pg10 partitioning.

[danielnelson]

I like the template idea, and we won't want to add an output which is not generally usable in many use cases. If you design a template configuration please post it here for comments.

[svenklemm]

I was thinking about having the following placeholders:
TABLE - tablename quoted as identifier
COLUMNS - comma separated list of columns with datatype
PK_COLUMNS - comma separated list of primary key columns (time+ all tags)
TABLENAME - tablename quoted as literal to use in function calls (required for timescale)

The template syntax is just a suggestion but curly braces shouldnt conflict with any SQL.

Example Templates

-- Default Value
CREATE TABLE {TABLE}({COLUMNS},PRIMARY KEY({PK_COLUMNS}));

-- PostgreSQL 10 Partitioning by year and month
CREATE TABLE {TABLE}({COLUMNS}) PARTITION BY RANGE (EXTRACT(YEAR FROM time),EXTRACT(MONTH FROM time));

-- Timescale
CREATE TABLE {TABLE}({COLUMNS});
SELECT create_hypertable({TABLENAME}, 'time');

[danielnelson]

Would a user need to design tables for every input?

[leiserfg]

Awesome!

[svenklemm]

The metric name is used as table name so every input plugin gets its own table. But the user would choose one of the 3 different example templates (or create their own) and the plugin would use those for all metricts and fill out the placeholder values with the metric specific stuff.

[danielnelson]

Would the user need to specify all the columns in order to create the table?

[svenklemm]

No the user wouldnt specify the columns, he would use the {COLUMNS} placeholder where he wants the columns to be in the statement. The plugin would replace {COLUMNS} with the actual columns used in the measurement.

[danielnelson]

What if the fields on the measurement are dynamic and new fields are added after the table is created?

[leiserfg]

I was reading the adaptor for prometheus from the same guys of timescaledb and there they use jsonb for the metrics. That way the metrics can be added dynamically.

[svenklemm]

I was thinking about having an option for jsonb tags and values, but otherwise new fields would have to result in an alter table statement and wouldnt use the template.

[danielnelson]

While some inputs create a static set of fields, quite a few of them create fields dynamically.

I would imagine the alter table design would be tricky to get right, we would need to load the current table schema on startup in order to know when to alter, and when you factor in many telegraf agents it gets harder.

You might also be interested in looking at the CrateDB output we recently added, it is almost compatible with postgres but has some differences. #3210

[svenklemm]

Ok havnt used any plugins with dynamic fields so i thought all plugins would handle dynamic stuff with tags. Using jsonb fields for tags and values would solve the dynamic field problem. I've seen the cratedb plugin it uses the postgres protocol but has different datatypes from what i've seen. Using jsonb probably matches the cratedb types most closely.

[leiserfg]

I think that using jsonb for metrics is better, in the worst case it can be configurable too (dynamicFields: bool [true as default])

[svenklemm]

I have added support for jsonb tags and jsonb fields and made using jsonb the default.

[leiserfg]

hooray! Merge it!

[fouts-bnet]

Can someone please post a schema needed to get this up and running?

2018-03-09T23:08:01Z E! Error during insert: ERROR: column "in" does not exist (SQLSTATE XX000)
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0xd0c14a]

goroutine 74 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
        /usr/lib/go-1.8/src/database/sql/sql.go:2422 +0x7a
database/sql.(*Rows).Close(0x0, 0xc4207c1ae0, 0x2)
        /usr/lib/go-1.8/src/database/sql/sql.go:2418 +0x3d
github.com/influxdata/telegraf/plugins/outputs/postgresql.(*Postgresql).Write(0xc4201ec1e0, 0xc420088900, 0x21, 0x21, 0x1e4d560, 0xc4202945a0)
        /home/zfouts/gopath/src/github.com/influxdata/telegraf/plugins/outputs/postgresql/postgresql.go:332 +0x1266
github.com/influxdata/telegraf/internal/models.(*RunningOutput).write(0xc42012f360, 0xc420088900, 0x21, 0x21, 0x0, 0x0)
        /home/zfouts/gopath/src/github.com/influxdata/telegraf/internal/models/running_output.go:179 +0x10c
github.com/influxdata/telegraf/internal/models.(*RunningOutput).Write(0xc42012f360, 0x1642750, 0xc420624000)
        /home/zfouts/gopath/src/github.com/influxdata/telegraf/internal/models/running_output.go:161 +0x497
github.com/influxdata/telegraf/agent.(*Agent).flush.func1(0xc420624000, 0xc42012f360)
        /home/zfouts/gopath/src/github.com/influxdata/telegraf/agent/agent.go:238 +0x59
created by github.com/influxdata/telegraf/agent.(*Agent).flush
        /home/zfouts/gopath/src/github.com/influxdata/telegraf/agent/agent.go:243 +0xa5

[[outputs.postgresql]]
  address = "host=xxx port=xxx user=xxx password=xxx dbname=xxx"
  tags_as_jsonb = false
  fields_as_jsonb = false
  table_template = "CREATE TABLE {TABLE}({COLUMNS})"

[srclosson]

I've made my own stab at this one as well, combining the two with some optimizations. The plugin tries to combine items with the same timestamp into the same table, but also asks that the table be created first. This is still a work in progress, that I don't know that I'll continue with because it served my purposes to find out timescale didn't have the performance that influxdb did. Comments are welcome.

https://github.com/srclosson/telegraf/tree/timescale/plugins/outputs

[leogaggl]

Any updates on this? Will the Postgres output plugin be included in the 1.7 release? Any idea on availability?

[mmariani]

I am currently using the version from https://github.com/svenklemm/telegraf/tree/postgres which has been recently updated.

[tscruggs]

@svenklemm Have you seen any issues with Telegraf storing the fields column as jsonb? Using your latest update here: https://github.com/svenklemm/telegraf/tree/postgres, I have a working configuration with metrics being output in individual columns successfully with jsonb = false. When enabled, the fields column is correctly initialized as jsonb, but all rows for fields and tags are null, only time is populated.

[rudigerlove]

Hi,
Does telegraf still stupport output to TimescaleDB?
What are your views on using Timescale instead of Influx pour certain data which needs "sql" like queries for data that is going to be a mix of relational and time series.
thx

[ghost]

Hi
Does telegraf still stupport output to TimescaleDB?
Regards,
IZ

[opannapo]

+1