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.

Copy to Clipboard
Copy to Clipboard

How to install Debian/Ubuntu

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

          *  Run the PostgreSQL repository setup script:

Copy to Clipboard

In Debian:

Copy to Clipboard
  • Install Timescale GPG key
Copy to Clipboard
  • Update your local repository list:
Copy to Clipboard
  • Install TimescaleDB:
Copy to Clipboard
  • Ensure that your apt repository is up to date. Then Install the PostgreSQL-client package:
Copy to Clipboard

In Ubuntu:

Copy to Clipboard
  • Install Timescale GPG key
Copy to Clipboard
  • Update your local repository list:
Copy to Clipboard
  • Install TimescaleDB:
Copy to Clipboard
  • Make sure your apt repository is up to date. Then Install the PostgreSQL-client package:
Copy to Clipboard
  • 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:

Copy to Clipboard
  • On your local system,open the psql command-line for  Postgres superuser:
Copy to Clipboard
  • Set the password for the Postgres user:
Copy to Clipboard

Exit from the PostgreSQL:

Copy to Clipboard
  •  Use psql client to connect to PostgreSQL:
Copy to Clipboard
  • At the psql prompt, create an empty database like  tsdb:
Copy to Clipboard
  • Then Connect to the database you created:
Copy to Clipboard
  • Add the TimescaleDB extension:

    CREATE EXTENSION IF NOT EXISTS timescaledb;
  •  Check the TimescaleDB extension using the \dx command at the psql prompt.
Copy to Clipboard

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

Copy to Clipboard

In RHEL/Centos :

Copy to Clipboard
  • Create the Timescale repository
Copy to Clipboard
  • Install TimescaleDB:
Copy to Clipboard
  • Make sure your  yum repository is up to date:
Copy to Clipboard
  • Install the PostgreSQL-client package:
Copy to Clipboard
  • Install the PostgreSQL-client package:
Copy to Clipboard
  • 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:
Copy to Clipboard
  • On your local system,open the psql command-line for  Postgres superuser:
Copy to Clipboard
  • Set the password for the Postgres user:
Copy to Clipboard
  • Exit from the PostgreSQL:
Copy to Clipboard
  • Use psql client to connect to PostgreSQL:
Copy to Clipboard
  • At the psql prompt, create an empty database like  tsdb:
Copy to Clipboard
  •   Then Connect to the database you created:
Copy to Clipboard
  • Add the TimescaleDB extension:
    CREATE EXTENSION IF NOT EXISTS timescaledb;
  • Check the TimescaleDB extension using the \dx command at the psql prompt.
Copy to Clipboard
  • After you have established the extension and the database, you can connect to your database directly using this command:
Copy to Clipboard

About the Author

Aryamol Ramanan

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.