How to Enable TimescaleDB on an Existing PostgreSQL Database

If you have a PostgreSQL cluster up-and-running, and you need to handle data that changes with time (like metrics collected from a system) you should consider using a time-series database that is designed to store this kind of data.

TimescaleDB is an open-source time-series database optimized for fast ingest and complex queries that supports full SQL. It is based on PostgreSQL and it offers the best of NoSQL and Relational worlds for Time-series data. 

In this blog, we will see how to manually enable TimescaleDB in an existing PostgreSQL database and how to do the same task using ClusterControl.

Enabling TimescaleDB Manually

For this blog, we will use CentOS 7 as the operating system and PostgreSQL 11 as the database server.

By default, you don’t have TimescaleDB enabled for PostgreSQL:

world=# dx

                 List of installed extensions

  Name   | Version |   Schema |     Description

---------+---------+------------+------------------------------

 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

(1 row)

So first, you need to add the corresponding repository to install the software:

$ cat /etc/yum.repos.d/timescale_timescaledb.repo

[timescale_timescaledb]

name=timescale_timescaledb

baseurl=https://packagecloud.io/timescale/timescaledb/el/7/$basearch

repo_gpgcheck=1

gpgcheck=0

enabled=1

gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey

sslverify=1

sslcacert=/etc/pki/tls/certs/ca-bundle.crt

metadata_expire=300

We will assume you have the PostgreSQL repository in place as this TimescaleDB installation will require dependencies from there.

Next step is to install the package:

$ yum install timescaledb-postgresql-11

And configure it in your current PostgreSQL database. For this, edit your postgresql.conf file and add ‘timescaledb‘ in the shared_preload_libraries parameter:

shared_preload_libraries = 'timescaledb'

Or if you already have something added there:

shared_preload_libraries = 'pg_stat_statements,timescaledb'

You can also configure the max_background_workers for TimescaleDB to specify the max number of background workers.

timescaledb.max_background_workers=4

Keep in mind that this change requires a database service restart:

$ service postgresql-11 restart

And then, you will have your TimescaleDB installed:

postgres=# SELECT * FROM pg_available_extensions WHERE name='timescaledb';

    name     | default_version | installed_version |                              comment



-------------+-----------------+-------------------+-----------------------------------------------

--------------------

 timescaledb | 1.6.0           | | Enables scalable inserts and complex queries f

or time-series data

(1 row)

So now, you need to enable it:

$ psql world

world=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

WARNING:

WELCOME TO

 _____ _                               _ ____________

|_   _(_)                             | | | _  ___ 

  | |  _ _ __ ___   ___ ___ ___ __ _| | ___| | | | |_/ /

  | | | |  _ ` _  / _ / __|/ __/ _` | |/ _  | | | ___ 

  | | | | | | | | |  __/__  (_| (_| | |  __/ |/ /| |_/ /

  |_| |_|_| |_| |_|___||___/_____,_|_|___|___/ ____/

               Running version 1.6.0

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

Done.

world=# dx

                                      List of installed extensions

    Name     | Version |   Schema |                         Description



-------------+---------+------------+--------------------------------------------------------------

-----

 plpgsql     | 1.0 | pg_catalog | PL/pgSQL procedural language

 timescaledb | 1.6.0   | public | Enables scalable inserts and complex queries for time-series

data

(2 rows)

Now, let’s see how to enable it using ClusterControl.

Using ClusterControl to Enable TimescaleDB

We will assume you have your PostgreSQL cluster imported in ClusterControl or even deployed using it. 

To enable TimescaleDB using ClusterControl, you just need to go to your PostgreSQL Cluster Actions and press on the “Enable TimescaleDB” option.

You will receive a warning about the database restart. Confirm it.

You can monitor the task in the ClusterControl Activity section.

Then you will have your TimescaleDB ready to use.

Conclusion

Now you have your TimescaleDB up and running, you can handle your time-series data in a more performant way. For this, you can create new tables or even migrate your current data, and of course, you should know how to use it to take advantage of this new concept.