TIMESCALEDB

WHAT IS TIMESCALE DB?

TimescaleDB is a relational database for time-series data that is implemented as an extension to PostgreSQL, which means it executes within a PostgreSQL server as part of the same process, with code that offers new features for time-series data management.

ADVANTAGES OF TIMESCALE DB

• Reliability 
• Robustness
• Security
• Ecosystem
• Specifically for time-series data

WHAT IS TIME SERIES DATA?

Time series data is frequently changing, meaning the data change with time. We can use this for various places like healthcare, DevOps monitoring, IoT, etc.

The two main concepts of TimescaleDB:

• Hypertables 
• Chunks

Hypertables contain singular tables; also, the TimescaleDB can be deployed as either a single node, with physical replicas, or as a multi-node cluster to enable distributed hyper tables.

Chunks have a lot of individual tables which store the data.

The hyper table’s data can be either one or two dimensions. It can be built up by a time interval and an  (optional) “partition key” value. Approximately all user interactions with TimescaleDB are with hyper tables. Creating tables, indexes, altering tables, selecting data, and inserting data must be executed on the hyper table.

HOW TO INSTALL AND SET UP THE TIMESCALE DB EXTENSION IN POSTGRESQL?

Timescaledb, similarly to PostgreSQL, supports many different ways of installation, including installation on Ubuntu, Debian, RHEL/Centos, Windows, or cloud platforms.

Below command will pull a Docker image from Docker Hub if it still needs to be installed and then run it.

How to install Debian/Ubuntu

1. Add the TimescaleDB third-party repository:* PostgreSQL third-party repository to get the latest PostgreSQL packages:

          *  Run the PostgreSQL repository setup script:

In Debian:

• Install Timescale GPG key

• Update your local repository list:

• Install TimescaleDB:

• Ensure that your apt repository is up to date. Then Install the PostgreSQL-client package:

In Ubuntu:

• Install Timescale GPG key

• Update your local repository list:

• Install TimescaleDB:

• Make sure your apt repository is up to date. Then Install the PostgreSQL-client package:

• Edit the config file in this path  (vi /etc/postgresql/14/main/postgresql.conf)
   Add this line  shared_preload_libraries = ‘timescaledb’
• Restart PostgreSQL and create the TimescaleDB extension:
  

Restart the service after enabling TimescaleDB with timescale db-tune:

• On your local system,open the psql command-line for  Postgres superuser:

• Set the password for the Postgres user:

Exit from the PostgreSQL:

•  Use psql client to connect to PostgreSQL:

• At the psql prompt, create an empty database like  tsdb:

• Then Connect to the database you created:

• Add the TimescaleDB extension:
  
  CREATE EXTENSION IF NOT EXISTS timescaledb;

•  Check the TimescaleDB extension using the \dx command at the psql prompt.

 After you have established the extension and the database, you can connect to your database directly using this command:

In RHEL/Centos :

• Create the Timescale repository

• Install TimescaleDB:

• Make sure your  yum repository is up to date:

• Install the PostgreSQL-client package:

• Install the PostgreSQL-client package:

• Edit the config file  (vi /var/lib/pgsql/14/data/postgresql.conf)

Add this line  shared_preload_libraries = ‘timescaledb’

• Restart PostgreSQL and create the TimescaleDB extension:Restart the service after enabling TimescaleDB with timescale db-tune:
  

• On your local system,open the psql command-line for  Postgres superuser:

• Set the password for the Postgres user:

• Exit from the PostgreSQL:

• Use psql client to connect to PostgreSQL:

• At the psql prompt, create an empty database like  tsdb:

•   Then Connect to the database you created:

• Add the TimescaleDB extension:
  CREATE EXTENSION IF NOT EXISTS timescaledb;

• Check the TimescaleDB extension using the \dx command at the psql prompt.

• After you have established the extension and the database, you can connect to your database directly using this command:

Cloud Dev-Ops Engineer | Cloud Control

Cloud DevOps Engineer with more than 1 years of experience in supporting, automating, and optimizing deployments to hybrid cloud platforms using DevOps processes, tools, CI/CD, containers, and Kubernetes in both Production and Development environments.