Connecting to PostgreSQL instance

Edit online

Tech preview The Postgres Operator automatically creates authentication credentials as secrets. To connect from an application, service instance users must get the access credentials for the PostgreSQL instance.

Before you begin

Ensure that the Postgres Operator is deployed and that a PostgreSQL cluster instance exists.
Important: In the following commands, my-postgres is an example cluster name. Replace my-postgres with your actual PostgreSQL cluster name.

Procedure

  1. Retrieve the connection information from the secret.

    The Postgres Operator stores connection credentials in Kubernetes secrets. You can retrieve the username, password, and connection URI.

    1. View the secret contents. 
      oc get secret my-postgres-superuser -n postgres-namespace -o yaml
    2. View the username. 
      oc get secret my-postgres-superuser -n postgres-namespace -o jsonpath='{.data.username}' | base64 -d
    3. View the password. 
      oc get secret my-postgres-superuser -n postgres-namespace -o jsonpath='{.data.password}' | base64 -d
    4. View the connection URI. 
      oc get secret my-postgres-superuser -n postgres-namespace -o jsonpath='{.data.uri}' | base64 -d
  2. Retrieve the TLS certificate by using one of these methods:
    • To retrieve the operator-managed server certificate, run: 
      oc get secret my-postgres-ca -n postgres-namespace -o jsonpath='{.data.ca\.crt}' | base64 -d
    • To retrieve the server certificate managed by cert-manager, run: 
      oc get secret <certificate_secret_name> -n postgres-namespace -o jsonpath='{.data.ca\.crt}' | base64 -d
  3. Connect to PostgreSQL from within the same cluster by using one of the following methods:
    • To connect from inside a pod, run: 
      oc exec -it my-postgres-1 -n postgres-namespace -- psql -U postgres
    • To connect from an application using the service name, run: 
      psql -h my-postgres-rw.postgres-namespace.svc.cluster.local -U postgres -d postgres
    • To connect from an application using the service name with the retrieved server certificate, run: 
      psql "host=my-postgres-rw.postgres-namespace.svc.cluster.local user=postgres  dbname=postgres sslmode=verify-full sslrootcert=<server_certificate_path>"
  4. Optional: Connect to PostgreSQL from an external host

    To connect to PostgreSQL from other managed clusters or external hosts, use MetalLB to assign an external IP address.

    Important: Before you begin, ensure that Enable External Access is set to true during operator deployment and that a VIP is allocated automatically.
    1. Check the assigned external IP address. 
      
oc get svc my-postgres-rw-lb -n postgres-namespace -o jsonpath='{.status.loadBalancer.ingress[0].ip}'
    2. Connect from other clusters using the external IP address. 
      psql -h <EXTERNAL_IP> -U postgres -d postgres

      Replace <EXTERNAL_IP> with the IP address from the previous substep.

Results

You are now connected to the PostgreSQL database. You can run SQL queries and manage the database.