Examples
========
.. raw:: html
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 :ref:`Quick start ` .
.. Seealso::
For a list of available options, see :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 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:17.5
# 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 for minio** : *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
data:
additionalCommandArgs:
- "--min-chunk-size=5MB"
- "--read-timeout=60"
- "-vv"
A basic cluster with backups configured.
**Simple cluster with backup configured for Scaleway Object Storage** :
*Prerequisites*: The configuration assumes a Scaleway Object Storage
bucket exists. Update ``backup.barmanObjectStore`` with your Scaleway
parameters. :
::
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: pg-backup-scaleway
spec:
instances: 3
storage:
storageClass: standard
size: 1Gi
backup:
barmanObjectStore:
destinationPath: "s3:///backups/" # change with your buckets name.
endpointURL: "https://s3..scw.cloud" # change with your buckets location/region.
s3Credentials:
accessKeyId:
name: scaleway
key: ACCESS_KEY_ID
secretAccessKey:
name: scaleway
key: ACCESS_SECRET_KEY
region:
name: scaleway
key: ACCESS_REGION
A basic cluster with backups configured to work with Scaleway Object
Storage..
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: 1
imageName: ghcr.io/cloudnative-pg/postgis:17
storage:
size: 1Gi
postgresql:
parameters:
log_statement: ddl
- --
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
name: postgis-example-app
spec:
name: app
owner: app
cluster:
name: postgis-example
extensions:
- name: postgis
- name: postgis_topology
- name: fuzzystrmatch
- name: postgis_tiger_geocoder
: An example of a PostGIS cluster. See :ref:`PostGIS Container Images ` 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.
Managed services
----------------
**Cluster with managed services** :
::
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-managed-services
spec:
instances: 1
storage:
size: 1Gi
managed:
services:
## disable the default services
disabledDefaultServices: ["ro", "r"]
additional:
- selectorType: rw
serviceTemplate:
metadata:
name: "test-rw"
labels:
test-label: "true"
annotations:
test-annotation: "true"
spec:
type: LoadBalancer
: Declares a service with the ``managed`` stanza. Includes default
service disabled and new ``rw`` service template of ``LoadBalancer``
type defined.
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 :ref:`API Reference ` .
Pooler configuration
--------------------
**Pooler with custom service config** :
::
apiVersion: postgresql.cnpg.io/v1
kind: Pooler
metadata:
name: pooler-example-rw
spec:
cluster:
name: cluster-example
instances: 3
type: rw
serviceTemplate:
metadata:
labels:
app: pooler
spec:
type: LoadBalancer
pgbouncer:
poolMode: session
parameters:
max_client_conn: "1000"
default_pool_size: "10"
Logical replication via declarative Publication and Subscription objects
------------------------------------------------------------------------
Two test manifests contain everything needed to set up logical
replication:
**Source cluster with a publication** :
::
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example
spec:
instances: 3
imageName: ghcr.io/cloudnative-pg/postgresql:17
storage:
size: 1Gi
bootstrap:
initdb:
postInitApplicationSQL:
- CREATE TABLE numbers (i SERIAL PRIMARY KEY, m INTEGER)
- INSERT INTO numbers (m) (SELECT generate_series(1,10000))
- ALTER TABLE numbers OWNER TO app
- CREATE TABLE numbers_two (i SERIAL PRIMARY KEY, m INTEGER)
- INSERT INTO numbers_two (m) (SELECT generate_series(1,10000))
- ALTER TABLE numbers_two OWNER TO app
- CREATE SCHEMA another_schema
- ALTER SCHEMA another_schema OWNER TO app
- CREATE TABLE another_schema.numbers_three (i SERIAL PRIMARY KEY, m INTEGER)
- INSERT INTO another_schema.numbers_three (m) (SELECT generate_series(1,10000))
- ALTER TABLE another_schema.numbers_three OWNER TO app
replicationSlots:
highAvailability:
synchronizeLogicalDecoding: true
managed:
roles:
- name: app
login: true
replication: true
postgresql:
parameters:
hot_standby_feedback: on
sync_replication_slots: on
- --
apiVersion: postgresql.cnpg.io/v1
kind: Publication
metadata:
name: cluster-example-pub
spec:
name: pub
dbname: app
cluster:
name: cluster-example
target:
allTables: true
Sets up a cluster, ``cluster-example`` with some tables created in the
``app`` database, and, importantly, *adds replication to the app user*.
A publication is created for the cluster on the ``app`` database: note
that the publication will be reconciled only after the cluster’s primary
is up and running.
**Destination cluster with a subscription** : *Prerequisites*: The
source cluster with publication, defined as above. :
::
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: cluster-example-dest
spec:
instances: 1
storage:
size: 1Gi
bootstrap:
initdb:
import:
type: microservice
schemaOnly: true
databases:
- app
source:
externalCluster: cluster-example
externalClusters:
- name: cluster-example
connectionParameters:
host: cluster-example-rw.default.svc
user: app
dbname: app
password:
name: cluster-example-app
key: password
- --
apiVersion: postgresql.cnpg.io/v1
kind: Subscription
metadata:
name: cluster-example-dest-sub
spec:
cluster:
name: cluster-example-dest
name: sub
dbname: app
publicationName: pub
externalClusterName: cluster-example
parameters:
failover: true
Sets up a cluster ``cluster-example-dest`` with:
- the source cluster defined in the ``externalClusters`` stanza. Note
that it uses the ``app`` role to connect, which assumes the source
cluster grants it ``replication`` privilege.
- a bootstrap import of microservice type, with ``schemaOnly`` enabled
A subscription is created on the destination cluster: note that the
subscription will be reconciled only after the destination cluster’s
primary is up and running.
After both clusters have been reconciled, together with the publication
and subscription objects, you can verify that that tables in the source
cluster, and the data in them, have been replicated in the destination
cluster
In addition, there are some standalone example manifests:
**A plain Publication targeting All Tables** : *Prerequisites*: an
existing cluster ``cluster-example`` . :
::
apiVersion: postgresql.cnpg.io/v1
kind: Publication
metadata:
name: publication-example
spec:
cluster:
name: cluster-example
name: pub-all
dbname: app
target:
allTables: true
**A Publication with a constrained publication target** :
*Prerequisites*: an existing cluster ``cluster-example`` . :
::
apiVersion: postgresql.cnpg.io/v1
kind: Publication
metadata:
name: publication-example-objects
spec:
cluster:
name: cluster-example
name: pub-objects
dbname: app
target:
objects:
- tablesInSchema: public
- table:
schema: another_schema
name: numbers_three
only: true
**A plain Subscription** : Prerequisites: an existing cluster
``cluster-example`` set up as source, with a publication ``pub-all`` . A
cluster ``cluster-example-dest`` set up as a destination cluster,
including the ``externalClusters`` stanza with connection parameters to
the source cluster, including a role with replication privilege. :
::
apiVersion: postgresql.cnpg.io/v1
kind: Subscription
metadata:
name: subscription-sample
spec:
name: sub
dbname: app
publicationName: pub-all
cluster:
name: cluster-example-dest
externalClusterName: cluster-example
All the above manifests create publications or subscriptions on the
``app`` database. The Database CRD offers a convenient way to create
databases declaratively. With it, logical replication could be set up
for arbitrary databases. Which brings us to the next section.
Declarative management of Postgres databases
--------------------------------------------
**A plain Database** : *Prerequisites*: an existing cluster
``cluster-example`` . :
::
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
name: db-one
spec:
name: one
owner: app
cluster:
name: cluster-example
**A Database with ICU local specifications** : *Prerequisites*: an
existing cluster ``cluster-example`` running Postgres 16 or more
advanced. :
::
# NOTE: this manifest will only work properly if the Postgres version supports
# ICU locales and rules (version 16 and newer)
apiVersion: postgresql.cnpg.io/v1
kind: Database
metadata:
name: db-icu
spec:
name: declarative-icu
owner: app
encoding: UTF8
localeProvider: icu
icuLocale: en
icuRules: fr
template: template0
cluster:
name: cluster-example