Skip to content

TimescaleDB®

For Elastisys Managed Services Customers

You can order Managed TimescaleDB® Apache 2 Edition by filing a service ticket. Here are the highlights:

  • Business continuity:
    • Standard Plan is configured with two replicas (one primary and one standby).
    • Premium Plan is configured with three replicas (one primary and two standby-s).
  • Disaster recovery:
    • Backup scope includes user definitions, data definitions, and the data per-se.
    • A full backup is taken every day between 0:00 am and 6:00 am CET. The backup retention period is 30 days unless otherwise requested by the customer.
    • Point-in-Time Recovery (PITR) is provided for the last 7 days with a recovery point objective of 5 minutes.
    • Long-term backup schemes can be enabled after discussion with the customer.
  • Only Apache 2 edition features are included (see below)
  • Monitoring, security patching and incident management: included.

For more information, please read ToS Appendix 3 Managed Additional Service Specification.

Open source vs source available

Please note that there are two versions of TimescaleDB:

  • TimescaleDB Apache 2 Edition, which is open source;
  • TimescaleDB "Community Edition", which -- despite its misleading name -- is not open source.

Although the so-called "Community Edition" is source-available, it is technically proprietary, given that it is licensed under the Timescale License (TSL). Amongst others, the TSL prohibits using TimescaleDB "Community Edition" for database-as-a-service.

Therefore, Elastisys does not offer TimescaleDB "Community Edition".

TimescaleDB Deployment Model
TimescaleDB on Welkin Deployment Model
This help you build a mental model on how to access TimescaleDB as an Application Developer and how to connect your application to TimescaleDB.

This page will help you succeed in connecting your application to the time-series database TimescaleDB which meets your security and compliance requirements.

TimescaleDB is an extension on top of our managed PostgreSQL. This means that your administrator will be setting up a complete PostgreSQL cluster for you and you just use it for TimescaleDB via the TimescaleDB extension.

Note

TimescaleDB is not a viable option for collecting all metrics from the Kubernetes cluster. The data is uncompressed and would take a lot of space to store and use a lot of resources to analyze, unless you want to use it with a very short retention period. This is not usually a problem for collecting application specific metrics, since they are not as many as the metrics that are generated from the Kubernetes cluster.

Important

Due to very different performance-tuning characteristics, Timescale and PostgreSQL databases should never run on the same PostgreSQL cluster. To comply with this, it is essential that every PostgreSQL database that gets created on the PostgreSQL cluster also has the Timescale extension created for it.

If you want to use TimescaleDB on your Welkin cluster, ask your administrator to provision a new PostgreSQL cluster inside your Welkin environment. Then set up the TimescaleDB extension.

Install Prerequisites

Before continuing, make sure you have access to the Kubernetes API, as describe here.

Make sure to install the PostgreSQL client on your workstation. On Ubuntu, this can be achieved as follows:

sudo apt-get install postgresql-client

Getting Access

Your administrator will set up a Secret inside Welkin, which contains all information you need to access your PostgreSQL cluster. The Secret has the following shape:

apiVersion: v1
kind: Secret
metadata:
  name: $SECRET
  namespace: $NAMESPACE
stringData:
  # PGHOST represents a cluster-scoped DNS name, which only makes sense inside the Kubernetes cluster.
  # E.g., postgresql1.postgres-system.svc.cluster.local
  PGHOST: $PGHOST
  # PGHOST_REPLICA represents a cluster-scoped DNS name that references the replica, can be used for read-only queries.
  PGHOST_REPLICA: $PGHOST_REPLICA

  # These fields map to the environment variables consumed by psql.
  # Ref https://www.postgresql.org/docs/13/libpq-envars.html
  PGUSER: $PGUSER
  PGPASSWORD: $PGPASSWORD
  PGSSLMODE: $PGSSLMODE

  # This is the Kubernetes Service name to which you can port-forward to in order to get access to the PostgreSQL cluster from outside the Kubernetes cluster.
  # Ref https://kubernetes.io/docs/tasks/access-application-cluster/port-forward-access-application-cluster/
  USER_ACCESS: $USER_ACCESS

Important

The Secret is very precious! Prefer not to persist any information extracted from it, as shown below.

To extract this information, proceed as follows:

export SECRET=            # Get this from your administrator
export NAMESPACE=         # Get this from your administrator

export PGHOST=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGHOST}' | base64 --decode)
export PGUSER=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGUSER}' | base64 --decode)
export PGPASSWORD=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGPASSWORD}' | base64 --decode)
export PGSSLMODE=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.PGSSLMODE}' | base64 --decode)
export USER_ACCESS=$(kubectl -n $NAMESPACE get secret $SECRET -o 'jsonpath={.data.USER_ACCESS}' | base64 --decode)

Important

Do not configure your application with the PostgreSQL admin username and password. Since the application will get too much permission, this will likely violate your access control policy.

Important

If you change the password for $PGUSER, you are responsible for keeping track of the new password.

Create an Application User

First, in one console, fetch the information from the access Secret as described above and port forward into the PostgreSQL master.

kubectl -n $NAMESPACE port-forward svc/$USER_ACCESS 5432

Important

Since humans are bad at generating random passwords, we recommend using pwgen.

Second, in another console, fetch the information from the access Secret again and run the PostgreSQL client to create the application database and user:

export APP_DATABASE=myapp
export APP_USERNAME=myapp
export APP_PASSWORD=$(pwgen 32)

cat <<EOF | psql -d postgres -h 127.0.0.1 -U $PGUSER \
    --set=APP_DATABASE=$APP_DATABASE \
    --set=APP_USERNAME=$APP_USERNAME \
    --set=APP_PASSWORD=$APP_PASSWORD
create database :APP_DATABASE;
create user :APP_USERNAME with encrypted password ':APP_PASSWORD';
grant all privileges on database :APP_DATABASE to :APP_USERNAME;
EOF

Continue with the second console in the next section to create a Secret with this information.

Create an Application Secret

First, check that you are on the right Welkin cluster, in the right application namespace:

kubectl get nodes
kubectl config view --minify --output 'jsonpath={..namespace}'; echo

Now, create a Kubernetes Secret in your application namespace to store the PostgreSQL application username and password. For consistency, prefer sticking to naming connection parameters as the environment variables consumed by psql.

cat <<EOF | kubectl apply -f -
apiVersion: v1
kind: Secret
metadata:
    name: app-postgresql-secret
type: Opaque
stringData:
    PGHOST: ${PGHOST}
    PGPORT: '5432'
    PGSSLMODE: ${PGSSLMODE}
    PGUSER: ${APP_USERNAME}
    PGPASSWORD: ${APP_PASSWORD}
    PGDATABASE: ${APP_DATABASE}
EOF

Warning

Although most client libraries follow the libpq definition of these environment variables, some do not, and this will require changes to the application Secret.

Notably node-postgres does not currently do so for PGSSLMODE. When this variable is set to require, it will do a full verification instead, requiring access to the PostgreSQL certificates to allow a connection. To get the intended mode for require set the variable to no-verify instead.

Expose PostgreSQL credentials to Your Application

To expose the PostgreSQL cluster credentials to your application, follow one of the following upstream documentation:

Set up the TimescaleDB extension on PostgreSQL

  • Connect to the created database:
\c $APP_DATABASE
  • Add the TimescaleDB extension:
CREATE EXTENSION IF NOT EXISTS timescaledb;

Follow the Go-Live Checklist

You should be all set. Before going into production, don't forget to go through the go-live checklist.

Welkin TimescaleDB Release Notes

Check out the release notes for the TimescaleDB/PostgreSQL cluster that runs in Welkin environments!

Further Reading