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
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
sudo apt update sudo apt install postgresql-10
The configuration file for PostgreSQL 10 is
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.
sudo add-apt-repository ppa:timescale/timescaledb-ppa sudo apt-get update
Then install TimescaleDB for PostgreSQL 10, if you have PG 9, replace
sudo apt install timescaledb-postgresql-10
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
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
# su - postgres $ psql psql (10.5 (Ubuntu 10.5-1.pgdg18.04+1)) Type "help" for help. postgres=# CREATE database test_db; CREATE DATABASE
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