Deploy PostgreSQL fully manageable database for Thingsboard

I installed the Things Board community version in The Dell Server locally and used the PostgreSQL database. In the working period, I generate some questions and try to make a note for future understanding.

ThingsBoard can use SQL or a hybrid database approach.

• After Installing the Thingsboard CE on the Ubuntu machine: What is necessary to back up the whole machine or only the database?

• After installing the things board on our Ubuntu-machine we can log thingsboard service.

$ less /var/log/thingsboard/thingsboard.log

• After that press Ctrl+n to view the latest input and check it.

Backup Database

Make a backup folder for the database.

• Create a backup folder

$ mkdir backup

• Enter the backup folder.

$ cd backup

• Now, we can copy everything to the current folder.

$ sudo cp /etc/thingsboard/conf/* ./

By *asterisk we can copy everything from the previous folder to the new folder.

• To check the copy file

$ ll 

• Basically, we need thingsboard.yml and thingsboard.conf files.

• After the backup procedure, we need to stop the things board service.

Configuration

• Service management commands:

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

• To check the status

$ sudo service thingsboard status

• Once PostgreSQL is installed we may want to create a new user or set the password for the the main user.
  The instructions below will help to set the password for the main Postgresql user.

Note

For password setting we must take the thingsboard datasource password.

  sudo su - postgres
  psql
  \password
  \q
  

• Enter the database to check the free space of the database.

• Use psql-console to enter the database

$ psql -U postgres -h localhost

• Now we will get the details of the database.

\l+

• check the disk space, log out from the database, and write this command.

$ df -h /

• Make sure we have enough space to place a backup of the database Check database size

$ sudo -u postgres psql -c "SELECT pg_size_pretty( pg_database_size('thingsboard') );"

• Make sure our PostgreSQL database is running

$ pg_lsclusters

• If there is enough free space - make a backup.

$ pg_dump -U postgres -h localhost -d thingsboard > "specify the file"

• Enter the password of postgres not our machine.

• Finally, we can check the backup file which is created.

$ ll -h  

• Restore the folder for any trouble

$ pg_restore -U things board -h localhost -d things board > "File name"

• To copy the config file

$ cd /etc/thingsboard/conf
$ ll
$ sudo mv thingsboard.yml thingsboard.yml.invalid
$ sudo cp ~/backup/thingsboard.yml ./

• Now we can see the copy folder

$ ll

• After finishing the backup processing, start the things board and service.

$ sudo service things board start

Midnight Commander

• We can use midnight commander to compare our things board files and our backup files.

$ sudo mc

File Storage

• Where are located the files with the application, like configuration files, rule chain, widget, dashboard, and so on?

• ThingsBoard can store binary content (files) in the database. Currently, it is used to store report files generated by the Generate Report Node. Stored files are accessed by Send Email Node to create email attachments. Another way to send a file is the REST API Call Node. Users can access stored files using Files or Reports widgets which is part of the Files Widgets Bundle.

• Enter the things board config file

$ cd /etc/thingsboard/conf
$ ll

• To check the user share file of things board and look at what we have here

$ cd /usr/share/thingsboard
$ ll

• Enter the "bin" folder

$ cd bin
$ ll

• Basically, it's executable and some instance and installed data, etc. We have a config file where we will see the data folder and here we can see the certificate and some third-party related data.

• Enter the extension folder, which is used for custom-developed role nodes like rule chains.

$ cd extensiions/
$ ll

Clean Database

• How to clean a Database? Or How do purge all data older than a specific date, like keep only the last six months' data?
• To remove previous data from our database, we can use The PSQL console or Swagger. Both have some recommended commands.

PSQL console

• First, open psql console and connect to the thingsboard.
• Enter the password of the database and connect with thingsboard.

\c thingsboard

• To see all tables,

\dt+

• And describe time-series,

\d ts_kv
thingsboard=# select * from ts_kv limit 5;

• Basically, Thingsboard stores all the data in to ts_kv table. By the below command, we can see the details of the ts_kv table.

thingsboard=# select * from ts_kv;

• If we want to delete the records from the ts_kv table using a timestamp, like Saturday, January 1, 2022, 11:14:40 AM to Saturday, February 1, 2022, 11:14:40 AM, we can do it by,

DELETE FROM ts_kv WHERE "ts" >= '1643673600000' AND "ts" < '1643964657892';

• With epoch time converter web application, We can easily convert the timestamp to human readable data.

Key: We should make a backup file for the database and stop the things board service before work.

Swagger-ui

• Use a browser to log in to the thingsboard swagger service.
• Automatically it will open a pop-up and ask for authorizations.

• Use bearer token: bearer

  Key: We don't need to stop things board service because Swagger is trying to communicate with the things board server.

Clean events

• If we want to clean our role chains, we can clean "events" from the database table.

$ psql -U postgres -h localhost

\c thingsboard
\dt+

How can we fix error writing /etc/something.conf: permission denied?

• We need to give the sdit permisssion by sudo chmod
• Like sudo vim /etc/something.conf

PgAdmin4

• Creating a Database in PostgreSQL using user name and password.

sudo su - postgres
psql
postgres=#

• Now create a new database and a user using the following commands.

postgres=# CREATE USER admin WITH PASSWORD 'admin@postgres';
postgres=# CREATE DATABASE testdb;
postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb to admin;
postgres=# \q

• Configuring PostgreSQL Client Authentication,

PostgreSQL uses client authentication to decide which user accounts can connect to which databases from which hosts and this is 
controlled by settings in the client   authentication configuration file, which on Ubuntu is located at /etc/postgresql/12/main/pg_hba.conf.

• Open this file using your favorite text editor as shown.

sudo vim /etc/postgresql/14/main/pg_hba.conf

• Restart the PostgreSQL service.

sudo systemctl restart postgresql

Installing pgAdmin4 in Ubuntu

• pgAdmin4 APT repository,

 curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
 sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

• Install pgAdmin4,

sudo apt install pgadmin4

• Email address and set a strong secure password as well:

sudo /usr/pgadmin4/bin/setup-web.sh

• Accessing pgAdmin4 Web Interface

http://127.0.0.1/pgadmin4

🚩 Connect with me on social

• LinkedIn: LinkedIn
• Twitter: Twitter
• Instagram: Instagram

🔔 Subscribe to my YouTube channel: YouTube