PostgreSQL on Portworx

Portworx Documentation has moved to https://docs.portworx.com

Portworx Enterprise version 2.13 has reached end of life and end of extended maintenance. Refer to the release support policy doc here.
Upgrade to the latest version of Portworx Enterprise for continued support. Documentation for the latest version of Portworx Enterprise can be found here.

Perform the steps in this topic to deploy PostgreSQL with Portworx on Kubernetes.

Prerequisites

A running Portworx cluster. Refer to the Installation page for details about how to install Portworx.
Kubectl. Refer to the Install and Set Up kubectl page of the Kubernetes documentation for details about installing kubectl.
Stork. Refer to the Using Stork with Portworx page for details about installing Stork.

Create a StorageClass

Create a file named px-postgres-sc.yaml, and copy in the following spec:
```
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
    name: px-postgres-sc
provisioner: kubernetes.io/portworx-volume
parameters:
  repl: "2"
allowVolumeExpansion: true
```
Note the following about this StorageClass:
- The provisioner parameter is set to kubernetes.io/portworx-volume. For details about the Portworx-specific parameters, refer to the Portworx Volume section of the Kubernetes website.
- Two replicas of each volume will be created
Apply the spec by entering the following command:
```
kubectl apply -f px-postgres-sc.yaml
```

Create a PVC

Create a file named px-postgres-vol.yaml with the following content:
```
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
  name: postgres-data
  annotations:
    volume.beta.kubernetes.io/storage-class: px-postgres-sc
spec:
  accessModes:
    - ReadWriteOnce
  resources:
    requests:
      storage: 1Gi
```
Note that this PVC references the px-postgres-sc storage class defined in the Create a StorageClass section. As a result, Kubernetes will automatically create a new PVC for each replica.
Apply the spec by entering the following command:
```
kubectl apply -f px-postgres-vol.yaml
```

Deploy PostgreSQL using Stork

Create a file named px-postgres-app.yaml with the following content:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  selector:
    matchLabels:
      app: postgres
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
    type: RollingUpdate
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      schedulerName: stork
      affinity:
        nodeAffinity:
          requiredDuringSchedulingIgnoredDuringExecution:
            nodeSelectorTerms:
            - matchExpressions:
              - key: px/enabled
                operator: NotIn
                values:
                - "false"
      containers:
      - name: postgres
        image: postgres:9.5
        imagePullPolicy: "IfNotPresent"
        ports:
        - containerPort: 5432
        env:
        - name: POSTGRES_USER
          value: pgbench
        - name: POSTGRES_PASSWORD
          value: superpostgres
        - name: PGBENCH_PASSWORD
          value: superpostgres
        - name: PGDATA
          value: /var/lib/postgresql/data/pgdata
        volumeMounts:
        - mountPath: /var/lib/postgresql/data
          name: postgredb
      volumes:
      - name: postgredb
        persistentVolumeClaim:
          claimName: postgres-data

Note the following:

Specifies Stork as scheduler (schedulerName: stork)
Sets the following environment variables:
- POSTGRES_USER (defines the superuser)
- POSTGRES_PASSWORD (specifies the superuser password)
- PGDATA (configures the location for the database files)
References the postgres-data PVC defined in the Create a PVC section.

Apply the spec by entering the following command:
```
  kubectl apply -f px-postgres-app.yaml
```

Verify your PostgreSQL installation

Enter the following kubectl get command to list your storage classes:
```
  kubectl get sc
```
```
  NAME             PROVISIONER                     AGE
  px-postgres-sc   kubernetes.io/portworx-volume   1h
```
In the above example output, note that the provisioner is set to kubernetes.io/portworx-volume

Enter the kubectl get pvc command to verify that the PVC is bound to a volume:

  kubectl get pvc

  NAME            STATUS    VOLUME                                     CAPACITY   ACCESS MODES   STORAGECLASS     AGE
  postgres-data   Bound     pvc-60e43292-06e3-11e8-96b4-022185d04910   1Gi        RWO            px-postgres-sc   1h

Use the kubectl get pods command to verify the status of the PostgreSQL pod:

  kubectl get pod

  NAME                        READY     STATUS    RESTARTS   AGE
  postgres-86cb8587c4-l9r48   1/1       Running   0          1h

Make a note of the name of the pod. You’ll need it in the next step.

Enter the following kubectl exec command, specifying your own pod name, to open a shell session into your pod. This example opens the postgres-86cb8587c4-l9r48 pod:
```
  kubectl exec -it postgres-86cb8587c4-l9r48 -- bash
```
```
  root@postgres-86cb8587c4-l9r48:/#
```

Start the PostgreSQL interactive shell. Use the-U flag to connect as the pgbench user:

  root@postgres-86cb8587c4-l9r48:/# psql -U pgbench

  psql (9.5.10)
  Type "help" for help.

List your databases:

  pgbench=# \l

                                List of databases
    Name    |  Owner  | Encoding |  Collate   |   Ctype    |  Access privileges
  -----------+---------+----------+------------+------------+---------------------
  pgbench   | pgbench | UTF8     | en_US.utf8 | en_US.utf8 |
  postgres  | pgbench | UTF8     | en_US.utf8 | en_US.utf8 |
  template0 | pgbench | UTF8     | en_US.utf8 | en_US.utf8 | =c/pgbench         +
            |         |          |            |            | pgbench=CTc/pgbench
  template1 | pgbench | UTF8     | en_US.utf8 | en_US.utf8 | =c/pgbench         +
            |         |          |            |            | pgbench=CTc/pgbench
  (4 rows)

Exit the PostgreSQL interactive shell:
```
  pgbench=# \q
```

Discussion Forum

If you have more questions about this application, please head over to our discussion forum and feel free to ask more questions.