Knowledgebase

Introduction

TimescaleDB is an open-source time-series database, built as an extension of PostgreSQL. In this tutorial we will:

1. Install TimescaleDB on Ubuntu 18.04
2. Create a database to store time-series data.
3. Create a regular user to access that database.
4. Allow remote connections for that user.

Prerequisites

• Ubuntu 18.04 LTS with ufw enabled
• Cloud compute instance with 1 CPU and 2 GB of memory
• Non-root account with sudo privilege
• Server packages are up to date.

1. Install PostgreSQL

Add PostgreSQL's third-party repository to get the latest package.

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -c -s)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list
$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
$ sudo apt-get update

Add TimescaleDB's third-party repo and install TimescaleDB.

$ sudo add-apt-repository ppa:timescale/timescaledb-ppa
$ sudo apt-get update
$ sudo apt install timescaledb-postgresql-11

2. Post-installation Configuration

Use timescaledb-tune command to configure TimescaleDB. One of the things it does is updating postgresql.conf file to include TimescaleDB library. This command is installed when we installed TimescaleDB.

$ sudo timescaledb-tune

Answer 'yes' to each question. Default value is fine for this tutorial.

Restart PostgreSQL service to reload new configuration.

$ sudo service postgresql restart

3. Set up TimescaleDB

Check if our installation is successful

To check if our installation is done, switch to user postgres and access PostgreSQL terminal.

$ sudo -i -u postgres
postgres@vultr:~$ psql

If our installation is successful, we are now in PostgreSQL terminal and it will display its prompt.

psql (11.7 (Ubuntu 11.7-2.pgdg18.04+1))
Type "help" for help.

postgres=# 

Create new user and database

Quit PostgreSQL terminal and exit the postgres user account. Then add new user and new database for that user to store our time-series data. Here, we use tutor as username and tutorialdb as database name.

postgres=# \q
postgres@vultr:~$ createuser --pwprompt tutor
Enter password for new role: 
Enter it again:
postgres@vultr:~$ createdb -O tutor tutorialdb

Extend database with TimescaleDB

Our newly created database is still a regular PostgreSQL database. It knows nothing about TimescaleDB. For it to have TimescaleDB features and functionalities we need to extend it using a superuser account (in this case postgres account).

Access the PostgreSQL terminal as the postgres user..

postgres@vultr:~$ psql
psql (11.7 (Ubuntu 11.7-2.pgdg18.04+1))
Type "help" for help.

Connect to the tutorialdb database.

postgres=# \c tutorialdb 
You are now connected to database "tutorialdb" as user "postgres".

Add the timescaledb extension, then quit.

tutorialdb=# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

tutorialdb=# \q

4. Open Remote Access

Next, we need to open remote access for the tutor user.

Open PostgreSQL Port

Allow PostgreSQL port 5432 using ufw.

$ sudo ufw allow 5432/tcp
$ sudo ufw reload

Listen to other than localhost

Find postgresql.conf.

$ sudo find / -name 'postgresql.conf' 

Using the location from the previous command, edit it to allow remote access.

$ sudo nano /etc/postgresql/11/main/postgresql.conf

Replace this line:

listen_addresses = 'localhost'

With:

listen_addresses = '*'

Allow remote access to tutor user

Find pg_hba.conf.

$ sudo find / -name 'pg_hba.conf' 

Using the location from the previous command, edit the file.

sudo nano /etc/postgresql/11/main/pg_hba.conf

Add both these lines at the end of the file:

host    all             tutor              0.0.0.0/0                    md5
host    all             tutor              ::/0                         md5

Restart PostgreSQL:

sudo systemctl restart postgresql

Conclusion

See the TimescaleDB documentation for information, tutorials, and API reference.