Cannot connect to remote postgreSQL database on Kubernetes cluster

You can try something like this:

kubectl exec -it $(kubectl get pods -n %YOURNAMESPACE% | grep postgres | cut -d " " -f1) -n %YOURNAMESPACE% -- bash -c "psql -U postgres -c 'SELECT current_database()'"

Or omit the namespace thing if you need it in current namespace:

kubectl exec -it $(kubectl get pods | grep postgres | cut -d " " -f1) -n -- bash -c "psql -U postgres -c 'SELECT current_database()'"

I have deployed your YAMLs and I was able to connect.

In --h you have to provide IP of node or host machine where PostgreSQL pod was deployed.

Test Case

I have tested this on my GKE cluster.

Based on your YAMLs and this tutorial. Mainly for ConfigMap and PV/PVC configuration.

  POSTGRES_DB: postgresdb  POSTGRES_USER: postgresadmin  POSTGRES_PASSWORD: admin123

Connection to PostgreSQL Database

• 1 - From SQL Pod

    $ kubectl get po    NAME                        READY   STATUS    RESTARTS   AGE    postgres-5586dc9864-pwpsn   1/1     Running   0          37m

You have to kubectl exec to PostgreSQL pod.

$ kubectl exec -ti postgres-5586dc9864-pwpsn -- bin/bashroot@postgres-5586dc9864-pwpsn:/#

Use psql command to connect database.

root@postgres-5586dc9864-pwpsn:/# psql -p 5432 -U postgresadmin -d postgresdbpsql (13.1 (Debian 13.1-1.pgdg100+1))Type "help" for help.postgresdb=#

• 2 - From cluster

Service Details:

$ kubectl get svcNAME         TYPE        CLUSTER-IP   EXTERNAL-IP   PORT(S)          AGEkubernetes   ClusterIP   10.28.0.1    <none>        443/TCP          60mpostgres     NodePort    10.28.14.2   <none>        5432:31431/TCP   45m

Pod Details:

$ kubectl get po -o wideNAME                        READY   STATUS    RESTARTS   AGE   IP          NODE                                       NOMINATED NODE   READINESS GATESpostgres-5586dc9864-pwpsn   1/1     Running   0          41m   10.24.1.6   gke-cluster-1-default-pool-8baf2b67-jjjh   <none>           <none>

Node details, where PostgreSQL pod was deployed.

$ kubectl get node -o wide | grep gke-cluster-1-default-pool-8baf2b67-jjjhgke-cluster-1-default-pool-8baf2b67-jjjh   Ready    <none>   56m   v1.16.15-gke.4300   10.154.15.222   35.197.210.241   Container-Optimized OS from Google   4.19.112+        docker://19.3.1

NodeIP address of node where PostgreSQL pod was deployed is 10.154.15.222.

Command:

$ kubectl exec -ti <podname> -- psql -h <Internal IP address of hosting node> -U postgresadmin --password -p <nodeport number from service> <database name>

Output:

$ kubectl exec -ti postgres-5586dc9864-pwpsn -- psql -h 10.154.15.222 -U postgresadmin --password -p 31431 postgresdbPassword:psql (13.1 (Debian 13.1-1.pgdg100+1))Type "help" for help.postgresdb=#