• TimescaleDB Community Edition based on CrunchyData PostgreSQL
  • Scope
  • Background
  • Test Case
  • Potential Remedy
  • Build
  • Deploy
• License

TimescaleDB Community Edition based on CrunchyData PostgreSQL

Date: 29 Nov 2023

Author: miah0x41

Scope

The timescaledb Community Edition packages built upon the CrunchyData PostgresSQL images intended to be used with the CrunchyData PostgreSQL Operator (PGO). This repository contains a static example of a Dockerfile that combines both.

Background

The default images used in the CrunchyData PostgreSQL Operator (PGO) are based on CrunchyData PostgreSQL, which is provisioned with the Apache Licensed timescaledb extension only. This lacks importance features such as compression based on a comparison of editions.

Test Case

A minimal use can be derived from the PGO Blog Post on using TimeScaleDB. First of all extend the cluster definition with and apply changes:

spec:
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          shared_preload_libraries: timescaledb

Connect to the cluster using a SUPERUSER and confirm the library was loaded:

-- Show libraries
SHOW shared_preload_libraries;

  shared_preload_libraries
-----------------------------
 pgaudit,pgaudit,timescaledb

-- Add TimescaleDB extension
CREATE EXTENSION timescaledb;

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

Note: Please enable telemetry to help us improve our product by running: ALTER DATABASE "flood" SET timescaledb.telemetry_level = 'basic';

CREATE EXTENSION

Create a hypertable and insert data:

CREATE TABLE hippos (
  observed_at timestamptz NOT NULL,
  total int NOT NULL
);

SELECT create_hypertable('hippos', 'observed_at');

INSERT INTO hippos
SELECT ts, (random() * 100)::int
FROM generate_series(CURRENT_TIMESTAMP - '1 year'::interval, CURRENT_TIMESTAMP, '1 minute'::interval) ts;

Enable compression and apply a policy:

-- Enable compression
ALTER TABLE hippos SET (
  timescaledb.compress
);

ERROR:  functionality not supported under the current "apache" license. Learn more at https://timescale.com/.
HINT:  To access all features and the best time-series experience, try out Timescale Cloud.

If the Community Edition is used, the compression can be enabled:

-- Enable compression
ALTER TABLE hippos SET (
  timescaledb.compress
);

ALTER TABLE

-- Apply compression policy
SELECT add_compression_policy('hippos', compress_after => INTERVAL '60d');

 add_compression_policy
------------------------
                   1000
(1 row)

Potential Remedy

The absence of the Community Edition was discussed in the PGO GitHub Issue #2692 and it was recognised that due to licensing concerns/restrictions the summary was that users should generate their own container images. The Dockerfile was based on a comment:

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.5-0

LABEL "org.opencontainers.image.authors"="miah0x41"

# Select user
USER root

# Add TimescaleDB repo and install community editions of components
RUN curl -sSL -o /etc/yum.repos.d/timescale_timescaledb.repo "https://packagecloud.io/install/repositories/timescale/timescaledb/config_file.repo?os=el&dist=8" && \
  microdnf update -y && \
  microdnf install -y timescaledb-2-loader-postgresql-15-2.12.2 && \
  microdnf install -y timescaledb-2-postgresql-15-2.12.2 && \
  microdnf install -y timescaledb-toolkit-postgresql-15-1.17.0 && \
  microdnf clean all

# Keep original user
USER 26

The user needs to determine compatible versions of the base image, the loader-postgresql, postgresql and toolkit-postgresql packages:

1. Use the base images from PGO i.e. ubi8-15.5-0.
2. Identify suitable packages from the Timescale Package Repository.
3. To determine a suitable toolkit package, the Changelog may need to be consulted.

Build

Any suitable Containerfile or Dockerfile build tool can be used. The example utilises buildah:

# Navigate to desired directory
cd /path/to/directory

# Clone repository
git clone https://github.com/miah0x41/crunchy-pg-tsdb-community.git

# Navigate to repository
cd crunchy-pg-tsdb-community

# Build image for Docker Hub
buildah bud -t docker.io/<username>/crunchy-pg-tsdb-community:15.5-2.12.2

# Login to Docker Hub
buildah login docker.io

# Push image to Docker Hub
buidah push docker.io/<username>/crunchy-pg-tsdb-community:15.5-2.12.2

Deploy

Update the cluster definition to use the new image from:

spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:ubi8-15.5-0
  postgresVersion: 15

Update to:

spec:
  image: docker.io/<username>/crunchy-pg-tsdb-community:15.5-2.12.2
  postgresVersion: 15

And the license type of the extension:

spec:
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          shared_preload_libraries: timescaledb
          timescaledb.license: timescale

Create a new cluster with the updated definition and repeat the test case, except the output of the compression commands should be as follows:

-- Enable compression
ALTER TABLE hippos SET (
  timescaledb.compress
);

ALTER TABLE

-- Apply compression policy
SELECT add_compression_policy('hippos', compress_after => INTERVAL '60d');

 add_compression_policy
------------------------
                   1000
(1 row)

License

The user should take care to understand the myriad of licenses involved. The contents of this repository is licensed under Apache 2.0. The artifacts generated are not!

The base image is from CrunchyData and subject to their licenses as per the Crunchy Data Container Suite and most notably the TimescaleDB Community Edition are subject to the Timescale License.