Create a Postgres Database using a PVC

Cloud applications often require a place to persist relational data. Containers inside pods have ephemeral filesystems that are lost when a pod restarts or terminates. Persistent volumes solve this problem by allowing data to persist beyond a pod's lifetime. This tutorial explains how to create a Postgres database on a persistent volume, and then demonstrates that the data survives the restart of the Postgres pod.

Note that the PVC data won't move if CloudFlow moves your project to a new location. See here for strategies to handle this. Or upgrade to our Pro Plan to allow you to specify a static location configuration.

You'll create the following resources:

a location-optimizer ConfigMap to specify a single location
a persistent volume claim for the database, postgres-pvc
a Postgres deployment, postgres-deployment, using the official Postgres image on Docker Hub
a Service, postgres-service, that points to the Postgres pod

Run in a Single Location

You'll likely want your database to run in a single location, so specify that your project should have maximumLocations of 1 in your location-optimizer ConfigMap resource:

postgres-single-location.yaml
apiVersion: v1
kind: ConfigMap
data:
  strategy: |
    {
        "strategy": "SolverServiceV1",
        "params": {
            "policy": "dynamic",
            "minimumLocations": 1,
            "maximumLocations": 1
        }
    }
metadata:
  name: location-optimizer

Create the PVC

Next, create the PersistentVolumeClaim to hold the database.

postgres-pvc-claim.yaml
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
spec:
  resources:
    requests:
      storage: 50Mi
  volumeMode: Filesystem
  accessModes:
    - ReadWriteMany

Apply and check the PersistentVolumeClaim resource.

$ kubectl apply -f postgres-pvc-claim.yaml
persistentvolumeclaim/postgres-pvc created
$ kubectl get pvc
NAME          STATUS    VOLUME   CAPACITY   ACCESS MODES   STORAGECLASS   AGE
postgres-pvc  Pending                                                     14m
$

Create the Deployment

Notes about the deployment:

Write-replicas, which you are creating below, must be no more than 1. If you need horizontal scale then you can make a separate deployment of read replicas.
The PVC is mounted at /var/lib/postgresql/data, and then we place the database files in the k8s folder underneath. The k8s subfolder and runAsUser are specified so that the Postgres container works properly with the security of the volume supporting the PVC.
In production you should use a Secret resource instead of supplying a password directly.

postgres-deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1 # must be no more than 1
  selector:
    matchLabels:
      app: postgres-deployment
  template:
    metadata:
      labels:
        app: postgres-deployment
    spec:
      securityContext:
        runAsUser: 999
      containers:
        - name: postgres
          image: postgres
          imagePullPolicy: Always
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_PASSWORD
              value: UseASecretInstead
            - name: PGDATA
              value: /var/lib/postgresql/data/k8s
          volumeMounts:
            - name: postgres-data
              mountPath: /var/lib/postgresql/data
          resources:
            requests:
              memory: ".5Gi"
              cpu: "500m"
            limits:
              memory: ".5Gi"
              cpu: "500m"
      volumes:
        - name: postgres-data
          persistentVolumeClaim:
            claimName: postgres-pvc

Apply and check the Deployment resource.

$ kubectl get pods -o wide
NAME                                   READY   STATUS    RESTARTS   AGE    IP              NODE        NOMINATED NODE   READINESS GATES
postgres-deployment-74fffd4c8b-98sf9   1/1     Running   0          6m7s   10.244.73.223   sfo-gwuie   <none>           <none>
postgres-deployment-74fffd4c8b-d6d5c   1/1     Running   0          6m7s   10.246.184.74   nyc-adljs   <none>           <none>
$

Create the Service

postgres-service.yaml
apiVersion: v1
kind: Service
metadata:
  name: postgres-service
spec:
  ports:
    - port: 5432
  selector:
    app: postgres-deployment

Apply and check the Service resource.

$ kubectl apply -f postgres-service.yaml
service/postgres-service created
$ kubectl get service
NAME               TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
ingress-upstream   ClusterIP   10.43.209.247   <none>        80/TCP     25h
kubernetes         ClusterIP   10.43.0.1       <none>        443/TCP    25h
postgres-service   ClusterIP   10.43.227.23    <none>        5432/TCP   6s
$

The Service gives you a hostname postgres-service that points to your deployment, one that you will use with the -h argument of psql in the next section. This hostname also insulates you from the fact that when the pod restarts it might come back with a different IP address.

Test It

We'll exec into the SFO pod, run psql, make a table, and do a query.

$ kubectl exec -it postgres-deployment-74fffd4c8b-98sf9 -- sh
postgres-deployment-74fffd4c8b-98sf9$ psql -h postgres-service
Password for user postgres: XXXXXXXXXXXXX
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=# CREATE TABLE PETS(
ID SERIAL PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL UNIQUE,
ANIMAL TEXT NOT NULL
);
CREATE TABLE

postgres=# INSERT INTO PETS (NAME,ANIMAL) VALUES('JED','CAT');
INSERT 0 1

postgres=# INSERT INTO PETS (NAME,ANIMAL) VALUES('BUCKLEY','DOG');
INSERT 0 1

postgres=# SELECT * FROM PETS;
 id |  name   | animal
----+---------+--------
  1 | JED     | CAT
  2 | BUCKLEY | DOG
(2 rows)

postgres=# quit
postgres-deployment-74fffd4c8b-98sf9$ exit

Now we'll delete the deployment but not the PVC, recreate the deployment, and then validate that the table is still present in the SFO location. This demonstrates that the PVC retained the data.

$ kubectl delete deploy postgres-deployment
deployment.apps "postgres-deployment" deleted

$ kubectl get pods -o wide
No resources found in default namespace.

$ kubectl apply -f postgres-deployment.yaml
deployment.apps/postgres-deployment created

$ kubectl get pods -o wide
NAME                                   READY   STATUS    RESTARTS   AGE   IP               NODE        NOMINATED NODE   READINESS GATES
postgres-deployment-74fffd4c8b-2fxsp   1/1     Running   0          18s   10.246.104.111   nyc-adljs   <none>           <none>
postgres-deployment-74fffd4c8b-8chhc   1/1     Running   0          18s   10.244.73.226    sfo-gwuie   <none>           <none>

$ kubectl exec -it postgres-deployment-74fffd4c8b-8chhc -- sh
postgres-deployment-74fffd4c8b-8chhc$ psql -h postgres-service
Password for user postgres:
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# SELECT * FROM PETS;
 id |  name   | animal
----+---------+--------
  1 | JED     | CAT
  2 | BUCKLEY | DOG
(2 rows)

postgres=# quit
postgres-deployment-74fffd4c8b-8chhc$ exit
$

And there you have it! Now you can access your Postgres database from within your CloudFlow project at host postgres-service, port 5432.

Create a Postgres Database using a PVC

Run in a Single Location​

Create the PVC​

Create the Deployment​

Create the Service​

Test It​

Run in a Single Location

Create the PVC

Create the Deployment

Create the Service

Test It