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. To get a bash shell use:
kubectl exec -it <primary-data-pod> -c pg-container -- bash
If you're accessing an HA instance, ensure you login into the primary pod. Use pg_autoctl show state in one of the data pods to identify which pod is the primary.
To list all the pods use:
kubectl get pods -l postgres-instance=<name-of-instance>,type=data,role=read-write
To connect directly to the database specified in pgConfig, as a user created in pgConfig, use the following command:
kubectl exec -it <primary-data-pod> -c <container-name> -- psql -d <database-name> -U <user-name>
For example:
kubectl exec -it pod/postgres-sample-0 -c pg-container -- psql -d postgres-sample -U pgappuser
where postgres-sample is the database name, and pgappuser the default app user.
psql (14.4 (VMware Postgres 14.4.0))
Type "help" for help.
Use the psql option conninfo to verify the database and user:
postgres-sample=> \conninfo
You are connected to database "postgres-sample" as user "pgappuser" via socket in "/tmp" at port "5432".
postgres-sample=>
You can use the database specified in pgConfig.dbname and the app user specified in spec.pgConfig.appUser for read-write operations. For eg:
postgres@postgres-sample-0:/$ psql -d postgres-sample -U pgappuser
psql (14.4 (VMware Postgres 14.4.0))
Type "help" for help.
postgres-sample=# create table foo(i int);
CREATE TABLE
postgres-sample=# insert into foo VALUES(1),(2);
INSERT 0 2
postgres-sample=# select * from foo;
i
---
1
2
(2 rows)
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 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}')
