Deploying VMware Tanzu Data for K8S on GCE Part 2: Access Postgres

Photo by Daniel Cheung on Unsplash

This article is part 2 of Tanzu SQL Postgres. I demo how to access the instance through service and Kube-proxy to DBeaver on your desktop. The whole idea is putting some data into the pg-instance, and other deployments can consume it.

In Part1, I used “kubectl exec” to test the postgres connection. Does anyone really use that to run CRUD?

ysung@ysung-a01 postgres-for-kubernetes-v1.0.0 % k exec -it pg-instance-1-0 -- psql
psql (11.9 (VMware Postgres 11.9.3))
Type "help" for help.
postgres=# \q

Let me show you a “little” better approach first. I can use kubectl port-forward to proxy the pg-instance-1 service to my local desktop port, then use DBeaver to access the instance.

First, let’s make sure the service is running and kube_dns can resolve it. “kubectl get svc” shows pg-instance-1 service has port 5432/TCP.

ysung@ysung-a01 postgres-for-kubernetes-v1.0.0 % k get svc
NAME                                TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)    AGE
kubernetes                          ClusterIP   10.96.0.1       <none>        443/TCP    67m
pg-instance-1                       ClusterIP   10.97.198.100   <none>        5432/TCP   2m20s
pg-instance-1-agent                 ClusterIP   None            <none>        <none>     2m20s
postgres-operator-webhook-service   ClusterIP   10.103.143.54   <none>        443/TCP    4m33s

The following is a DNS_Utils I used to check the resolution.

ysung@ysung-a01 deployments % cat test_dns.yaml
apiVersion: v1
kind: Pod
metadata:
  name: dnsutils
  labels:
    run: dns
spec:
  containers:
  - name: dnsutils
    image: gcr.io/kubernetes-e2e-test-images/dnsutils:1.3
    command:
      - sleep
      - "3600"
    imagePullPolicy: IfNotPresent
  restartPolicy: Always
ysung@ysung-a01 deployments % k apply -f test_dns.yaml
pod/dnsutils created
ysung@ysung-a01 deployments % k exec dnsutils -- nslookup pg-instance-1
Server:  10.96.0.10
Address: 10.96.0.10#53
Name: pg-instance-1.default.svc.cluster.local
Address: 10.97.198.100

This nslookup confirms that I have my Tanzu SQL Postgres exposed as a service and accessed through the Cluster_IP. Now I can use port forward to proxy this service to my local desktop port (localhost).

ysung@ysung-a01 kubectl % k port-forward service/pg-instance-1 5432:5432
Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432

The default template of DBeaver Postgres connection setting is perfect (nothing to change).

Let’s load some data! Check this out https://www.postgresqltutorial.com/postgresql-sample-database/

After I restored the dvdrental from EDB, you can see my port-forward terminal traffic like this.

ysung@ysung-a01 kubectl % k port-forward service/pg-instance-1 5432:5432
Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432
Handling connection for 5432

And I have data restored to my Tanzu SQL Postgres instance. Next part, let’s deploy a Blockduster online store consuming this instance in K8s. Stay tuned.

ysung@ysung-a01 kubectl % k exec -it pg-instance-1-0 -- psql
psql (11.9 (VMware Postgres 11.9.3))
Type "help" for help.
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 dev1      | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 dverental | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(5 rows)
postgres=# \c dverental
You are now connected to database "dverental" as user "postgres".
dverental=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)
dverental=#