Examples
The examples show configuration files for setting up your PostgreSQL cluster.
Important
These examples are for demonstration and experimentation purposes. You can execute them on a personal Kubernetes cluster with Minikube or Kind, as described in Quick start .
See also
For a list of available options, see API reference .
Basics
Basic cluster :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
storage:
size: 1Gi
A basic example of a cluster.
Custom cluster :
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.
Cluster with customized storage class :
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 .
Cluster with persistent volume claim (PVC) template configured :
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
: A basic cluster with an explicit persistent volume claim template.
Extended configuration example :
# 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:16.1
# 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
enableSuperuserAccess: true
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
: A cluster that sets most of the available options.
Bootstrap cluster with SQL files :
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
: A cluster example that executes a set of queries defined in a secret
and a ConfigMap right after the database is created.
Sample cluster with customized ``pg_hba`` configuration :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
postgresql:
pg_hba:
- hostssl app all all cert
storage:
size: 1Gi
: A basic cluster that enables the user app to authenticate using certificates.
Sample cluster with Secret and ConfigMap mounted using projected volume template :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-projected-volume
spec:
instances: 3
projectedVolumeTemplate:
sources:
- secret:
name: sample-secret
items:
- key: tls.crt
path: certificate/tls.crt
- key: tls.key
path: certificate/tls.key
- configMap:
name: sample-configmap
items:
- key: key1
path: config/key1
- key: key2
path: config/key2
storage:
size: 1Gi
- --
apiVersion: v1
data:
tls.crt: LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSUNQekNDQWVXZ0F3SUJBZ0lRSHVDY2lKcDVkZis0dHZBcDBrTk9rekFLQmdncWhrak9QUVFEQWpCTk1TTXcKSVFZRFZRUUxFeHB3YjNOMFozSmxjM0ZzTFc5d1pYSmhkRzl5TFhONWMzUmxiVEVtTUNRR0ExVUVBeE1kY0c5egpkR2R5WlhOeGJDMXZjR1Z5WVhSdmNpMWpZUzF6WldOeVpYUXdIaGNOTWpJd09USTVNRGMxTVRBNVdoY05Nakl4Ck1qSTRNRGMxTVRBNVdqQk5NVXN3U1FZRFZRUURFMEp3YjNOMFozSmxjM0ZzTFc5d1pYSmhkRzl5TFhkbFltaHYKYjJzdGMyVnlkbWxqWlM1d2IzTjBaM0psYzNGc0xXOXdaWEpoZEc5eUxYTjVjM1JsYlM1emRtTXdXVEFUQmdjcQpoa2pPUFFJQkJnZ3Foa2pPUFFNQkJ3TkNBQVJRd2lhUm0wSmN4TzlBVlE0MVlqVlRqNUhDSVVFblFQOXNZRXFKCmYvNE1mVm53NkdDOThjNDNmQTVuS2UwSnQ5ZEV3QXREYktkdkRoeDlUTmIzdVY0K280R21NSUdqTUE0R0ExVWQKRHdFQi93UUVBd0lEcURBVEJnTlZIU1VFRERBS0JnZ3JCZ0VGQlFjREFUQU1CZ05WSFJNQkFmOEVBakFBTUI4RwpBMVVkSXdRWU1CYUFGQjN6cUVKbWFORENoRDdkWGptSWRlNEhUY0pFTUUwR0ExVWRFUVJHTUVTQ1FuQnZjM1JuCmNtVnpjV3d0YjNCbGNtRjBiM0l0ZDJWaWFHOXZheTF6WlhKMmFXTmxMbkJ2YzNSbmNtVnpjV3d0YjNCbGNtRjAKYjNJdGMzbHpkR1Z0TG5OMll6QUtCZ2dxaGtqT1BRUURBZ05JQURCRkFpQkMwS2M5WWxYelBpL0lhTkRnWHkwawpYTDJpNlZzSHRORTFxN3MzWXh6Mm53SWhBS2IxUW5EVTlqRnNVK0l5a292TitVU1ZpVm5vU2MvZ2RXWkxmMnhoCjZ2WUsKLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo=
tls.key: LS0tLS1CRUdJTiBFQyBQUklWQVRFIEtFWS0tLS0tCk1IY0NBUUVFSUtucWxVQjFqU2sxWW5VZzAyb0tGbXlRdDJLUEZwaFc0K1lmQUFmUFdRenVvQW9HQ0NxR1NNNDkKQXdFSG9VUURRZ0FFVU1JbWtadENYTVR2UUZVT05XSTFVNCtSd2lGQkowRC9iR0JLaVgvK0RIMVo4T2hndmZITwpOM3dPWnludENiZlhSTUFMUTJ5bmJ3NGNmVXpXOTdsZVBnPT0KLS0tLS1FTkQgRUMgUFJJVkFURSBLRVktLS0tLQo=
kind: Secret
metadata:
name: sample-secret
type: kubernetes.io/tls
- --
apiVersion: v1
data:
key1: value1
key2: value2
key3: value3
kind: ConfigMap
metadata:
name: sample-configmap
A basic cluster with the existing Secret and ConfigMap mounted
into Postgres pod using projected volume mount.
Backups
Customized storage class and backups : Prerequisites: Bucket storage must be available. The sample config is for AWS. Change it to suit your setup. :
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:
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. :
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
name: pg-backup-example
spec:
cluster:
name: pg-backup
: An example of a backup that runs against the previous sample.
Simple cluster with backup configured : Prerequisites: The
configuration assumes minio is running and working. Update
backup.barmanObjectStore with your minio parameters or your cloud
solution. :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-backup
spec:
instances: 3
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: csi-hostpath-sc
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
A basic cluster with backups configured.
Replica clusters
Replica cluster by way of backup from an object store : Prerequisites:
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, and a backup
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
name: cluster-example-trigger-backup
spec:
cluster:
name: cluster-example-with-backup
applied and completed. :
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
: A replica cluster following a cluster with backup configured.
Replica cluster by way of volume snapshot : Prerequisites:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-volume-snapshot
spec:
instances: 3
primaryUpdateStrategy: unsupervised
# Persistent storage configuration
storage:
storageClass: csi-hostpath-sc
size: 1Gi
walStorage:
storageClass: csi-hostpath-sc
size: 1Gi
# Backup properties
backup:
volumeSnapshot:
className: csi-hostpath-snapclass
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
applied and healthy, and a volume snapshot
apiVersion: postgresql.cnpg.io/v1
kind: Backup
metadata:
name: backup-with-volume-snapshot
spec:
method: volumeSnapshot
cluster:
name: cluster-example-with-volume-snapshot
applied and completed. :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-replica-from-snapshot
spec:
instances: 1
storage:
storageClass: csi-hostpath-sc
size: 1Gi
walStorage:
storageClass: csi-hostpath-sc
size: 1Gi
bootstrap:
recovery:
source: cluster-example-with-volume-snapshot
volumeSnapshots:
storage:
name: cluster-example-with-volume-snapshot-2-1692618163
kind: VolumeSnapshot
apiGroup: snapshot.storage.k8s.io
walStorage:
name: cluster-example-with-volume-snapshot-2-wal-1692618163
kind: VolumeSnapshot
apiGroup: snapshot.storage.k8s.io
replica:
enabled: true
source: cluster-example-with-volume-snapshot
externalClusters:
- name: cluster-example-with-volume-snapshot
connectionParameters:
host: cluster-example-with-volume-snapshot-rw.default.svc
user: postgres
dbname: postgres
password:
name: cluster-example-with-volume-snapshot-superuser
key: password
barmanObjectStore:
destinationPath: s3://backups/
endpointURL: http://minio:9000
s3Credentials:
accessKeyId:
name: minio
key: ACCESS_KEY_ID
secretAccessKey:
name: minio
key: ACCESS_SECRET_KEY
wal:
maxParallel: 8
: A replica cluster following a cluster with volume snapshot configured.
Replica cluster by way of streaming (pg_basebackup) : Prerequisites:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
storage:
size: 1Gi
applied and healthy. :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-replica-example
spec:
instances: 1
bootstrap:
pg_basebackup:
source: cluster-example
replica:
enabled: true
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
: A replica cluster following cluster-example with streaming
replication.
PostGIS
PostGIS example :
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
: An example of a PostGIS cluster. See PostGIS clusters for details.
Managed roles
Cluster with declarative role management :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-roles
spec:
instances: 3
storage:
size: 1Gi
managed:
roles:
- name: app
createdb: true
login: true
- name: dante
ensure: present
comment: my database-side comment
login: true
superuser: false
createdb: true
createrole: false
inherit: false
replication: false
bypassrls: false
connectionLimit: 4
validUntil: "2053-04-12T15:04:05Z"
inRoles:
- pg_monitor
- pg_signal_backend
passwordSecret:
name: cluster-example-dante
- --
apiVersion: v1
data:
username: ZGFudGU=
password: ZGFudGU=
kind: Secret
metadata:
name: cluster-example-dante
type: kubernetes.io/basic-auth
: Declares a role with the managed stanza. Includes password
management with Kubernetes secrets.
Declarative tablespaces
Cluster with declarative tablespaces :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-tablespaces
spec:
instances: 3
storage:
size: 1Gi
tablespaces:
- name: atablespace
storage:
size: 1Gi
storageClass: standard
temporary: true
- name: another_tablespace
storage:
size: 2Gi
storageClass: standard
temporary: true
- name: tablespacea1
storage:
size: 2Gi
storageClass: standard
Cluster with declarative tablespaces and backup : Prerequisites:
The configuration assumes minio is running and working. Update
backup.barmanObjectStore with your minio parameters or your cloud
solution. :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-with-tablespaces
spec:
instances: 3
storage:
size: 1Gi
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
tablespaces:
- name: atablespace
storage:
size: 1Gi
storageClass: standard
- name: another_tablespace
storage:
size: 2Gi
storageClass: standard
- name: tablespacea1
storage:
size: 2Gi
storageClass: standard
Restored cluster with tablespaces from object store :
Prerequisites: The previous cluster applied and a base backup
completed. Remember to update bootstrap.recovery.backup.name with
the backup name. :
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-restore-with-tablespaces
spec:
instances: 3
storage:
size: 1Gi
bootstrap:
recovery:
backup:
name: cluster-example-with-tablespaces-20231128093940
tablespaces:
atablespace:
storage:
size: 1Gi
storageClass: standard
another_tablespace:
storage:
size: 2Gi
storageClass: standard
tablespacea1:
storage:
size: 2Gi
storageClass: standard
For a list of available options, see API reference .