9_databases.adoc

Step 9: Databases

Postgres via Deployments

1. PersistentVolume

2. PersistentVolumeClaim

3. Deployment

4. Service

$ minikube docker-env
# or
$ minishift docker-env

$ eval $(minishift docker-env)

Pre-pull the docker image

$ docker pull postgres:10.5

Check to see if you have any preconfigured PVs

$ kubectl get pv --all-namespaces

Create the PV, PVC, Deployment and Service

$ kubectl create -f kubefiles/postgres-pv.yml
$ kubectl get pv/postgres-pv
NAME          CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS      CLAIM     STORAGECLASS   REASON    AGE
postgres-pv   2Gi        RWO            Retain           Available             mystorage                14s

$ kubectl create -f kubefiles/postgres-pvc.yml
$ kubectl get pv/postgres-pv
NAME          CAPACITY   ACCESS MODES   RECLAIM POLICY   STATUS    CLAIM               STORAGECLASS   REASON    AGE
postgres-pv   2Gi        RWO            Retain           Bound     demo/postgres-pvc   mystorage                49s

$ kubectl get pvc/postgres-pvc
NAME           STATUS    VOLUME        CAPACITY   ACCESS MODES   STORAGECLASS   AGE
postgres-pvc   Bound     postgres-pv   2Gi        RWO            mystorage      27s

$ kubectl create -f kubefiles/postgres-deployment.yml
$ kubectl get pods
NAME                       READY     STATUS    RESTARTS   AGE
postgres-bf856c974-9hdrp   1/1       Running   0          5s

$ kubectl create -f kubefiles/postgres-service.yml

Now, make the Postgres port 5432 available at localhost

$ kubectl port-forward <posgtres-pod> 5432:5432

Access with pgAdmin https://www.postgresql.org/ftp/pgadmin/pgadmin4/v3.4

Figure 1. pgAdmin Add Server

Figure 2. pgAdmin Add Server 2

Tools → Query Tool

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
ALTER ROLE myuser CREATEDB;
CREATE DATABASE mydb;
ALTER DATABASE mydb owner to "myuser"

Note: You can also run psql CLI from within the Postgres Pod

$ kubectl exec -it postgres-bf856c974-xqtf8 /bin/bash
# replacing postgres-bf856c974-xqtf8 with your pod id
$ psql --username "$POSTGRES_USER" --dbname "$POSTGRES_DB"

postgresdb-# \l

psql tips:

\l (list databases)
\c newDatabase (switch database)
\dt (list tables)
\q quit

Run the Spring Boot + Postgres app on localhost. This is using Hibernate’s ability to generate database schema from the object model

$ cd hellodata/boot_postgres

$ mvn spring-boot:run -Dspring-boot.run.arguments=--spring.config.location=src/main/resources/application-local.properties

There are 2 application.properties files, one for localhost testing and one for "production" when it runs inside of Kubernetes/OpenShift

Check pgAdmin

Figure 3. pgAdmin Tables

Add a test question by POST’ing the testquestion.json file

$ curl -X POST http://localhost:8080/questions -d @testquestion.json --header "Content-Type: application/json"

Query for the questions in the database via the REST API

$ curl http://localhost:8080/questions

Figure 4. pgAdmin Questions

Now, let’s run the Spring Boot app as a Pod

$ mvn clean package -DskipTests
$ docker build -t 9stepsawesome/mybootdata:v1 .
$ cd ../../
$ kubectl create -f kubefiles/mybootdata-deployment.yml
$ kubectl create -f kubefiles/mybootdata-service.yml
$ kubectl get service/mybootdata -o jsonpath="{.spec.ports[*].nodePort}"
$ QAHOSTPORT=$(minikube ip):$(kubectl get service/mybootdata -o jsonpath="{.spec.ports[*].nodePort}")
$ curl $QAHOSTPORT/questions
$ curl -X POST $QAHOSTPORT/questions -d @anotherquestion.json --header "Content-Type: application/json"

Figure 5. pgAdmin Questions

Figure 6. Browser Questions

Sharing the in-container data with the host MacOS or Windows

https://github.com/kubernetes/minikube/blob/master/docs/persistent_volumes.md

To share the in-VM directory with the host OS

minikube automatically shares /Users with the VM

minikube ssh
cd /Users

minishift does not automatically share a folder with the VM, so for equivalent functionality

minishift hostfolder add -t sshfs --source /Users --target /Users Users

Note: The Postgres image will not start by default on /Users/ due to permissions problems

Postgres via Operator

(under development)

https://crunchydata.github.io/postgres-operator/stable/installation/manual-installation/

https://github.com/CrunchyData/postgres-operator