CODEWITHSUNDEEP
postgresqlkubernetes
hotmeatballsoup
hotmeatballsoup

Reputation: 625

Running the Postgres CLI client from a Kubernetes jumpbox

I have setup a Postgres pod on my Kubernetes cluster, and I am trying to troubleshoot it a bit.

I would like to use the official Postgres image and deploy it to my Kubernetes cluster using kubectl. Given that my Postgres server connection details are:

host: mypostgres
port: 5432
username: postgres
password: 12345

And given that I think the command will be something like:

kubectl run -i --tty --rm debug --image=postgres --restart=Never -- sh

What do I need to do so that I can deploy this image to my cluster, connect to my Postgres server and start running SQL command against it (for troubleshooting purposes)?

Upvotes: 2

Views: 6572

Answers (1)

larsks
larsks

Reputation: 312263

If you're primarily interested in troubleshooting, then you're probably looking for the kubectl port-forward command, which will expose a container port on your local host. First, you'll need to deploy the Postgres pod; you haven't shown what your pod manifest looks like, so I'm going to assume a Deployment like this:

apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: postgres
  name: postgres
  namespace: sandbox
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
      - env:
        - name: POSTGRES_PASSWORD
          value: secret
        - name: POSTGRES_USER
          value: example
        - name: POSTGRES_DB
          value: example
        image: docker.io/postgres:13
        name: postgres
        ports:
        - containerPort: 5432
          name: postgres
          protocol: TCP
        volumeMounts:
        - mountPath: /var/lib/postgresql
          name: postgres-data
      strategy: Recreate
      volumes:
      - emptyDir: {}
        name: postgres-data

Once this is running, you can access postgres with the port-forward command like this:

kubectl -n sandbox port-forward deploy/postgres 5432:5432

This should result in:

Forwarding from 127.0.0.1:5432 -> 5432
Forwarding from [::1]:5432 -> 5432

And now we can connect to Postgres using psql and run queries against it:

$ psql -h localhost -U example example
psql (13.4)
Type "help" for help.

example=#

kubectl port-forward is only useful as a troubleshooting mechanism. If you were trying to access your postgres pod from another pod, you would create a Service and then use the service name as the hostname for your client connections.

Update

If your goal is to deploy a client container so that you can log into it and run psql, the easiest solution is just to kubectl rsh into the postgres container itself. Assuming you were using the Deployment shown earlier in this question, you could run:

kubectl rsh deploy/postgres

This would get you a shell prompt inside the postgres container. You can run psql and not have to worry about authentication:

$ kubectl rsh deploy/postgres
$ psql -U example example
psql (13.4 (Debian 13.4-1.pgdg100+1))
Type "help" for help.

example=#

If you want to start up a separate container, you can use the kubectl debug command:

kubectl debug deploy/postgres

This gets you a root prompt in a debug pod. If you know the ip address of the postgres pod, you can connect to it using psql. To get the address of the pod, run this on your local host:

$ kubectl get pod/postgres-6df4c549f-p2892 -o jsonpath='{.status.podIP}'
10.130.0.11

And then inside the debug container:

root@postgres-debug:/# psql -h 10.130.0.11 -U example example

In this case you would have to provide an appropriate password, because you are accessing postgres from "another machine", rather than running directly inside the postgres pod.

Note that in the above answer I've used the shortcut deploy/<deployment_name, which avoids having to know the name of the pod created by the Deployment. You can replace that with pod/<podname> in all cases.

Upvotes: 5

Related Questions