Install and Setup TimescaleDB on Ubuntu 16.04 | 18.04 with PostgreSQL 11 Support
PostgreSQL is a general purpose and object-relational database management system, probably the most advanced open source database system…. you can also 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… TimescaleDB is like PostgreSQL, but optimized for speed and scale..
If you’re a seasoned database administrator and you want a database system that works like a traditional relational database, yet scales in ways like NoSQL databases, then TimescaleDB might be something to look at..
To learn how to install PostgreSQL and TimescaleDB on Ubuntu, follow the steps below
Step 1: Add PostgresSQL Repository to Ubuntu
Adding PostgresSQL repository to Ubuntu is easy… all you have to run is run 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…
Step 2: Update and Install PostgreSQL
Now that the repository and key are added, run the commands below to update and install the latest PostgresSQL 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.
Step 3: Creating PostgreSQL Linux User Password…
After installing PostgreSQL, it’s a good idea to create / change the default PostgreSQL user password… Run the commands below to create / change the user password in the bash shell and not PostgreSQL interactive shell..
Set password for Linux user (postgres)
sudo passwd postgres
You should be prompted to create a new Linux password for postgres user..
Enter new UNIX password: Retype new UNIX password: passwd: password updated successfully
After setting a new password, every time you want to access PostgreSQL interactive shell, you’ll be prompted to confirm the password you created after running the commands above…
Step 4: Accessing PostgreSQL
Now that PostgreSQL is installed, to access its interactive shell and manage databases… you need to log in as the postgres user… to do that run the commands below:
sudo su -l postgres
Then use the psql command in an interactive shell when you want to create and manage PostgreSQL databases.
psql
Set password for DB administrator (postgres)
su - postgres psql
On psql shell, run below command to change database admin password.
postgres=# password
OR
postgres=# password postgres
After that, quit and exit..
\q exit
Step 5: Installing TimescaleDB for PostgreSQL 11
Now that PostgreSQL is installed, run the commands below to install and configure TimescaleDB to use 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 appropriate package for 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 commands above..
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 optimized 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 TimescaleDB extension is properly added to the parameter shared_preload_librariesas well as offer suggestions for tuning memory, parallelism, and other settings…
When you’re done, logon onto PostgreSQL and connect to TimesaleDB…
sudo su -l postgres psql
Create a test database and extend it 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 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
Then database is TimescaleDB enabled!
Congratulations! You have successfully installed PostgreSQL 11 with TimescaleDB on Ubuntu 16.04 | 18.04
You may also like the post below: