how to build crunchy image with extension?

[chaoyun]

Can anyone can let me know how to build the crunchy image with extension, like timescaledb?

[jmccormick2001]

you are going to need to create your own container image based off of our existing crunchy-postgres container essentially adding the RPM packages for timescaledb into that Dockerfile to be added to the container image, then you'll need to modify the setup.sql script to perform any SQL command specific to enable that extension. Hope that helps.

[chaoyun]

sorry for the delay response. Yes, I just did as what you said. It works. Thanks a lot.

[jwmullally]

In the case of TimescaleDB, here is the Dockerfile that worked for me. This follows the standard TimescaleDB extension installation instructions.

FROM crunchydata/crunchy-postgres:centos7-11.1-2.3.0
USER root
RUN curl -sSL -o /etc/yum.repos.d/timescale_timescaledb.repo "https://packagecloud.io/install/repositories/timescale/timescaledb/config_file.repo?os=el&dist=7" && \
    yum update -y && \
    yum install -y timescaledb-postgresql-11 && \
    yum clean all 
RUN sed -i "s/^\(shared_preload_libraries.*\)'/\1,timescaledb'/" /opt/cpm/conf/postgresql.conf.template* && \
    sed -i '/\\c PG_DATABASE$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/cpm/bin/setup.sql
USER 26

If you are using postgres-operator, change the crunchy-postgres image used in the database container deployment template to the new image built above.

[tabdulazim]

Just an updated answer that works with postgres12 HA version. There is also a ready public docker image docker pull funbits/crunchy-postgres-ha-timescaledb:centos7-12.3-4.4.0

I also ran timescaledb-tune then took all the config values and added them to the k8s ConfigMap of the cluster e.g. <cluster-name>-pgha-config

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres-ha:centos7-12.3-4.4.0

USER root

RUN curl -sSL -o /etc/yum.repos.d/timescale_timescaledb.repo "https://packagecloud.io/install/repositories/timescale/timescaledb/config_file.repo?os=el&dist=7" && \
    yum --disablerepo=crunchypg12 update -y && \
    yum --disablerepo=crunchypg12 install -y timescaledb-postgresql-12 && \
    yum clean all 

RUN sed -i '/shared_preload_libraries:/ s/$/,timescaledb/' /opt/cpm/conf/postgres-ha-initdb.yaml && \
    sed -i '/\\c "\${PGHA_DATABASE}"$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/cpm/bin/sql/setup.sql

USER 26

Update
To use with operator 4.6.0 version and timescaledb 2.0

docker pull funbits/crunchy-postgres-gis-ha-timescaledb-2:centos8-12.5-4.6.0

FROM registry.developers.crunchydata.com/crunchydata/crunchy-postgres-gis-ha:centos8-12.5-3.0-4.6.0
USER root

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" && \
    yum --disablerepo=crunchypg12 update -y && \
    yum --disablerepo=crunchypg12 install -y timescaledb-2-postgresql-12 && \
    yum clean all

RUN sed -i '/shared_preload_libraries:/ s/$/,timescaledb/' /opt/crunchy/conf/postgres-ha/postgres-ha-initdb.yaml && \
    sed -i '/\\c "\${PGHA_DATABASE}"$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/crunchy/bin/postgres-ha/sql/setup.sql && \
    sed -i '/SET application_name="container_setup";$/a CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;' /opt/crunchy/bin/postgres-ha/sql/setup.sql

USER 26