Examples ======== In this section, you can find some examples of configuration files to set up your PostgreSQL Cluster. .. Important:: These examples are here for demonstration and experimentation purposes, and can be executed on a personal Kubernetes cluster with Minikube or Kind as described in the :ref:`Quickstart ` . .. Seealso:: For a list of available options, please refer to the :ref:`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 PVC Template (Persistent Volume Claim) 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 that 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 will execute 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 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 existing Secret and Configmap mounted into Postgres pod using projected volume mount. Backups ------- - \*Customized storage class and backups*\* : *Prerequisites*: bucket storage should be available. The sample config is for AWS, please change 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. Please 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 via 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 via 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 via 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 “PostGIS cluster” (see the :ref:`PostGIS section ` 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. Please 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