Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgresql output plugin (for using with timescaledb) #3408

Open
leiserfg opened this issue Oct 30, 2017 · 24 comments

Comments

Projects
None yet
10 participants
@leiserfg
Copy link

commented Oct 30, 2017

No description provided.

@hlihhovac

This comment has been minimized.

Copy link

commented Oct 31, 2017

+1

@svenklemm svenklemm referenced this issue Nov 5, 2017

Open

PostgreSQL output plugin #3428

3 of 3 tasks complete
@svenklemm

This comment has been minimized.

Copy link

commented Nov 6, 2017

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

PR #3428

@leiserfg

This comment has been minimized.

Copy link
Author

commented Nov 6, 2017

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

This comment has been minimized.

Copy link

commented Nov 6, 2017

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

@danielnelson

This comment has been minimized.

Copy link
Contributor

commented Nov 7, 2017

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

This comment has been minimized.

Copy link

commented Nov 8, 2017

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

This comment has been minimized.

Copy link
Contributor

commented Nov 8, 2017

Would a user need to design tables for every input?

@leiserfg

This comment has been minimized.

Copy link
Author

commented Nov 8, 2017

Awesome!

@svenklemm

This comment has been minimized.

Copy link

commented Nov 8, 2017

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

This comment has been minimized.

Copy link
Contributor

commented Nov 9, 2017

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

@svenklemm

This comment has been minimized.

Copy link

commented Nov 9, 2017

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

This comment has been minimized.

Copy link
Contributor

commented Nov 9, 2017

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

@leiserfg

This comment has been minimized.

Copy link
Author

commented Nov 10, 2017

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

This comment has been minimized.

Copy link

commented Nov 10, 2017

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

This comment has been minimized.

Copy link
Contributor

commented Nov 10, 2017

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

This comment has been minimized.

Copy link

commented Nov 10, 2017

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

This comment has been minimized.

Copy link
Author

commented Nov 18, 2017

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

@svenklemm

This comment has been minimized.

Copy link

commented Nov 26, 2017

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

@leiserfg

This comment has been minimized.

Copy link
Author

commented Nov 26, 2017

hooray! Merge it!

@danielnelson danielnelson added this to the 1.6.0 milestone Dec 1, 2017

@russorat russorat added the new plugin label Jan 20, 2018

@russorat russorat modified the milestones: 1.6.0, 1.7.0 Jan 26, 2018

@fouts-bnet

This comment has been minimized.

Copy link

commented Mar 9, 2018

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

This comment has been minimized.

Copy link

commented Apr 29, 2018

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

@danielnelson danielnelson removed this from the 1.7.0 milestone Jun 3, 2018

@danielnelson danielnelson added this to the 1.8.0 milestone Jun 3, 2018

@leogaggl

This comment has been minimized.

Copy link

commented Jun 11, 2018

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

@mmariani

This comment has been minimized.

Copy link

commented Jun 11, 2018

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

@russorat russorat modified the milestones: 1.8.0, 1.9.0 Sep 4, 2018

@russorat russorat modified the milestones: 1.9.0, 1.10 Oct 22, 2018

@russorat russorat modified the milestones: 1.10.0, 1.11.0 Jan 14, 2019

@tscruggs

This comment has been minimized.

Copy link

commented Feb 25, 2019

@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.

@danielnelson danielnelson modified the milestones: 1.11.0, 1.12.0 May 24, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.