Deploying a Postgres Instance

This section describes how to deploy a Postgres instance to your Kubernetes cluster, using the VMware Postgres Operator.

Prerequisites

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-2.0.2	 v2.0.2    	       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.

Configuring a Postgres Instance

Create an instance Custom Resource (CR)

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://registry.tanzu.vmware.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.

Specify the Postgres version

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.

Specify namespaces

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
  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

Create custom database name and user account

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.

Update the monitor resources

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.

Configure node affinity and tolerations

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.

Set up quality of service

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:"

Set up a security profile

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.

Configure an internal load balancer

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.

Configure Persistent Volume Claim Retention Policy

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
....

Deploying a Postgres Instance

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.

Using the Postgres Instance

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)