Thingsboard remove old DATA

Putty 설정

 

 

psql 명령구문

sudo -i -u postgres

psql

\c thingsboard;

 

 

\dt

select * from information_schema.columns where table_name = 'ts_kv' order by column_name;

select * from information_schema.columns where table_name = 'event' order by column_name; 

Remove old events from Thingsboard

The date is contained in the uid_event field in V1 UUID format.

So first, you need to write a function uuid_timestampin order to convert the UUID to a timestamp. I found the solution, here: https://stackoverflow.com/a/24191574/5300212

 

CREATE FUNCTION uuid_timestamp(id uuid) RETURNS timestamptz AS $$
  select TIMESTAMP WITH TIME ZONE 'epoch' +
      (((('x' || lpad(split_part(id::text, '-', 1), 16, '0'))::bit(64)::bigint) +
      (('x' || lpad(split_part(id::text, '-', 2), 16, '0'))::bit(64)::bigint << 32) +
      ((('x' || lpad(split_part(id::text, '-', 3), 16, '0'))::bit(64)::bigint&4095) << 48) - 122192928000000000) / 10000000 ) * INTERVAL '1 second';    
$$ LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT;
  
 

to delete all events older than 1 days ago, you can run a query like :

DELETE FROM public.event WHERE uuid_timestamp(event_uid::uuid) < now() - '30 days'::interval;

DELETE FROM public.ts_kv where ts::bigint < (extract(epoch from now() - '30 days'::interval) * 1000);

stackoverflow.com/questions/54643417/how-to-remove-old-events-from-thingsboard

 

How to remove old events from Thingsboard?

Timestampe bigint 시간표현

 

DO $$ 
DECLARE
BEGIN 
   RAISE NOTICE 'current_time % = %', 
	now(), (extract(epoch from now()) * 1000);
   RAISE NOTICE '1 day ago % = %', 
	now() - '1 days'::interval, (extract(epoch from now() - '1 days'::interval) * 1000);
   RAISE NOTICE '30 days ago % = %', 
	now() - '30 days'::interval, (extract(epoch from now() - '30 days'::interval) * 1000);
END $$;

 

Table Information include ts :: bigint

사무실 서버 아이피 DNS 설정

 

ID : smart1st

PW : zzzzzzzz

 

thingsboard : 1234

 

MobaXterm SFTP 접속

 

 

Vacuumdb

vacuumdb -u thingsboard;

vacuumdb -f thingsboard;

vacuumdb 실행후 데이터 사용량 변화

 

Thingsboard Webserver

http://121.125.154.172:8080/home

References

 

PSQL 명령어 정리

browndwarf.tistory.com/51

알아두면 유용한 psql 명령어 정리

www.postgresqltutorial.com/plpgsql-variables/

PL/pgSQL Variables

thingsboard.io/docs/user-guide/install/pe/old-upgrade-instructions/

Upgrade instructions

 

Thingsboard 데이터베이스 정리

Thingsboard 데이터베이스의 크기는 지속적으로 증가하고 있습니다. 오래된 데이터는 수시로 삭제되어야합니다.

 

www.gitmemory.com/issue/DDorch/ScadaSupAgro/24/527168088

Clean up Thingsboard database - ScadaSupAgro

 

github.com/HomoEfficio/dev-tips/blob/master/%EC%9C%88%EB%8F%84%EC%9A%B0%20Git%20Bash%EC%97%90%EC%84%9C%20.sh%20%ED%8C%8C%EC%9D%BC%EC%9D%84%20%EC%8B%A4%ED%96%89%20%ED%8C%8C%EC%9D%BC%EB%A1%9C%20%EB%B3%80%ED%99%98%20%ED%95%98%EA%B8%B0.md

 

 

ko.wikipedia.org/wiki/%EC%95%84%ED%8C%8C%EC%B9%98_%EC%B9%B4%EC%82%B0%EB%93%9C%EB%9D%BC

아파치 카산드라 - 위키백과, 우리 모두의 백과사전