Telegraf: Add a postgresql log parser like pgbadger

[luvpreetsingh]

Actually the current input plugins to monitor the postgresql database are not good.

Firstly, we have to reset them every other time to get the live data.
Secondly, they don't give many necessary details like queries/s, conncetion/s, and many others helpful stats.

I suggest you to add a logparser to parse the logs of postgresql, provided the logging should be configured in the postgres.conf file . It is a very effective method.

This is the thing that I'm actually talking about,
https://github.com/dalibo/pgbadger

You must give it a try once, it's really good. I think it wuld be a good addition to telegraf as people won't need to go anywhere else for postgresdb monitoring.
I would have loved to write this plugin but at the moment I have 0 knowledge of Go language.

[luvpreetsingh]

http://dalibo.github.io/pgbadger/

particularly the JSON format will be easy for you, if you find it helpful.

[james-lawrence]

Firstly, we have to reset them every other time to get the live data.

what do you mean by this?

Secondly, they don't give many necessary details like queries/s, conncetion/s, and many others helpful stats.

the explicit stats you mentioned can be done using postgresql_extensible plugin by enabling some extensions (pg_stat_statements for example) and writing the necessary queries.

[luvpreetsingh]

I have used both postgresql_extensible and this pgbadger tools.

I initially wanted to get every data through Telegraf, so, I paid extra attention to get postgres stats in telegraf, as I would not need anything else then, Just telegraf would work.

But postgresql_extensible is not good enough.

Firstly, suppose you today created a new database and inserted 5 rows. This plugin will show that 5 rows have been inserted, and then you never used postgres after that for a week.
After a week, it still will show that, 5 rows are inserted. If I add 3 new rows now, it will tell me that 8 rows have been inserted. Same is with deletion, updation and everything.

So, that's why, we have to reset it again and again everyday, to get data for only today.

It does not give you live data, instead it gives you data after the last time it was reset to 0.
Also, it puts a large number of extra data in the influxbd, which can be very annoying when viewing data over Grafana.

pgbadger is pretty simple, just configure the postgres.conf file, and boom, it gives you everything about the log file you gave it.
It can easily be run daily with a cronjob, with a pretty simple command.

And the stats given are really helpful, unlike postgresql_extensible plugin.
You should really consider this.

[james-lawrence]

Firstly, suppose you today created a new database and inserted 5 rows. <snip>

i suggest taking the derivative between each point. it'll solve having to reset the metrics, influxdb has a builtin for doing this.

You should really consider this.

never said anything about not considering it. I'm pointing out that some of your comments are perfectly solvable without any additional work. (or are not really a problem in standard postgresql setups with a load balancer like pgbouncer in front.)

[james-lawrence]

went and pulled an example from our setup:
telegraf configuration:

  [[inputs.postgresql_extensible.query]]
    sqlquery="SELECT * FROM pg_stat_database"
    version=901
    withdbname=false
    tagvalue="datname"
    measurement="pg.stat.database"

influxdb query:

SELECT derivative(max("xact_commit"), 1s) AS "transactions", derivative(max("xact_rollback"), 1s) AS "rollbacks" FROM "infrastructure"."autogen"."pg.stat.database" WHERE time > now() - 24h GROUP BY time(5m)

[phemmer]

Yeah, derivative is your friend. It's used all over the place in InfluxDB for stuff like this. In fact having a constantly incrementing value is way more powerful than a counter that resets periodically. What if your counter resets once a day, but you want to see per-hour or per-minute data. If you're relying on a reset-to-0, you can't do this. But with derivative, you can use any interval you want.

Another big problem with pulling this sort of data through logs is that enabling logs can have a very significant performance/resource impact on the host. On high volume servers, it is not feasible to enable logging for every single statement. But you can instead use queries to obtain the data with little to no performance impact.

I'm not saying this shouldn't be implemented, just that the advantages over the current solutions are not as significant as they would seem.

As for implementation, rather than reinventing the wheel within telegraf, I think it would make more sense for telegraf to be able to consume data generated by pgbadger.

[luvpreetsingh]

Thank you for all this info. I was hesitating to put this feature issue first, but now it has helped me and will help the product also.

Also, talking about logparsers,

In current telegraf version, Can I parse logs other than those of nginx and apache , like syslogs and other apps ?

[james-lawrence]

havent used it but there is a general log parser: https://github.com/influxdata/telegraf/tree/master/plugins/inputs/logparser

[luvpreetsingh]

Okay, One more thing, I don't want to create a new issue, that's why I'm asking here,

Like postgresql_extensible gets its data from the Postgresql Statistics Collector,from where does Redis' input plugin gets its data, all the stats ?

[danielnelson]

@sLuvpreet33 Please ask general questions at the InfluxData Community site. This question isn't related to the issue and it will make understanding this issue more complex in the future, hurting the chances that someone will work on it.