[toc] # Thingsboard PostgreSQL restore ## Overview ### goal 假設說有tenant的某幾個dashboard被刪除, 或是消失 透過TB的備份`.sql`檔案, 在uptime直接回復。 (如果要回檔效果, 可能要更改merge的條件) ### step 前置, 你一定要有時常備份的`.sql`檔案 create temp db (`temp_database`) import the `.sql` file export `temp_database.dashboard` to a `.csv` file connect to production db (`thingsboard`) create a table `dashboard_restore`, which has the same schema as `dashboard` import the `.csv` file to `dashboard_restore` merge `dashboard_restore` to `dashboard` drop table`thingsboard.dashboard_restore` and db`tmp_database` ## backup database ```== pg_dump -U thingsboard -h 127.0.0.1 -d thingsboard -f backup.sql ``` ## restore database 這裡以 `tenant@5giotlead.com` 的 dashboards 為例) ### 1. create temp database, import backup.sql #### create tmp database ```== psql -h 127.0.0.1 -U thingsboard ``` ```== create database temp_restored; exit ``` output: ``` CREATE DATABASE ``` #### import backup.sql ```== psql -h 127.0.0.1 -U thingsboard -d temp_restored -W < backup.sql ``` ### 2. verify dashboard counts ```== psql -h 127.0.0.1 -U thingsboard ``` ```== \c temp_restored SELECT count(*) FROM dashboard WHERE tenant_id IN ( SELECT id FROM tenant WHERE email = 'tenant@5giotlead.com' LIMIT 1 ); ``` output: ``` count ------- 9 ``` ### 3. export dashboards to a csv file ```sql \COPY (SELECT * FROM dashboard WHERE tenant_id IN ( SELECT id FROM tenant WHERE email = 'tenant@5giotlead.com')) TO 'restore_data.csv' WITH CSV HEADER; ``` output: ``` COPY 9 ``` ### 4. connect to the production database ```== \c thingsboard ``` ### 5. create table dashboard_restore (copied the schema of `dashboard` ) ```== CREATE TEMP TABLE dashboard_restore AS SELECT * FROM dashboard LIMIT 0; ``` output: ``` SELECT 0 ``` ### 6. import .csv file ```== \copy dashboard_restore FROM 'restore_data.csv' CSV HEADER; ``` output: ``` COPY 9 ``` ### 7. Merge dashboard_restore to dashboard ```== INSERT INTO dashboard SELECT * FROM dashboard_restore ON CONFLICT (id) DO NOTHING; ``` output: ``` INSERT 0 1 ``` ### 8. verify result ```== SELECT count(*) FROM dashboard WHERE tenant_id IN ( SELECT id FROM tenant WHERE email = 'tenant@5giotlead.com' LIMIT 1 ); ``` output: ``` count ------- 9 ``` ### 9. drop the temp table and temp database ```== DROP TABLE dashboard_restore; DROP DATABASE temp_restored; ``` output: ``` DROP TABLE DROP DATABASE ```