How To Install TimescaleDB on Ubuntu 22.04|20.04|18.04

This tutorial will take you through the steps to install TimescaleDB time-series database on Ubuntu 22.04|20.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.

Original content from computingforgeeks.com - post 3841

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 22.04|20.04|18.04

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

Step 1: Update your system

Ensure your system is running the latest packages.

sudo apt update && sudo apt -y full-upgrade

Reboot system to ensure everything is working fine

[ -f /var/run/reboot-required ] && sudo reboot -f

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:

curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc|sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

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 database server is /etc/postgresql/*/main/postgresql.conf

Set PostgreSQL admin user’s password

sudo su - postgres
psql -c "alter user postgres with password 'StrongDBPassw0rd'"
exit

Step 3: Install TimescaleDB on Ubuntu 22.04|20.04|18.04

The last step is the installation of TimescaleDB on server.

Ubuntu 20.04|18.04

Add PPA to the system using the commands below:

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

Ubuntu 22.04:

## Import GPG key ###
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 55EE6BF7698E3D58D72C0DD9ECB3980CC59E610B

## Add repo ###
sudo tee /etc/apt/sources.list.d/timescale-ubuntu-timescaledb-ppa-jammy.list<<EOF
deb https://ppa.launchpadcontent.net/timescale/timescaledb-ppa/ubuntu/ focal main
EOF

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

$ sudo apt 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 [email protected] 

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 22.04|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 (12.11 (Ubuntu 12.11-1.pgdg22.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 1.7.5
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