Deploying VMware Tanzu Data for K8S on GCE Part 2: Access Postgres
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#53Name: 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=#