This section describes how to deploy a Postgres instance to your Kubernetes cluster, using the VMware Postgres Operator.
Ensure you have installed the VMware Postgres Operator docker images and created the Postgres operator in your Kubernetes cluster. See Installing a Postgres Operator for instructions.
Verify that the Postgres operator is installed and running in your system:
helm list
NAME REVISION UPDATED STATUS CHART APP VERSION NAMESPACE
postgres-operator 1 Fri Oct 14 16:03:19 2022 DEPLOYED postgres-operator-3.0.0 v3.0.0 default
Request an expandable storage volume for your Postgres instance, to be able to resize the volume online. For more information, see Allow Volume Expansion.
Ensure that the storage class VOLUMEBINDINGMODE
field is set to volumeBindingMode=WaitForFirstConsumer
, to avoid Postgres pods and Persistent Volumes (PV) scheduling issues. For more details on the Kubernetes storage class binding modes see Volume Binding Mode.
To verify the ALLOWVOLUMEEXPANSION
and VOLUMEBINDINGMODE
fields use:
kubectl get storageclasses
The output would be similar to:
NAME PROVISIONER RECLAIMPOLICY VOLUMEBINDINGMODE ALLOWVOLUMEEXPANSION AGE
standard (default) k8s.io/minikube-hostpath Delete WaitForFirstConsumer true 4h25m
where standard
is the name of the default storage class in this example Kubernetes cluster, and will be used throughout this documentation.
If you're planning to bind a TAP application workload to the Postgres database, and wish to change the default pgappuser
application user name, edit your instance yaml before deployment. For details see Custom Database Name and User Account.
Note: OpenShift clusters: Postgres Instances cannot run on the "default" namespace due to the fact Openshift does not assign SCC (Security Context Constraints) to pods running on the namespace.
Target the namespace where you want to create the Postgres instance:
kubectl config set-context --current --namespace=<POSTGRES-NAMESPACE>
where POSTGRES-NAMESPACE
is the namespace you want to deploy the Postgres instance.
From this namespace, create a secret that Kubernetes will use to access the registry that stores the VMware Postgres Operator images:
kubectl create secret --namespace=POSTGRES-NAMESPACE docker-registry regsecret \
--docker-server=https://tanzu-sql-postgres.packages.broadcom.com
--docker-username=`USERNAME`
--docker-password=`PASSWD`
Use my-postgres-secret
for the field imagePullSecret
in your custom Postgres yaml file, that you create in the next steps.
Locate the sample Postgres manifest postgres.yaml
in the ./samples
directory of the location where you unpacked the VMware Postgres Operator distribution.
cd ./postgres-for-kubernetes-v*
Copy the example postgres.yaml
to a new file, and customize the values according to your needs. The values in the sample file are only examples, and some fields are commented out. For a full list and details on the Postgres CR values see the Postgres Deployment Properties page.
IMPORTANT: The default values for spec.resources.data.limits
, and spec.storageSize
specify a very small Postgres instance that may be too limited for your use case.
To review the defaults for your instance use a command similar to:
kubectl get postgres <your-instance-name> -o yaml
To further customize your instance CRD, review the following sections. To deploy the instance see Deploying a Postgres Instance. To customize the PostgreSQL server parameters, refer to Customizing the PostgreSQL Server.
The VMware Postgres Operator by default deploys the latest Postgres version (for VMware Postgres Operator 2.0.2, the Postgres version is 15.2). To view the available Postgres versions for your Operator, run the command:
kubectl get postgresversion
The command displays:
NAME DB VERSION
postgres-11 11.18
postgres-12 12.13
postgres-13 13.9
postgres-14 14.6
postgres-15 15.1
where:
NAME
denotes the postgresVersion
CR name. Each postgres major version has one CR. This value can be used in the Postgres manifest file to choose the postgres version.DB VERSION
displayes the minor version supported for that particular Postgres major version.Use the values under the column NAME
to specify the spec.postgresVersion.name
field in the Postgres instance manifest if you require a specific version of Postgres, for example:
...
postgresVersion:
name: postgres-13
...
See Deploying a Postgres Instance on how to deploy your Postgres instance.
The sample configuration manifest omits a namespace, so the Postgres object will be created in whatever namespace is set in the kubectl context. If you wish to create objects in a different namespace, ensure that you have created your registry secrets in the new namespace and defined the namespace
field nested under the metadata
field. For example, to create a postgres instance postgres-sample
in the postgres-databases
namespace, edit the file accordingly:
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
namespace: postgres-databases
spec:
imagePullSecret:
name: postgres-databases-registry-secret
......
where spec.imagePullSecret.name
is the registry secret you defined during the Postgres Operator deployment, see Create a Kubernetes Access Secret.
You may create multiple Postgres instances with the same YAML file, separating the configurations with three ---
:
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-ha-sample
namespace: postgres-databases
spec:
storageClassName: standard
storageSize: 800M
serviceType: LoadBalancer
# readOnlyServiceType: ClusterIP
highAvailability:
enabled: true
---
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: pg-mypostgres
namespace: postgres-databases
spec:
storageClassName: standard
storageSize: 10G
highAvailability:
enabled: false
When creating a Postgres instance, the default database name matches the instance name, as described in Configuring the Postgres Instance Manifest file.
To create a custom database name and account username, configure the pgConfig
field values in the manifest file. The following example creates a Postgres instance called postgres-sample
, with a database named custom-pg-sample
and an admin user called pgadmin
.
It also creates three additional users for the database custom-pg-sample
: an app user, read-only user, and a read-write user.
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
.....
pgConfig:
dbname: custom-pg-sample
username: pgadmin
appUser: pgappuser
readOnlyUser: pgrouser
readWriteUser: pgrwuser
Where:
dbname
(optional) is the name of the default database created when the Postgres instance is initiated. The dbname
string must be less than 63 characters, and can contain any characters and capitalization. If the dbname
field is left empty, the database name defaults to the instance name.username
(optional) is the database username account for the specified database. By default this user inherits all Read/Write permissions to all databases in the instance. If left empty, the default username is pgadmin
.appUser
(optional) specifies the name of the Postgres user used to bind an application with the Postgres instance. The default Service Binding application user is pgappuser
. The user has data definition (CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME) and data manipulation (SELECT, INSERT, UPDATE, DELETE) privileges. You may change the value during instance deployment to a value of your choice.readOnlyUser
(optional) specifies the name of the Postgres user with read-only privileges. If the field is left empty, the default name is pgrouser
.readWriteUser
(optional) specifies the name of the Postgres user with data manipulation privileges (SELECT, INSERT, UPDATE, DELETE). If the field is left empty, the default name is pgrwuser
.When the Operator creates a Postgres instance, it also creates a monitor pod that holds the state information for the instance environment.
To view the default values use:
kubectl get postgres/postgres-sample -o yaml
Alter the monitor resources in the instance yaml
to reflect your requirements. For example, change the CPU limit from 800m to 900m:
.....
resources:
monitor:
limits:
cpu: 900m
memory: 800Mi
requests:
cpu: 800m
memory: 800Mi
....
Apply the changes:
kubectl apply -f postgres.yaml
The monitor will restart and the new values will take effect. Verify the changes using the describe
command:
kubectl describe pod/postgres-sample-monitor-0
The output includes the new updates:
...
Containers:
monitor:
Container ID: docker://9dc1f58fbe8042497d05004e1d084f8976996d2d92c1dad474cb6996eed2319b
Image: postgres-instance:latest
Image ID: docker://sha256:f493b6e8139a9728663034914b4a8e5c3416fca0f548d49f61a52e4ed2ec3be3
Port: <none>
Host Port: <none>
Args:
/usr/local/apps/start_monitor
State: Running
Started: Thu, 24 Jun 2021 12:36:15 -0700
Ready: False
Restart Count: 0
Limits:
cpu: 900m
memory: 800Mi
Requests:
cpu: 800m
memory: 800Mi
...
For details on resource requests
and limits
see Managing Resources for Containers in the Kubernetes documentation.
VMware Postgres Operator supports the affinity/anti-affinity and tolerations feature, that introduces advanced scheduling for pods. Affinity rules help schedule pods with mission critical workloads on specific high performant and resilient nodes. This feature also allows pod scheduling based on the failover strategy, or low latency goals.
The VMware Postgres Operator adds a monitor and data pod affinity and tolerations section to the Postgres manifest. The monitorPodConfig
includes the following fields:
monitorPodConfig:
# tolerations:
# - key:
# operator:
# value:
# effect:
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- podAffinityTerm:
labelSelector:
matchExpressions:
- key: type
operator: In
values:
- data
- monitor
- key: postgres-instance
operator: In
values:
- postgres-sample
topologyKey: kubernetes.io/hostname
weight: 100
Edit the tolerations section to customize the values based on your environment. By default, the VMware Postgres Operator does not apply any tolerations.
The default affinity
rule is a pod preferred
anti-affinity
rule that tries to avoid scheduling the monitor and data pods of the same instance (key: postgres-instance) on the same node, based on the standard topologyKey:kubernetes.io/hostname
node label. The matchExpressions
use the operator In
. You may create your own custom rules using operators like NotIn
, Exists
, DoesNotExist
, Gt
, Lt
, NotIn
, or DoesNotExist
. See Inter-pod affinity and anti-affinity for more information on the pod affinity or anti-affinity rules.
The dataPodConfig
includes the following:
dataPodConfig:
# tolerations:
# - key:
# operator:
# value:
# effect:
affinity:
podAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- podAffinityTerm:
labelSelector:
matchExpressions:
- key: type
operator: In
values:
- data
- monitor
- key: postgres-instance
operator: In
values:
- postgres-sample
topologyKey: kubernetes.io/hostname
weight: 100
For details on the tolerations
sub-key, refer to the Taints and Tolerations topic in the Kubernetes documentation.
For further examples on VMware Postgres Operator affinity and tolerations, see Postgres Deployment Properties.
To implement a Guaranteed Quality of Service (QoS) for any of the resources (for example, primary, mirror, metrics or monitor), set the limits equal to the requests. When the limits are higher than the request, the QoS is Burstable. By default, the monitor, primary, and the mirror have a Guaranteed QoS. To check the status.qosClass
of your instance, use:
kubectl describe pod/postgres-sample-0 | grep "QoS Class:"
To enable a security profile (seccomp) for the instance, edit the field seccompProfile:type: RuntimeDefault
. The default RuntimeDefault
is the most restrictive. For further details on the field, see the Postgres Deployment Properties page, and also Restrict a Container's Syscalls with seccomp in the Kubernetes documentation.
When deploying instances in a public cloud, you can enable cloud-specific behaviour on the load balancer service. Edit the Postgres manifest file and change the default serviceType
to LoadBalancer
, and edit the field serviceAnnotations
with the values required for your cloud environment. For example, for Azure, AWS, or Google, you could use similar to:
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
serviceType: LoadBalancer
serviceAnnotations:
service.beta.kubernetes.io/azure-load-balancer-internal: "true",
service.beta.kubernetes.io/azure-load-balancer-internal-subnet: "apps-subnet"
cloud.google.com/load-balancer-type: "Internal"
service.beta.kubernetes.io/aws-load-balancer-internal: "true"
For more information, see Internal Load Balancer in the Kubernetes documentation.
The type and annotations can also be set for the read-only replica service. This service is created even for a single Postgres instance. The type can be set in anticipation of scaling up to high-availability at a later time.
readOnlyServiceType: ClusterIP
readOnlyServiceAnnotations:
service.beta.kubernetes.io/azure-load-balancer-internal: "true",
service.beta.kubernetes.io/azure-load-balancer-internal-subnet: "apps-subnet"
cloud.google.com/load-balancer-type: "Internal"
service.beta.kubernetes.io/aws-load-balancer-internal: "true"
The readOnlyServiceType
is set to ClusterIP
by default but can be changed. The readOnlyServiceAnnotations
are typically used when readOnlyServiceType
is set to LoadBalancer
.
The Postgres Operator allows you to specify a retention policy for the instance persistent volume claims (PVC). The retention policy determines if the PVC will be deleted or retained when the database instance is deleted.
By default, the policy is delete
. To retain the PVCs set the value of spec.persistentVolumeClaimPolicy
to retain
in the instance yaml. The retain
policy allows you to recreate the instance, and reattach the retained PVCs to the instance.
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
....
# serviceAnnotations:
seccompProfile:
type: RuntimeDefault
imagePullSecret:
name: regsecret
persistentVolumeClaimPolicy: delete
# highAvailability:
# enabled: true
# logLevel: Debug
....
The Postgres Operator allows you to create instances with enabled or disabled checksum. You can set the checksum field value only before creation of the instances and you can not change it once it is deployed. By default, the checksum option is disabled with the value false
. To enable checksum, set the value of spec.customConfig.initDb.checksum
to true
in the instance's yaml file.
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
name: postgres-sample
spec:
customConfig:
initDb:
checksum: true
....
Request a Postgres instance using your manifest file.
kubectl apply -f postgres.yaml
postgres.sql.tanzu.vmware.com/postgres-sample created
The Postgres operator deploys the resources according to your specification, and also initializes the Postgres instance. If there are no existing Persistent Volume Claims (PVC) for the instance, new PVCs are created and used for the deployment. If a PVC for the instance already exists, it is used as-is with the available data.
Check the status of the instance to verify that it was created successfully:
kubectl get postgres/postgres-sample
You should see output similar to:
NAME STATUS DB VERSION BACKUP LOCATION AGE
postgres-sample Running 15.1 4m29s
where DB VERSION
displays the corresponding major/minor version associated with the spec.postgresVersion.name
field in the instance manifest file. If left at the default value, it defaults to postgres-15
and the DB VERSION
column displays 15.1.
If you are in an HA configuration (for details see Configuring High Availability in VMware Postgres Operator), ensure you are connecting to the primary pod. To confirm which pod is primary or secondary, use a command similar to:
kubectl exec pod/postgres-sample-1 -- pg_autoctl show state
Name | Node | Host:Port | TLI: LSN | Connection | Current State | Assigned State
-------+-------+------------------------------------------------------------------------+----------------+--------------+---------------------+--------------------
node_1 | 1 | postgres-sample-0.postgres-sample-agent.default.svc.cluster.local:5432 | 2: 0/3002690 | read-only | secondary | secondary
node_2 | 2 | postgres-sample-1.postgres-sample-agent.default.svc.cluster.local:5432 | 2: 0/3002690 | read-write | primary | primary
Use the locally installed kubectl
tool (pre-authenticated to securely access the Kubernetes cluster) to run the psql
utility on the postgres-sample-0
pod:
kubectl exec -it postgres-sample-0 -- psql
psql (11.13 (VMware Postgres 11.13.1))
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
----------------------+----------+-----------+---------+-------+-----------------------
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
pg-instance-example | postgres | SQL_ASCII | C | C |
(4 rows)
(Enter \q to exit the `psql` utility.)
The newly created database uses UTF-8 encoding. To verify the encoding run:
postgres=# show server_encoding;
server_encoding
-----------------
UTF8
(1 row)
If you want to verify whether the checksums are "on" or "off", you can run the following command:
kubectl exec -t postgres-sample-0 -- psql -c "SHOW data_checksums"
data_checksums
----------------
off
(1 row)
For more information, see Accessing a Postgres Instance in Kubernetes.