TimescaleDB

cpln helm install RELEASE_NAME oci://ghcr.io/controlplane-com/templates/timescaledb \
  --version 1.2.0 --gvc GVC_NAME \
  --set config.credentialsSecretName=SECRET_NAME

image: timescale/timescaledb:2.28.3-pg18 # PostgreSQL 18 + TimescaleDB Community edition

resources:
  minCpu: 200m
  minMemory: 512Mi
  maxCpu: 500m
  maxMemory: 1024Mi # timescaledb-tune sizes shared_buffers/workers from this at first boot

config:
  # REQUIRED PREREQUISITE SECRET — CREATE IT BEFORE YOU INSTALL.
  # A `dictionary` secret holding exactly three keys: `username`, `password`
  # and `database`. If it does not exist at install time the deployment
  # WEDGES silently — `cpln logs` returns nothing at all. See Prerequisites
  # in the README for the exact `cpln secret create-dictionary` command.
  credentialsSecretName: my-timescaledb-credentials

volumeset:
  capacity: 10 # initial capacity in GiB (minimum is 10)
  autoscaling:
    enabled: false # Set to true to enable autoscaling
    maxCapacity: 100 # Maximum capacity in GiB when autoscaling is enabled
    minFreePercentage: 10 # Minimum free percentage to trigger scaling when autoscaling is enabled
    scalingFactor: 1.2 # Scaling factor to determine how much to scale up when autoscaling is triggered

internalAccess: # Sets the internal firewall scope - if set to none, replicas will not be able to reach each other
  type: same-gvc # options: none, same-gvc, same-org, workload-list
  workloads:  # Note: can only be used if type is same-gvc or workload-list
    #- //gvc/GVC_NAME/workload/WORKLOAD_NAME

publicAccess:
  enabled: false # exposes 5432 via a TCP load balancer; connections are unencrypted — prefer internal access

pgbouncer:
  enabled: false
  image: edoburu/pgbouncer:v1.25.1-p0
  poolMode: transaction # options: session, transaction, statement
  defaultPoolSize: 25   # number of real Postgres connections PgBouncer maintains
  maxClientConn: 1000   # maximum number of client connections PgBouncer accepts
  replicas: 1

  resources:
    cpu: 200m
    memory: 128Mi

backup:
  enabled: false
  image: ghcr.io/controlplane-com/backup-images/postgres-backup:18.1.0 # PG18 client, matches server major
  schedule: "0 2 * * *"   # daily at 2am UTC

  resources:
    cpu: 100m
    memory: 128Mi

  provider: aws # Options: aws, gcp, or minio

  aws:
    bucket: my-backup-bucket
    region: us-east-1
    cloudAccountName: my-backup-cloudaccount
    policyName: my-backup-policy
    prefix: timescaledb/backups # folder name where your backups will be stored

  gcp:
    bucket: my-backup-bucket
    cloudAccountName: my-backup-cloudaccount
    prefix: timescaledb/backups # folder name where your backups will be stored

  minio: # Backup to a self-hosted MinIO workload (or any S3-compatible endpoint)
    endpoint: http://my-minio-workload:9000 # e.g. http://WORKLOAD_NAME:9000 for an internal MinIO template deployment
    bucket: my-backup-bucket
    # REQUIRED PREREQUISITE SECRET when provider is `minio` — a `dictionary`
    # secret holding exactly two keys: `accessKey` and `secretKey`. For the
    # minio template these are its admin.username and admin.password.
    credentialsSecretName: my-timescaledb-minio-credentials
    prefix: timescaledb/backups # folder name where your backups will be stored

cpln port-forward RELEASE_NAME-timescaledb 5432:5432 --gvc GVC_NAME

psql "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" -c "select extversion from pg_extension where extname = 'timescaledb'"

cpln workload exec RELEASE_NAME-timescaledb --gvc GVC_NAME --container timescaledb -- psql -U USERNAME -d DATABASE -c 'select version()'

CREATE TABLE metrics (time timestamptz NOT NULL, device text, value double precision);
SELECT create_hypertable('metrics', by_range('time'));

INSERT INTO metrics VALUES (now(), 'sensor-1', 23.5);

SELECT time_bucket('1 hour', time) AS bucket, device, avg(value)
FROM metrics
GROUP BY bucket, device
ORDER BY bucket;

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:DeleteObject",
                "s3:ListBucket",
                "s3:GetObjectVersion",
                "s3:DeleteObjectVersion",
                "s3:GetBucketLocation",
                "s3:AbortMultipartUpload",
                "s3:ListBucketMultipartUploads",
                "s3:ListMultipartUploadParts"
            ],
            "Resource": [
                "arn:aws:s3:::YOUR_BUCKET_NAME",
                "arn:aws:s3:::YOUR_BUCKET_NAME/*"
            ]
        }
    ]
}

cpln secret create-dictionary --name SECRET_NAME \
  --entry accessKey=MINIO_ACCESS_KEY \
  --entry secretKey=MINIO_SECRET_KEY

pg_dump -Fc "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" -f DATABASE.bak

psql "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" -c "SELECT timescaledb_pre_restore();"
pg_restore -Fc -d "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" DATABASE.bak
psql "postgresql://USERNAME:PASSWORD@127.0.0.1:5432/DATABASE" -c "SELECT timescaledb_post_restore();"

ALTER ROLE USERNAME WITH PASSWORD 'NEW-STRONG-PASSWORD';

cpln workload force-redeployment RELEASE_NAME-pgbouncer --gvc GVC_NAME