Skip to main content
Version: 3.1

PostgreSQL on Portworx

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

Prerequisites​

Create a StorageClass​

  1. 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: pxd.portworx.com
    parameters:
      repl: "2"
    allowVolumeExpansion: true

    Note the following about this StorageClass:

    • The provisioner parameter is set to pxd.portworx.com. 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

  2. Apply the spec by entering the following command:

    kubectl apply -f px-postgres-sc.yaml

Create a PVC​

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

  2. Apply the spec by entering the following command:

    kubectl apply -f px-postgres-vol.yaml

Deploy PostgreSQL using Stork​

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

  2. Apply the spec by entering the following command:

    kubectl apply -f px-postgres-app.yaml

Verify your PostgreSQL installation​

  1. Enter the following kubectl get command to list your storage classes:

    kubectl get sc
    NAME             PROVISIONER                     AGE
    px-postgres-sc   pxd.portworx.com   1h

    In the above example output, note that the provisioner is set to pxd.portworx.com

  2. 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-xxxxxxxx-xxxx-xxxx-xxxx-022185d04910   1Gi        RWO            px-postgres-sc   1h

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

  4. 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:/#

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

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

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

Last updated on