How to deploy TimescaleDB on Azure and why to avoid it

Xavier Geerinck -

Update 06/SEP/2022

To provide an update, the post will allow you to deploy an Azure DB for Postgres and enable the TimescaleDB extension. However, due to Timescale its licensing scheme and "Community License" usage, you will be unable to utilize the most crucial features (e.g., add_retention_policy). Since deploying an Azure DB for Postgres is similar in cost to Azure Data Explorer we have decided to switch towards the latter.

If you do end up deploying the post, you will hit the issue of the license which stated: HINT:  Upgrade your license to 'timescale' to use this free community feature. and gave must be superuser to execute ALTER SYSTEM command while trying to change it.

Old Post

When working with IoT Projects we often want to use a timeseries database. In this space quite some options exist, ranging from unstructured stores such as InfluxDB, MongoDB, CosmosDB, ... to structures stores such as TimescaleDB, SQL Server, ...

Personally, I like using structured stores as it has a couple of benefits:

  • Easier out of the box querying as it uses native SQL
  • Open source

Now how can you create a TimescaleDB on Azure?

What you will learn

In this article you will learn how to:

  • Deploy a Postgres Server on Azure
  • Enable the TimescaleDB Extension and create a TimescaleDB
  • Connect to your TimescaleDB

Deploying on Azure

To deploy TimescaleDB on Azure, we will be utilizing Postgres for Azure Databases (referred to as flexible-server in the Azure CLI). This will create a Postgres Database and on there we can load the Timescale library.

In short:

  1. Create a Postgres Server
  2. Create a Database on the server
  3. Enable the Timescale extension

Configuring Parameters

Let's get started by configuring our parameters, just copy the below and fill in the details you want to use:

# Azure General
LOCATION='westeurope'
SUBSCRIPTION_ID='YOUR_SUB_ID'
RG_NAME='YOUR_RG_NAME'

az account set --subscription $SUBSCRIPTION_ID

# DB Specific
DB_SERVER_NAME='YOUR_SERVER_NAME'
DB_SERVER_USER='YOUR_USER'
DB_SERVER_PASS='YOUR_PASSWORD'
DB_NAME='YOUR_DB_NAME'

Creating the Server and Database

Once the details are configured, create the Postgres server with version 14 in the Burstable tier

💡 You can view the skus and tiers with az postgres flexible-server list-skus -l $LOCATION -o table`
# Create Postgres Server with major version 14
# Note: the tier is Burstable 
az postgres flexible-server create -l $LOCATION -g $RG_NAME \
    -n $DB_SERVER_NAME -u $DB_SERVER_USER -p $DB_SERVER_PASS \
    --tier Burstable --sku-name Standard_B2s \
    --version 14

Enabling the TimescaleDB Extensions and Libraries

We now have a Postgres server but TimescaleDB is not enabled yet. For this, configure the Postgres extensions and libraries to be enabled through the CLI and restart the server.

💡 You can view the server parameters with az postgres flexible-server parameter list -g $RG_NAME -s $DB_SERVER_NAME -o table
# Preload the TimescaleDB library on server start
az postgres flexible-server parameter set -g $RG_NAME -s $DB_SERVER_NAME \
    --name shared_preload_libraries --value timescaledb

# Allow the extension TimescaleDB to be created
az postgres flexible-server parameter set -g $RG_NAME -s $DB_SERVER_NAME \
    --name azure.extensions --value timescaledb
    
# Use the timescale license
ALTER SYSTEM SET timescaledb.license = 'timescale';
-- reload setting changes
SELECT pg_reload_conf();
    
# Restart the server
az postgres flexible-server restart --resource-group $RG_NAME --name $DB_SERVER_NAME

# Create DB
az postgres flexible-server db create -g $RG_NAME -s $DB_SERVER_NAME -d $DB_NAME
❗we upgrade the license from Apache 2 to the Timescale community edition https://docs.timescale.com/timescaledb/latest/timescaledb-edition-comparison/#timescaledb-apache-2-vs-timescaledb-community-editions to enable Timeseries features such as retention

Now we are ready to create our actual Database!

Managing our TimescaleDB Server

First, we need to be able to connect to our database. So let's add a firewall rule to the database with our current IP.

Note: the script below fetches your IP from ifconfig.co

Connecting to our TimescaleDB

# Get connection string
CONN_STR=$(az postgres flexible-server show-connection-string -s $DB_SERVER_NAME -d $DB_NAME -u $DB_SERVER_USER -p $DB_SERVER_PASS | jq -r '.connectionStrings.psql_cmd')

# Add current IP to Firewall
MY_IP=$(curl ifconfig.co)
az postgres flexible-server firewall-rule create \
    -g $RG_NAME -n $DB_SERVER_NAME -r allowip \
    --start-ip-address $MY_IP --end-ip-address $MY_IP

Create a Database Alias to run SQL Queries

Next, we create a Postgres Moniker with our password from the parameters above and configure the dbq alias that we can use to run commands

# Run command to enable extension
PASS_ENCODED=$(echo $DB_SERVER_PASS | sed 's/@/%40/g' | sed 's/!/%21/g')
DB_CONN_STR="postgresql://$DB_SERVER_USER:$PASS_ENCODED@$DB_SERVER_NAME.postgres.database.azure.com/$DB_NAME?sslmode=require"
alias dbq="docker run -it --rm postgres psql '$DB_CONN_STR' -c"

Creating the TimescaleDB Extension

Finally, we run the command below that will create the TimescaleDB extension in our database:

dbq "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"

Upon execution we will be welcomed to TimescaleDB!

➜ dbq "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"                         
WARNING:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 2.5.1
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started
 2. API reference documentation: https://docs.timescale.com/api/latest
 3. How TimescaleDB is designed: https://docs.timescale.com/timescaledb/latest/overview/core-concepts


CREATE EXTENSION

IoT Data Tables

As a bonus, I always create the following tables in my TimescaleDB to allow me to manage sensor data and their issues (they are quite simple but do the trick for me).

sensor_data

dbq "$(cat <<EOF
CREATE TABLE sensor_data (   
    time            TIMESTAMPTZ     NOT NULL,   
    sensor_type     VARCHAR(50)     NOT NULL,
    sensor_id       VARCHAR(50)     NOT NULL,   
    label           VARCHAR(255)    NOT NULL,   
    value           FLOAT           NOT NULL 
);

SELECT create_hypertable(
    'sensor_data',  
    'time'
);

-- Add a retention policy to remove data after 1 week (168 hours)
SELECT add_retention_policy(
    'sensor_data', 
    INTERVAL '168 hours'
);
EOF
)"

sensor_issue

CREATE TABLE sensor_issue (   
    time            TIMESTAMPTZ     NOT NULL,   
    sensor_type     VARCHAR(50)     NOT NULL,   
    sensor_id       VARCHAR(50)     NOT NULL,   
    error           TEXT            NOT NULL,   
    body            TEXT            NOT NULL 
);

SELECT create_hypertable(
    'sensor_issue',  
    'time'
);

-- Add a retention policy to remove data after 1 week (168 hours)
SELECT add_retention_policy(
    'sensor_issue', 
    INTERVAL '168 hours'
);