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. For example:
kubectl exec -it postgres-sample-0 -- /bin/bash
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;
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 TKGI or GKE deployment, 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 Minikube deployments, 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}')
