search

thingsboard / thingsboard-edge

Apache License 2.0
98 stars 74 forks source link

Deleting old TB Edge PostgreSQL data #28

Closed akseerali closed 1 year ago

akseerali commented 1 year ago

Hi,

I have set up a TB Edge instance along with PostgreSQL DB by using the docker on Linux using instructions: https://thingsboard.io/docs/user-guide/install/edge/docker/

Following is my system architecture for testing the TB GW, Edge, and Cloud Server: image

I am currently sending the simulated data to 6 devices on the Edge. I want to delete the data from 2 devices older than 3 years and delete the data from other devices older than 4 months. In this regard, I have tested the TTL parameter value from the Timeseries node in the rule chain by following the instructions from the source https://thingsboard.io/docs/user-guide/rule-engine-2-0/action-nodes/#save-timeseries-node.

So far, I have found that this TTL value has no effect on the time series data and I can clearly see all the previous data from the local dashboard.

Furthermore, I have found that we can specify the data cleanup interval in the docker configuration, source: https://thingsboard.io/docs/user-guide/install/config/#sql-configuration-parameters.

Question: Is there a way to clean the previous data from TB Edge? If yes, can we specify a different data cleanup interval for different devices? Is there some set of instructions on how to modify the docker container configuration to fix this issue?

Thanks

volodymyr-babak commented 1 year ago

Hello @akseerali

TTL parameter in Timeseries node is only applicable for Cassandra Database. PostgreSQL database doesn't have functionality for custom TTL on a specific row entry. Edge is designed to connect to PostgreSQL only, and that's why custom TTL per device is not available on edge. You can have custom TTL per device only in the case of the ThingsBoard server that is connected to Cassandra Database as time-series data. Currently, the only way to clean up data on the edge is to set TTL for the entire ts_kv table by modifying SQL configuration parameters: https://thingsboard.io/docs/edge/user-guide/install/config/#sql-configuration-parameters

Please search for SQL_TTL_TS_TS_KEY_VALUE_TTL. By default, this parameter value is 0, and records in the ts_kv table are never expired. If you'll add this parameter to your docker image configuration - ts records are going to be removed according to this configuration.

akseerali commented 1 year ago

Hi @volodymyr-babak,

Thanks for the information. So, based on the information in the given link, I have modified the configuration in 'yaml' file as:

`version: '2.2' services: mytbedge: restart: always image: "thingsboard/tb-edge:3.4.0EDGE" ports:

However, I still can't see any change in the data. Please have a look, thanks.

volodymyr-babak commented 1 year ago

@akseerali

please add

SQL_TTL_TS_ENABLED: true SQL_TTL_TS_EXECUTION_INTERVAL: 3600000 SQL_TTL_TS_TS_KEY_VALUE_TTL: 1800

to the mytbedge container(instead of postgres1) and wait for 1 hour (3600000 milliseconds) before the timeseries clean-up procedure will be started. Your data should be cleaned up after 1 hour of edge service was restarted.

akseerali commented 1 year ago

Hi @volodymyr-babak,

Thanks a lot for the information. I am now able to delete the old data from Devices; however, the data in Assets still appears. Please have a look at the attached figure below:

image

Is there a method to clean the data from all the fields simultaneously?

Thanks

volodymyr-babak commented 1 year ago

hi @akseerali

here is the source code of the procedure, that removes ts data: https://github.com/thingsboard/thingsboard-edge/blob/v3.4/dao/src/main/resources/sql/schema-ts-psql.sql

Please scroll to cleanup_timeseries_by_ttl. As you can see, it removes data for devices and assets: image

So, by design, your asset data should be removed.

Could you please provide more information on your setup - your asset and devices are on the Tenant level? Do the assigned to any customer?

akseerali commented 1 year ago

Hi @volodymyr-babak,

Thanks for the information. I have assets and devices on the tenant level and assigned them to a single customer for testing in the cloud. So far, unfortunately, I haven't seen the telemetry data being cleared on the edge database assets. I'll look at the PostgreSQL database regarding the ts data cleaning mechanism to find out a reason. I'll also try removing the data by running manual queries and updating you with all the details.

akseerali commented 1 year ago

Hi @volodymyr-babak,

The old data is able to clear automatically from both the assets and devices. In my second test, I once again made sure to recreate some assets and devices and assign them to the TB Edge. I also checked the database functions to clear the data from the assets and devices and found everything was fine.