Cannot connect to Postgres deployed to Kubernetes

1/8/2019

I am stuck trying to debug a connection issue with PostgreSQL deployed with minikube. I've tried creating the postgres-service with both type NodePort and LoadBalancer but I cannot seem to connect with psql from my localhost.

I've copied in all the config (apologies), as I'm sure there's probably just a silly mistake somewhere!

apiVersion: v1
kind: Secret
metadata:
  name: postgres-credentials
type: Opaque
data:
  user: YWRtaW4=
  password: YWRtaW4=
  database: YWRtaW4=
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: postgres-pvc
  labels:
    type: local
spec:
  accessModes:
    - ReadWriteMany
  resources:
    requests:
      storage: 2Gi
  volumeName: postgres-pv
---
apiVersion: v1
kind: PersistentVolume
metadata:
  name: postgres-pv
  labels:
    type: local
spec:
  capacity:
    storage: 2Gi
  storageClassName: standard
  accessModes:
    - ReadWriteMany
  hostPath:
    path: /data/postgres-pv-local
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      name: postgres-container
  template:
    metadata:
      labels:
        name: postgres-container
        tier: backend
    spec:
      containers:
        - name: postgres-container
          image: mdillon/postgis:10
          ports:
            - containerPort: 5432
          env:
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: user

            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: password

            - name: POSTGRES_DB
              valueFrom:
                secretKeyRef:
                  name: postgres-credentials
                  key: database

          volumeMounts:
            - name: postgres-volume-mount
              mountPath: /var/lib/postgresql/data
      volumes:
        - name: postgres-volume-mount
          persistentVolumeClaim:
            claimName: postgres-pvc
---
apiVersion: v1
kind: Service
metadata:
  name: postgres-service
spec:
  type: NodePort
  selector:
    app: postgres-container
  ports:
    - protocol: TCP
      port: 5432
      targetPort: 5432

Trying to connect on my localhost:

$ kubectl get services
NAME               TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
kubernetes         ClusterIP   10.96.0.1       <none>        443/TCP          30h
postgres-service   NodePort    10.105.42.187   <none>        5432:32252/TCP   7m32s

$ export PGPASSWORD=admin
$ psql -h localhost -U admin -p 32252 admin
psql: could not connect to server: Connection refused

I've also tried creating a Ubuntu deployment in the cluster, using kubectl run -i --tty --attach --image=ubuntu -- bash, installing psql and connecting on 5432/32252, localhost/postgres-service, etc... but that doesn't work either.

-- Greg Brown
kubernetes
postgresql

2 Answers

1/9/2019

The cause turned out to be a mismatch in label key in the service selector, app: postgres-container, and the deployment label, name: postgres-container.

Now I can connect, using the minikube ip for NodePort:

$ export PGPASSWORD=admin
$ psql -h 192.168.99.100 -U admin -p 30917 admin

or localhost when the service is type LoadBalancer.

-- Greg Brown
Source: StackOverflow

1/9/2019

Minikube run in a VM. In here, psql -h localhost -U admin -p 32252 admin you are trying to connect to postgres that is exposed in localhost. But it is not exposed in the localhost. Since you defined NodePort type service, you can access it using Node ip. So, instead of using localhost, try Minikube ip.

 $ minikube ip
-- nightfury1204
Source: StackOverflow