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

Accessing a Pod with Kubectl

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

kubectl exec -it postgres-sample-0 -- psql
psql (11.13 (VMware Postgres 11.13.1))
Type "help" for help.

postgres=#

You can also simply execute a bash shell in the pod and then execute Postgres utilities as needed. If it's an HA instance, ensure you login into the primary pod by running pg_autoctl show state in one of the data pods, which will indicate which pod is the primary.

To list all the pods:

kubectl get pods -l postgres-instance=<name-of-instance>,type=data,role=read-write

To get a bash shell use:

kubectl exec -it <primary-data-pod> -c pg-container -- bash

where postgres-sample-0 is the example primary data pod.

postgres@postgres-sample-0:/$ createdb mydb
postgres@postgres-sample-0:/$ psql mydb
psql (11.13 (VMware Postgres 11.13.1))
Type "help" for help.

mydb=# create role user1 login;

For each Postgres 14 instance, exec into the primary pod

List all the pods:

kubectl get pods -l postgres-instance=<name-of-instance>,type=data,role=read-write
kubectl exec -it <primary-data-pod> -c pg-container -- bash

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 Tanzu Postgres database using Postgres connection parameters passed as command-line options, or in a connection string. For example:

PGPASSWORD=$password psql -h $host -p $port -d $dbname -U $username
psql (11.13 (VMware Postgres 11.13.1))
Type "help" for help.

postgres-sample=#

where $password, $host, $port, $dbname, $username are the connection parameters required to access the database. To acquire those, depending on your environment, you may use the following methods.

For the sample database name, database role, and password from a Kubernetes secret use:

dbname=$(kubectl get secret postgres-sample-db-secret -o go-template='{{.data.dbname | base64decode}}')
username=$(kubectl get secret postgres-sample-db-secret -o go-template='{{.data.username | base64decode}}')
password=$(kubectl get secret postgres-sample-db-secret -o go-template='{{.data.password | base64decode}}')

To get the application user for a Service Binding use a command similar to:

dbname=$(kubectl get secrets postgres-sample-app-user-db-secret -o jsonpath='{.data.database}' | base64 -D)
username=$(kubectl get secrets postgres-sample-app-user-db-secret -o jsonpath='{.data.username}' | base64 -D)
password=$(kubectl get secrets postgres-sample-app-user-db-secret -o jsonpath='{.data.password}' | base64 -D)

For a remote Kubernetes environment, get the external host address and port from the Postgres load balancer:

host=$(kubectl get service postgres-sample -o jsonpath='{.status.loadBalancer.ingress[0].ip}')
port=$(kubectl get service postgres-sample -o jsonpath='{.spec.ports[0].port}')

For a local Minikube Kubernetes environment, the Postgres load balancer is not used. Get the external host address and port using:

host=$(minikube ip)
port=$(kubectl get service postgres-sample -o jsonpath='{.spec.ports[0].nodePort}')
check-circle-line exclamation-circle-line close-line
Scroll to top icon