Trouble connecting to postgres from outside Kubernetes cluster

12/7/2018

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?

-- ivan
kubernetes
postgresql

1 Answer

12/8/2018

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.

-- Prafull Ladha
Source: StackOverflow