Exclude devices/variables from logging

Hi!

My PostgreSQL-db is getting toooo big, for regular backups even w/ Auto prune @ 2 years (I would like to use Never but that is not doable).
I have 700+ tables in my indigo_history db and I think I use about 50-100 of them. Is it possible to exlude the devices/variables I don't need (or exclude all and include only the ones I need)?

Best regards, L

Not in the sql logger I don’t think but if you move to the Grafana plugin you get to choose which devices you store in influxdb. That might work for you?

Thanx.
Ok, to bad.
Can't switch db now after many years of postgres, would be far to much work on the frontend part...
Might construct my own db logger by triggers on the devices I need but I don't know if that is going to be a mess.

You obviously use your postgres db more than me. I thought the same, but realised I got more value on having it in Grafana even if I lost some history.

However, a quick search found this... http://mfadhel.com/transferring_data_to_influx/. you might be able to migrate the data you want over?

Simon

[jay (support)]

You could write a script or something that runs once a day and just deletes the extraneous data.

That is actually a great idea. And make a whitelist of devices I would like to keep data for.

Thanks!

[matt (support)]

Take a look at the release notes for Indigo 7.4.1 and search on sqlLoggerIgnoreStates. You can use the Global Property Manager plugin (linked in the release notes) to add a new property to the devices you want skipped. If you add sqlLoggerIgnoreStates with a value of * then Indigo won't write new rows to the table for that device. Might be a pain if you have to do it for a lot of devices, but you should only need to do it once.

Interresting, but it will not delete the table for that device I guess? Can I drop that table or is it going to be recreated but not populated w/ values?

//L

[matt (support)]

You should be able to drop it. I believe it won't be re-created (but there is a possibility it will be but either be empty or just have a single row).

Thanks.

Does the SQL logger do "VACUUM FULL" on the tables?
And when do they prune old data? I have set variables to 1 week auto prune (from 2 years) but I don't see that the tables use less space and they do still have data for 2 years.

//L

[matt (support)]

The pruning happens on the tables after new items are inserted on tables (so when device states or variable values change).

Indigo doesn't do a VACUUM FULL on the table, but probably should at least on every server restart. We should also perform the pruning when the plugin first starts up. I'll add both to my feature request / todo list.

Have you tried manually running the VACUUM FULL on the database file? Make sure you stop the SQL Logger Plugin before you do so, but I'm curious if that works for you and shrinks the file.

Indigo doesn't do a VACUUM FULL on the table, but probably should at least on every server restart. We should also perform the pruning when the plugin first starts up. I'll add both to my feature request / todo list.

Just a warning, that puts the table into an exclusive lock state and could be problematic if plugins are updating all their states/devices at startup.

It might be more than you want to take on, but I had started to modify the SQL Logger at one point to utilize a queue system where the updates were stuck in a queue which was being pulled/executed on a separate thread. This allowed batching up of commands while performing something that locked a table. Was also looking into a faster way to add columns to the table based on some postgre team recommendations, but then Weather Underground went and quit and made that all a moot point in my case (as that was my offending table).

Adam

I did a VACUUM(FULL, ANALYZE, VERBOSE) on db indigo_history (700+ tables), took about 10 minutes and did trim away 2 GB of 9+GB total.
I kept the SQL-plugin running and no errors in log, my frontend webpage worked during the process aswell (the pages I tested) so I guess the exclusive lock is only for the table that is vacuumed for the moment and not all tables at ones.

//L

so I guess the exclusive lock is only for the table that is vacuumed for the moment and not all tables at ones.

Correct, tables are only going to lock one at a time unless you put the database in single-user mode.... and commands can wait a set amount of time, if the operation occurs quickly then it isn't an issue. My mentioning of it was more in the fact that on startup there seems like there is a high likelihood of things attempting to insert into the very table that is being locked.

I've seen errors with this in Indigo not in vacuuming but when a state definition change causes the table to be modified. When Dave would add states to Weather Underground, for instance, I would get a TON of errors while the plugin attempted to add (or modify if a data type change) the column. Large table and had tons of rows, though.

Might be over thinking it in this case, but performance of the database from applications is a big portion of my job so I tend to worry about those things outside of it as well. I guess my point is it could be prevented fairly easily to be sure it doesn't occur.

I believe one should minimize errors and maximize performance to, both at work and at home so I appreciate every input I can get!

Now I have to decide if I should make a whitelist of devices I want to keep forever(auto prune = forever) an prune the rest to about 6 months via a script OR copy data periodically from these devices to new tables that Indigo does not prune and set auto prune to 6 month
The latter might be better if I install a new device (different ID) that replace the old one, then I can map the new device to my custom table.

//L