https://blog.crunchydata.com/blog/using-timescaledb-extension-with-the-pgo-the-postgres-operator

Crunchy Data Home

  * Home
  * Cloud

    Crunchy Bridge

    Overview Documentation Pricing Get Started

    Cloud Partners

    AWS Azure GCP
    Create your account Login Contact us
  * Products

    Featured Products

    Crunchy Bridge

    Fully managed cloud Postgres available on AWS, Azure, & GCP.

    Crunchy PostgreSQL for Kubernetes

    The Postgres Operator easily lets you automate your Postgres in
    Kubernetes, whether it's one or thousands of instances.

    Crunchy High Availability PostgreSQL

    Integrated high availability PostgreSQL solution for enterprises
    with always on requirements.

    Crunchy Hardened PostgreSQL

    Hardened, secure, access controlled PostgreSQL to meet advanced
    security requirements.

    All Crunchy Products

    Crunchy Bridge Crunchy PostgreSQL for Kubernetes Crunchy High
    Availability PostgreSQL Crunchy Hardened PostgreSQL Crunchy
    Certified PostgreSQL Crunchy PostgreSQL for Cloud Foundry Crunchy
    MLS PostgreSQL Crunchy Spatial
  * Solutions

    Industries

    Government Healthcare Finance SaaS

    Use Cases

    Internal Database-as-a-Service Embedded PostgreSQL Edge Computing

    Integrations

    Ansible Tower JFrog Kong OpenShift
  * Learn
    Crunchy Data Developer Portal Software Documentation Value of a
    Subscription
  * Customers

  * Blog
  * Contact Us
  * Download
  * Login
    Access Portal Crunchy Bridge

[Blog_3]

Using TimescaleDB with PGO, the open source Postgres Operator

November 23, 2021 Jonathan S. Katz
PostgreSQL Operator

One of the many reasons "the answer is Postgres" is due to its
extensibility.

The ability to extend Postgres has given rise to an ecosystem of
Postgres extensions that change the behavior of the database to
support a wide range of interesting capabilities. At Crunchy Data we
are big fans of PostGIS, the geospatial extender for Postgres.

Another extension we are asked about often is TimescaleDB.

TimescaleDB is an open-source extension designed to make SQL scalable
for time-series data. Timescale, Inc., the company behind
TimescaleDB, provides an Apache 2 edition of TimescaleDB that is
packaged as a Postgres extension that provides automated partitioning
across time and space.

We are often asked about the potential to deploy the Apache 2 edition
of TimescaleDB as an extension within our Crunchy PostgreSQL for
Kubernetes using PGO, the open source Postgres Operator. We announced
that we added the Apache 2 edition of TimescaleDB to PGO 4.7, and we
have brought TimescaleDB into PGO v5.

Let us look at how you can deploy the TimescaleDB extension as part
of an HA Postgres cluster native to Kubernetes using the PGO Postgres
Operator.

Deploying TimescaleDB on Kubernetes with PGO

For brevity, we will assume that you have already deployed PGO, the 
Postgres Operator from Crunchy Data in your Kubernetes environment.
Read more on how you can quickly get started with PGO:

https://access.crunchydata.com/documentation/postgres-operator/v5/
quickstart/

Since TimescaleDB is packaged in the PostgreSQL container, you can
customize your Postgres configuration to get TimescaleDB up and
running. We can do this before starting up the Postgres container by
creating a manifest that looks similar to this:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: hippo
spec:
  image: registry.developers.crunchydata.com/crunchydata/crunchy-postgres:centos8-13.5-0
  postgresVersion: 13
  instances:
    - name: instance1
      dataVolumeClaimSpec:
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.36-0
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 1Gi
  patroni:
    dynamicConfiguration:
      postgresql:
        parameters:
          shared_preload_libraries: timescaledb

The above loads the TimescaleDB shared library along with the other
standard shared libraries loaded with PGO. You can apply the manifest
with kubectl apply.

Once the cluster is initialized, connect to the Postgres cluster as a
superuser. You may want to create your own account that can be a
superuser, or configure access to the postgres user.

For the purposes of this exercise, we can connect to the database
using kubectl exec:

kubectl exec -it -n postgres-operator -c database \
  $(kubectl get pods -n postgres-operator --selector='postgres-operator.crunchydata.com/cluster=hippo,postgres-operator.crunchydata.com/role=master' -o name) \
  -- psql

First, confirm that the TimescaleDB library was loaded:

SHOW shared_preload_libraries ;

should yield something similar to:

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

This setup has the added benefit that the TimescaleDB library will be
loaded on any replicas added to this Postgres cluster.

Now, add the TimescaleDB extension to the database:

CREATE EXTENSION timescaledb;

To test that TimescaleDB is successfully installed, go ahead and
create a "hypertable" and start inserting 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;

This is just a small data set so you can observe that the TimescaleDB
set up works. Try using a smaller increment (e.g. '5
seconds'::interval) to generate a larger data set!

Next Steps

At Crunchy Data, we want to provide users with the benefits of
trusted open source Postgres, including the powerful ecosystem of
Postgres extensions. TimescaleDB provides interesting functionality
for managing time series data in Postgres. By enabling the easy
deployment of TimeScaleDB through PGO, you can easily deploy
TimescaleDB to manage your time series data in Postgres natively in
Kubernetes.

(Interested in seeing PGO in action? Join us for a free webinar on
Wednesday, Dec 15th. There is also a paid, in-depth training
available on November 29th.)

  * Tweet
  * 
  * 

Multifactor SSO Authentication for Postgres on Kubernetes [blog1028]

Like what you're reading? Stay informed by subscribing for our
newsletter!

[                    ] 

Newsletter

Like what you're reading? Stay informed by subscribing for our
newsletter!

 

Read More

 

Crunchy News

Crunchy Data Solutions logo

  *  
  *  
  *  
  *  

  * Privacy Policy
  * (c) 2018-2021 Crunchy Data Solutions, Inc.

Products

  * Crunchy Bridge
  * Crunchy PostgreSQL for Kubernetes
  * Crunchy High Availability PostgreSQL
  * Crunchy Certified PostgreSQL
  * Crunchy PostgreSQL for Cloud Foundry
  * Crunchy MLS PostgreSQL
  * Crunchy Spatial

Services & Support

  * Enterprise PostgreSQL Support
  * Red Hat Partner
  * Trusted PostgreSQL
  * Crunchy Data Subscription

Resources

  * Customer Portal
  * Software Documentation
  * Blog
  * Events
  * Videos
  * DISA STIG for PostgreSQL
  * CIS Benchmark for PostgreSQL

Company

  * About Crunchy Data
  * News
  * Careers
  * Contact Us
  * Newsletter