How to access pgsql from pgadmin on kubernetes

1/2/2019

I am playing with kubenetes. I have created a StatefulSet running postgresql. I have created a service with ClusterIP: None. I have launched a pod with pgadmin4. I can get to pgadmin from my browser. When I try to get to my pgsql server from pgadmin, it tells me that either the ip or the port are not accessible. The error message displays the ip address, so I know that it is resolving the right pod name.

This is MicroK8s on Ubuntu.

Here are my configs.

--- pomodoro-pgsql StatefulSet ---

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: pomodoro-pgsql
  namespace: pomodoro-services  
spec:
  selector:
    matchLabels:
      app: pomodoro-pgsql
      env: development
  serviceName: pomodoro-pgsql
  replicas: 1
  template:
    metadata:
      labels:
        app: pomodoro-pgsql
        env: development
    spec:
      containers:
      - name: pomodoro-pgsql
        image: localhost:32000/pomodoro-pgsql
        env:
        - name: POSTGRES_PASSWORD
          value: blahblah
        - name: POSTGRES_USER
          value: blahblah
        - name: POSTGRES_DB
          value: blahblah
        ports:
        - name: pgsql
          containerPort: 5432
        volumeMounts:
        - name: data
          mountPath: /var/lib/postgresql/data
  volumeClaimTemplates:
  - metadata:
      name: data
    spec:
      resources:
        requests:
          storage: 1Gi
      accessModes:
      - ReadWriteOnce

--- pomodoro-pgsql Headless Service ---

apiVersion: v1
kind: Service
metadata:
  name: pomodoro-pgsql
  namespace: pomodoro-services
spec:
  clusterIP: None
  selector:
    app: pomodoro-pgsql
    env: development
  ports:
  - name: pgsql
    port: 5432    

--- pgadmin4 Pod --

apiVersion: v1
kind: Pod
metadata:
  name: pomodoro-pgadmin
  namespace: pomodoro-services
  labels:
    env: development
spec:
  containers:
  - name: pomodoro-pgadmin
    image: localhost:32000/pomodoro-pgadmin
    env:
    - name: PGADMIN_DEFAULT_EMAIL
      value: blahblah
    - name: PGADMIN_DEFAULT_PASSWORD
      value: blahblah
    imagePullPolicy: IfNotPresent
  restartPolicy: Always 

--- pgadmin4 Service ---

apiVersion: v1
kind: Service
metadata:
  name: pomodoro-pgadmin
  namespace: pomodoro-services
spec:
  type: NodePort
  ports:
  - port: 5002
    targetPort: 80
  selector:
      app: pomodoro-pgadmin
      env: development

I am able to see the ip addresses through dig

microk8s.kubectl run `
    --namespace pomodoro-services `
    -it srvlookup `
    --image=tutum/dnsutils --rm `
    --restart=Never `
    -- dig SRV pomodoro-pgsql.pomodoro-services.svc.cluster.local

Here is the error from pgadmin. Note that the IP is correct for the pod.

Unable to connect to server:

could not connect to server: Operation timed out
Is the server running on host "pomodoro-pgsql-0.pomodoro-pgsql.pomodoro-
services.svc.cluster.local" (10.10.10.219) and accepting
TCP/IP connections on port 5432?

Here are the logs from the pgsql pod

2019-01-02 04:23:05.576 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2019-01-02 04:23:05.576 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2019-01-02 04:23:05.905 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-01-02 04:23:06.430 UTC [21] LOG:  database system was shut down at 2019-01-01 20:01:36 UTC
2019-01-02 04:23:06.630 UTC [1] LOG:  database system is ready to accept connections

As requested, here are the results from kubectl get services (IPs have been changed.)

NAME                 TYPE        CLUSTER-IP      EXTERNAL-IP   PORT(S)          AGE
pomodoro-pgadmin     NodePort    10.1.18.45      <none>        5002:30437/TCP   12h
pomodoro-pgsql       ClusterIP   None            <none>        5432/TCP         46h
pomodoro-ping-rapi   ClusterIP   10.1.18.36      <none>        8888/TCP         47h

[update 1/2/2019] I connected to another container in the cluster and tried to telnet, and then to psql into postgres. I could not connect with either program. I could run psql on the container running the postgresql server. My current theory is that the server has exposed 5432 locally, but it is filtered from other pods.

I have confirmed that /var/lib/postgresql/data/postgresql.conf contains the following:

listen_addresses = '*'

Using microk8s.kubctl port-forward pomodoro-pgsql-0 5432:5432 I was able to connect to 5432 through telnet.

_> telnet localhost 5432
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

[update 1/2/2019]

Results kubctl exec pomodoro-pgsql-0 -- nslookup pomodoro-pgsql

nslookup: can't resolve '(null)': Name does not resolve
nslookup: can't resolve 'pomodoro-pgsql': Try again
command terminated with exit code 1

Results kubctl exec pomodoro-pgsql-0 -- nslookup pomodoro-pgsql-0

Name:      pomodoro-pgsql-0
Address 1: 10.1.1.19 pomodoro-pgsql-0.pomodoro-pgsql.pomodoro-services.svc.cluster.local
nslookup: can't resolve '(null)': Name does not resolve

Note: IPs change when computer is restarted.

-- Phillip Scott Givens
kubernetes
microk8s

1 Answer

6/9/2019

The problem was a firewall rule for the computer running microk8s. I found out that this is documented on their web page where the docs tell us to do this:

sudo iptables -P FORWARD ACCEPT
sudo apt-get install iptables-persistent
-- Phillip Scott Givens
Source: StackOverflow