The Postgres Operator provides easy to run PostgreSQL cluster on Kubernetes. We have provided cluster-wide Postgres Operator for easy deployment of Postgres SQL database servers. The Postgres Operator defines a kind of Cluster that ensures the existence of a database or database cluster. For your convenience, we provide some working examples in the next sections together with some advanced features.
The original, full documentation about PostgresOperator structure is available on its website with the official manual for creating a minimal PostgreSQL cluster located in the quickstart section. If you seek advanced configuration or description and explanation of the YAML fields, consult the official documentation.
Deploying a Cluster Instance
It is possible to deploy either a single or cluster instance. For testing purposes, we advise to deploy a minimal (single) instance that uses NFS storage as an underlying storage and consumes limited resources (performance is low). For better availability and performance, cluster deployment can be used. In this case, multiple instances run in the cluster where one of them is a leader and others follow and sync data from the leader.
Single Instance
In this example, a single instance with generated username and password is created. It is possible to provide a custom Secret object instead of an automatically generated username/password - see section Custom Database Credentials.
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: test-cluster # name, can be modified
spec:
instances: 1 # single instance
imageName: 'cerit.io/cloudnative-pg/postgresql:15.0' # image to use
primaryUpdateStrategy: unsupervised
bootstrap:
initdb:
database: mydb # db to create, can be modified
owner: myowner # user to create, can be modified
storage:
size: 10Gi
storageClass: nfs-csi # storage class to use, can be modified
Run kubectl create -f minimal-cn.yaml -n [namespace], you should see a pod named test-cluster-1 (if metadata.name has not been changed from the example) running in your namespace.
The minimal manifest can be downloaded here.
Cluster Instance
To deploy the cluster version, you can download cluster manifest. The only difference is in the line denoting number of instances:
numberOfInstances: 3
That requests 3 node cluster. This type of setup is resilient to node failure — if a node running this instance fails, the database is recreated on another node, data is reconnected from NFS storage, and operations resume. On the other hand, this setup is not speed-superior even when resources are added.
Note: Cluster instances consume more resources and you must consider how many resources are available in your namespace/project. Cluster instance consumes numberOfInstances * limits of resources.
Accessing the Database
To access the database, you need to know the credentials and the location of the database.
Database Credentials
If you used custom Secret object, use those credentials. Otherwise, issue command (test-cluster-app if metadata.name has not been changed from the example, otherwise [metadata-name]-app)
kubectl get secret test-cluster-app -n [namespace] -o 'jsonpath={.data.pgpass}' | base64 -d
It will show the string in the form: dbhost:port:dbname:dbuser:password. For example:
test-cluster-rw:5432:mydb:myowner:uLUmkvAMwR0lJtw5ksUVKihd5OvCrD28RFH1JTLbbzO6BhEAtnWJr1L0nWpTi3GL
Use dbuser:password as credentials to connect.
Database Access
Database can be accessed (connected to) from the same namespace where it is deployed, a different namespace, or from outside of the Kubernetes cluster. Databases commonly use read-only connections (targeting usually replicas) and read-write connections (targeting master instances).
Access From the Same Namespace
If the cluster has been created with the name test-cluster:
kind: Cluster
metadata:
name: test-cluster
...
The hostname to use of the read-only replica is test-cluster-ro and the hostname of the writable master is test-cluster-rw. If not explicitly set otherwise, the port is well-known postgres port 5432.
Access From a Different Namespace
Assuming the database name is test-cluster, the read-only hostname is test-cluster-ro.[namespace].svc.cluster.local, and the writable master is test-cluster-rw.[namespace].svc.cluster.local, replace [namespace] with the name of the namespace where the database is deployed.
If not explicitly set otherwise, the port is well-known postgres port 5432.
Access from outside of the Kubernetes Cluster
To access the database from the external world (outside the Kubernetes cluster), a new service object of type LoadBalancer must be created.
If you want read-only access as well (in addition to writable access), two LoadBalancers must be created. In this case, please contact us at k8s@ics.muni.cz.
There is a slight difference in created objects depending on whether you are accessing the database from Masaryk University/Masaryk University VPN or from anywhere else.
MU/MU VPN
Assuming the database name is again test-cluster, the following are examples of needed services for both to and rw accesses.
apiVersion: v1
kind: Service
metadata:
name: test-cluster-lb-rw
annotations:
metallb.universe.tf/address-pool: privmuni
spec:
type: LoadBalancer
ports:
- port: 5432
targetPort: 5432
selector:
cnpg.io/cluster: test-cluster # if name was changed provide [cluster-name]
role: primary
The annotation metallb.universe.tf/allow-shared-ip ensures that both LoadBalancers share the same IP address and are distinguished by ports:
5433port is for read-only replicas5432is for writable replicas
This example assigns IP addresses that are reachable only from internal network from Masaryk University or via VPN service of Masaryk University.
Non-MU accesses
Assuming the database name is again test-cluster, the following are examples of needed services for both to and rw accesses.
apiVersion: v1
kind: Service
metadata:
name: test-cluster-lb-rw
annotations:
metallb.universe.tf/address-pool: default
spec:
type: LoadBalancer
ports:
- port: 5432
targetPort: 5432
selector:
cnpg.io/cluster: test-cluster # if name was changed provide [cluster-name]
role: primary
In this case, the IP addresses are allocated from the public IP pool. When accessing the database from the external world, we strongly recommend to setup the Network Policy.
Retrieve Public IP to Connect
After creating this service, the database is reachable on a public IP address that can be found via command:
kubectl get svc [name_of_loadbalancer_service_from_previous_step] -n [namespace_where_cluster_is_deployed]
The output will be similar to the one below where EXTERNAL-IP denotes IP to connect (hostname/address in pgAdmin) and PORT port to connect.
kubectl get svc test-cluster-external -n test-postgres-ns
NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE
test-cluster-external LoadBalancer 10.43... 147.251.X.Y 5432/TCP 21h
Network Policy
To increase security, a network policy is used to allow network access to the database only from certain pods. The network policy is recommended in all cases mentioned above and strongly recommended if access from outside of the Kubernetes cluster has been set up. See Network Policy for general concepts.
You should restrict access from either a particular Pod or a particular external IP address. However, access from cloudnativegp namespace must be allowed so that the operator managing the Postgres cluster can manage the instance.
Access Only from Specific Namespace Allowed
This example enables access only from the specific namespace of the Kubernetes cluster (download here). Provide the cluster’s name (if it was changed) as spec.podSelector.matchLabels.cnpg.io/cluster and replace [namespace] with the namespace where postgres is deployed. Other namespaces are allowed by adding more namespaceSelector items into the list.
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
name: cnpg-network-policy
spec:
podSelector:
matchLabels:
cnpg.io/cluster: test-cluster
policyTypes:
- Ingress
ingress:
- from:
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: cloudnativepg
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: [namespace]
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: [any_other_namespace]
...
Access Only from Specific External IP Address Allowed
This example enables access only from the specific external IP address (download here). Provide the cluster’s name (if it was changed) as spec.podSelector.matchLabels.cnpg.io/cluster, replace [namespace] with the namespace where the cluster is deployed, and replace [IP]/32 with your external IP /32 (this slash 32 part must be provided as the trailing part of the address). More IP blocks can be allowed by adding whole ipBlock part into the yaml.
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
name: cnpg-network-policy
spec:
podSelector:
matchLabels:
cnpg.io/cluster: test-cluster
policyTypes:
- Ingress
ingress:
- from:
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: cloudnativepg
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: [namespace]
- ipBlock:
cidr: [IP]/32
- ipBlock:
cidr: [another_IP]/32
Data Backups
Postgres operator offers automatic backups to S3 storage implemented via periodic wal streaming and regular backups (basically pgdump equivalent) using CronJobs.
Firstly, you need to get an S3 storage account, e.g. at DU Cesnet, particularly KEY-ID and SECRET-ID are needed. Using these two IDs, you can create a Secret object via template, replacing KEY-ID and SECRET-ID with real values obtained from S3 account.
To enable the backup, add the backup section to the cluster manifest. See template, the last section backup.
...
backup:
barmanObjectStore:
destinationPath: "s3://[BUCKET]"
endpointURL: "https://[S3URL]"
s3Credentials:
accessKeyId:
name: aws-creds
key: ACCESS_KEY_ID
secretAccessKey:
name: aws-creds
key: ACCESS_SECRET_KEY
retentionPolicy: "30d"
Replace [BUCKET] and [S3URL] with real values. Do not replace ACCESS_KEY_ID and ACCESS_SECRET_KEY, they need to match the Secret keywords that identify the values for them.
Full Backups
For full backups, additional resources must be created.
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
name: [backup-name]
spec:
schedule: "0 0 0 * * *"
backupOwnerReference: self
cluster:
name: [db-name]
Replace [backup-name] with the appropriate name of the resource (must be unique within a namespace), and [db-name] with the name of the database cluster from the main manifest, these two names must match. E.g., cluster manifest beginning with:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: test-cluster
Needs backup manifest:
apiVersion: postgresql.cnpg.io/v1
kind: ScheduledBackup
metadata:
name: backup
spec:
schedule: "0 0 0 * * *"
backupOwnerReference: self
cluster:
name: test-cluster
The schedule is NOT a standard cron schedule - this second/minutes/hours/day in month/month/day of week.
High Availability Cluster Setup
A replicated cluster can be utilized for high-availability cases. Starting from Postgres version 11, the operator supports additional settings to increase high availability.
- Increase
walsegments history using the following snippet. Change thewal_keep_sizevalue as appropriate. The default value is about 500MB which might be small. However, this value is allocated from the underlying storage. Ifzfs-csiis used, it enforces disk size and DBwalscan easily consume all disk space.postgresql: parameters: wal_keep_size: 64GB - Enable high availability option:
replicationSlots: highAvailability: enabled: true
Keep in mind that these options work for Postgres 11 and newer only. Full cluster example with the HA options:
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: test-cluster
spec:
instances: 3
imageName: 'ghcr.io/cloudnative-pg/postgresql:14.7-3'
primaryUpdateStrategy: unsupervised
bootstrap:
initdb:
database: dbname
owner: dbowner
postgresql:
parameters:
wal_keep_size: 64GB
replicationSlots:
highAvailability:
enabled: true
resources:
requests:
memory: "4096Mi"
cpu: 1
limits:
memory: "4096Mi"
cpu: 1
storage:
size: 10Gi
storageClass: zfs-csi
Local Storage vs NFS
It is possible to use local storage (SSD) instead of NFS or any network supported PVC. While it is not possible to directly request local storage in the volume section, it is possible to use local storage. You can download the single instance manifest which can also be used for the cluster instance (set the desired numberOfInstances).
Basically, the zfs-csi storage class can be used to use local storage. Special care must be taken when setting the limit. It cannot be increased in the future and the limit is enforced, however, it is fasted storage that is offered.
Custom Database Credentials
If you want to use custom database credentials, a Secret object must be created (save this yaml to secret.yaml)
apiVersion: v1
kind: Secret
type: kubernetes.io/basic-auth
stringData:
password: [password]
username: [username]
metadata:
name: [secret_name]
Run kubectl create -f secret.yaml -n [namespace_where_postgres_will_be_deployed].
In the cluster manifest, change the boostrap.initdb section to reference the newly created secret.
...
bootstrap:
initdb:
database: [database-name]
owner: [username_from_secret]
secret:
name: [secret_name]
Egress Network Policy
If you want to control the outgoing traffic (egress), it is necessary to allow particular ports and ipblocks - see the following example. If ingress-only restriction is suitable for you, you can remove the egress rules.
Please note that if you want to use the data backups and also restrict the egress communication, you may need to add an egress rule enabling the communication to the backup server!
apiVersion: networking.k8s.io/v1
kind: NetworkPolicy
metadata:
name: postgres-cnpg-np
spec:
podSelector:
matchLabels:
# This Network Policy is applied to our Postgres CNPG Cluster resource named "test-cluster"
cnpg.io/cluster: test-cluster
policyTypes:
- Ingress
- Egress # <---
ingress:
# Enables ingress Postgres port for all Pods
- ports:
- port: 5432
protocol: TCP
from:
- podSelector: {}
# Enables ingress communication from the Postgres CNPG Operator installed in the Kubernetes
- ports:
- port: 8000
protocol: TCP
from:
- namespaceSelector:
matchLabels:
kubernetes.io/metadata.name: cloudnativepg
podSelector:
matchLabels:
app.kubernetes.io/name: cloudnative-pg
egress: # <---
# Enables egress communication to the Postgres CNPG Operator installed in the Kubernetes
- ports:
- port: 6443
protocol: TCP
to:
- ipBlock:
cidr: 10.43.0.1/32
- ipBlock:
cidr: 10.16.62.14/32
- ipBlock:
cidr: 10.16.62.15/32
- ipBlock:
cidr: 10.16.62.16/32
- ipBlock:
cidr: 10.16.62.17/32
- ipBlock:
cidr: 10.16.62.18/32
Caveats
Deploy Errors
-
If you encounter a deployment error and want to delete and recreate, you must make sure that the running instance is deleted before recreating. If you create a new instance too early, the instance will never be created and you will have to use a different name.
-
If you run out of quotas during the cluster deployment, only instances within quotas will be deployed. Unfortunately, even in this case, you cannot remove/redeploy a database using these deployments.
-
Local SSD variant is not resilient to cluster failure. Data may be lost in this case (e.g. if cluster is restored from backup, local data may not be available). Regular backups are strongly recommended.
-
It may happen that a replica fails and cannot join the cluster, in which case the replica and PVC must be removed at once using kubectl delete pod/test-cluster-1 pvc/test-cluster-1 -n [namespace]
if the failed replica istest-cluster-1`. The replica will be rebuilt and synchronized with the rest of the cluster. However, its number will be increased.
Images
Currently, cloudnative-pg docker images do not contain cs_CZ locale, so Czech collate cannot be used. For this reason, we have created two local images: cerit.io/cloudnative-pg/postgresql:10.23-3 and cerit.io/cloudnative-pg/postgresql:15.0 which contain Czech locales.
List of public images is available here.