Knowledgebase

How to Install TimescaleDB on Ubuntu 18.04 Print

  • 0

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

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.

Introduction TimescaleDB is an open-source time-series database, built as an extension of PostgreSQL. In this tutorial we will: Install TimescaleDB on Ubuntu 18.04 Create a database to store time-series data. Create a regular user to access that database. 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.

Was this answer helpful?
Back

Powered by WHMCompleteSolution