postgresql

TimescaleDB

  • Reliability
  • Robustness
  • Security
  • Ecosystem
  • Specifically for time-series data
eCloudControl Engineering Team5 min read
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.

#  docker pull timescale/timescaledb-ha:pg14-latest
# docker run -d --name timescale db -p 5432:5432 -e POSTGRES_PASSWORD=********* timescale/timescaledb

How to install Debian/Ubuntu

  • Add the TimescaleDB third-party repository: PostgreSQL third-party repository to get the latest PostgreSQL packages:
# apt install gnupg postgresql-common apt-transport-https lsb-release wget
  • Run the PostgreSQL repository setup script:
  # /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

In Debian:

# echo "deb https://packagecloud.io/timescale/timescaledb/debian/ $(lsb_release -c -s) main" | sudo tee /etc/apt/sources.list.d/timescaledb.list
  • Install Timescale GPG key
  #  wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
  • Update your local repository list:
#  apt update
  • Install TimescaleDB:
# apt install timescaledb-2-postgresql-14
  • Ensure that your apt repository is up to date. Then Install the PostgreSQL-client package:
# apt-get install postgresql-client

In Ubuntu:

 #  echo "deb https://packagecloud.io/timescale/timescaledb/ubuntu/ $(lsb_release -c -s) main" |   sudo tee /etc/apt/sources.list.d/timescaledb.list
  • Install Timescale GPG key
# wget --quiet -O - https://packagecloud.io/timescale/timescaledb/gpgkey | sudo apt-key add -
  • Update your local repository list:
 # apt update
  • Install TimescaleDB:
# apt install timescaledb-2-postgresql-14
  • Make sure your apt repository is up to date. Then Install the PostgreSQL-client package:
 # apt-get install postgresql-client
  • 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:

# systemctl restart postgresql
  • On your local system,open the psql command-line for Postgres superuser:
# sudo -u postgres psql
  • Set the password for the Postgres user:
 # \password postgres

Exit from the PostgreSQL:

# \q
  • Use psql client to connect to PostgreSQL:
#  psql -U postgres -h localhost
  • At the psql prompt, create an empty database like tsdb:
# CREATE database tsdb;
  • Then Connect to the database you created:
# \c tsdb
  • Add the TimescaleDB extension:

CREATE EXTENSION IF NOT EXISTS timescaledb;

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

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

# psql -U postgres -h localhost -d tsdb

In RHEL/Centos :

# yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm
  • Create the Timescale repository
 # tee /etc/yum.repos.d/timescale_timescaledb.repo
  • Install TimescaleDB:
# apt install timescaledb-2-postgresql-14
  • Make sure your yum repository is up to date:
 # yum update
  • Install the PostgreSQL-client package:
# dnf install postgresql14
  • Install the PostgreSQL-client package:
# yum install postgresql-client
  • 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. **
# systemctl restart postgresql
  • On your local system,open the psql command-line for Postgres superuser:
 # sudo -u postgres psql
  • Set the password for the Postgres user:
# \password postgres
  • Exit from the PostgreSQL:
 # \q
  • Use psql client to connect to PostgreSQL:
 #  psql -U postgres -h localhost
  • At the psql prompt, create an empty database like tsdb:
 # CREATE database tsdb;
  • Then Connect to the database you created:
 # \c tsdb
  • Add the TimescaleDB extension:

CREATE EXTENSION IF NOT EXISTS timescaledb;

  • **Check the TimescaleDB extension using the \dx command at the psql prompt. **
 # \dx
  • After you have established the extension and the database, you can connect to your database directly using this command:
# psql -U postgres -h localhost -d tsdb

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.

TIMESCALEDB