Atlantic.Net Blog

How to Install TimescaleDB on Ubuntu 20.04

Hitesh Jethva
by Atlantic.Net (191posts) under VPS Hosting
0 Comments

TimescaleDB is a free, open-source, powerful database system powered by PostgreSQL. It is an extension of PostgreSQL and is specially designed to analyze time-series data with PostgreSQL. TimescaleDB is very similar to PostgreSQL, but it is optimized for speed and scale. Generally, relational databases are mainly used to store data, and they cannot handle the large volumes of time series data. This where TimescaleDB excels; it combines the speed of NoSQL databases and the ease-of-use of relational databases.

In this tutorial, we will show you how to install and use TimescaleDB on Ubuntu 20.04.

Prerequisites

  • A fresh Ubuntu 20.04 VPS on the Atlantic.Net Cloud Platform
  • A root password configured on your server

Step 1 – Create an Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 20.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged in to your Ubuntu 20.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – Install PostgreSQL Database

First, you will need to install a PostgreSQL server in your server. By default, it is available in the Ubuntu standard repository. You can install it easily using the following command:

apt-get install postgresql postgresql-contrib -y

Once the installation is completed, log in to PostgreSQL and set the postgres password:

su - postgres
psql -c "alter user postgres with password 'password'"

Next, exit from the PostgreSQL shell with the following command:

exit

Step 3 – Install TimescaleDB

By default, TimescaleDB is not available in the Ubuntu 20.04 default repository, so you will need to add the repository for it.

First, install all the required dependencies with the following command:

apt-get install gnupg2 software-properties-common curl git unzip -y

Once all the dependencies are installed, add the TimescaleDB repository with the following command:

add-apt-repository ppa:timescale/timescaledb-ppa -y

Next, install TimescaleDB by running the following command:

apt-get install timescaledb-postgresql-12 -y

Once the TimescaleDB is installed, it is recommended to tune some desired configurations. You can do it with the following command:

timescaledb-tune --quiet --yes

You should get the following output:

Next, restart the PostgreSQL service to apply the configuration changes:

systemctl restart postgresql

Step 4 – Create a New Database and Enable TimescaleDB

In this section, we will create a new database, enable TimescaleDB, and connect the database to it.

First, log in to the PostgreSQL shell and create a new database named testdb with the following command:

su - postgres
psql
CREATE DATABASE testdb;

Next, change the database to testdb and connect it to the TimescaleDB with the following command:

\c testdb
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

You should get the following output:

The time-series data are hypertables which consist of many individual tables. We will create a regular SQL table and then convert it into a hypertable via the function create_hypertable.

First, create a table to store temperature and humidity information.

CREATE TABLE conditions (
time        TIMESTAMP WITH TIME ZONE NOT NULL,
device_id   TEXT,
temperature  NUMERIC,
humidity     NUMERIC
);

Next, transform your table into a hypertable with the following command:

SELECT create_hypertable('conditions', 'time');

Next, insert some data into the hypertable with the following command:

INSERT INTO conditions(time, device_id, temperature, humidity)
VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

Next, insert multiple rows of data with the following command:

INSERT INTO conditions
VALUES
(NOW(), 'weather-pro-000002', 71.0, 51.0),
(NOW(), 'weather-pro-000003', 70.5, 50.5),
(NOW(), 'weather-pro-000004', 70.0, 50.2);

You can now verify your inserted data with the following command:

INSERT INTO conditions
VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

You should get the following output:

time              |     device_id      | temperature | humidity
-------------------------------+--------------------+-------------+----------
2020-12-03 15:48:24.329461+00 | weather-pro-000002 |        70.1 |     50.1
(1 row)

INSERT 0 1

You can also delete data from the hypertable using the following command:

DELETE FROM conditions WHERE temperature > 80;

Once the data is deleted, you can run the VACUUM command to reclaim space still used by data that has been deleted.

VACUUM conditions;

Conclusion

Congratulations! You have successfully installed and configured TimescaleDB on Ubuntu 20.04. You can now use TimescaleDB for storing time-series data and use those data to create graphs. Get started with TimescaleDB on VPS hosting from Atlantic.Net!

Get A Free To Use Cloud VPS

Free Tier Includes:
G3.2GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G3.2GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources