Accessing a Postgres Instance in Kubernetes

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 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=#
    

Accessing a High Availability Postgres cluster

In a High Availability (HA) Postgres architecture, when clients (for example libpq or psql) use LoadBalancer or ClusterIP to connect to the cluster, the requests go to all nodes, primary and mirrors. Because mirrors in an HA cluster are read-only, queries that attempt to commit transactions to a mirror node connection will generate errors.

Configure the clients to use only the primary node, in order to avoid commit errors. Use one of the following methods, depending on your client:

  • For programming languages apart from Java, which commonly use libq as an underlying engine to connect to Postgres, use a request similar to:

    postgres://[loadbalancer IP]:5432/db?target_session_attrs=read-write
    

    where the connection parameter target_session_attrs=read-write requests that the connection be directed to a host that allows read-write access. Refer to Postgres Database Connection Control Functions.

  • For JDBC client interfaces, use a connection string similar to:

    jdbc://[loadbalancer IP]:5432/db?targetServerType=primary
    

    where the JDBC connection parameter targetServertype=primary directs the clients to the primary server in an HA configuration. See JDBC connection parameter.

  • Alternative option:

    Users who cannot modify their application connection strings may set the environment variable PGTARGETSESSIONATTRS=read-write, which sets the server type for the connection. Refer to Postgres Environment Variables.

Note: Any clients or applications that attempt to connect to an HA Postgres cluster must implement a “connection retry” mechanism, which issues retries until there is a valid response from the correct server type. The “connection retry” mechanism must be provided by the client application.