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 -fStep 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.gpgAdd 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-12The 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... DoneThen install TimescaleDB for PostgreSQL 12, if you have PG 11, replace accordingly:
sudo apt install timescaledb-postgresql-12Accept 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 --yesCommand 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.confRestart 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.confFind 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 DATABASEAdd 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 EXTENSIONThat’s it for today!. Connect to your newly created database:
psql -U postgres -h localhost -d test_dbReference: TimescaleDB official Documentation
