Trouble connecting to postgres from outside Kubernetes cluster

Question

I've launched a postgresql server in minikube, and I'm having difficulty connecting to it from outside the cluster.

---

Update

It turned out my cluster was suffering from unrelated problems, causing all sorts of broken behavior. I ended up nuking the whole cluster and vm and starting from scratch. Now I've got working. I changed the deployment to a statefulset, though I think it could work either way.

Setup and test:

kubectl --context=minikube create -f postgres-statefulset.yaml
kubectl --context=minikube create -f postgres-service.yaml

url=$(minikube service postgres --url --format={{.IP}}:{{.Port}})

psql --host=${url%:*} --port=${url#*:} --username=postgres --dbname=postgres \
     --command='SELECT refobjid FROM pg_depend LIMIT 1'
Password for user postgres:
 refobjid
----------
     1247

postgres-service.yaml

apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
    role: service
spec:
  selector:
    app: postgres
  type: NodePort
  ports:
    - name: postgres
      port: 5432
      targetPort: 5432
      protocol: TCP

postgres-statefulset.yaml

apiVersion: apps/v1beta2
kind: StatefulSet
metadata:
  name: postgres
  labels:
    app: postgres
    role: service
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
      role: service
  serviceName: postgres
  template:
    metadata:
      labels:
        app: postgres
        role: service
    spec:
      containers:
        - name: postgres
          image: postgres:9.6
          env:
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              value: postgres
            - name: POSTGRES_DB
              value: postgres
          ports:
            - containerPort: 5432
              name: postgres
              protocol: TCP

---

Original question

I created a deployment running one container (postgres-container) and a NodePort (postgres-service). I can connect to postgresql from within the pod itself:

$ kubectl --context=minikube exec -it postgres-deployment-7fbf655986-r49s2 \
    -- psql --port=5432 --username=postgres --dbname=postgres

But I can't connect through the service.

$ minikube service --url postgres-service
http://192.168.99.100:32254

$ psql --host=192.168.99.100 --port=32254 --username=postgres --dbname=postgres
psql: could not connect to server: Connection refused
        Is the server running on host "192.168.99.100" and accepting
        TCP/IP connections on port 32254?

I think postgres is correctly configured to accept remote TCP connections:

$ kubectl --context=minikube exec -it postgres-deployment-7fbf655986-r49s2 \
    -- tail /var/lib/postgresql/data/pg_hba.conf
host    all             all             127.0.0.1/32            trust
...
host all all all md5

$ kubectl --context=minikube exec -it postgres-deployment-7fbf655986-r49s2 \
    -- grep listen_addresses /var/lib/postgresql/data/postgresql.conf
listen_addresses = '*'

My service definition looks like:

apiVersion: v1
kind: Service
metadata:
  name: postgres-service
spec:
  selector:
    app: postgres-container
  type: NodePort
  ports:
    - port: 5432
      targetPort: 5432
      protocol: TCP

And the deployment is:

apiVersion: apps/v1beta2
kind: Deployment
metadata:
  name: postgres-deployment
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres-container
  template:
    metadata:
      labels:
        app: postgres-container
    spec:
      containers:
        - name: postgres-container
          image: postgres:9.6
          env:
            - name: POSTGRES_USER
              value: postgres
            - name: POSTGRES_PASSWORD
              value: postgres
            - name: POSTGRES_DB
              value: postgres
          ports:
            - containerPort: 5432

The resulting service configuration:

$ kubectl --context=minikube get service postgres-service -o yaml
apiVersion: v1
kind: Service
metadata:
  creationTimestamp: 2018-12-07T05:29:22Z
  name: postgres-service
  namespace: default
  resourceVersion: "194827"
  selfLink: /api/v1/namespaces/default/services/postgres-service
  uid: 0da6bc36-f9e1-11e8-84ea-080027a52f02
spec:
  clusterIP: 10.109.120.251
  externalTrafficPolicy: Cluster
  ports:
  - nodePort: 32254
    port: 5432
    protocol: TCP
    targetPort: 5432
  selector:
    app: postgres-container
  sessionAffinity: None
  type: NodePort
status:
  loadBalancer: {}

I can connect if I use port-forward, but I'd like to use the nodePort instead. What am I missing?

Answer 1

I just deployed postgres and exposed its service through NodePort and following is my pod and service.

[root@master postgres]# kubectl get pods
NAME                        READY     STATUS    RESTARTS   AGE
postgres-7ff9df5765-2mpsl   1/1       Running   0          1m

[root@master postgres]# kubectl get svc postgres
NAME         TYPE        CLUSTER-IP       EXTERNAL-IP   PORT(S)          AGE
postgres     NodePort    10.100.199.212   <none>        5432:31768/TCP   20s

And this is how connected to postgres though the nodeport:

[root@master postgres]# kubectl exec -it postgres-7ff9df5765-2mpsl --  psql -h 10.6.35.83 -U postgresadmin --password -p 31768 postgresdb
Password for user postgresadmin: 
psql (10.4 (Debian 10.4-2.pgdg90+1))
Type "help" for help.

postgresdb=# 

In above, 10.6.35.83 is my node/host IP (not pod IP or clusterIP) and port is the NodePort defined in service. The issue is you're not using the right IP to connect to the postgresql.