Deploying a Postgres Instance

This section describes how to deploy a Postgres instance to your Kubernetes cluster, using the Postgres operator. Use these instructions either to deploy a brand new instance (by provisioning a new empty Persistent Volume Claims in Kubernetes), or to update an instance by re-using existing Persistent Volumes (PVC) if available.

Prerequisites

Ensure you have installed the Tanzu Postgres 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       	Mon Jun 14 16:03:19 2021	DEPLOYED	postgres-operator-1.3.0	 v1.3.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
```

Configuring a Postgres Instance

Go to the directory where you unpacked the Tanzu Postgres distribution.
```
cd ./postgres-for-kubernetes-v*
```
Create a Kubernetes manifest file using a filename of your choice. Use as guidelines the example postgres.yaml provided with the release, in the ./samples directory. Specify your Postgres instance configuration values according to your needs. For example:
```
apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: postgres-sample
spec:
  storageClassName: standard
  storageSize: 800M
  cpu: "0.8"
  memory: 800Mi
  monitorStorageClassName: standard
  monitorStorageSize: 1G
  resources:
    monitor:
      limits:
        cpu: 800m
        memory: 800Mi
      requests:
        cpu: 800m
        memory: 800Mi
    metrics:
      limits:
        cpu: 100m
        memory: 100Mi
      requests:
        cpu: 100m
        memory: 100Mi
  pgConfig:
    dbname: postgres-sample
    username: pgadmin
  serviceType: ClusterIP
# highAvailability:
#   enabled: true
# logLevel: Debug
# backupLocation:
#   name: backuplocation-sample
# certificateSecretName:
```
Learn more about the available configuration for the Postgres CRD from the 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:"
```
IMPORTANT: The default values for spec.memory, spec.CPU, spec.storageClassName, 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 commands similar to:
```
kubectl get postgres <your-instance-name> -o yaml
```

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

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:
  memory: 800Mi
  cpu: "0.8"
  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:
  memory: 800Mi
  cpu: "0.8"
  storageClassName: standard
  storageSize: 10G
  highAvailability:
    enabled: false

Custom Database Name and User Account

When creating a Postgres instance, the default database name matches the instance name, as described in step 2 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 postgres-sample and a user called pgadmin.

apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: postgres-sample
spec:
  memory: 800Mi
  cpu: "0.8"
  storageClassName: standard
  storageSize: 10G
  pgConfig:
     dbname: postgres-sample
     username: pgadmin

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

Updating 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

apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  annotations:
    kubectl.kubernetes.io/last-applied-configuration: |
      {"apiVersion":"sql.tanzu.vmware.com/v1","kind":"Postgres","metadata":{"annotations":{},"name":"postgres-sample","namespace":"default"}}
  creationTimestamp: "2021-06-24T19:16:05Z"
  generation: 2
  labels:
    app: postgres
    postgres-instance: postgres-sample
  name: postgres-sample
  namespace: default
  resourceVersion: "518617"
  uid: bc75c719-b3da-42e6-b653-e1ce472364ac
spec:
  backupLocation: {}
  cpu: "0.8"
  highAvailability: {}
  memory: 800Mi
  monitorStorageClassName: standard
  monitorStorageSize: 1G
  pgConfig:
    dbname: postgres-sample
    username: pgadmin
  resources:
    monitor:
      limits:
        cpu: 800m
        memory: 800Mi
      requests:
        cpu: 800m
        memory: 800Mi
  serviceType: ClusterIP
  storageClassName: standard
  storageSize: 800M

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.

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    BACKUP LOCATION   AGE
postgres-sample   Running                     4m29s

Using the Postgres Instance

If you are in an HA configuration (for details see Configuring High Availability in Tanzu Postgres), ensure you are connecting to the primary pod. To confirm which pod is primary or secondary, use a command similar to:

kubectl exec -ti 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 -- bash -c "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)