Accessing a Postgres Instance in Kubernetes

After you deploy a Postgres instance to Kubernetes, you can access databases either by executing Postgres utilities from within Kubernetes, or by using a locally-installed tool, such as psql, to access the Postgres instance running in Kubernetes.

Accessing a Pod with Kubectl

Use the kubectl tool to run utilities directly in a Postgres pod. This psql command connects to the postgres database as the postgres user, the defaults.

$ kubectl exec -it my-postgres-0 -- psql
psql (11.5-3.ubuntu1804.vmware)
Type "help" for help.

postgres=#

You can also simply execute a bash shell in the pod and then execute Postgres utilities as needed. For example:

$ kubectl exec -it my-postgres-0 -- /bin/bash
$ postgres@my-postgres-0:/$ createdb mydb
$ postgres@my-postgres-0:/$ psql mydb
$ psql (11.5-3.ubuntu1804.vmware)
Type "help" for help.

mydb=# create role user1 login;

Accessing Postgres with External Clients

If you have installed psql or another Postgres client application outside of Kubernetes (for example, on your local client machine), you can connect to a Postgres database on Kubernetes using Postgres connection parameters, passed as command-line options or in a connection string.

  1. These commands get the sample database name, database role, and password from a Kubernetes secret.

    $ dbname=$(kubectl get secrets my-postgres-db-secret -o jsonpath='{.data.dbname}' | base64 -D)
    $ username=$(kubectl get secrets my-postgres-db-secret -o jsonpath='{.data.username}' | base64 -D)
    $ password=$(kubectl get secrets my-postgres-db-secret -o jsonpath='{.data.password}' | base64 -D)
    
  2. For a TKGi or GKE deployment, these commands get the external host address and port from the Postgres load balancer.

    $ host=$(kubectl get service my-postgres -o jsonpath='{.status.loadBalancer.ingress[0].ip}')
    $ port=$(kubectl get service my-postgres -o jsonpath='{.spec.ports[0].port}')
    
  3. For Minikube deployments, the Postgres load balancer is not used. Get the external host address and port using these commands:

    $ host=$(minikube ip)
    $ port=$(kubectl get service my-postgres -o jsonpath='{.spec.ports[0].nodePort}')
    
  4. Execute the psql command, specifying the connection parameters.

    $ PGPASSWORD=$password psql -h $host -p $port -d $dbname -U $username
    psql (11.5)
    Type "help" for help.
    
    my-postgres=#