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

  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           Mon Jun 14 16:03:19 2021    DEPLOYED    postgres-operator-1.2.0  v1.2.0          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
    

Configuring a Postgres Instance

  1. Go to the directory where you unpacked the Tanzu Postgres distribution.

    $ cd postgres-v*
    
  2. Create a Kubernetes manifest file using a filename of your choice, for example pg-instance-example.yaml. Use as guidelines the example yaml provided with the release. Specifying your Postgres instance configuration values according to your needs. For example:

    apiVersion: sql.tanzu.vmware.com/v1
    kind: Postgres
    metadata:
      name: pg-instance-example
    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
      pgConfig:
        dbname: pg-instance-example
        username: pgadmin
      serviceType: ClusterIP
    # highAvailability:
    #   enabled: true
    # logLevel: Debug
    # backupLocationSecret:
    #   name: s3-secret-example
    # certificateSecretName:
    

    Where:

    • name (required) in the Metadata section, is the name of the created Postgres instance. The Postgres operator appends -0 to the end of the name when it creates the instance pod, for example pg-instance-example-0. The new Postgres instance includes a default database with the same name as the instance. To specify a different database name from the instance name, see Creating a Custom Database Name and User Account.
    • storageClassName (optional) specifies the Kubernetes StorageClass storage type. The standard StorageClass is the default Kubernetes storage class. This field cannot be updated on a running instance.
    • storageSize (optional) is the size of the pgdata Persistent Volume Claim (PVCs) for the Postgres instance. The default is 800M. To specify use type M, Mi, or MB.
    • cpu (optional) is the amount of cpu the Postgres instance requires, in millicores. Ensure that you do not exceed your Kubernetes nodes’ CPU capacity. This field can be updated on a running instance.
    • memory (optional) is the amount of memory to allocate to the Postgres instance pod, in bytes. The default for the Postgres instance is 800 mebibytes, or about 800 megabytes. You can express memory as a plain integer or as a fixed-point integer using one of these suffixes: E, P, T, G, M, K. You can also use the power-of-two equivalents: Ei, Pi, Ti, Gi, Mi, Ki. Ensure that you do not exceed your Kubernetes nodes’ memory capacity. This field can be updated on a running instance by using the kubectl apply -f updated_manifest_yaml_file command.
    • monitorStorageClassName (optional) is the storage class allocated to the Postgres monitor. By default it is set to standard. It does not need to match the instance storageClassName.
    • monitorStorageSize(optional) is the storage size allocated to the monitor. By default it is set to 1G.
    • cpu under spec:resources:monitor:limits (optional) specifies the limit of CPU resources allocated to the monitor pod, that is created when a Postgres instance is initialized.
    • memory under spec:resources:monitor:limits (optional) specifies the limit of memory resources allocated to the monitor pod, that is created when a Postgres instance is initialized.
    • cpu under spec:resources:monitor:requests (optional) specifies the minimum required CPU resources for the monitor pod to ensure successful pod operation. It is used by the Kubernetes scheduler to decide which node to place the pod on, and reserve the necessary CPU resources on that node.
    • memory under spec:resources:monitor:requests (optional) specifies the minimum required memory resources for the monitor pod to ensure successful pod operation. It is used by the Kubernetes scheduler to decide which node to place the pod on, and reserve the necessary memory resources on that node.
    • dbname (optional) see Custom Database Name and User Account. This field can not be updated on a running instance.
    • username(optional) see Custom Database Name and User Account. This field can not be updated on a running instance.
    • serviceType:<LoadBalancer|ClusterIP> (optional) specifies the type of publishing service for your Postgres instance. If left empty, the default is ClusterIP. For more information see Kubernetes Publishing Services. This field can be updated on a running instance by using the kubectl apply -f command. Note: For Tanzu Application Services (TAS) applications, set the service type for LoadBalancer. For details see Binding a TAS Application to a Postgres Instance.
    • highAvailability:<false|true> (optional) determines if the created instance will be in a single or cluster mode configuration. The default is enabled:true. See Configuring High Availability on how to create a clustered Postgres instance configuration. This field can be updated on a running instance by using the kubectl apply -f command.
    • name (optional) under backupLocationSecret:name section defines the access secret for a S3 compatible backup storage location. To enable S3 storage backup at the time of the instance deployment, provide the required S3 secret file. For details on how to create the S3 secret, see Backing Up and Restoring Tanzu Postgres. Leave the field empty if you are not planning to enable backups at initialization time. You may edit and re-apply the instance manifest file at a later stage, when you have your S3 secret configured.
    • certificateSecretName(optional) defines the CA secret created during TLS configuration. For more information about creating this secret, see Configuring TLS for Tanzu Postgres Instances.

    The minimum entries for the configuration yaml file are:

    apiVersion: sql.tanzu.vmware.com/v1
    kind: Postgres
    metadata:
      name: <your-instance-name>
    

    The following yaml fields have been set by default to these values:

    Field Default Value DataType
    DefaultCPU 0.8 String
    DefaultMemory 800Mi String
    DefaultStorageClassName standard String
    DefaultStorageSize 800M String
    DefaultDatabaseUsername pgadmin String
    DefaultServiceType ClusterIP String
    DefaultMonitorCPU 0.8 resource.Quantity
    DefaultMonitorMemory 800Mi resource.Quantity
    DefaultMonitorStorageClassName standard String
    DefaultMonitorStorageSize 1G resource.Quantity
    highAvailability enabled:false Boolean

    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 example yaml configuration manifest uses the default namespace, therefore this field is not specifically set in the file. If you wish to create instances in a different namespace, ensure that you have created your registry secrets in the new namespace and defined the namespace field in the yaml. For example, to create a postgres instance pg-instance-example in the postgres-databases namespace, edit the file accordingly:

    apiVersion: sql.tanzu.vmware.com/v1
    kind: Postgres
    metadata:
      name: pg-instance-example
      namespace: postgres-databases
    spec:
      memory: 800Mi
      cpu: "0.8"
      storageClassName: standard
      storageSize: 800M
      pgConfig:
         dbname: pg-instance-example
         username: pgadmin
      serviceType: ClusterIP
      highAvailability:
         enabled: true
    

    Specifying Multiple Instances

    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: pg-instance-example
      namespace: postgres-databases
    spec:
      memory: 800Mi
      cpu: "0.8"
      storageClassName: standard
      storageSize: 800M
      pgConfig:
         dbname: pg-instance-example
         username: pgadmin
      serviceType: ClusterIP
      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
      pgConfig:
          dbname: my-postgres
          username: pgadmin
      serviceType: ClusterIP
      highAvailability:
          enabled: false
      backupLocationSecret:
          name:
    

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 pg-instance-example, with a database named pg-instance-example and a user called pgadmin.

apiVersion: sql.tanzu.vmware.com/v1
kind: Postgres
metadata:
  name: pg-instance-example
spec:
  memory: 800Mi
  cpu: "0.8"
  storageClassName: standard
  storageSize: 10G
  pgConfig:
     dbname: pg-instance-example
     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. Release 1.2 allows the user to alter the default monitor resources.

To view the default values use:

$ kubectl get postgres/pg-instance-example -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":"pg-instance-example","namespace":"default"}}
  creationTimestamp: "2021-06-24T19:16:05Z"
  generation: 2
  labels:
    app: postgres
    postgres-instance: pg-instance-example
  name: my-postgres
  namespace: default
  resourceVersion: "518617"
  uid: bc75c719-b3da-42e6-b653-e1ce472364ac
spec:
  backupLocationSecret: {}
  cpu: "0.8"
  highAvailability: {}
  memory: 800Mi
  monitorStorageClassName: standard
  monitorStorageSize: 1G
  pgConfig:
    dbname: pg-instance-example
    username: pgadmin
  resources:
    monitor:
      limits:
        cpu: 800m
        memory: 800Mi
      requests:
        cpu: 800m
        memory: 800Mi
  serviceType: ClusterIP
  storageClassName: standard
  storageSize: 800M
status:
  currentState: Running
  instanceImage: ""
  operatorVersion: ""

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 pg-instance-example.yaml

The monitor will restart and the new values will take effect. Verify the changes using the describe command:

$ kubectl describe pod/pg-instance-example-monitor-0

The output includes the new updates:

...
Containers:
  my-postgres-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

  1. Request a Postgres instance using your manifest file.

    $ kubectl apply -f pg-instance-example.yaml
    
    postgres.sql.tanzu.vmware.com/pg-instance-example 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/pg-instance-example
    

    You should see output similar to:

    NAME                   STATUS    AGE
    pg-instance-example   Created    28s
    

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/pg-instance-example-1 -- pg_autoctl show state

Name   | Node |                                                      Host:Port |    LSN  | Reachable|  Current State | Assigned State
-------+-------+---------------------------------------------------------------+----------+-----------+----------+-----------------
node_1 |    1 | pg-instance-example-1.my-postgres-agent.default.svc.cluster.local:5432 | 0/501B5C0 |    yes |   primary   |    primary
node_2 |    2 | pg-instance-example-0.my-postgres-agent.default.svc.cluster.local:5432 | 0/501B5C0 |    yes |   secondary |    secondary

Use the locally installed kubectl tool (pre-authenticated to securely access the Kubernetes cluster) to run the psql utility on the pg-instance-example-0 pod:

$ kubectl exec -it pg-instance-example-0 -- bash -c "psql"
psql (11.5-3.ubuntu1804.pivotal)
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.