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

  1. 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       	Fri Feb 25 16:03:19 2022	DEPLOYED	postgres-operator-1.8.0	 v1.7.2    	       default
    
  2. 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
    
  3. 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 CR

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

  2. From this namespace, create a secret that Kubernetes will use to access the registry that stores the Tanzu Postgres 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.

  3. Locate the sample Postgres manifest postgres.yaml in the ./samples directory of the location where you unpacked the Tanzu Postgres distribution.

    cd ./postgres-for-kubernetes-v*
    
  4. 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, 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 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.

Specify the Postgres version

The Tanzu Postgres Operator by default deploys the latest Postgres version (for Tanzu Operator 1.8.0, the Postgres version is 14.3). To view the available Tanzu Postgres versions for your Operator, run the command:

kubectl get postgresversion

The command displays:

NAME          DB VERSION
postgres-11   11.16
postgres-12   12.11
postgres-13   13.7
postgres-14   14.4        

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.

When upgrading the Tanzu Postgres Operator, the Operator will ensure that existing Postgres instances have the appropriate version reference added to the object. Specifically, the Operator will set spec.postgresVersion.name to postgres-11. If you are tracking manifest files in source control, update those manifests files to reflect the change.

IMPORTANT: Existing Postgres instances cannot be upgraded to a different major version.

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

Create 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
    appUser: pgappuser

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.
  • appUser (optional) specifies the name of the Postgres user with read-write privileges. It will be used to bind an application with the Postgres instance. The default Service Binding application user is pgappuser. You may change during instance deployment to a value of your choice.

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
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"},"spec":{"cpu":"0.8","memory":"800Mi","monitorStorageClassName":"standard","monitorStorageSize":"1G","pgConfig":{"dbname":"postgres-sample","username":"pgadmin"},"postgresVersion":{"name":"postgres-14"},"resources":{"metrics":{"limits":{"cpu":"100m","memory":"100Mi"},"requests":{"cpu":"100m","memory":"100Mi"}},"monitor":{"limits":{"cpu":"800m","memory":"800Mi"},"requests":{"cpu":"800m","memory":"800Mi"}}},"serviceType":"ClusterIP","storageClassName":"standard","storageSize":"800M"}}
  creationTimestamp: "2021-11-19T18:51:40Z"
  generation: 3
  labels:
    app: postgres
    postgres-instance: postgres-sample
  name: postgres-sample
  namespace: default
  resourceVersion: "12427"
  uid: 1ccb90a2-9990-4a2c-8cf9-a3b9d5e1e53e
spec:
  backupLocation: {}
  cpu: 800m
  highAvailability: {}
  memory: 800Mi
  monitorStorageClassName: standard
  monitorStorageSize: 1G
  pgConfig:
    dbname: postgres-sample
    username: pgadmin
  postgresVersion:
    name: postgres-14
  resources:
    metrics:
      limits:
        cpu: 100m
        memory: 100Mi
      requests:
        cpu: 100m
        memory: 100Mi
    monitor:
      limits:
        cpu: 800m
        memory: 800Mi
      requests:
        cpu: 800m
        memory: 800Mi
  serviceType: ClusterIP
  storageClassName: standard
  storageSize: 800M
status:
  currentState: Running
  dbVersion: "14.1"

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

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

Deploying a Postgres Instance

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

  2. 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   14.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-11 and the DB VERSION column displays 14.1.

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)

See also Accessing a Postgres Instance in Kubernetes.

check-circle-line exclamation-circle-line close-line
Scroll to top icon