PostgreSQL Timescale DB

Installation in Ubuntu

Installing Timescale DB following the documentation here

Checked the ubuntu version on the Dell machine and if postgreSQL is already installed.

No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.6 LTS
Release:        20.04
Codename:       focal

pavan@gqc-Inspiron-N5110:~$ psql --version
psql (PostgreSQL) 15.2 (Ubuntu 15.2-1.pgdg20.04+1)

Since postgreSQL is present, removed it before moving onto installing TimescaleDB.
```
sudo apt-get --purge remove postgresql postgresql-*
sudo reboot now
```

Install PostgreSQL TimeScale DB

sudo apt install gnupg postgresql-common apt-transport-https lsb-release wget
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
sudo apt update
sudo apt install timescaledb-2-postgresql-14

sudo timescaledb-tune

Setup timescale DB

Start psql

sudo apt-get update
sudo apt-get install postgresql-client
sudo systemctl restart postgresql
sudo -u postgres psql

Setup the password for the user posetgres
note
Password used here is postgres
```
\password postgres # Enter your password to the prompt that comes up
```
```
\q
```

Connect to postgreSQL throug psql client and setup timescaleDB

psql -U postgres -h localhost

CREATE database tsdb;
\c tsdb
CREATE EXTENSION IF NOT EXISTS timescaledb;

Verify the timeseriesDB by
```
\dx
```
Finally you can connect to the DB via,
```
psql -U postgres -h localhost -d tsdb
```

Migrating from TDEngine to TimescaleDB

Existing table schema in TDEngine

Sensor_mesh DB:

Collimator DB:

Hydrotrek supertable and Collimator supertable are the same. Collimator tables were not deleted after they were renamed to Hydrotrek from the notebook.

Steps for deepvibe timescale DB

Clone git@github.com:gqc/deepvibe-api-drf.git
Create a separate virtual env: conda create -n deepvibe-api-drf python=python
Activate the virtual environment created above.
Install the requirements by pip install -r requirements.txt
Modify the psql port to 5433.
1. Open /etc/postgresql/14/main/postgresql.conf and replace the port=XXXX with port=5433
2. Restart psql service by sudo service postgresql restart

Start postgreSQL

sudo systemctl restart postgresql
psql -U postgres -h localhost -p 5433

Create a copy of settings.py as local_settings.py and add your database details into it. For my case it was:

# Database
 # https://docs.djangoproject.com/en/4.2/ref/settings/#databases
 DATABASES = {
 'default': {
     'ENGINE': 'timescale.db.backends.postgresql',
     'NAME': 'deepvibe',
     'USER': 'postgres',
     'PASSWORD': 'postgres',
     'HOST': 'localhost',
     'PORT': '5433'
 }
 }

Run migrate script.
```
python manage.py migrate
```
Create super user
```
python manage.py createsuperuser
```
I used postgres as the username and postgres as the password.
Verify the table creation.
1. Start psql by psql -U postgres -h localhost -p 5433
2. \list will list all the databases.
3. \c deepvibe will connect to the deepvibe DB.
4. \dt will list the tables.
5. \d accelerometer_history will show the table details.

Notes

(01/30/2024) Pavan

Notes on the timescale db integration with deepvibe server. The psql server is setup locally on the Ubuntu Dell machine and all the testing is done on the local network.

The deepVibe server changed to work with PostgreSQL timescale DB is present on the timescale_db branch in the repo.
The new interface is present in the files, timescaleDB_interface.c and timescaleDB_interface.h
deepVibe server is communicating with the REST endpoint, deepvibe-api-DRF, using CURL
I've created a generic, post_to_deepvibe_API_DRF() function, which takes in the endpoint address and a dictionary of fields, which are constructed through respective write to table functions.
The timescale DB is accessed from Grafana dashboard.
1. Install postgreSQL plugin if not already installed.
2. Add a new datasource to connect the timescale DB instance. Make sure to tick timescale-db field during the process.
3. In the dashboard, once you pick the postgreSQL as the datasource, the panel selectors will change from pure SQL to field based selection and we can set up queries as required.
4. Existing dashboard json file is present as grafana_dashboard_for_timescale_db.json in the repo under timescale_db branch. You may import that file as a new grafana dashboard to begin with.

danger

When working with grafana cloud, postgreSQL endpoint needs to open access to all the possible addresses grafana might be accessing data from. Jake had done this before during a test. Can raise security questions.

(12/12/2023) Jake

If you make model changes, you will need to run python manage.py makemigrations first to generate the migration files. python manage.py migrate is used to apply migration files to the database.
This repository is the API. If you run the app using python manage.py runserver, you can try calling the endpoint: localhost:8000/api/example But it will return an empty set.
I was working on setting up a production server for this API on one of the office machines.

(12/10/2023) Jake

Then Django steps:

Virtual env
pip install
Create empty database (deepvibe)
Create local_settings.py and reference your specific database
python manage.py migrate
python manage.py createsuperuser

PostgreSQL Timescale DB

Installation in Ubuntu​

Migrating from TDEngine to TimescaleDB​

Existing table schema in TDEngine​

Steps for deepvibe timescale DB​

Notes​

(01/30/2024) Pavan​

(12/12/2023) Jake​

(12/10/2023) Jake​