Below you find steps for setting up a CNPG Cluster with multiple databases within Kubernetes.
Ensure to have proper resources within a fully functioning Kubernetes cluster available before starting!
kubectl apply --server-side -f \
https://raw.githubusercontent.com/cloudnative-pg/cloudnative-pg/release-1.29/releases/cnpg-1.29.1.yaml
Better yet, in your Kubernetes control repo, download the Release file as per above
and apply that. This way you have a stable local version, which does not change over night. This article will fortgoing be using a local control repo as reference.
Check the deployment:
kubectl rollout status deployment \
-n cnpg-system cnpg-controller-manager
---
apiVersion: postgresql.cnpg.io/v1
kind: ClusterImageCatalog
metadata:
name: postgresql-global
spec:
images:
- major: 15
image: ghcr.io/cloudnative-pg/postgresql:15.14-system-trixie
- major: 16
image: ghcr.io/cloudnative-pg/postgresql:16.10-system-trixie
- major: 17
image: ghcr.io/cloudnative-pg/postgresql:17.6-system-trixie
- major: 18
image: ghcr.io/cloudnative-pg/postgresql:18.3-system-trixie
kubectl apply /yaml/cnpg/clusterimagecatalog.yaml
This is it already for that part.
For the bootstrap, you will want to do some considerations first:
one or more databases?
what kind of storage backend?
superuser or not?
pgbouncer or not? (pooling)
how to import database content?
...
In our example, we will bootstrap the cluster with the following options:
apiVersion: v1
kind: Namespace
metadata:
name: postgres
---
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
namespace: postgres
name: my-cnpg-cluster
spec:
instances: 3
enableSuperuserAccess: true # this creates a superuser secret
pg_hba:
- hostssl app app 10.244.0.0/16 md5 # allow all secure connections from the POD CIDR
bootstrap:
initdb:
database: db1
owner: db1
dataChecksums: true
walSegmentSize: 32
localeCollate: 'en_US.utf8'
localeCType: 'en_US.utf8'
primaryUpdateStrategy: unsupervised
imageCatalogRef:
apiGroup: postgresql.cnpg.io
kind: ClusterImageCatalog
name: postgresql-global
major: 18
storage:
storage:
pvcTemplate:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
storageClassName: hyper-converged # replace this with your own storageclass
volumeMode: Filesystem
walStorage:
storage:
pvcTemplate:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
storageClassName: hyper-converged # replace this with your own storageclass
volumeMode: Filesystem
---
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
name: pooler-my-cnpg-cluster-rw
namespace: postgres
spec:
cluster:
name: my-cnpg-cluster
instances: 3
type: rw
pgbouncer:
poolMode: session
parameters:
max_client_conn: "1000"
default_pool_size: "10"
The operator will do the required things and within a few minutes your cluster should be online, however the database is empty.
CNPG is designed to import data from an existing cluster. HOwever that is not always practicable or possible. Therefore this option focusses on importing an SQL dump created with pg_dump.
It is highly recommendable to create a local yaml file to prep the import data, but to execute it manually once (ie. via kubectl) to avoid double data entries.
**Adding the secrets for the roles and the roles + databases below is best added to the actual bootstrap yaml, as that can always be reapplied without destroying the cluster. **
Within the cluster configuration yaml from the bootstrap, create a secret for the db role
Within the cluster configuration yaml from the bootstrap, add the role and db to the cluster
Create a config map with the SQL data.
---
apiVersion: v1
kind: ConfigMap
metadata:
name: sql-dump
namespace: postgres
data:
dump.sql: |
--
-- PostgreSQL database dump
--
---
apiVersion: batch/v1
kind: Job
metadata:
name: import-database
namespace: postgres
spec:
template:
spec:
containers:
- name: import
image: bitnami/postgresql
command:
- /bin/bash
- -c
- |
psql -h psql -h $PGHOST -U $PGUSER -d $PGDATABASE -f /sql/dump.sql
env:
- name: PGHOST
value: "my-cluster-rw.postgres.svc.cluster.local"
- name: PGUSER
valueFrom:
secretKeyRef:
name: my-cluster-app
key: username
- name: PGPASSWORD
valueFrom:
secretKeyRef:
name: my-cluster-app
key: password
- name: PGDATABASE
valueFrom:
secretKeyRef:
name: my-cluster-app
key: dbname
volumeMounts:
- name: sql-volume
mountPath: /sql
volumes:
- name: sql-volume
configMap:
name: sql-dump
restartPolicy: Never
They can be both in the same yaml. The job will run, create the pod, import the data and complete. This way is not documented in the official documention!
Create a service for the rw-instance of the cluster, either Loadbalancer or Nodeport, depending on your setup. I am using Nodeport with external haproxy LB
use a regular psql client and connect to your databases as usual.
There are generally two supported options:
This is the most easy way and recommended in non-prod environments. The CNPG Operator creates everything required automatically and your client- or replica connections are encrypted by default.
The Barman plugin enables backups and WAL archiving. This is rather important to avoid you WAL running big very soon. You will need an S3-compatible storage solution.
Ensure Cert-manager is installed and running. Then install it via kubectl:[6]
kubectl apply -f \
https://github.com/cloudnative-pg/plugin-barman-cloud/releases/download/v0.12.0/manifest.yaml
You need to configure an object store. Create a local definition, adopt the values and apply it via kubectl.
apiVersion: barmancloud.cnpg.io/v1
kind: ObjectStore
metadata:
name: minio-store
spec:
configuration:
destinationPath: s3://backups/
endpointURL: http://minio:9000
s3Credentials:
accessKeyId:
name: minio
key: ACCESS_KEY_ID
secretAccessKey:
name: minio
key: ACCESS_SECRET_KEY
wal:
compression: gzip
kubectl cnpg is a very helpful plugin for kubectl for day-to-day management of your CNPG cluster. The instalation procedure is documented here [7]
Gives you a full detailed status about your cluster
kubectl cnpg status <your-cluster> -n <your-namespace>
https://cloudnative-pg.io/docs/1.29/installation_upgrade - Operator installation ↩︎
https://cloudnative-pg.io/docs/1.29/image_catalog - Image catalog ↩︎
https://cloudnative-pg.io/docs/1.29/storage - Storage options including Longhorn ↩︎
https://longhorn.io/docs/archives/1.7.0/high-availability/data-locality/ - Longhorn data-locality ↩︎
https://cloudnative-pg.io/docs/1.29/connection_pooling - Connection pooling ↩︎
https://cloudnative-pg.io/plugin-barman-cloud/docs/installation/ Barman installation ↩︎
https://cloudnative-pg.io/documentation/1.20/kubectl-plugin/ kubectl cnpg plugin installation ↩︎