Deploying Timescale PostgreSQL on Kubernetes

The evolution of cloud services has brought about new changes in the industry. However, an emerging issue that many are facing is the shift towards "cloud-suitable" licenses by open-source systems. This new move restricts deployment options and encourages the use of only PaaS services. One such service is TimescaleDB, a popular option for storing timeseries data. As a fan of this system, I've explored ways to deploy it outside of PaaS services. In this article, we'll dive into how to deploy TimescaleDB on Kubernetes.

Kubernetes Cluster Configuration

First let's configure our Kubernetes cluster. I am using an Azure Kubernetes (AKS) cluster, providing an easy out of the box setup.

# Login to Azure
az login

# Set our Subscription scope
az account set --subscription SUBSCRIPTION_ID

# Get our clusters (we need the RG_NAME and CLUSTER_NAME)
az aks list -o table

# Configure Kubectl
az aks install-cli
az aks get-credentials -g RG_NAME -n CLUSTER_NAME

Once we executed the above we can see if everything worked by running kubectl get nodes which will display the configured nodes:

NAME                                STATUS   ROLES   AGE   VERSION
aks-agentpool-37111935-vmss000000   Ready    agent   11d   v1.24.10
aks-ray-10853311-vmss000002         Ready    agent   14m   v1.24.10

As you can see i have 2 node pools configured that automatically scale the underlying infrastructure when required for me

Lastly, we will configure Helm which is a Kubernetes package manager.

curl https://raw.githubusercontent.com/helm/helm/main/scripts/get-helm-3 | bash

Installing TimescaleDB

To now install TimescaleDB we could go the manual approach, but luckily for us, TimescaleDB offers Helm charts that allow us to easily install TimescaleDB on any Kubernetes cluster.

# Configuring the HELM Repo
helm repo add timescale 'https://charts.timescale.com'
helm repo update

# Installing TimescaleDB
helm install timescaledb timescale/timescaledb-single \
    --set nodeSelector.agentpool=agentpool

Note that we are installing Timescale specifically on our agentpool nodes. To get the label name simply run kubectl get nodes --show-labels

This will output the below

NAME: timescaledb
LAST DEPLOYED: Wed Apr 19 10:20:58 2023
NAMESPACE: default
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
TimescaleDB can be accessed via port 5432 on the following DNS name from within your cluster:
timescaledb.default.svc.cluster.local

To get your password for superuser run:

    # superuser password
    PGPASSWORD_POSTGRES=$(kubectl get secret --namespace default "timescaledb-credentials" -o jsonpath="{.data.PATRONI_SUPERUSER_PASSWORD}" | base64 --decode)

    # admin password
    PGPASSWORD_ADMIN=$(kubectl get secret --namespace default "timescaledb-credentials" -o jsonpath="{.data.PATRONI_admin_PASSWORD}" | base64 --decode)

To connect to your database, choose one of these options:

1. Run a postgres pod and connect using the psql cli:
    # login as superuser
    kubectl run -i --tty --rm psql --image=postgres \
      --env "PGPASSWORD=$PGPASSWORD_POSTGRES" \
      --command -- psql -U postgres \
      -h timescaledb.default.svc.cluster.local postgres

    # login as admin
    kubectl run -i --tty --rm psql --image=postgres \
      --env "PGPASSWORD=$PGPASSWORD_ADMIN" \
      --command -- psql -U admin \
      -h timescaledb.default.svc.cluster.local postgres

2. Directly execute a psql session on the master node

   MASTERPOD="$(kubectl get pod -o name --namespace default -l release=timescaledb,role=master)"
   kubectl exec -i --tty --namespace default ${MASTERPOD} -- psql -U postgresNAME: timescaledb
LAST DEPLOYED: Wed Apr 19 10:20:58 2023
NAMESPACE: default
STATUS: deployed
REVISION: 1
TEST SUITE: None
NOTES:
TimescaleDB can be accessed via port 5432 on the following DNS name from within your cluster:
timescaledb.default.svc.cluster.local

To get your password for superuser run:

    # superuser password
    PGPASSWORD_POSTGRES=$(kubectl get secret --namespace default "timescaledb-credentials" -o jsonpath="{.data.PATRONI_SUPERUSER_PASSWORD}" | base64 --decode)

    # admin password
    PGPASSWORD_ADMIN=$(kubectl get secret --namespace default "timescaledb-credentials" -o jsonpath="{.data.PATRONI_admin_PASSWORD}" | base64 --decode)

To connect to your database, choose one of these options:

1. Run a postgres pod and connect using the psql cli:
    # login as superuser
    kubectl run -i --tty --rm psql --image=postgres \
      --env "PGPASSWORD=$PGPASSWORD_POSTGRES" \
      --command -- psql -U postgres \
      -h timescaledb.default.svc.cluster.local postgres

    # login as admin
    kubectl run -i --tty --rm psql --image=postgres \
      --env "PGPASSWORD=$PGPASSWORD_ADMIN" \
      --command -- psql -U admin \
      -h timescaledb.default.svc.cluster.local postgres

2. Directly execute a psql session on the master node

   MASTERPOD="$(kubectl get pod -o name --namespace default -l release=timescaledb,role=master)"
   kubectl exec -i --tty --namespace default ${MASTERPOD} -- psql -U postgres

Connecting to TimescaleDB

We can now connect to our TimescaleDB (running as service kubectl get service/timescaledb). First, we need to fetch our Postgres Password:

PGPASSWORD_ADMIN=$(kubectl get secret --namespace default timescaledb-credentials -o jsonpath="{.data.PATRONI_admin_PASSWORD}" | base64 --decode)

Next, we can connect to the database through pgsql as we normally would

kubectl run -i --tty --rm psql --image=postgres \
  --env "PGPASSWORD=$PGPASSWORD_ADMIN" \
  --command -- psql -U admin \
  -h timescaledb.default.svc.cluster.local postgres

Note: I am using timescaledb as the service name, if you want to change this make sure to run helm with a different name

In the prompt that pops-up we run \l to get the databases configured:

postgres=> \l
                                             List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+---------+---------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 |            | libc            | =c/postgres          +
           |          |          |         |         |            |                 | postgres=CTc/postgres
(3 rows)

And there you go! You have successfully configured TimescaleDB on Kubernetes. Note that a multi-node distributed cluster setup is also possible by using the timescaledb-multi helm chart.

Summary

We have successfully installed TimescaleDB on a Kubernetes cluster. If you wish to remove it, you can simply run helm delete timescaledb. Hopefully this post was interesting, let me know what you think in the comments below!