(Last Updated On: September 24, 2018)

This tutorial will take you through the steps to install TimescaleDB time-series database on Ubuntu 18.04 LTS. TimescaleDB is an open-source time-series database optimized for fast ingest and complex queries. It speaks “full SQL” and is correspondingly easy to use like a traditional relational database, yet scales in ways previously reserved for NoSQL databases. TimescaleDB offers the best of both NoSQL and relational database systems.

Features of TimescaleDB

Below are the benefits of using TimescaleDB

  • Easy to Use: Has a full SQL interface for all SQL natively supported by PostgreSQL (including secondary indexes, non-time based aggregates, sub-queries, JOINs, window functions). it also supports API functions and optimizations and a robust support for Data retention policies.
  • Reliability: TimescaleDB is engineered up from PostgreSQL for full compatibility with existing PostgreSQL ecosystem and its cool features like streaming replication and backups.
  • Scalability: It has transparent time/space partitioning for both scaling up (single node) and scaling out (forthcoming). High data write rates (including batched commits, in-memory indexes, transactional support, support for data backfill). TimescaleDB also gives you parallelized operations across chunks and servers.

How to Install TimescaleDB on Ubuntu 18.04 LTS

Follow the steps provided here to get TimescaleDB up and running on your Ubuntu 18.04 LTS server.

Step 1: Update your system

Ensure your system is running the latest packages.

sudo apt update
sudo apt upgrade

Reboot system to ensure everything is working fine

sudo reboot

Step 2: Install PostgreSQL

TimescaleDB requires PostgreSQL 9.6 or later+. We need to install it before we can setup TimescaleDB.

Import  the repository signing key:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Add PostgreSQL apt repository:

cat >/etc/apt/sources.list.d/pgdg.list<<EOF
deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
EOF

Update the package lists and install postgresql package

sudo apt update
sudo apt install postgresql-10

The configuration file for PostgreSQL 10 is /etc/postgresql/10/main/postgresql.conf

Set PostgreSQL admin user’s password

# su - postgres
$ psql -c "alter user postgres with password 'StrongPassword'"

Step 3: Install TimescaleDB

The last step is the installation of TimescaleDB on Ubuntu 18.04 server.

Add PPA:

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

Then install TimescaleDB for PostgreSQL 10, if you have PG 9, replace 10 with 9

sudo apt install timescaledb-postgresql-10

Edit postgresql.conf to load necessary TImescaleDB libraries.

sudo vim  /etc/postgresql/10/main/postgresql.conf

Find the line below and change the value as shown (uncomment if needed):

shared_preload_libraries = 'timescaledb'

See screenshot below

Restart postgresql service after saving the changes

sudo systemctl restart postgresql

Step 4: Test TImescaleDB installation

We can now test our TImescaleDB installation by creating a new empty database or convert an existing PostgreSQL database to use TimescaleDB.

Connect to PostgreSQL, using a superuser named postgres

su - postgres
$ psql
psql (10.5 (Ubuntu 10.5-1.pgdg18.04+1))
Type "help" for help.
postgres=# CREATE database test_db;
CREATE DATABASE

Add TimescaleDB:

Connect to the database

postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".

Extend the database with TimescaleDB

test_db=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 0.12.1
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION

That’s it for today!. Connect to your newly created database:

psql -U postgres -h localhost -d test_db

Reference: TimescaleDB official Documentation