search

thingsboard / thingsboard

Open-source IoT Platform - Device management, data collection, processing and visualization.
https://thingsboard.io
Apache License 2.0
16.9k stars 5.04k forks source link

Database Migration - PostgreSQL to Hybrid - v3.7.0PE #11163

Closed HannesPiadeno closed 1 month ago

HannesPiadeno commented 1 month ago

Component

Description Is it possible migrating a PostgreSQL database to a hybrid one on the newest Thingsboard version (v3.7.0PE)? I have found the Migrating Tool for Thingsboard. What would be a fast and easy way to migrate the Database, being able to afford losing the timeseries data.

Environment

pon0marev commented 1 month ago

No changes have been made to the historical data table structure since version 3.0.0, so the database migrator is compatible with Thignsbaord 3.7.0. I recommend following the second scenario in the migrator documentation because this method migrates data with minimal downtime.

HannesPiadeno commented 1 month ago

@pon0marev Thank you for the clarification. I have now successfully completed the migration process. I switched to a system with 8GB of RAM. During the migration, the JVM ran out of memory multiple times after several hours of running. After increasing the maximum heap size to java -Xms4g -Xmx4g -jar ..., the migration process finished in a few minutes. I migrated all output folders and restarted the Thingsboard instance. However, I am seeing the following warnings frequently when accessing the specified data:

2024-07-18 10:24:24,138 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='battery_meter_frequency', partition=1719792000000, ts=1720686263877, ts=1721291063877, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'battery_meter_frequency' AND partition = 1719792000000 AND ts >= 1720686263877 AND ts < 1721291063877 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 5635616557731639623 (see tombstone_warn_threshold)
2024-07-18 10:24:24,149 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='battery_meter_ac_voltage', partition=1719792000000, ts=1720686263877, ts=1721291063877, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'battery_meter_ac_voltage' AND partition = 1719792000000 AND ts >= 1720686263877 AND ts < 1721291063877 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token -3928693744907869428 (see tombstone_warn_threshold)
2024-07-18 10:24:24,158 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='nominal_full_pack_energy', partition=1719792000000, ts=1720686263877, ts=1721291063877, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'nominal_full_pack_energy' AND partition = 1719792000000 AND ts >= 1720686263877 AND ts < 1721291063877 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 4481287527997288296 (see tombstone_warn_threshold)
2024-07-18 10:24:24,165 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='calculated_State_of_Charge', partition=1719792000000, ts=1720686263877, ts=1721291063877, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'calculated_State_of_Charge' AND partition = 1719792000000 AND ts >= 1720686263877 AND ts < 1721291063877 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 516601141890561491 (see tombstone_warn_threshold)
2024-07-18 10:24:24,167 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='real_real_mode', partition=1719792000000, ts=1720686263878, ts=1721291063878, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'real_real_mode' AND partition = 1719792000000 AND ts >= 1720686263878 AND ts < 1721291063878 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token -8892119711211390435 (see tombstone_warn_threshold)
2024-07-18 10:24:24,175 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='battery_meter_ac_current', partition=1719792000000, ts=1720686263878, ts=1721291063878, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'battery_meter_ac_current' AND partition = 1719792000000 AND ts >= 1720686263878 AND ts < 1721291063878 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 4968953170440093648 (see tombstone_warn_threshold)
2024-07-18 10:24:24,181 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='battery_target_q', partition=1719792000000, ts=1720686263878, ts=1721291063878, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'battery_target_q' AND partition = 1719792000000 AND ts >= 1720686263878 AND ts < 1721291063878 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 8965226047667210684 (see tombstone_warn_threshold)
2024-07-18 10:24:24,195 [Thingsboard Cluster-io-3] WARN  c.d.o.d.i.core.cql.CqlRequestHandler - Query '[7 values] SELECT long_v, dbl_v, bool_v, str_v, json_v, key, ts FROM ts_kv_cf WHERE entity_type = ? AND entity_id = ? AND key = ? AND partition = ? AND ts >= ? AND ts < ? ORDER BY ts DESC LIMIT ? [entity_type='DEVICE', entity_id=c36e4690-77ee-11ee-bfe8-235b4ef6a4ac, key='battery_meter_inst_react_power', partition=1719792000000, ts=1720686263877, ts=1721291063877, "[limit]"=25000]' generated server side warning(s): Read 2000 live rows and 8000 tombstone cells for query SELECT bool_v, dbl_v, json_v, long_v, str_v FROM thingsboard.ts_kv_cf WHERE entity_type = 'DEVICE' AND entity_id = c36e4690-77ee-11ee-bfe8-235b4ef6a4ac AND key = 'battery_meter_inst_react_power' AND partition = 1719792000000 AND ts >= 1720686263877 AND ts < 1721291063877 ORDER BY ts DESC LIMIT 2000 ALLOW FILTERING; token 1275226817035334525 (see tombstone_warn_threshold)

thingsboard.log

pon0marev commented 1 month ago

@HannesPiadeno Have you performed compaction after migration? This is a very important step to optimize your SStables after migration. Without compaction, you may experience performance issues with fetching historical data. https://docs.datastax.com/en/cassandra-oss/3.0/cassandra/tools/toolsCompact.html Run the command:

nodetool compact -s thingsboard

This process will run in the background, but it requires free disk space the size of your cassandra database. Check compaction status: nodetool compactionstats

HannesPiadeno commented 1 month ago

@pon0marev I haven't used this command before. After following the provided instructions, I still encounter the same warnings when loading the dashboard with the specified data.

pon0marev commented 1 month ago

If you have a single cassandra database, you can remove tombstones by setting gc_grace_seconds=0. gc_grace_seconds is the time (in seconds, defined in Table properties) cassandra keep Tombstone so that other nodes which might be down during deletion of data can know the changes.

To do this, go to the Cassandra database: cqlsh And run the following query: ALTER TABLE thingsboard.ts_kv_cf WITH gc_grace_seconds = 0;

HannesPiadeno commented 1 month ago

@pon0marev We are running a single Cassandra database. Running this deleted all tombstones, thank you for the help.