Examples¶
In this section, you can find some examples of configuration files to set up your PostgreSQL Cluster.
Important
These are here for demonstration and experimentation purposes, and can be executed on a personal Kubernetes cluster with Minikube or Kind as described in the Quickstart .
Basic cluster : cluster-example.yaml a basic example of a cluster.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
storage:
size: 1Gi
Custom cluster : cluster-example-custom.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-custom
spec:
instances: 3
# Parameters and pg_hba configuration will be append
# to the default ones to make the cluster work
postgresql:
parameters:
max_worker_processes: "60"
pg_hba:
# To access through TCP/IP you will need to get username
# and password from the secret cluster-example-custom-app
- host all all all md5
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Require 1Gi of space per instance using default storage class
storage:
size: 1Gi
a basic cluster that uses the default storage class and custom
parameters for the postgresql.conf and pg_hba.conf files.
Customized storage class : cluster-storage-class.yaml :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgresql-storage-class
spec:
instances: 3
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: standard
size: 1Gi
a basic cluster that uses a specified storage class of standard .
Customized storage class and backups : Prerequisites : bucket storage should be available. The sample config is for AWS, please change to suit your setup : cluster-storage-class-with-backup.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pg-backup
spec:
instances: 3
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: standard
size: 1Gi
# Backup properties
backup:
barmanObjectStore:
destinationPath: s3://BUCKET_NAME/path/to/folder
s3Credentials:
accessKeyId:
name: aws-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-creds
key: ACCESS_SECRET_KEY
wal:
compression: gzip
a cluster with backups configured
Backup : Prerequisites : cluster-storage-class-with-backup.yaml
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pg-backup
spec:
instances: 3
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: standard
size: 1Gi
# Backup properties
backup:
barmanObjectStore:
destinationPath: s3://BUCKET_NAME/path/to/folder
s3Credentials:
accessKeyId:
name: aws-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-creds
key: ACCESS_SECRET_KEY
wal:
compression: gzip
applied and Healthy : backup-example.yaml : an example of a backup that runs against the previous sample
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
name: pg-backup-example
spec:
cluster:
name: pg-backup
Cluster with PVC (Persistent Volume Claim) configured : cluster-pvc-template.yaml : a basic cluster that with an explicit persistent volume claim template.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgresql-pvc-template
spec:
instances: 3
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
size: 1Gi
pvcTemplate:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1Gi
storageClassName: standard
volumeMode: Filesystem
Full example : cluster-example-full.yaml : a cluster that sets most of the available options.
# Example of definition of a test cluster using all the elements available
# in the CRD. Please change values appropriately for your environment.
# Remember that you can take advantage of convention over configuration
# and normally you dont need to use all these definitions.
apiVersion: v1
data:
password: VHhWZVE0bk44MlNTaVlIb3N3cU9VUlp2UURhTDRLcE5FbHNDRUVlOWJ3RHhNZDczS2NrSWVYelM1Y1U2TGlDMg==
username: YXBw
kind: Secret
metadata:
name: cluster-example-app-user
type: kubernetes.io/basic-auth
- -
apiVersion: v1
data:
password: dU4zaTFIaDBiWWJDYzRUeVZBYWNCaG1TemdxdHpxeG1PVmpBbjBRSUNoc0pyU211OVBZMmZ3MnE4RUtLTHBaOQ==
username: cG9zdGdyZXM=
kind: Secret
metadata:
name: cluster-example-superuser
type: kubernetes.io/basic-auth
- -
apiVersion: v1
kind: Secret
metadata:
name: backup-creds
data:
ACCESS_KEY_ID: a2V5X2lk
ACCESS_SECRET_KEY: c2VjcmV0X2tleQ==
- -
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-full
spec:
description: "Example of cluster"
imageName: ghcr.io/cloudnative-pg/postgresql:14.4
# imagePullSecret is only required if the images are located in a private registry
# imagePullSecrets:
# - name: private_registry_access
instances: 3
startDelay: 300
stopDelay: 300
primaryUpdateStrategy: unsupervised
postgresql:
parameters:
shared_buffers: 256MB
pg_stat_statements.max: 10000
pg_stat_statements.track: all
auto_explain.log_min_duration: 10s
pg_hba:
- host all all 10.244.0.0/16 md5
bootstrap:
initdb:
database: app
owner: app
secret:
name: cluster-example-app-user
# Alternative bootstrap method: start from a backup
#recovery:
# backup:
# name: backup-example
superuserSecret:
name: cluster-example-superuser
storage:
storageClass: standard
size: 1Gi
backup:
barmanObjectStore:
destinationPath: s3://cluster-example-full-backup/
endpointURL: http://custom-endpoint:1234
s3Credentials:
accessKeyId:
name: backup-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: backup-creds
key: ACCESS_SECRET_KEY
wal:
compression: gzip
encryption: AES256
data:
compression: gzip
encryption: AES256
immediateCheckpoint: false
jobs: 2
retentionPolicy: "30d"
resources:
requests:
memory: "512Mi"
cpu: "1"
limits:
memory: "1Gi"
cpu: "2"
affinity:
enablePodAntiAffinity: true
topologyKey: failure-domain.beta.kubernetes.io/zone
nodeMaintenanceWindow:
inProgress: false
reusePVC: false
PostGIS example : postgis-example.yaml : an example of “PostGIS cluster” (see the PostGIS section for details.)
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: postgis-example
spec:
instances: 3
imageName: ghcr.io/cloudnative-pg/postgis:14
bootstrap:
initdb:
postInitTemplateSQL:
- CREATE EXTENSION postgis;
- CREATE EXTENSION postgis_topology;
- CREATE EXTENSION fuzzystrmatch;
- CREATE EXTENSION postgis_tiger_geocoder;
storage:
size: 1Gi
Replica cluster via streaming : Prerequisites : cluster-example.yaml applied and Healthy
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
storage:
size: 1Gi
: cluster-example-replica-streaming.yaml : a replica cluster following cluster-example with
streaming replication.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-replica-example
spec:
instances: 1
bootstrap:
pg_basebackup:
source: cluster-example
replica:
enabled: false
source: cluster-example
storage:
size: 1Gi
# note the namespace default in the host name `cluster-example-rw.default.svc`
# remember to change accordingly with the namespace of the main cluster
externalClusters:
- name: cluster-example
connectionParameters:
host: cluster-example-rw.default.svc
user: streaming_replica
sslmode: verify-full
dbname: postgres
# NOTE: if this cluster is created in a different namespace than the main cluster
# remember to create the `-replication` and `-ca` secrets in the follower namespace
# before creating the follower cluster
sslKey:
name: cluster-example-replication
key: tls.key
sslCert:
name: cluster-example-replication
key: tls.crt
sslRootCert:
name: cluster-example-ca
key: ca.crt
Simple cluster with backup configured : Prerequisites : The
configuration assumes minio is running and working. Please update
backup.barmanObjectStore with your minio parameters or your
cloud solution : cluster-example-with-backup.yaml a basic cluster with backups configured.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-backup
spec:
instances: 3
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: standard
size: 1Gi
# Backup properties
# This assumes a local minio setup
backup:
barmanObjectStore:
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
Replica cluster via backup : Prerequisites : cluster-storage-class-with-backup.yaml applied and Healthy.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pg-backup
spec:
instances: 3
# Example of rolling update strategy:
# - unsupervised: automated update of the primary once all
# replicas have been upgraded (default)
# - supervised: requires manual supervision to perform
# the switchover of the primary
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: standard
size: 1Gi
# Backup properties
backup:
barmanObjectStore:
destinationPath: s3://BUCKET_NAME/path/to/folder
s3Credentials:
accessKeyId:
name: aws-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-creds
key: ACCESS_SECRET_KEY
wal:
compression: gzip
And a backup cluster-example-trigger-backup.yaml applied and Completed.
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
name: cluster-example-trigger-backup
spec:
cluster:
name: cluster-example-with-backup
: cluster-example-replica-from-backup-simple.yaml : a replica cluster following a cluster with backup configured.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-replica-from-backup-simple
spec:
instances: 1
bootstrap:
recovery:
source: cluster-example-backup
replica:
enabled: true
source: cluster-example-backup
storage:
size: 1Gi
externalClusters:
- name: cluster-example-backup
barmanObjectStore:
destinationPath: s3://backups/
endpointURL: http://minio:9000
s3Credentials:
accessKeyId:
name: minio
key: ACCESS_KEY_ID
secretAccessKey:
name: minio
key: ACCESS_SECRET_KEY
Bootstrap cluster with SQL files : cluster-example-initdb-sql-refs.yaml : a cluster example that will execute a set of queries defined in a Secret and a ConfigMap right after the database is created.
apiVersion: v1
kind: ConfigMap
metadata:
name: post-init-sql-configmap
data:
configmap.sql: |
create table configmaps (i integer);
insert into configmaps (select generate_series(1,10000));
- --
apiVersion: v1
kind: Secret
metadata:
name: post-init-sql-secret
stringData:
secret.sql: |
create table secrets (i integer);
insert into secrets (select generate_series(1,10000));
- --
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-initdb
spec:
instances: 3
bootstrap:
initdb:
database: appdb
owner: appuser
postInitSQL:
- create table numbers (i integer)
- insert into numbers (select generate_series(1,10000))
postInitTemplateSQL:
- create extension intarray
postInitApplicationSQL:
- create table application_numbers (i integer)
- insert into application_numbers (select generate_series(1,10000))
postInitApplicationSQLRefs:
configMapRefs:
- name: post-init-sql-configmap
key: configmap.sql
secretRefs:
- name: post-init-sql-secret
key: secret.sql
storage:
size: 1Gi
For a list of available options, please refer to the API Reference .
