How To Install TimescaleDB on Ubuntu 20.04|18.04

This tutorial will take you through the steps to install TimescaleDB time-series database on Ubuntu20.04|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 20.04|18.04

Follow the steps provided here to get TimescaleDB up and running on your Ubuntu20.04|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:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Update the package lists and install postgresql package:

sudo apt update
sudo apt install postgresql-12

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

Set PostgreSQL admin user’s password

sudo su - postgres
psql -c "alter user postgres with password 'StrongDBP@ssw0rd'"

Step 3: Install TimescaleDB on Ubuntu 20.04|18.04

The last step is the installation of TimescaleDB on server.

Add PPA:

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

Update APT package index to confirm if the repository was indeed added:

$ sudo apt-get update
Hit:1 https://download.docker.com/linux/ubuntu focal InRelease
Hit:2 http://apt.postgresql.org/pub/repos/apt focal-pgdg InRelease
Hit:3 http://ppa.launchpad.net/timescale/timescaledb-ppa/ubuntu focal InRelease
Hit:4 http://security.ubuntu.com/ubuntu focal-security InRelease
Hit:5 http://nova.clouds.archive.ubuntu.com/ubuntu focal InRelease
Hit:6 http://nova.clouds.archive.ubuntu.com/ubuntu focal-updates InRelease
Hit:7 http://nova.clouds.archive.ubuntu.com/ubuntu focal-backports InRelease
Reading package lists... Done

Then install TimescaleDB for PostgreSQL 12, if you have PG 11, replace accordingly:

sudo apt install timescaledb-postgresql-12

Accept installation prompts that are shown to you:

Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  postgresql-12 postgresql-client-12 timescaledb-loader-postgresql-12 timescaledb-tools
Suggested packages:
  postgresql-doc-12 libpq-dev
The following NEW packages will be installed:
  postgresql-12 postgresql-client-12 timescaledb-loader-postgresql-12 timescaledb-postgresql-12 timescaledb-tools
0 upgraded, 5 newly installed, 0 to remove and 60 not upgraded.
Need to get 19.8 MB of archives.
After this operation, 85.6 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Now update your PostgreSQL configuration settings for TimescaleDB:

sudo timescaledb-tune --quiet --yes

Command execution output:

sing postgresql.conf at this path:
/etc/postgresql/12/main/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202110271433

Recommendations based on 1.94 GB of available memory and 2 CPUs for PostgreSQL 12
shared_preload_libraries = 'timescaledb'	# (change requires restart)
shared_buffers = 508786kB
effective_cache_size = 1490MB
maintenance_work_mem = 254393kB
work_mem = 12719kB
timescaledb.max_background_workers = 8
max_worker_processes = 13
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
wal_buffers = 15262kB
min_wal_size = 512MB
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 20
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200
timescaledb.last_tuned = '2021-10-27T14:33:21Z'
timescaledb.last_tuned_version = '0.11.0'
Saving changes to: /etc/postgresql/12/main/postgresql.conf

Restart PostgreSQL service after making the change:

sudo systemctl restart postgresql 

IF NOT, you need to update edit postgresql.conf to load necessary TimescaleDB libraries manually:

sudo vim /etc/postgresql/12/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 

Confirm if postgresql service was restarted successfully without any issues:

$ systemctl status [email protected]
● [email protected] - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/[email protected]; enabled-runtime; vendor preset: enabled)
     Active: active (running) since Wed 2021-10-27 14:35:58 UTC; 1min 5s ago
    Process: 7461 ExecStart=/usr/bin/pg_ctlcluster --skip-systemctl-redirect 12-main start (code=exited, status=0/SUCCESS)
   Main PID: 7483 (postgres)
      Tasks: 8 (limit: 2343)
     Memory: 36.2M
     CGroup: /system.slice/system-postgresql.slice/[email protected]
             ├─7483 /usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf
             ├─7485 postgres: 12/main: checkpointer
             ├─7486 postgres: 12/main: background writer
             ├─7487 postgres: 12/main: walwriter
             ├─7488 postgres: 12/main: autovacuum launcher
             ├─7489 postgres: 12/main: stats collector
             ├─7490 postgres: 12/main: TimescaleDB Background Worker Launcher
             └─7491 postgres: 12/main: logical replication launcher

Oct 27 14:35:55 frank-ubuntu-01 systemd[1]: [email protected]: Succeeded.
Oct 27 14:35:55 frank-ubuntu-01 systemd[1]: Stopped PostgreSQL Cluster 12-main.
Oct 27 14:35:55 frank-ubuntu-01 systemd[1]: Starting PostgreSQL Cluster 12-main...
Oct 27 14:35:58 frank-ubuntu-01 systemd[1]: Started PostgreSQL Cluster 12-main.

Step 4: Test TimescaleDB installation on Ubuntu 20.04|18.04

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

$ sudo 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