How to Install TimescaleDB with PostgreSQL on Ubuntu Linux

This article explains how to install TimescaleDB with PostgreSQL on Ubuntu Linux.

PostgreSQL is a general-purpose, object-relational database management system, probably the most advanced open-source database. You can add custom functions using different programming languages such as C/C++, Java, etc.

TimescaleDB, on the other hand, is powered by PostgreSQL and can be used to analyze time-series data with a PostgreSQL query language. Timescale is like PostgreSQL but optimized for speed and scale.

Suppose you’re a seasoned database administrator and want a system that works like a traditional relational database yet scales in ways like NoSQL databases. In that case, TimescaleDB might be something to look at.

Follow the steps below to learn how to install PostgreSQL and TimescaleDB on Ubuntu.

Add PostgresSQL Repository to Ubuntu

Adding the PostgreSQL repository to Ubuntu is easy. All you have to run is the commands below to add the repository key.,., the key is there to authenticate and validate packages from the repository.

Run the commands below to add the repository key and the repository.

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

When you’re done, continue below.

Update and Install PostgreSQL

Once the repository and key are added, run the commands below to update and install the latest PostgreSQL packages.

To install PostgreSQL 11, run the commands below

sudo apt update
sudo apt-get install postgresql-11

After installing PostgreSQL, the commands below can be used to stop, start, enable, and check its status.

sudo systemctl stop postgresql.service
sudo systemctl start postgresql.service
sudo systemctl enable postgresql.service
sudo systemctl status postgresql.service

This is what the status command shows

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor prese
   Active: active (exited) since Wed 2018-10-31 11:58:09 CDT; 12s ago
 Main PID: 7930 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4663)
   CGroup: /system.slice/postgresql.service

Oct 31 11:58:09 ubuntu1804 systemd[1]: Starting PostgreSQL RDBMS.
Oct 31 11:58:09 ubuntu1804 systemd[1]: Started PostgreSQL RDBMS.

Create a PostgreSQL Linux User Password.

After installing PostgreSQL, it’s a good idea to create/change the default PostgreSQL user password. Run the below commands to create/change the user password in the bash shell, not the PostgreSQL interactive shell.

Set password for Linux user (Postgres)

sudo passwd postgres

It would be best if you were prompted to create a new Linux password for Postgres users.

Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

After setting a new password, whenever you want to access the PostgreSQL interactive shell, you’ll be prompted to confirm the password you created after running the above commands.

Access PostgreSQL

Now that PostgreSQL is installed, access its interactive shell and manage databases. You need to log in as a Postgres user. To do that, run the commands below:

sudo su -l postgres

Then, use the psql command in an interactive shell to create and manage PostgreSQL databases.

psql

Set password for DB administrator (Postgres)

su - postgres
psql

On the psql shell, run the below command to change the database admin password.

postgres=# password
OR
postgres=# password postgres

After that, quit and exit.

\q
exit

Install TimescaleDB for PostgreSQL 11

Now that PostgreSQL is installed, run the commands below to install and configure TimescaleDB to manage your PostgreSQL server.

Add TimescaleDB’s third-party repository and install TimescaleDB, which will download any dependencies it needs from the PostgreSQL repo:

sudo add-apt-repository ppa:timescale/timescaledb-ppa
sudo apt-get update

Now install the appropriate package for the PG version using the commands below:

sudo apt install timescaledb-postgresql-11

At a minimum, you will need to update your PostgreSQL. Conf file to include our library to the parameter shared_preload_libraries. The easiest way to get started is to run timescaledb-tune, which is installed by default when you run the above commands.

sudo bash
echo "shared_preload_libraries = 'timescaledb'" >> /etc/postgresql/11/main/postgresql.conf

shared_preload_libraries = ‘timescaledb’

Run the commands below to enable TimescaleDB libraries and optimize PostgreSQL settings.

sudo timescaledb-tune

Use the prompt below to answer the questions.

Using postgresql.conf at this path:
/etc/postgresql/11/main/postgresql.conf

Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201905100950

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated

Doing that will ensure that the TimescaleDB extension is correctly added to the parameter shared_preload_librariesas and offer suggestions for tuning memory, parallelism, and other settings.

When you’re done, log on to PostgreSQL and connect to TimesaleDB.

sudo su -l postgres
psql

Create a test database and extend its capabilities with TimescaleDB.

CREATE database test_db;

#change into the test_db:
\c test_db

Output:
You are now connected to database "test_db" as user "postgres".

#create TimescaleDB extension:
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

You should see a similar screen as below:

WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.3.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

The database is TimescaleDB enabled!

Congratulations! You have successfully installed PostgreSQL 11 with TimescaleDB on Ubuntu 16.04 | 18.04