Learn how to - How To Install TimescaleDB on Ubuntu 22.04|20.04|18.04

Felix Williams Databases Linux Tutorials Ubuntu May 27, 2023

Learn how to – 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.

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

How to Install TimescaleDB on Ubuntu 18.04 LTS 1

Restart postgresql service after saving the changes:

sudo systemctl restart postgresql@12-main.service

Confirm if postgresql service was restarted successfully without any issues:

$ systemctl status postgresql@12-main.service
● postgresql@12-main.service - PostgreSQL Cluster 12-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; 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/postgresql@12-main.service
             ├─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]: postgresql@12-main.service: 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

Cart

Learn how to – How To Install TimescaleDB on Ubuntu 22.04|20.04|18.04