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.
Ubuntu 18.04 LTS with ufw enabled
Cloud compute instance with 1 CPU and 2 GB of memory
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
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
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=#
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
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
Next, we need to open remote access for the tutor user.
Allow PostgreSQL port 5432 using ufw.
$ sudo ufw allow 5432/tcp
$ sudo ufw reload
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 = '*'
tutor
userFind 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
See the TimescaleDB documentation for information, tutorials, and API reference.